在上一篇文章中，我们知道了如何获取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>