I have backup of oracle database size is almost 40 GB.This 40GB database backup contains lots of data that I don't care about. I just wanted to restore DB objects like tables structure, procedures, functions, triggers etc. limitation: I have only Oracle 11G Express Edition(Free) which has limitation of only 10 GB.

is there any way to restore db objects from database backups?

  • So you would like to have the DB structure, but no data, right? – ppeterka Aug 29 '16 at 8:44

In case of expdp/impdp backup


This will import only the DB objects, but no data. Should you require some data, you can get them for example on table, tablespace, or schema level:

impdp ... CONTENT=DATA_ONLY TABLES=my_table,my_table_2...
impdp ... CONTENT=DATA_ONLY TABLESPACES=tablespace_1,tablespace_2...
impdp ... CONTENT=DATA_ONLY SCHEMAS=schema_1,schema_2

In case of exp/imp backup

You can try partially importing the tables or tablespaces (syntax same as with impdp):

imp ... TABLES=(jones.accts, scott.emp, scott.dept)
imp ... TABLESPACES=tablespace_1,tablespace_2...

In case of rman backup

From this answer on partial restore from RMAN backup:

RMAN is a physical backup - it copies files - and not surprisingly is restricted to physical restoration. As a consequence, its supported granularities are: block, file, tablespace and database.

So if you are lucky, and can get by with restoring a tablespace, it might work if there are different tablespaces in the backup and the one you need fits into the 10GB limit.

Using Oracle 11g EE

If you have access to a full Oracle 11g (beware, the development license prohibits using it for internal data processing), just import the full DB, and do a selective export if none of the above is successful.

| improve this answer | |

Try importing only structure with metadata_only

impdp scott/tiger DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp CONTENT=METADATA_ONLY
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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