0

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.

Community
  • 1
  • 1
TaoPR
  • 5,932
  • 3
  • 25
  • 35
  • These tables should be assigned to the right tablespaces upon importing I believe, based on the log it created the tables in the correct tablespaces I created. I checked `user_ts_quotas` and see the *max_size* is set to -1 while the *size* looks okay. Does this mean any thing? – TaoPR Jun 18 '15 at 09:59
  • BYTES are all non-negative values and to me, they seem to be okay. MAXBYTES are all -1 for FOO. I guess this is unlimited quota because I ran altering this account with unlimited quota given, but no fields in this `user_ts_quotas` changes. – TaoPR Jun 18 '15 at 10:14
  • I'm thinking of running `impdp` (data only) under `system` account to cope with privilege issues. But currently I can't because I will need to assign "schemas" to "FOO" which `impdp` won't allow me to supply this parameter. I have no idea why. – TaoPR Jun 18 '15 at 10:17
  • Extending by 8 doesn't mean it thinks it needs 8MB for the entire table, it means it's filled the available/allowed space and can't get another 8 bytes. Can you add the tablespace and data file definitions? How big is ABC in the source database, and how much space is currently used in total in XE? – Alex Poole Jun 18 '15 at 10:28
  • How big is the tablespace ABC currently in your XE database? –  Jun 18 '15 at 10:41
  • I have added the definitions of the tablespace ABC I used in the question. I have no idea how to check how much space occupied in total, how can i examine? – TaoPR Jun 18 '15 at 10:41
  • Ahh, found it. The total size occupied by my tablespace ABC is 5115.875 MB in total. – TaoPR Jun 18 '15 at 10:51
  • I suggest you run impdp with the SQLFILE option to get the DDL. For each index, table creation DDL you should see an accompanying storage clause which will show the space each table will require. You should then be able to size your tablespace(s) accordingly prior to the import. The other option would be to set all tablespace to autoextend, and keep an eye on the disk space usage whilst the import runs. The first way allows you to better plan/predict/control the outcome, the latter is probably easier. I don't use XE so I do not know what limitations are present. – TenG Jun 18 '15 at 11:49
  • 1
    Do you need to use Express Edition? The Oracle licenses are pretty liberal if you're just [developing](http://www.oracle.com/technetwork/indexes/downloads/index.html): "All software downloads are free, and most come with a Developer License that allows you to use full versions of the products at no charge while developing and prototyping your applications, or for strictly self-educational purposes." Most people who use XE probably shouldn't be. – Jon Heller Jun 18 '15 at 19:04
  • **UPDATED** I have just found a way to cope with this issue. What I've done were: `1` Assign unlimited tablespace quota to the user FOO. `2` Alter the datafile which is assigned to the tablespace to "autoextend on" and "unlimited maxsize". However, the limitation of 11GB in size of XE now breaks me from carrying on. – TaoPR Jun 19 '15 at 01:05
  • @JonHeller Seems like I cannot stick with XE for this development anymore. The limitation is too small to do anything with my tables and I don't think spending too much time fixing this is a good idea. I've just decided to switch to higher edition then. – TaoPR Jun 19 '15 at 01:07

0 Answers0