在上一篇文章中，我们知道了如何获取Oracle数据库中某个用户下的所有表、索引、约束、触发器的创建脚本？那么对于数据库中特定的某张表而言，又该如何获取建表语句、及索引、约束、外键约束、触发器、对象权限的创建脚本呢？
在这里，给出一个实现上述功能的脚本：
OCM11g-> cat table_ddl.sql
-- Be sure the variable 'S' means the Schema name,the 'T' means table name.
-- All these variables should be UPPERCASE.
def S='&1'
def T='&2'
set lines 2000 pages 0 ver off echo off head off feed off
set newpage none
set trimspool on
set long 5000000
col output for a1000 word_wrapped
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',false);
spool &T..sql
select dbms_metadata.get_ddl('TABLE','&T','&S') output from dual;
select dbms_metadata.get_dependent_ddl('INDEX','&T','&S') output from dual;
select dbms_metadata.get_dependent_ddl('CONSTRAINT','&T','&S') output from dual;
select dbms_metadata.get_dependent_ddl('REF_CONSTRAINT','&T','&S') output from dual;
select dbms_metadata.get_dependent_ddl('TRIGGER','&T','&S') output from dual;
-- Uncomment to generate object level grants
select dbms_metadata.get_dependent_ddl('OBJECT_GRANT','&T','&S') output from dual;
spool off;
OCM11g->
下述是对于HR用户下的EMPLOYEES表的输出结果：
SQL> conn hr/hR
Connected.
SQL> @table_ddl.sql
Enter value for 1: HR
Enter value for 2: EMPLOYEES
CREATE TABLE "HR"."EMPLOYEES"
( "EMPLOYEE_ID" NUMBER(6,0),
"FIRST_NAME" VARCHAR2(20),
"LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE,
"EMAIL" VARCHAR2(25) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE,
"PHONE_NUMBER" VARCHAR2(20),
"HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE,
"JOB_ID" VARCHAR2(10) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE,
"SALARY" NUMBER(8,2),
"COMMISSION_PCT" NUMBER(2,2),
"MANAGER_ID" NUMBER(6,0),
"DEPARTMENT_ID" NUMBER(4,0),
CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0) ENABLE,
CONSTRAINT "EMP_EMAIL_UK" UNIQUE ("EMAIL")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
TABLESPACE "EXAMPLE" ENABLE,
CONSTRAINT "EMP_EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
TABLESPACE "EXAMPLE" ENABLE,
CONSTRAINT "EMP_MANAGER_FK" FOREIGN KEY ("MANAGER_ID")
REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ENABLE,
CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID")
REFERENCES "HR"."JOBS" ("JOB_ID") ENABLE,
CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID")
REFERENCES "HR"."DEPARTMENTS" ("DEPARTMENT_ID") ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
TABLESPACE "EXAMPLE" ;
CREATE UNIQUE INDEX "HR"."EMP_EMAIL_UK" ON "HR"."EMPLOYEES" ("EMAIL")
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
TABLESPACE "EXAMPLE" ;
CREATE UNIQUE INDEX "HR"."EMP_EMP_ID_PK" ON "HR"."EMPLOYEES" ("EMPLOYEE_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
TABLESPACE "EXAMPLE" ;
CREATE INDEX "HR"."EMP_DEPARTMENT_IX" ON "HR"."EMPLOYEES" ("DEPARTMENT_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
TABLESPACE "EXAMPLE" ;
CREATE INDEX "HR"."EMP_JOB_IX" ON "HR"."EMPLOYEES" ("JOB_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
TABLESPACE "EXAMPLE" ;
CREATE INDEX "HR"."EMP_MANAGER_IX" ON "HR"."EMPLOYEES" ("MANAGER_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
TABLESPACE "EXAMPLE" ;
CREATE INDEX "HR"."EMP_NAME_IX" ON "HR"."EMPLOYEES" ("LAST_NAME", "FIRST_NAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
TABLESPACE "EXAMPLE" ;
ALTER TABLE "HR"."EMPLOYEES" MODIFY ("EMAIL" CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE);
ALTER TABLE "HR"."EMPLOYEES" ADD CONSTRAINT "EMP_EMAIL_UK" UNIQUE ("EMAIL")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
TABLESPACE "EXAMPLE" ENABLE;
ALTER TABLE "HR"."EMPLOYEES" ADD CONSTRAINT "EMP_EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
TABLESPACE "EXAMPLE" ENABLE;
ALTER TABLE "HR"."EMPLOYEES" MODIFY ("HIRE_DATE" CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE);
ALTER TABLE "HR"."EMPLOYEES" MODIFY ("JOB_ID" CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE);
ALTER TABLE "HR"."EMPLOYEES" MODIFY ("LAST_NAME" CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE);
ALTER TABLE "HR"."EMPLOYEES" ADD CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0) ENABLE;
ALTER TABLE "HR"."EMPLOYEES" ADD CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID")
REFERENCES "HR"."DEPARTMENTS" ("DEPARTMENT_ID") ENABLE;
ALTER TABLE "HR"."EMPLOYEES" ADD CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID")
REFERENCES "HR"."JOBS" ("JOB_ID") ENABLE;
ALTER TABLE "HR"."EMPLOYEES" ADD CONSTRAINT "EMP_MANAGER_FK" FOREIGN KEY ("MANAGER_ID")
REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ENABLE;
CREATE OR REPLACE TRIGGER "HR"."SECURE_EMPLOYEES"
BEFORE INSERT OR UPDATE OR DELETE ON employees
BEGIN
secure_dml;
END secure_employees;
/
ALTER TRIGGER "HR"."SECURE_EMPLOYEES" DISABLE;
CREATE OR REPLACE TRIGGER "HR"."UPDATE_JOB_HISTORY"
AFTER UPDATE OF job_id, department_id ON employees
FOR EACH ROW
BEGIN
add_job_history(:old.employee_id, :old.hire_date, sysdate,
:old.job_id, :old.department_id);
END;
/
ALTER TRIGGER "HR"."UPDATE_JOB_HISTORY" ENABLE;
GRANT SELECT, REFERENCES ON "HR"."EMPLOYEES" TO "OE";
SQL>
