The oracle 10g DB that is being currently used has exceeded the limit of 4GB. For the application to continue the db needs to be upgraded to 11g. But the upgrade doc requires a data pump export to be done from 10g and then data pump import it to 11g.

But the concern is the 10g exceeding its limit of 4GB and hence will not allow a data pump export to be done. Is there any alternate option to move the data to 11g other than data pump export and import.

Have tried the export with expdp but i get an error saying the database has exceeded th 4gb limit.

  • i tried to take a backup using sql developer. But sql developer by default shows views also in its data export section. Is it necessary to take data backup of views? As far as i know views don't store data physically at all.
    – zapping
    Commented May 18, 2012 at 3:37
  • I guess it is not only the data you want to migrate but complete schema(s), including views, sequences, stored procedures, etc. Commented May 18, 2012 at 4:04
  • which user do you use to run the export? What is the default tablespace of this user?
    – miracle173
    Commented May 18, 2012 at 6:35
  • @szilard yes the entire schema. Its for moving the db to a 11g XE.
    – zapping
    Commented May 18, 2012 at 10:51
  • @miracle173 ran it with the system account and also with the schema user account. but with both says the db exceeds max limit of 4GB
    – zapping
    Commented May 18, 2012 at 10:52

2 Answers 2


I would try the following:

  1. Install an oracle 10g database software that I download from the oracle software download page (i have the appropriate license)

  2. create an empty database with this software

  3. using the exp-tool (classical export, not datapump) to make an export from the database

  4. import the dumpfile (with the classical imp tool) in the empty 10g database I created

  5. now i do the export from this database and import this in the 11g database

step 3. is possible because the classical export does not create any table in the database in contrast to the datapump export. so i think i will not hit the 4g LIMIT ERROR

step 5 is possible because now the expdp is not constrained by any 4G limit.

it is not supported to import in step 4 into an 11g database but one can try if this is possible.

  • Thank you. Worked with the original/classical import/export (imp/exp). Did a exp for the schema from the 10g XE. And was able to import the dump file using imp to 11g XE. With a procedure was able to compare the no. of objects both the DBs and no. of rows in the tables and no. of rows returned by the views. While doing the imp to 11g XE there were compilation warnings. But using developer did a recompile of entire schema and the procedures got compiled without issues.
    – zapping
    Commented May 20, 2012 at 12:58

Did you try to export the whole database? Can you try instead exporting it in parts, say each schema independently? Or maybe (if individual schema is huge with big tables) export partial schemas (big tables one by one, the rest in a final turn)?

  • There are 2-3 tables with over 2 million rows. Will try but that will mean importing triggers, views, procedures also the custom way. since they will compile while importing?
    – zapping
    Commented May 18, 2012 at 10:55
  • Do you think i will have to drop the tables after importing. That could sometime cause issues with existing procedures or could run triggers? or to the views used
    – zapping
    Commented May 18, 2012 at 10:57
  • I was thinking about the best way. Probably I would first export everything, i.e. all objects but not the data. Then I would export tables (one by one) and import their data one by one into the previously imported empty tables (TABLE_EXISTS_ACTION=APPEND). Commented May 20, 2012 at 12:43

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.