Oracle Express – How to Restore a 10.91G Table?

oraclerestore

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

Best Answer

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.

Related Question