Find tablespaces used within datapump dump file

Question

Given an Oracle datapump file is it possible to find/retrieve the used tablespaces without accessing the original/source schema?

I tried to use the strings Linux command but I'm unable to find pattern indicating a tablespace.

Any idea?

Cowslip

Answer 1

You can use sqlfile option to dump DDL statements to a file.

impdp directory=expdir dumpfile=myexp.dmp sqlfile=myddl.sql

Myles

Thanks, I suppose there are not "direct ways" to get this information without passing through a sqlfile. Anyway, combining the output of your command with the following bash commands I was able to get a unique list...maybe someone will find it useful grep TABLESPACE outputfile.sql | sort | uniq | awk -F" " '{print $2}' | uniq – Cowslip

I don't think so. The old imp tool files contained DDL and DML tatements that you could read using unix strings command. The new expdp tool writes files that are more like backup/restore technology, a binary format. – Myles

I did this: cat dumpfile_sql.txt|egrep 'DEFAULT TABLESPACE'|cut -d\" -f2 to get the TS and cat data_pump_dir/dumpfile_sql.txt|egrep 'CREATE USER '|cut -d\" -f2 to get the user – Insubordinate

Answer 2

It's similar to get schema names -

strings myexp.dmp | grep TS_NAME | sed -e 's/.*<TS_NAME>\([^<]*\)<\/TS_NAME>.*/\1/g' | sort -u

Singlebreasted

Answer 3

So the export will be inside the myexp.dmp file, but sometimes it is helpful to add in that same line the following log: myexp_log_file.log; this will provide the entire scope of what the export/import did.

Ejectment