0

I'm facing a new problem about importing data on Oracle.

I've got a dump file of around 40GB, and I'm trying to import this to my Oracle Database 10g Release 10.2.0.4.0.

On our system we have 3 tablespaces (Datalob/Data and Index), one of our Customer is using only one tablespace (MIG).

if I try to import the data (from customer), I get the error ORA-01652 unable to extend segment by ... because i don't have enough space. too bad, I can't make the tablespace bigger/ or is not allowed.

is there a way to import the tablespace for Datalob(all lob fields) to the tablespace DATALOB, all index to INDEX and normal data to DATA?

like Remap_Tablespace=MIG(LOB):DATALOB remap_tablespcae=MIG(IDX):INDEX

domiSchenk
  • 880
  • 4
  • 23
  • 41
  • @Mat If I understand the OP correctly, it's the other way round - the dumpfile contains only one tablespace, and he wants to split it among three tablespaces. – Frank Schmitt Dec 20 '12 at 16:37
  • @FrankSchmitt yes you are right – domiSchenk Dec 21 '12 at 07:18
  • I think what you're implicitly asking is for a "pure" datapump solution. The answers below are workarounds in my opinion, not solutions. I've done my research on the web, and it seems to me that this is a very uncommon request (which surprises me). There are many descriptions of how to remap tablespaces, but none go the next step to ask your question: how to split tables from a single TS into many TSes. For me, the simple answer is that it can't be done. And to the extent that I can use a work-around, I find it easier to use the standard `alter table/index` command than the solutions below. – cartbeforehorse Mar 12 '13 at 14:12

2 Answers2

2

I'd use a sequential approach - not very elegant, but it should work:

  • import only the metadata (into your "table" tablespace)
  • move the LOB segments to the LOB tablespace
  • move the indices to the index tablespace
  • import the contents
Frank Schmitt
  • 30,195
  • 12
  • 73
  • 107
  • Similar method is to extract the metadata to a SQL script, update the tablespaces in there, run the script then import. – Mat Dec 20 '12 at 16:45
1

Pre-create the table using the tablespaces you want and use TABLE_EXISTS_ACTION=APPEND.

RMAN Express
  • 498
  • 3
  • 14