OraFAQ Forum: Server Administration » can we view the data present in the Dump file.

Home » RDBMS Server » Server Administration » can we view the data present in the Dump file.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

can we view the data present in the Dump file. [message #113488]

Mon, 04 April 2005 03:13

deepa_balu
Messages: 74
Registered: March 2005

Member

I want to search a string within the Data of few tables.
I do not know the particular Tablename or the column name.

I have a dump file .Just like how we can view the DDL of the Objects of
dump file ,,,,,,, is there any way that we can view the data present in the
Dump file.

Report message to a moderator

Re: can we view the data present in the Dump file. [message #113533 is a reply to message #113488]

Mon, 04 April 2005 08:07

Frank Naude
Messages: 4587
Registered: April 1998

Senior Member

Use impdp with the SQLFILE= option to write out all SQL DDL to a specified file. Here is an example:

Export data:

$ expdp scott/tiger directory=exp
Export: Release 10.1.0.2.0 - 64bit Production on Monday, 04 April, 2005 15:19
Copyright (c) 2003, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/******** directory=exp
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/SE_PRE_SCHEMA_PROCOBJACT/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."DEPT" 5.656 KB 4 rows
. . exported "SCOTT"."EMP" 7.820 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.585 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
/app/oracle/expdat.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:21

Export using SQLFILE:

$ impdp scott/tiger directory=exp sqlfile=dll.sql
Import: Release 10.1.0.2.0 - 64bit Production on Monday, 04 April, 2005 15:23
Copyright (c) 2003, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Master table "SCOTT"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_SQL_FILE_FULL_01":  scott/******** directory=exp sqlfile=dll.sql
Processing object type SCHEMA_EXPORT/SE_PRE_SCHEMA_PROCOBJACT/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Job "SCOTT"."SYS_SQL_FILE_FULL_01" successfully completed at 15:23

Inspect DLL:

$ head -20 dll.sql
-- CONNECT SCOTT
-- new object type path is: SCHEMA_EXPORT/SE_PRE_SCHEMA_PROCOBJACT/PROCACT_SCHEMA

BEGIN
sys.dbms_logrep_imp.instantiate_schema(schema_name=>'SCOTT', export_db_name=>'ORCL', inst_scn=>'4078305');
COMMIT;
END;
/

-- new object type path is: SCHEMA_EXPORT/TABLE/TABLE
CREATE TABLE "SCOTT"."DEPT"
   (    "DEPTNO" NUMBER(2,0),
        "DNAME" VARCHAR2(14),
        "LOC" VARCHAR2(13)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;
...

Best regards.

Frank

Report message to a moderator

Previous Topic:	can user have right on two tablespace ?
Next Topic:	Buffer Cache

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jan 25 03:37:12 CST 2025