oracle的Export data,Export Oracle Databaes Objects into sql file

Here it is,

dmp file Log:

C:\Documents and Settings\Dharan>expdp [email protected] directory=DUMP_DI

R dumpfile=scott.dmp content=metadata_only Full=Y

Export: Release 11.1.0.6.0 - Production on Sunday, 31 January, 2010 16:47:32

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

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Produc

tion

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORA-31631: privileges are required

ORA-39161: Full database jobs require privileges

C:\Documents and Settings\Dharan>expdp [email protected] directory=DUMP_DI

R dumpfile=scott.dmp content=metadata_only Full=Y

Export: Release 11.1.0.6.0 - Production on Sunday, 31 January, 2010 16:48:42

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

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Produc

tion

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SCOTT"."SYS_EXPORT_FULL_01": SCOTT/********@LOCAL-DB directo

ry=DUMP_DIR dumpfile=scott.dmp content=metadata_only Full=Y

Processing object type DATABASE_EXPORT/TABLESPACE

Processing object type DATABASE_EXPORT/PROFILE

Processing object type DATABASE_EXPORT/SYS_USER/USER

Processing object type DATABASE_EXPORT/SCHEMA/USER

Processing object type DATABASE_EXPORT/ROLE

Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE

Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA

Processing object type DATABASE_EXPORT/RESOURCE_COST

Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK

Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK

Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE

Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_

GRANT

Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY

Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type DATABASE_EXPORT/CONTEXT

Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM

Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM

Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC

Processing object type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/OWNER_GRANT/OBJECT_GRAN

T

Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROC

ACT_SYSTEM

Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ

Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PRO

CACT_SYSTEM

Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/PRE_TABLE_ACTION

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRA

NT

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/CROSS_SCHEMA/OBJECT_GR

ANT

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATI

STICS

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT

Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC

Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/GRANT/OWNER_GRANT/OBJECT_G

RANT

Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/FUNCTION

Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/GRANT/OWNER_GRANT/OBJECT_

GRANT

Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE

Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/GRANT/OWNER_GRANT/OBJECT

_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/COMPILE_PACKAGE/PACKAGE_SP

EC/ALTER_PACKAGE_SPEC

Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION

Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE

Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW

Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRAN

T

Processing object type DATABASE_EXPORT/SCHEMA/VIEW/COMMENT

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BOD

Y

Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_BODY

Processing object type DATABASE_EXPORT/SCHEMA/JAVA_SOURCE/JAVA_SOURCE

Processing object type DATABASE_EXPORT/SCHEMA/JAVA_CLASS/JAVA_CLASS

Processing object type DATABASE_EXPORT/SCHEMA/JAVA_CLASS/GRANT/OWNER_GRANT/OBJEC

T_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/JAVA_RESOURCE/JAVA_RESOURCE

Processing object type DATABASE_EXPORT/SCHEMA/JAVA_RESOURCE/GRANT/OWNER_GRANT/OB

JECT_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/OPERATOR/OPERATOR

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/

INDEX

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/FUNCTIONAL_

AND_BITMAP/INDEX_STATISTICS

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/DOMAIN_INDEX/INDEX

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_TABLE_ACTION

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER

Processing object type DATABASE_EXPORT/SCHEMA/VIEW/TRIGGER

Processing object type DATABASE_EXPORT/SCHEMA/EVENT/TRIGGER

Processing object type DATABASE_EXPORT/SCHEMA/MATERIALIZED_VIEW

Processing object type DATABASE_EXPORT/SCHEMA/JOB

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCACT_INSTAN

CE

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCDEPOBJ

Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ

Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA

Processing object type DATABASE_EXPORT/AUDIT

Master table "SCOTT"."SYS_EXPORT_FULL_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SCOTT.SYS_EXPORT_FULL_01 is:

C:\SCOTT.DMP

Job "SCOTT"."SYS_EXPORT_FULL_01" successfully completed at 16:51:20Sql File Log:

C:\Documents and Settings\Dharan>IMPDP [email protected] directory=DUMP_DI

R dumpfile=scott.dmp SQLFILE=SCOTT.sql

Import: Release 11.1.0.6.0 - Production on Sunday, 31 January, 2010 17:01:47

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

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Produc

tion

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SCOTT"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded

Starting "SCOTT"."SYS_SQL_FILE_FULL_01": SCOTT/********@LOCAL-DB direc

tory=DUMP_DIR dumpfile=scott.dmp SQLFILE=SCOTT.sql

Processing object type DATABASE_EXPORT/TABLESPACE

Processing object type DATABASE_EXPORT/PROFILE

Processing object type DATABASE_EXPORT/SYS_USER/USER

Processing object type DATABASE_EXPORT/SCHEMA/USER

Processing object type DATABASE_EXPORT/ROLE

Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE

Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA

Processing object type DATABASE_EXPORT/RESOURCE_COST

Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK

Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK

Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE

Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_

GRANT

Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY

Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type DATABASE_EXPORT/CONTEXT

Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM

Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM

Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC

Processing object type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/OWNER_GRANT/OBJECT_GRAN

T

Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROC

ACT_SYSTEM

Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ

Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PRO

CACT_SYSTEM

Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/PRE_TABLE_ACTION

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRA

NT

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/CROSS_SCHEMA/OBJECT_GR

ANT

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATI

STICS

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT

Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC

Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/GRANT/OWNER_GRANT/OBJECT_G

RANT

Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/FUNCTION

Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/GRANT/OWNER_GRANT/OBJECT_

GRANT

Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE

Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/GRANT/OWNER_GRANT/OBJECT

_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/COMPILE_PACKAGE/PACKAGE_SP

EC/ALTER_PACKAGE_SPEC

Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION

Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE

Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW

Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRAN

T

Processing object type DATABASE_EXPORT/SCHEMA/VIEW/COMMENT

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BOD

Y

Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_BODY

Processing object type DATABASE_EXPORT/SCHEMA/JAVA_SOURCE/JAVA_SOURCE

Processing object type DATABASE_EXPORT/SCHEMA/JAVA_CLASS/JAVA_CLASS

Processing object type DATABASE_EXPORT/SCHEMA/JAVA_CLASS/GRANT/OWNER_GRANT/OBJEC

T_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/JAVA_RESOURCE/JAVA_RESOURCE

Processing object type DATABASE_EXPORT/SCHEMA/JAVA_RESOURCE/GRANT/OWNER_GRANT/OB

JECT_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/OPERATOR/OPERATOR

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/

INDEX

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/FUNCTIONAL_

AND_BITMAP/INDEX_STATISTICS

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/DOMAIN_INDEX/INDEX

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_TABLE_ACTION

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER

Processing object type DATABASE_EXPORT/SCHEMA/VIEW/TRIGGER

Processing object type DATABASE_EXPORT/SCHEMA/EVENT/TRIGGER

Processing object type DATABASE_EXPORT/SCHEMA/MATERIALIZED_VIEW

Processing object type DATABASE_EXPORT/SCHEMA/JOB

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCACT_INSTAN

CE

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCDEPOBJ

Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ

Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA

Processing object type DATABASE_EXPORT/AUDIT

Job "SCOTT"."SYS_SQL_FILE_FULL_01" successfully completed at 17:13:35

c:\SET SERVEROUTPUT ON;

DECLARE

vTableCnt NUMBER :=0;

vProcedureCnt NUMBER :=0;

vFunctionCnt NUMBER :=0;

vPackageCnt NUMBER :=0;

BEGIN

SELECT

(SELECT COUNT(1) FROM USER_OBJECTS WHERE OBJECT_TYPE ='TABLE' AND STATUS ='VALID') TABLE_COUNT,

(SELECT COUNT(1) FROM USER_OBJECTS WHERE OBJECT_TYPE ='PROCEDURE' AND STATUS ='VALID') PROCEDURE_COUNT,

(SELECT COUNT(1) FROM USER_OBJECTS WHERE OBJECT_TYPE ='FUNCTION' AND STATUS ='VALID') FUNCTION_COUNT,

(SELECT COUNT(1) FROM USER_OBJECTS WHERE OBJECT_TYPE ='PACKAGE' AND STATUS ='VALID') PACKAGE_COUNT

INTO vTableCnt,vProcedureCnt,vFunctionCnt,vPackageCnt

FROM

DUAL;

DBMS_OUTPUT.PUT_LINE('-------------------------------------------');

DBMS_OUTPUT.PUT_LINE('TABLE COUNT :- '||vTableCnt);

DBMS_OUTPUT.PUT_LINE('PROCEDURE COUNT :- '||vProcedureCnt);

DBMS_OUTPUT.PUT_LINE('FUNCTION COUNT :- '||vFunctionCnt);

DBMS_OUTPUT.PUT_LINE('PACKAGE COUNT :- '||vPackageCnt);

DBMS_OUTPUT.PUT_LINE('-------------------------------------------');

END;I have tables,procedures,functions and packages available in the schema.

I did not get the DDL statement for even tables in the .sql file

-------------------------------------------

TABLE COUNT :- 10

PROCEDURE COUNT :- 4

FUNCTION COUNT :- 8

PACKAGE COUNT :- 1

-------------------------------------------

PL/SQL procedure successfully completed.Thanks,

Dharan V