Home » RDBMS Server » Backup & Recovery » How to get all table names from .dmp file
How to get all table names from .dmp file [message #268495] Tue, 18 September 2007 22:35 Go to next message
vinodhere
Messages: 9
Registered: August 2007
Location: Mumbai
Junior Member
Please tell us how to get all table names from .dmp file. So that I can write only those tables in import statment.
Is there any way to get all the tables from .dmp file?
Re: How to get all table names from .dmp file [message #268497 is a reply to message #268495] Tue, 18 September 2007 22:48 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

From .dmp! No. It's a binary one.

You can have a try by" cat filename "to see whether name appears there or not. Better check Export log file if you have.
Re: How to get all table names from .dmp file [message #268501 is a reply to message #268495] Tue, 18 September 2007 23:32 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Is there any way to get all the tables from .dmp file?
I'd do the following:
strings unknown.dmp | grep "CREATE TABLE "
& the output will get you close to what you want
Re: How to get all table names from .dmp file [message #268518 is a reply to message #268495] Tue, 18 September 2007 23:59 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Who knows what his .dmp file holds?
Here is the test.You can have get if you make a dump by exp/imp. But if you have dump file(export by expdp/impdp) then you can't have it.
SQL> host expdp system/arju directory=dexport 
dumpfile=testforsearch.dmp schemas=arju

Export: Release 10.2.0.1.0 - Production on Wednesday, 19 September, 2007 0:41:32

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** 
directory=dexport dumpfile=testforsearch.dmp schemas=arju
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "ARJU"."DEPT1"                              5.601 KB       2 rows
. . exported "ARJU"."EXCEPTIONS"                         5.906 KB       2 rows
. . exported "ARJU"."TEST1"                              4.953 KB       7 rows
. . exported "ARJU"."TEST_EX"                            4.929 KB       1 rows
. . exported "ARJU"."EMP"                                    0 KB       0 rows
. . exported "ARJU"."TEST"                                   0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /backup/testforsearch.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 00:43:50


SQL> !strings /backup/testforsearch.dmp |grep "CREATE TABLE"

SQL>

[edit:fix linesize]

[Updated on: Wed, 19 September 2007 00:09]

Report message to a moderator

Re: How to get all table names from .dmp file [message #268520 is a reply to message #268495] Wed, 19 September 2007 00:06 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

By the way my first solution also work if you have .dmp file by exp/imp.So @OP, have a try.

SQL> host exp arju/arju file=/backup/testfordump.dmp

Export: Release 10.2.0.1.0 - Production on Wed Sep 19 00:50:04 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user ARJU
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user ARJU
About to export ARJU's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export ARJU's tables via Conventional Path ...
. . exporting table                ADMIN_WORK_AREA
. . exporting table                          DEPT1          2 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                            EMP          0 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                     EXCEPTIONS          2 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                    GLOBAL_TEST
. . exporting table                           TEST          0 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                          TEST1          7 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                        TEST_EX          1 rows exported
EXP-00091: Exporting questionable statistics.
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.

SQL> !cat  /backup/testfordump.dmp |grep "CREATE TABLE"
CREATE TABLE "DEPT1" ("DEPTNO" NUMBER(3, 0), "DNAME" 
VARCHAR2(15), "LOC" VARCHAR2(25))  PCTFREE 10 PCTUSED 
40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 
FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" 
LOGGING NOCOMPRESS

CREATE TABLE "EMP" ("EMPNO" NUMBER(5, 0), "ENAME" VARCHAR2(15) 
NOT NULL ENABLE, "JOB" VARCHAR2(10), "MGR" NUMBER(5, 0), 
"HIREDATE" DATE, "SAL" NUMBER(7, 2), "COMM" NUMBER(7, 2), 
"DEPTNO" NUMBER(3, 0) NOT NULL ENABLE)  PCTFREE 10 PCTUSED 40

 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 
FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" 
LOGGING NOCOMPRESS
CREATE TABLE "EXCEPTIONS" ("ROW_ID" ROWID, "OWNER" VARCHAR2(30),
 "TABLE_NAME" VARCHAR2(30), "CONSTRAINT" VARCHAR2(30))  PCTFREE

 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE 
"USERS" LOGGING NOCOMPRESS

CREATE TABLE "TEST" ("COL" VARCHAR2(10))  PCTFREE 10 PCTUSED 40 

INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 
FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS"

 LOGGING NOCOMPRESS
CREATE TABLE "TEST1" ("A" VARCHAR2(10))  PCTFREE 10 PCTUSED 40
 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 

FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" 

LOGGING NOCOMPRESS
CREATE TABLE "TEST_EX" ("A" VARCHAR2(10))  PCTFREE 10 
PCTUSED 40 
INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 
FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" 
LOGGING NOCOMPRESS


[edit:fix linesize]

[Updated on: Wed, 19 September 2007 00:08]

Report message to a moderator

Re: How to get all table names from .dmp file [message #268523 is a reply to message #268495] Wed, 19 September 2007 00:12 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Arju,

Thank you for the update.
My test was against a file made with "exp" & I saw the "CREATE TABLE ".
Re: How to get all table names from .dmp file [message #268552 is a reply to message #268495] Wed, 19 September 2007 01:29 Go to previous message
vinodhere
Messages: 9
Registered: August 2007
Location: Mumbai
Junior Member
Thanks All..
Previous Topic: ORA-00209
Next Topic: preparing test server using production server RMAN hot backup
Goto Forum:
  


Current Time: Mon May 20 16:39:51 CDT 2024