3

I've received a .dmp file of an enterprise database, but currently only have access to a free version of Oracle. I would ultimately like to import the data to PostgreSQL. But currently am wondering if there's a way to restore the largest table (10.91G) in chunks if need be, so I can export its contents to csvs to import into PostgreSQL. Currently if I try to selectively restore the 10.91GB table with 

impdp system/pw tables=TEST.BIG_TABLE directory=test_data dumpfile=test_data.dmp log=log.log;

I get 

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
ORA-39002: invalid operation
ORA-31694: master table "SYSTEM"."SYS_IMPORT_TABLE_01" failed to load/unload
ORA-02354: error in exporting/importing data
ORA-39776: fatal Direct Path API error loading table "SYSTEM"."SYS_IMPORT_TABLE_
01"
ORA-12953: The request exceeds the maximum allowed database size of 11 GB

The largest Tablespace I've been able to create so far was of SIZE 10500M, the command: CREATE TABLESPACE test DATAFILE 'test.dbf' SIZE 10600M ONLINE; produced error ORA-12953: The request exceeds the maximum allowed database size of 11 GB

The results of select tablespace_name, sum(bytes) from dba_data_files group by tablespace_name are:

tablespace | sum(bytes)/1000000
-----------+-------------------
SYSAUX     | 692.06016
UNDOTBS1   | 398.45888
USERS      | 104.8576
SYSTEM     | 524.288
TEST       | 11010.048
Improve this question
1
  • I think the 11GB limit includes the preinstalled system data. Oracle XE will never allow you to import this much data.
    – Andrew Brennan
    Commented Oct 17, 2016 at 10:31

2 Answers 2

Reset to default
3

The space a table takes up and the space it needs can differ.

I'd recommend using impdp with the sqlfile option to put the create table script into a file. Edit that to try to maximise the use of space (set pctfree to 0). Remove any primary key or unique constraint, as any index will eat into your available storage.

The INITIAL will be the amount of space initially allocated. Start with whatever is in the sqlfile output and nudge the value down until the create statement succeeds. Then use impdp with TABLE_EXISTS_ACTION=TRUNCATE so it will insert the data into the already created table.

Cross your fingers that there's enough room. If not, you can look at applying a WHERE clause as part of the impdp and split the data into two.

Improve this answer
2
  • Clarifying question: What do I do with the sqlfile.sql? After your suggested modifications do I run that as a query in SQL Developer? (I think yes, just wanted it noted for anyone else coming along)
    – raphael
    Commented Oct 19, 2016 at 15:15
  • Finally got it in, had to also include a WHERE clause to split the data in half.
    – raphael
    Commented Oct 19, 2016 at 18:03
1

This is important

ORA-12953: The request exceeds the maximum allowed database size of 11 GB

Maybe if you try to use "query" in impdp you can import less data...

example:

impdp bert/bert directory=data_pump_dir dumpfile=all_of_movies.dmp schemas=movies query=\"where last_mod_date is not null and last_mod_date > SYSDATE-90\"
Improve this answer
1
  • I've updated the error message I got, but this is a good point about how to chunk the import. First to figure out the extents of a particular column I could filter on...
    – raphael
    Commented Oct 19, 2016 at 15:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.