如何在 SQL*Plus 中获取 Oracle 创建表语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/937398/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get Oracle create table statement in SQL*Plus
提问by thursdaysgeek
I have a table that exists in an Oracle database, but doesn't show on my list of tables in the tool SQL Developer. However, if I go to SQL*Plus, and do a
我有一个存在于 Oracle 数据库中的表，但没有显示在工具 SQL Developer 的表列表中。但是，如果我去 SQL*Plus，并做一个
select table_name from user_tables;
I get the table listed. If I type
我得到了列出的表。如果我输入
desc snp_clearinghouse;
it shows me the fields. I'd like to get the create statement, because I need to add a field. I can modify the table to add the field, but I still need the create statement to put into our source control. What pl/sql statement is used to get the create statement for a table?
它向我展示了字段。我想获得 create 语句，因为我需要添加一个字段。我可以修改表来添加字段，但我仍然需要将 create 语句放入我们的源代码管理中。什么 pl/sql 语句用于获取表的 create 语句？
回答by cletus
From Get table and index DDL the easy way:
set heading off;
set echo off;
Set pages 999;
set long 90000;
spool ddl_list.sql
select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual;
select dbms_metadata.get_ddl('INDEX','DEPT_IDX','SCOTT') from dual;
spool off;
回答by Gary Thomann
Same as above but generic script found here gen_create_table_script.sql
与上面相同，但在此处找到了通用脚本 gen_create_table_script.sql
-- #############################################################################################
--
-- %Purpose: Generate 'CREATE TABLE' Script for an existing Table in the database
--
-- Use: SYSTEM, SYS or user having SELECT ANY TABLE system privilege
--
-- #############################################################################################
--
set serveroutput on size 200000
set echo off
set feedback off
set verify off
set showmode off
--
ACCEPT l_user CHAR PROMPT 'Username: '
ACCEPT l_table CHAR PROMPT 'Tablename: '
--
DECLARE
CURSOR TabCur IS
SELECT table_name,owner,tablespace_name,
initial_extent,next_extent,
pct_used,pct_free,pct_increase,degree
FROM sys.dba_tables
WHERE owner=upper('&&l_user')
AND table_name=UPPER('&&l_table');
--
CURSOR ColCur(TableName varchar2) IS
SELECT column_name col1,
DECODE (data_type,
'LONG', 'LONG ',
'LONG RAW', 'LONG RAW ',
'RAW', 'RAW ',
'DATE', 'DATE ',
'CHAR', 'CHAR' || '(' || data_length || ') ',
'VARCHAR2', 'VARCHAR2' || '(' || data_length || ') ',
'NUMBER', 'NUMBER' ||
DECODE (NVL(data_precision,0),0, ' ',' (' || data_precision ||
DECODE (NVL(data_scale, 0),0, ') ',',' || DATA_SCALE || ') '))) ||
DECODE (NULLABLE,'N', 'NOT NULL',' ') col2
FROM sys.dba_tab_columns
WHERE table_name=TableName
AND owner=UPPER('&&l_user')
ORDER BY column_id;
--
ColCount NUMBER(5);
MaxCol NUMBER(5);
FillSpace NUMBER(5);
ColLen NUMBER(5);
--
BEGIN
MaxCol:=0;
--
FOR TabRec in TabCur LOOP
SELECT MAX(column_id) INTO MaxCol FROM sys.dba_tab_columns
WHERE table_name=TabRec.table_name
AND owner=TabRec.owner;
--
dbms_output.put_line('CREATE TABLE '||TabRec.table_name);
dbms_output.put_line('( ');
--
ColCount:=0;
FOR ColRec in ColCur(TabRec.table_name) LOOP
ColLen:=length(ColRec.col1);
FillSpace:=40 - ColLen;
dbms_output.put(ColRec.col1);
--
FOR i in 1..FillSpace LOOP
dbms_output.put(' ');
END LOOP;
--
dbms_output.put(ColRec.col2);
ColCount:=ColCount+1;
--
IF (ColCount < MaxCol) THEN
dbms_output.put_line(',');
ELSE
dbms_output.put_line(')');
END IF;
END LOOP;
--
dbms_output.put_line('TABLESPACE '||TabRec.tablespace_name);
dbms_output.put_line('PCTFREE '||TabRec.pct_free);
dbms_output.put_line('PCTUSED '||TabRec.pct_used);
dbms_output.put_line('STORAGE ( ');
dbms_output.put_line(' INITIAL '||TabRec.initial_extent);
dbms_output.put_line(' NEXT '||TabRec.next_extent);
dbms_output.put_line(' PCTINCREASE '||TabRec.pct_increase);
dbms_output.put_line(' )');
dbms_output.put_line('PARALLEL '||TabRec.degree);
dbms_output.put_line('/');
END LOOP;
END;
/
