Background
I am trying to import a huge Oracle database dump whose size is larger than 40GB to my Oracle Express environment. As you know, Oracle Express does allow only up to database size of 11GB. Therefore, I can't "impdp" the entire bulk of this dump file to my Oracle XE. I need to selectively import only some tables which won't exceed the XE limitation.
Preparation
I created tablespaces required for the import which point to a directory DIR on my harddrive as follows:
Directory object {DIR} created as /db/
Tablespace ABC is created at /db/abc.dbf allocated a size of 6GB
I created the tablespace with only size applied, no other additional arguments.
Hint*: Tablespace name ABC is required by the dump file for import.
I successfully imported the entire database structure
First of all, I ran this command below to create table structures in my target XE environment without copying any data. Just to make sure my dump file contains all tables I want:
$> impdp system/pwd directory=DIR dumpfile=FOO.dump
content=METADATA_ONLY schemas=FOO;
The command above is executed correctly. All table structures are created in my XE environment under schema FOO.
Next, I want to start importing table data (BUT FAILED)
Then I start off with the first table to import, say table abc. I ran this command to import only data to my pre-created table.
$> impdp FOO/pwd directory=DIR dumpfile=FOO.dump
content=DATA_ONLY tables=abc;
You may notice I switch from the user system to FOO. I'm aware of this. Due to the fact that my tables are created under schema FOO, so I use FOO to import it to the existing tables under this schema.
However, this fails. I've got errors as shown below:
Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Master table "FOO"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "FOO"."SYS_IMPORT_TABLE_01": FOO/******** directory=DIR dumpfile=DUMP.dump logfile=import_table.log content=data_only tables=item_keyword_tab
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object "FOO"."ABC" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-01653: unable to extend table FOO.ABC by 8 in tablespace ABC
Job "ABC"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 02:03:33
The error message seems to mention "the tablespace cannot be extended by only 8 MB". I can guarantee I have created the sufficient tablespace "ABC" which is as large as 6 GB and it won't exceed the XE limitation.
Can anybody please help me figure this out? How should I do?
My assumptions are:
The schema "FOO" is lack of privilege so it cannot extend the table while importing. I explored the documentation but "extending the tablespace" is not one of the adjustable privilege of users.
Extending the tablespace by 8 MB is very small in size. I don't think this is a true reason why it fails. There might be other underlying cause.