Oracle 스키마 스크립트

select * from all_objects WHERE OWNER='SCOTT';
select * from all_constraints;

select * from user_objects;
select * from user_all_tables;
select * from user_constraints;
select * from user_procedures;

select * from user_tab_comments;

select * from user_col_comments;

---- 테이블 CREATE

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
FROM USER_ALL_TABLES u
WHERE u.nested='NO' AND (u.iot_type is null or u.iot_type='IOT');

----- CONSTRAINTS

SELECT DBMS_METADATA.GET_DDL('CONSTRAINT', U.CONSTRAINT_NAME)
FROM user_constraints U
WHERE U.INDEX_NAME IS NOT NULL AND U.INDEX_OWNER IS NOT NULL;

----- CONSTRAINTS

SELECT DBMS_METADATA.GET_DDL('REF_CONSTRAINT', U.CONSTRAINT_NAME)
FROM user_constraints U
WHERE U.CONSTRAINT_TYPE='R';

--테이블 스키마 뽑기

--DBMS_METADATA.GET_DDL('TABLE', '테이블명', '테이블소유자')

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

--인덱스 스키마 뽑기

--DBMS_METADATA.GET_DDL('INDEX', '인덱스명', '인덱스소유자')

SELECT DBMS_METADATA.GET_DDL('INDEX', INDEX_NAME, 'EIS')||';'
FROM USER_INDEXES;

--테이블 스페이스 스키마 뽑기                
-- TABLESPACE와 USER는 DBA 궈한이 있는 유저로 실행
SELECT DBMS_METADATA.GET_DDL('TABLESPACE',TABLESPACE_NAME)||';'
FROM DBA_TABLESPACES
WHERE TABLESPACE_NAME NOT IN ('SYSTEM','TEMP','USERS','TOOLS')
AND STATUS = 'ONLINE';

--유저 스키마 뽑기

SELECT DBMS_METADATA.GET_DDL('USER', USERNAME)||';'

FROM DBA_USERS;

--SEQUENCE 스키마 뽑기

DBMS_METADATA.GET_DDL('SEQUENCE', '시퀀스명', '시퀀스소유자'

SELECT DBMS_METADATA.GET_DDL('SEQUENCE', SEQUENCE_NAME, 'EIS')||';'
FROM USER_SEQUENCES;