Oracle轻松取得建表和索引的DDL语句

获取Oracle数据库中表和索引的DDL语句可以通过以下几个步骤来实现：

1. 登录Oracle数据库：打开SQL*Plus或SQL Developer等客户端工具，在命令行中输入用户名、密码和数据库连接字符串，登录到Oracle数据库。

$ sqlplus username/password@database
或
$ sqlplus /nolog
SQL> connect username/password@database

1. 查询表或索引信息：在SQL*Plus或SQL Developer中执行针对特定表或索引信息的查询语句。例如，查询名为EMPLOYEE的表的DDL语句，可以使用以下语句：

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMPLOYEE') FROM DUAL;

结果输出为该表的DDL语句，包括表结构定义、约束信息、索引定义等内容。

1. 查询多个表或索引信息：如果需要查询多个表或索引的DDL语句，可以使用以下语句：

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE', t.table_name) AS table_ddl
     FROM user_tables t
     WHERE t.table_name IN ('EMPLOYEE', 'DEPARTMENT')
     UNION ALL
     SELECT DBMS_METADATA.GET_DDL('INDEX', i.index_name) AS index_ddl
     FROM user_indexes i
     WHERE i.table_name IN ('EMPLOYEE', 'DEPARTMENT')
     ORDER BY 1;

该语句会输出名为EMPLOYEE和DEPARTMENT的表及其关联的索引的DDL语句，同时按照表名或索引名的字母顺序排列。

示例1: 获取表的DDL语句

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMPLOYEE') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','EMPLOYEE')
--------------------------------------------------------------------------------
  CREATE TABLE "HR"."EMPLOYEE"
   (    "ID" NUMBER(10,0),
        "NAME" VARCHAR2(255 CHAR),
        "DEPARTMENT_ID" NUMBER(10,0),
        "SALARY" NUMBER(5,2)
   ) TABLESPACE "USER_DATA"

示例2: 获取多个表和索引的DDL语句

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE', t.table_name) AS table_ddl
     FROM user_tables t
     WHERE t.table_name IN ('EMPLOYEE', 'DEPARTMENT')
     UNION ALL
     SELECT DBMS_METADATA.GET_DDL('INDEX', i.index_name) AS index_ddl
     FROM user_indexes i
     WHERE i.table_name IN ('EMPLOYEE', 'DEPARTMENT')
     ORDER BY 1;

TABLE_DDL
--------------------------------------------------------------------------------
 CREATE TABLE "HR"."DEPARTMENT"
   (    "ID" NUMBER(10,0),
        "NAME" VARCHAR2(255 CHAR)
   ) TABLESPACE "USER_DATA"

 CREATE TABLE "HR"."EMPLOYEE"
   (    "ID" NUMBER(10,0),
        "NAME" VARCHAR2(255 CHAR),
        "DEPARTMENT_ID" NUMBER(10,0),
        "SALARY" NUMBER(5,2)
   ) TABLESPACE "USER_DATA"

 CREATE INDEX "HR"."EMP_IDX1" ON "HR"."EMPLOYEE" ("DEPARTMENT_ID") TABLESPACE "USER_DATA"

 CREATE INDEX "HR"."DEPT_IDX1" ON "HR"."DEPARTMENT" ("NAME") TABLESPACE "USER_DATA"

 CREATE INDEX "HR"."DEPT_IDX2" ON "HR"."DEPARTMENT" ("ID") TABLESPACE "USER_DATA"

 CREATE UNIQUE INDEX "HR"."EMP_PK" ON "HR"."EMPLOYEE" ("ID") TABLESPACE "USER_DATA"