Oracle 스키마의 전체 DDL을 생성하는 방법(스크립트 가능)

Oracle 스키마의 전체 DDL을 생성하는 방법(스크립트 가능)

Oracle 스키마 내의 모든 테이블, 뷰, 인덱스, 패키지, 프로시저, 함수, 트리거, 유형, 시퀀스, 동의어, 인가 등에 대해 DDL을 생성하는 방법을 알려 주시겠습니까?이상적으로는 저도 행을 복사하고 싶지만, 그다지 중요하지 않습니다.

SQL Developer에서 마법사를 사용할 수 없도록 매번 수동으로 작업하지 않고 일정된 작업을 수행하려고 합니다.

서로 인가 및 동의어가 있는 여러 스키마에서 실행하는 것이 이상적이기 때문에 스키마 이름이 새로운 스키마 이름과 일치하도록 출력에서 검색/바꾸기를 수행할 수 있는 방법을 원합니다.

감사합니다!

SQL*Plus 및 dbms_metadata 패키지를 통해 스키마를 파일로 스풀아웃할 수 있습니다.그런 다음 sed를 통해 스키마 이름을 다른 이름으로 바꿉니다.이는 Oracle 10 이상에서 작동합니다.

sqlplus<<EOF
set long 100000
set head off
set echo off
set pagesize 0
set verify off
set feedback off
spool schema.out

select dbms_metadata.get_ddl(object_type, object_name, owner)
from
(
    --Convert DBA_OBJECTS.OBJECT_TYPE to DBMS_METADATA object type:
    select
        owner,
        --Java object names may need to be converted with DBMS_JAVA.LONGNAME.
        --That code is not included since many database don't have Java installed.
        object_name,
        decode(object_type,
            'DATABASE LINK',      'DB_LINK',
            'JOB',                'PROCOBJ',
            'RULE SET',           'PROCOBJ',
            'RULE',               'PROCOBJ',
            'EVALUATION CONTEXT', 'PROCOBJ',
            'CREDENTIAL',         'PROCOBJ',
            'CHAIN',              'PROCOBJ',
            'PROGRAM',            'PROCOBJ',
            'PACKAGE',            'PACKAGE_SPEC',
            'PACKAGE BODY',       'PACKAGE_BODY',
            'TYPE',               'TYPE_SPEC',
            'TYPE BODY',          'TYPE_BODY',
            'MATERIALIZED VIEW',  'MATERIALIZED_VIEW',
            'QUEUE',              'AQ_QUEUE',
            'JAVA CLASS',         'JAVA_CLASS',
            'JAVA TYPE',          'JAVA_TYPE',
            'JAVA SOURCE',        'JAVA_SOURCE',
            'JAVA RESOURCE',      'JAVA_RESOURCE',
            'XML SCHEMA',         'XMLSCHEMA',
            object_type
        ) object_type
    from dba_objects 
    where owner in ('OWNER1')
        --These objects are included with other object types.
        and object_type not in ('INDEX PARTITION','INDEX SUBPARTITION',
           'LOB','LOB PARTITION','TABLE PARTITION','TABLE SUBPARTITION')
        --Ignore system-generated types that support collection processing.
        and not (object_type = 'TYPE' and object_name like 'SYS_PLSQL_%')
        --Exclude nested tables, their DDL is part of their parent table.
        and (owner, object_name) not in (select owner, table_name from dba_nested_tables)
        --Exclude overflow segments, their DDL is part of their parent table.
        and (owner, object_name) not in (select owner, table_name from dba_tables where iot_type = 'IOT_OVERFLOW')
)
order by owner, object_type, object_name;

spool off
quit
EOF

cat schema.out|sed 's/OWNER1/MYOWNER/g'>schema.out.change.sql

모든 내용을 스크립트에 넣고 cron(스케줄러)을 통해 실행합니다.고급 기능을 사용하면 개체 내보내기가 까다로울 수 있습니다.위의 코드에 예외를 추가할 필요가 있어도 놀라지 마십시오.

각 오브젝트에 대해 개별적으로 ddl을 생성하는 경우

문의는 다음과 같습니다.

--모든 사용자 객체에 대해 DDL 생성

- 1. 모든 테이블의 경우

SELECT DBMS_METADATA.GET_DDL('TABLE', TABLE_NAME) FROM USER_TABLES;

- 2. 모든 인덱스

SELECT DBMS_METADATA.GET_DDL('INDEX', INDEX_NAME) FROM USER_INDEXES WHERE INDEX_TYPE ='NORMAL';

- 3. 모든 뷰에 대하여

SELECT DBMS_METADATA.GET_DDL('VIEW', VIEW_NAME) FROM USER_VIEWS;

또는

SELECT TEXT FROM USER_VIEWS

--4. 모든 미관용

SELECT QUERY FROM USER_MVIEWS

- 5. 모든 기능용

SELECT DBMS_METADATA.GET_DDL('FUNCTION', OBJECT_NAME) FROM USER_PROCEDURES WHERE OBJECT_TYPE = 'FUNCTION'

===============================================================================================

GET_DDL 함수는 LOB, Materialized VIEW, TABLE 파티션 등의 object_type을 지원하지 않습니다.

따라서 DDL 생성을 위한 통합 쿼리는 다음과 같습니다.

SELECT OBJECT_TYPE, OBJECT_NAME,DBMS_METADATA.GET_DDL(OBJECT_TYPE, OBJECT_NAME, OWNER)
  FROM ALL_OBJECTS 
  WHERE (OWNER = 'XYZ') AND OBJECT_TYPE NOT IN('LOB','MATERIALIZED VIEW', 'TABLE PARTITION') ORDER BY OBJECT_TYPE, OBJECT_NAME;

PARGE의 get_ddl 프로시저에서는 스펙 AND 본문이 모두 반환되므로 all_objects에 대한 쿼리를 변경하여 패키지 본문이 선택으로 반환되지 않도록 하는 것이 좋습니다.

지금까지 쿼리를 다음과 같이 변경했습니다.

SELECT DBMS_METADATA.GET_DDL(REPLACE(object_type, ' ', '_'), object_name, owner)
FROM all_OBJECTS
WHERE (OWNER = 'OWNER1')
and object_type not like '%PARTITION'
and object_type not like '%BODY'
order by object_type, object_name;

취득하는 오브젝트 타입에 따라서는, 다른 변경이 필요하게 되는 경우가 있습니다.

먼저 스키마 메타데이터를 내보냅니다.

expdp dumpfile=filename logfile=logname directory=dir_name schemas=schema_name

를 사용하여 Import 합니다.sqlfileoption (데이터는 Import하지 않고 스키마 DDL만 해당 파일에 씁니다)

impdp dumpfile=filename logfile=logname directory=dir_name sqlfile=ddl.sql

PACKE_BODY 등의 오브젝트에 문제가 있습니다.

SELECT DBMS_METADATA.get_ddl(object_Type, object_name, owner) FROM ALL_OBJECTS WHERE OWNER = 'WEBSERVICE';


ORA-31600 invalid input value PACKAGE BODY parameter OBJECT_TYPE in function GET_DDL
ORA-06512: на  "SYS.DBMS_METADATA", line 4018
ORA-06512: на  "SYS.DBMS_METADATA", line 5843
ORA-06512: на  line 1
31600. 00000 -  "invalid input value %s for parameter %s in function %s"
*Cause:    A NULL or invalid value was supplied for the parameter.
*Action:   Correct the input value and try the call again.



SELECT DBMS_METADATA.GET_DDL(REPLACE(object_type,' ','_'), object_name, owner)
  FROM all_OBJECTS 
  WHERE (OWNER = 'OWNER1');

전체 스키마(USER)의 DDL 스크립트를 생성하려면 dbms_metadata.get_ddl을 사용합니다.

Tim Hall이 작성한SQL*Plus에서 다음 스크립트를 실행합니다.

프롬프트가 표시되면 사용자 이름을 입력합니다.

set long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on
column ddl format a1000

begin
   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
end;
/

variable v_username VARCHAR2(30);

exec:v_username := upper('&1');

select dbms_metadata.get_ddl('USER', u.username) AS ddl
from   dba_users u
where  u.username = :v_username
union all
select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', tq.username) AS ddl
from   dba_ts_quotas tq
where  tq.username = :v_username
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee) AS ddl
from   dba_role_privs rp
where  rp.grantee = :v_username
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', sp.grantee) AS ddl
from   dba_sys_privs sp
where  sp.grantee = :v_username
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', tp.grantee) AS ddl
from   dba_tab_privs tp
where  tp.grantee = :v_username
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('DEFAULT_ROLE', rp.grantee) AS ddl
from   dba_role_privs rp
where  rp.grantee = :v_username
and    rp.default_role = 'YES'
and    rownum = 1
union all
select to_clob('/* Start profile creation script in case they are missing') AS ddl
from   dba_users u
where  u.username = :v_username
and    u.profile <> 'DEFAULT'
and    rownum = 1
union all
select dbms_metadata.get_ddl('PROFILE', u.profile) AS ddl
from   dba_users u
where  u.username = :v_username
and    u.profile <> 'DEFAULT'
union all
select to_clob('End profile creation script */') AS ddl
from   dba_users u
where  u.username = :v_username
and    u.profile <> 'DEFAULT'
and    rownum = 1
/

set linesize 80 pagesize 14 feedback on trimspool on verify on

이 쿼리의 출력은 매우 깨끗합니다(여기 원본).

clear screen
accept uname prompt 'Enter User Name : '
accept outfile prompt  ' Output filename : '

spool &&outfile..gen

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/

SELECT dbms_metadata.get_ddl('USER','&&uname') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','&&uname') from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','&&uname') from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','&&uname') from dual;

spool off

언급URL : https://stackoverflow.com/questions/10886450/how-to-generate-entire-ddl-of-an-oracle-schema-scriptable