CODEWITHSUNDEEP
oracle-databaseoracle10g
Kerby82
Kerby82

Reputation: 5146

List the contents of Oracle export dump file

I have an oracle full dump. I know I have to imp the dump with the fromuser touser clause. However I don't know the names of the schemas included in the dump.

how can I open the dump created with Oracle 10g just to check and analyze the content?

Upvotes: 12

Views: 61233

Answers (4)

Guest Mazen
Guest Mazen

Reputation: 1

If you looking for a list of tables I found this poor solution, I don't know if it helps :

IMP  user/pass@db SHOW=Y FILE='C:\2022\markit.dmp' FULL=y INDEXFILE='C:\2022\markit.dmp.idx.log'

the output I got

Upvotes: -1

stw2019
stw2019

Reputation: 21

if you are working in a UNIX environment, you may use command strings to show readable information from the dump files.

strings test.dmp|grep CREATE.*TABLE

Upvotes: 1

shinobi92
shinobi92

Reputation: 127

imp SCOTT/tiger show=Y fiLE=IMPORT_FILE.DMP log=**logfile.log** fromuser=scott touser=scott

if you are only interested in the name of object names and schema this is good for list of objects not the contents inside table.

once your command is successfully finished, use your favorite text editor like notepad /vim to open the log file generated by imp command by adding parameter LOG=logfile.log. in this file imp command will output all the actions it will perform as real case scenario. now search for the text "importing " on each location you will get SCHEMA NAMES followed by the object_names contained in that schema.

followed by commands to create/alter the objects as they were in original database.
after that the data insertions will be simulated you may get idea how many records in each schema.

but it won't list them.

SAMPLE import file for importing just one table EMP

---------start output of imp command -------- 

Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

**. importing SCOTT's objects into SCOTT**  
 "ALTER SESSION SET CURRENT_SCHEMA= "SCOTT""  
 "CREATE TABLE "EMP" ("EMPNO" NUMBER(4, 0), "ENAME" VARCHAR2(10), "JOB" VARCH"  
 "AR2(9), "MGR" NUMBER(4, 0), "HIREDATE" DATE, "SAL" NUMBER(7, 2), "COMM" NUM"  
 "BER(7, 2), "DEPTNO" NUMBER(2, 0))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRAN"  
 "S 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAU"  
 "LT) TABLESPACE "USERS" LOGGING NOCOMPRESS"  
. . skipping table "EMP"                                

 "CREATE UNIQUE INDEX "PK_EMP" ON "EMP" ("EMPNO" )  PCTFREE 10 INITRANS 2 MAX"  
 "TRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL D"  
 "EFAULT) TABLESPACE "USERS" LOGGING"  
 "ALTER SESSION SET CURRENT_SCHEMA= "SCOTT""  
 "ALTER TABLE "EMP" ADD  CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") USING INDE"  
 "X PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREE"  
 "LIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING ENABLE "  

 "ALTER TABLE "EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFEREN"  
 "CES "DEPT" ("DEPTNO") ENABLE NOVALIDATE"  
 "ALTER TABLE "EMP" ENABLE CONSTRAINT "FK_DEPTNO""  

Import terminated successfully without warnings.

---------end of out of imp command ---------

Look here

This tool claims and offers you to take a dump file and put the data into Excel/Access/CSV formated file. which maybe what you are looking for.

it show whats inside the tables with out import to real database

just in case someone is looking to find which dump file contains the data needed.

instead of going through all the old dumps you should keep a logfile with same name of each DUMPFILE suffix appended with its date and time.
like FULL_DB_DUMP_15012014_1240pm.DMP

so that you just open the relevant log file to get the idea which files contain what schema of your need or interest.

[2]: ON a second thought: if your dump file is upto 2GB and you can identify ORACLE SQL.
Use a hex editor like HxD, Hexor, HexEdit in readonly mode to open the dump file. Once opened you can browse the contents of dump file in binary/ascii form. this may be a crude method but it will give you a partial view of schema and objects names DDL with some contents of views/triggers/Functions/Procedures etc. Although, you will not be able to read & understand the whole contents. but it servers the purpose. maybe relatively fast.

Upvotes: 2

Sathyajith Bhat
Sathyajith Bhat

Reputation: 21851

Just use the SHOW=Y parameter

IMP SCOTT/TIGER SHOW=Y FILE=import_file.dmp

Upvotes: 18

Related Questions