Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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 May 18 '12 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. –  Szilard Barany May 18 '12 at 4:04
    
which user do you use to run the export? What is the default tablespace of this user? –  miracle173 May 18 '12 at 6:35
    
@szilard yes the entire schema. Its for moving the db to a 11g XE. –  zapping May 18 '12 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 May 18 '12 at 10:52

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
    
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 May 20 '12 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)?

share|improve this answer
    
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 May 18 '12 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 May 18 '12 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). –  Szilard Barany May 20 '12 at 12:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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