DB내부의 모든 object에 대한 생성 script 만들기..

DBMS_METADATA package를 아십니까?

- 보통.. DB 내부에 생성되어 있는 Object에 대한 DDL (생성 Script)를 뽑기 위해서
여러가지 방법을 사용합니다. 예를 들어, export/import를 이용하여 script를 생성할수도
있고, 또한 시중에 나와 있는 Tool들을 이용해서 DDL을 만들수 있읍니다.

 그러나 DB내부에 기본적으로 제공되는 DDL생성 package가 있읍니다.
 이 package는 table, index는 물론, plsql로 생성해주며, 또한 User나 Role에
 Grant된 정보들도 뽑을수 있읍니다....

참고하시길..
 
 

SQL> SET LONG 1000000
SQL> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;
                                                                               
DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
--------------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSTEM"  ENABLE,
         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
   ) 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 "SYSTEM"                                                      
 
이때, segment attribute를 빼고 script를 생성하고자 한다면....

SQL> execute dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES', false);
SQL> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;
                                                                               
DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
--------------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") ENABLE,
         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
   )                                                        

또한 constraint(null, pk등), referencial constraint(fk), 그리고 ';' 로 output이
끝나도록 하기를 원하면...

SQL> execute dbms_metadata.set_transform_param(dbms_metadata.session_transform,'CONSTRAINTS', FALSE);
SQL> execute dbms_metadata.set_transform_param(dbms_metadata.session_transform,'REF_CONSTRAINTS', FALSE);
SQL> execute dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR', TRUE);

SQL> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;

DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
--------------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0)
   ) ;

이렇게 하면 됩니다.... 물론 pl/sql의 source도 뽑을수 있지요..

SQL> CREATE OR REPLACE PROCEDURE abc AS
     BEGIN
       NULL;
     END abc;
/

SQL> SET LONG 1000000
SQL> SELECT dbms_metadata.get_ddl(object_type, object_name)
     FROM   user_objects
     WHERE  object_name = 'ABC';

DBMS_METADATA.GET_DDL('PROCEDURE',OBJECT_NAME)
--------------------------------------------------------------
  CREATE OR REPLACE PROCEDURE "PLSQL_USER"."ABC" AS
BEGIN
   NULL;
END abc;

게다가... 임의의 User에게 Grant된 것도 뽑을수 있읍니다...캬......

ex)

SQL> CREATE USER HIKIM IDENTIFIED BY HIKIM;
SQL> GRANT CONNECT, RESOURCE, DBA TO HIKIM;
SQL> GRANT SELECT ON SCOTT.EMP TO HIKIM;

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','HIKIM')  --> system privelige.
     FROM   DUAL;

   GRANT UNLIMITED TABLESPACE TO "HIKIM"                                   
                                                                               
                                                                               
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','HIKIM')    --> role
   FROM   DUAL;
                                                                               
   GRANT "CONNECT" TO "HIKIM"                                             
   GRANT "RESOURCE" TO "HIKIM"                                            
   GRANT "DBA" TO "HIKIM"                                                 
  

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','HIKIM')   --> grant받은 object
FROM   DUAL;
                                                                               
  GRANT SELECT ON "SCOTT"."EMP" TO "HIKIM"                                

SELECT DBMS_METADATA.GET_GRANTED_DDL('DEFAULT_ROLE','HIKIM')  --> default role 받기
FROM   DUAL;
                                                                               
   ALTER USER "HIKIM" DEFAULT ROLE ALL                                    

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'DBA')   --> DBA role로 grant된 role.
FROM   DUAL;
                                                                               
   GRANT "SELECT_CATALOG_ROLE" TO "DBA" WITH ADMIN OPTION;                     
   GRANT "EXECUTE_CATALOG_ROLE" TO "DBA" WITH ADMIN OPTION;                    
   GRANT "DELETE_CATALOG_ROLE" TO "DBA" WITH ADMIN OPTION;                     

좋군요...ㅋㅋ.

If you get knocked down, don’t stay down…REBOUND!
The road to success has many obstacles. If you trip and fall, don’t stay down.
Get back up, dust yourself off and keep going. Winners never quit and quitters never win.
 
      Gabriel Corchado  


아래 사이트에서 스크랩하였습니다.
https://www.dbguide.net/dbqa/dbqa120001.jsp?mode=view&pg=28&idx=398