How to get all table names from .dmp file [message #268495] |
Tue, 18 September 2007 22:35 |
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 #268518 is a reply to message #268495] |
Tue, 18 September 2007 23:59 |
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 |
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
|
|
|
|
|