[FW] Script to get DDL of a schema user (TRIGGER, FUNCTION, PROCEDURE, TABLE DDL, INDEX_DDL)

sqlplus / as sydba<<EOF set long 999999999 set head off set echo off set pagesize 0 set verify off set feedback off spool schema_$1_ddl.out select dbms_metadata.get_ddl(object_type, object_name, owner) from (     --Convert DBA_OBJECTS.OBJECT_TYPE to DBMS_METADATA object type:     select         owner,         object_name,         decode(object_type,             'JOB',          'PROCOBJ',             'PACKAGE',      'PACKAGE_SPEC',             'PACKAGE BODY', 'PACKAGE_BODY',             'TYPE',         'TYPE_SPEC',             'TYPE BODY',    'TYPE_BODY',             object_type         ) object_type    from dba_objects     where owner in ('$1')         --These objects are included with other object types.         and object_type not in ('INDEX PARTITION','LOB','LOB PARTITION','TABLE PARTITION')         --Ignore system-generated types that support collection processing.         and not (object_type like 'TYPE' and object_name like 'SYS_PLSQL_%') ) order by owner, object_type, object_name; spool off quit EOF

Usage:

sh test.sh TEST (depends on which schema you want retrieve~)

方法2:
or Use Oracle DataPump

SQL> create or replace directory dp as '/home/oracle/';

SQL> create or replace directory dp as '/home/oracle/';

expdp \'/ as sysdba\' SCHEMAS=TEST,JAYCHU directory=dp dumpfile=full_meta.dmp logfile=full_meta.log CONTENT=METADATA_ONLY

impdp \'/ as sysdba\' directory=dp dumpfile=full_meta.dmp logfile=full_meta_implog.txt sqlfile=ddl_dump.sql

the example of ddl_Dump.sql

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

-- CONNECT SYS

ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';

-- new object type path: SCHEMA_EXPORT/USER

-- CONNECT SYSTEM

 CREATE USER "TEST" IDENTIFIED BY VALUES 'S:D5260B66A5E8C4DC857A1ABE54569A0E45BC39B1B9A4D25B3B5D542BDF0E;H:6A881F008133C22B7B356D9D921817E7;T:FE98C666DDCE9AC8AB5020DCA8464EEF420AB759FAB1B58FF4F73D290F8CAB437EC3F31A3A9F287295AF574FC1B8EF1DFC5440B000532FD1E5DC6634EC5C0E47C2EF37F04FF71B24F1B83FE1F3F3A90B;7A0F2B316C212D67'

      DEFAULT TABLESPACE "USERS"

      TEMPORARY TABLESPACE "TEMP";

-- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT

GRANT CREATE ANY TRIGGER TO "TEST";

GRANT UNLIMITED TABLESPACE TO "TEST";

-- new object type path: SCHEMA_EXPORT/ROLE_GRANT

 GRANT "CONNECT" TO "TEST";

 GRANT "RESOURCE" TO "TEST";

-- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE

 ALTER USER "TEST" DEFAULT ROLE ALL;

-- new object type path: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

-- CONNECT TEST

BEGIN

sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'MSG', inst_scn=>'5841316');

COMMIT;

END;

/

-- new object type path: SCHEMA_EXPORT/TABLE/TABLE

-- CONNECT SYS

CREATE TABLE "TEST"."TAB1"

   (    "A" VARCHAR2(20 BYTE),

        "B" VARCHAR2(20 BYTE)

   ) SEGMENT CREATION DEFERRED

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

 NOCOMPRESS LOGGING

  TABLESPACE "USERS" ;

-- new object type path: SCHEMA_EXPORT/FUNCTION/FUNCTION

-- CONNECT TEST

CREATE EDITIONABLE FUNCTION       Days_Between (first_dt   DATE,

                                  second_dt  DATE)

RETURN NUMBER IS

 dt_one NUMBER;

 dt_two NUMBER;

BEGIN

 dt_one := TO_NUMBER(TO_CHAR(first_dt, 'DDD'));

 dt_two := TO_NUMBER(TO_CHAR(second_dt, 'DDD'));

 RETURN (dt_two - dt_one);

END Days_Between;

/

-- new object type path: SCHEMA_EXPORT/PROCEDURE/PROCEDURE

CREATE EDITIONABLE PROCEDURE      addtuple1(i IN NUMBER) AS

BEGIN

    INSERT INTO tab1 VALUES(i, 'xxx');

END addtuple1;

/

-- new object type path: SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION

ALTER FUNCTION "TEST"."DAYS_BETWEEN"

  COMPILE

    PLSQL_OPTIMIZE_LEVEL=  2

    PLSQL_CODE_TYPE=  INTERPRETED

    PLSQL_DEBUG=  FALSE    PLSCOPE_SETTINGS=  'IDENTIFIERS:NONE'

 REUSE SETTINGS TIMESTAMP '2016-01-29 09:57:53'

/

-- new object type path: SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

ALTER PROCEDURE "TEST"."ADDTUPLE1"

  COMPILE

    PLSQL_OPTIMIZE_LEVEL=  2

    PLSQL_CODE_TYPE=  INTERPRETED

    PLSQL_DEBUG=  FALSE    PLSCOPE_SETTINGS=  'IDENTIFIERS:NONE'

 REUSE SETTINGS TIMESTAMP '2016-01-29 10:00:16'

/

-- new object type path: SCHEMA_EXPORT/TABLE/TRIGGER

CREATE EDITIONABLE TRIGGER test.MONTOR_linktab1

    AFTER INSERT OR UPDATE OR DELETE

    ON test.tab1

DECLARE

    log_action VARCHAR2(20);

    v_date VARCHAR2(20);

BEGIN

    select to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') into v_date FROM DUALf@msgtest;

    IF INSERTING THEN

        log_action := 'Insert';

    ELSIF UPDATING THEN

        log_action := 'Update';

    ELSIF DELETING THEN

        log_action := 'Delete';

    ELSE

        DBMS_OUTPUT.PUT_LINE('This code is not reachable.');

    END IF;

    INSERT INTO test.tab1 VALUES(v_date,log_action);

END;

/

ALTER TRIGGER "TEST"."MONTOR_LINKTAB1" ENABLE;

ALTER TRIGGER "TEST"."MONTOR_LINKTAB1"

  COMPILE

    PLSQL_OPTIMIZE_LEVEL=  2

    PLSQL_CODE_TYPE=  INTERPRETED    PLSCOPE_SETTINGS=  'IDENTIFIERS:NONE'

;

-- new object type path: SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

-- new object type path: SCHEMA_EXPORT/STATISTICS/MARKER

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

Ref:

http://stackoverflow.com/questions/10886450/how-to-generate-entire-ddl-of-an-oracle-schema-scriptable