很多时候开发人员需要跟我们确认表的结构及索引情况，偶尔要一两个还好接受，但是多的时候显得麻烦了，下面是自己为了应对这些状况写的脚本，后面还可以根据自己的需求进行修改
$ vim getDDL.sh
#!/bin/bash
OWNER=$1
TNAME=$2
sqlplus / as sysdba <<EOF
spool ${OWNER}.${TNAME}.log
set serveroutput on
set long 9999
set pagesize 0
set line 280
set feedback off
set heading off
set echo off
--输出信息采用缩排或换行格式化
execute dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'pretty', true);
--确保每个语句都带分号
execute dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'sqlterminator', true);
--关闭表索引、外键等关联（后面单独生成）
execute dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'constraints', false);
execute dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'ref_constraints', false);
execute dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'constraints_as_alter', false);
--关闭存储、表空间属性
execute dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'storage', false);
execute dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'tablespace', false);
--关闭创建表的PCTFREE、NOCOMPRESS等属性
execute dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'segment_attributes', false);
declare
ITEXT char(9999);
TTEXT char(9999);
begin
select dbms_metadata.get_ddl('TABLE',upper('${TNAME}'),upper('${OWNER}')) into TTEXT from dual;
dbms_output.put_line(TTEXT);
for curidx in (select index_name,table_name from dba_indexes where table_name=upper('${TNAME}') and table_owner=upper('${OWNER}')) loop
dbms_output.put_line('索引: [表: '||curidx.table_name||', 索引: '||curidx.index_name);
select dbms_metadata.get_ddl('INDEX',curidx.index_name,'${OWNER}') into ITEXT from dual;
dbms_output.put_line(ITEXT);
end loop;
end;
/
spool off
EOF
$ chmod +x getDDL.sh
使用方式也很简单，脚本后面第一个参数是用户名，第二个参数是表名，不区分大小写，生成的文件名为[用户名].[表明].log
$ ./getDDL.sh SYSTEM REDO_DB
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 1 00:34:31 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>
CREATE TABLE "SYSTEM"."REDO_DB" SHARING=METADATA
( "DBID" NUMBER NOT NULL ENABLE,
"GLOBAL_DBNAME" VARCHAR2(129),
"DBUNAME" VARCHAR2(32),
"VERSION" VARCHAR2(32),
"THREAD#" NUMBER NOT NULL ENABLE,
"RESETLOGS_SCN_BAS" NUMBER,
"RESETLOGS_SCN_WRP" NUMBER,
"RESETLOGS_TIME" NUMBER NOT NULL ENABLE,
"PRESETLOGS_SCN_BAS" NUMBER,
"PRESETLOGS_SCN_WRP" NUMBER,
"PRESETLOGS_TIME" NUMBER NOT NULL ENABLE,
"SEQNO_RCV_CUR" NUMBER,
"SEQNO_RCV_LO" NUMBER,
"SEQNO_RCV_HI" NUMBER,
"SEQNO_DONE_CUR" NUMBER,
"SEQNO_DONE_LO" NUMBER,
"SEQNO_DONE_HI" NUMBER,
"GAP_SEQNO" NUMBER,
"GAP_RET" NUMBER,
"GAP_DONE" NUMBER,
"APPLY_SEQNO" NUMBER,
"APPLY_DONE" NUMBER,
"PURGE_DONE" NUMBER,
"HAS_CHILD" NUMBER,
"ERROR1" NUMBER,
"STATUS" NUMBER,
"CREATE_DATE" DATE,
"TS1" NUMBER,
"TS2" NUMBER,
"GAP_NEXT_SCN" NUMBER,
"GAP_NEXT_TIME" NUMBER,
"CURSCN_TIME" NUMBER,
"RESETLOGS_SCN" NUMBER NOT NULL ENABLE,
"PRESETLOGS_SCN" NUMBER NOT NULL ENABLE,
"GAP_RET2" NUMBER,
"CURLOG" NUMBER,
"ENDIAN" NUMBER,
"ENQIDX" NUMBER,
"SPARE4" NUMBER,
"SPARE5" DATE,
"SPARE6" VARCHAR2(65),
"SPARE7" VARCHAR2(129),
"TS3" NUMBER,
"CURBLKNO" NUMBER,
"SPARE8" NUMBER,
"SPARE9" NUMBER,
"SPARE10" NUMBER,
"SPARE11" NUMBER,
"SPARE12" NUMBER
) PCTFREE 10 PCTUSED 40 INITRANS 1 MA
XTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS
1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLA
SH_CACHE DEFAULT)
TABLESPACE "SYSAUX"
SQL>
索引: [表: REDO_DB, 索引: REDO_DB_IDX
CREATE INDEX "SYSTEM"."REDO_DB_IDX" ON "SYSTEM"."REDO_DB" ("DBID", "THREAD#", "RESETLOGS_SCN", "RESETLOGS_TIME")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSAUX"
SQL> SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
$ ls -lrt
total 2988032
-rwxr-xr-x 1 oracle oinstall 695 Jan 1 00:28 getDDL.sh
-rw-r--r-- 1 oracle oinstall 18964 Jan 1 00:34 SYSTEM.REDO_DB.log
$ cat SYSTEM.REDO_DB.log
SQL> set serveroutput on
SQL> set long 9999
SQL> set pagesize 0
SQL> set line 280
SQL> set feedback off
SQL> set heading off
SQL> set echo off
SQL> select dbms_metadata.get_ddl('TABLE',upper('REDO_DB'),upper('SYSTEM')) from dual;
CREATE TABLE "SYSTEM"."REDO_DB" SHARING=METADATA
( "DBID" NUMBER NOT NULL ENABLE,
"GLOBAL_DBNAME" VARCHAR2(129),
"DBUNAME" VARCHAR2(32),
"VERSION" VARCHAR2(32),
"THREAD#" NUMBER NOT NULL ENABLE,
"RESETLOGS_SCN_BAS" NUMBER,
"RESETLOGS_SCN_WRP" NUMBER,
"RESETLOGS_TIME" NUMBER NOT NULL ENABLE,
"PRESETLOGS_SCN_BAS" NUMBER,
"PRESETLOGS_SCN_WRP" NUMBER,
"PRESETLOGS_TIME" NUMBER NOT NULL ENABLE,
"SEQNO_RCV_CUR" NUMBER,
"SEQNO_RCV_LO" NUMBER,
"SEQNO_RCV_HI" NUMBER,
"SEQNO_DONE_CUR" NUMBER,
"SEQNO_DONE_LO" NUMBER,
"SEQNO_DONE_HI" NUMBER,
"GAP_SEQNO" NUMBER,
"GAP_RET" NUMBER,
"GAP_DONE" NUMBER,
"APPLY_SEQNO" NUMBER,
"APPLY_DONE" NUMBER,
"PURGE_DONE" NUMBER,
"HAS_CHILD" NUMBER,
"ERROR1" NUMBER,
"STATUS" NUMBER,
"CREATE_DATE" DATE,
"TS1" NUMBER,
"TS2" NUMBER,
"GAP_NEXT_SCN" NUMBER,
"GAP_NEXT_TIME" NUMBER,
"CURSCN_TIME" NUMBER,
"RESETLOGS_SCN" NUMBER NOT NULL ENABLE,
"PRESETLOGS_SCN" NUMBER NOT NULL ENABLE,
"GAP_RET2" NUMBER,
"CURLOG" NUMBER,
"ENDIAN" NUMBER,
"ENQIDX" NUMBER,
"SPARE4" NUMBER,
"SPARE5" DATE,
"SPARE6" VARCHAR2(65),
"SPARE7" VARCHAR2(129),
"TS3" NUMBER,
"CURBLKNO" NUMBER,
"SPARE8" NUMBER,
"SPARE9" NUMBER,
"SPARE10" NUMBER,
"SPARE11" NUMBER,
"SPARE12" NUMBER
) PCTFREE 10 PCTUSED 40 INITRANS 1 MA
XTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS
1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLA
SH_CACHE DEFAULT)
TABLESPACE "SYSAUX"
SQL> declare
2 ITEXT char(9999);
3 begin
4 for curidx in (select index_name,table_name from dba_indexes where table_name=upper('REDO_DB') and table_owner=upper('SYSTEM')) loop
5 dbms_output.put_line('索引: [表: '||curidx.table_name||', 索引: '||curidx.index_name);
6 select dbms_metadata.get_ddl('INDEX',curidx.index_name,'SYSTEM') into ITEXT from dual;
7 dbms_output.put_line(ITEXT);
8 end loop;
9 end;
10 /
索引: [表: REDO_DB, 索引: REDO_DB_IDX
CREATE INDEX "SYSTEM"."REDO_DB_IDX" ON "SYSTEM"."REDO_DB" ("DBID", "THREAD#", "RESETLOGS_SCN", "RESETLOGS_TIME")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSAUX"
SQL> spool off
