星期五, 1月 29, 2016

[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

沒有留言:

LinkWithin-相關文件

Related Posts Plugin for WordPress, Blogger...