DDL 뽑아내기 : dbms_metadata.get_ddl

-- 테이블 생성 스크립트 뽑아내기.

SQL> set line 150
SQL> set pages 10000
SQL> set long 999999999

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

DDL_QUERY

---

CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0) NOT NULL ENABLE,
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"

---

SQL> create or replace view v_emp_dept
2 as
3 select e.empno, e.deptno, d.dname from emp e, dept d
4 where e.deptno=d.deptno;

View created.

SQL> set long 9999999999

-- 뷰 생성 스크립트 뽑아내기.
SQL> select dbms_metadata.get_ddl('VIEW','V_EMP_DEPT','SCOTT') DDL_QUERY from dual;

DDL_QUERY

---

CREATE OR REPLACE FORCE VIEW "SCOTT"."V_EMP_DEPT" ("EMPNO", "DEPTNO", "DNAME") AS
select e.empno, e.deptno, d.dname from emp e, dept d
where e.deptno=d.deptno