Restoring 40 GB Oracle backup to get db objects into Oracle 11g XE

oracleoracle-xerestore

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?

Best Answer

In case of expdp/impdp backup

impdp ... CONTENT=METADATA_ONLY

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.

Related Question