5

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?

Andrea de Palo
  • 205
  • 1
  • 3
  • 5

3 Answers3

13

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

impdp directory=expdir dumpfile=myexp.dmp sqlfile=myddl.sql
Rob van Laarhoven
  • 8,737
  • 2
  • 31
  • 49
  • 2
    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` – Andrea de Palo Oct 30 '12 at 10:38
  • 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. – Rob van Laarhoven Oct 30 '12 at 13:27
  • 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 – Jim Zucker Aug 10 '19 at 23:58
3

It's similar to get schema names -

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

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.

JosueXIX
  • 1
  • 2