SQL 如何在 oracle 中的现有表上生成（或获取）ddl 脚本？我必须在 Hive 中重新创建它们
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26249892/
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 can I generate (or get) a ddl script on an existing table in oracle? I have to re-create them in Hive
提问by VSJ
How can I generate a DDL script on an existing table in oracle? I am working on a project where I have to re-create some tables that are present in Oracle table into Hive.
如何在 oracle 中的现有表上生成 DDL 脚本？我正在从事一个项目，我必须将 Oracle 表中存在的一些表重新创建到 Hive 中。
回答by a_horse_with_no_name
If your SQL client doesn't support this, then you can use the dbms_metadatapackage to get the source for nearly everything in your database:
如果您的 SQL 客户端不支持此功能，那么您可以使用该dbms_metadata包来获取数据库中几乎所有内容的源代码：
For a table use something like this:
对于表，请使用以下内容：
select dbms_metadata.get_ddl('TABLE', 'YOUR_TABLE_NAME')
from dual;
You can also do this for all tables at once:
您也可以一次对所有表执行此操作：
select dbms_metadata.get_ddl('TABLE', table_name)
from user_tables;
and spool the output into a SQL script.
并将输出假脱机到 SQL 脚本中。
More details are in the manual: http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_metada.htm
手册中有更多详细信息：http: //docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_metada.htm
回答by Lalit Kumar B
Just expanding a bit on @a_horse_with_no_name's answer. Using DBMS_METADATA, you might have to take care of the format in SQL*Plusin order to get the output properly.
只是在@a_horse_with_no_name 的答案上稍微扩展一下。使用DBMS_METADATA，您可能必须注意格式SQL*Plus才能正确获得输出。
For example, I want to get the DDLfor SCOTT.EMPtable.
例如，我想获取DDLforSCOTT.EMP表。
SQL> select dbms_metadata.get_ddl('TABLE', 'EMP')
2 from dual;
DBMS_METADATA.GET_DDL('TABLE','EMP')
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
SQL>
But, that is not what I expected.
但是，这不是我所期望的。
So, setting up the format properly, would give me my desired output
所以，正确设置格式，会给我我想要的输出
SQL> set long 100000
SQL> set head off
SQL> set echo off
SQL> set pagesize 0
SQL> set verify off
SQL> set feedback off
SQL> select dbms_metadata.get_ddl('TABLE', 'EMP')
2 from dual;
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX 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 "USERS" ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 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_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
SQL>
