Oracle - 如何从 sql 开发人员生成脚本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1564347/
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
Oracle - How to generate script from sql developer
提问by Raven
How to take script for schema of the tables, stored procedures of Oracle through SQL Developer tool (SQLPLUS command line interface)?
如何通过 SQL Developer 工具（SQLPLUS 命令行界面）为 Oracle 的表、存储过程的模式获取脚本？
回答by Guru
If you want to see DDL for the objects, you can use
如果要查看对象的 DDL，可以使用
select dbms_metadata.get_ddl('OBJECT_TYPE','OBJECT_NAME','OBJECT_OWNER')
from dual
/
For example this will give you the DDL script for emptable.
例如，这将为您提供emp表的 DDL 脚本。
select dbms_metadata.get_ddl('TABLE','EMP','HR')
from dual
/
You may need to set the longtype format to big number. For packages, you need to access dba_source, user_source, all_sourcetables. You can query for object name and type to see what code is stored.
您可能需要将long类型格式设置为大数字。对于包，您需要访问dba_source, user_source, all_source表。您可以查询对象名称和类型以查看存储的代码。
回答by Sudhir
This worked for me:
这对我有用：
- In SQL Developer, right click the object that you want to generate a script for. i.e. the table name
- Select Quick DLL > Save To File
- This will then write the create statement to an external sql file.
- 在 SQL Developer 中，右键单击要为其生成脚本的对象。即表名
- 选择快速 DLL > 保存到文件
- 然后，这会将 create 语句写入外部 sql 文件。
Note, you can also highlight multiple objects at the same time, so you could generate one script that contains create statements for all tables within the database.
请注意，您还可以同时突出显示多个对象，因此您可以生成一个脚本，其中包含数据库中所有表的 create 语句。
回答by psp
In Oracle the location that contains information about all database objects including tables and stored procedures is called the Data Dictionary. It is a collection of views that provides you with access to the metadata that defines the database. You can query the Data Dictionary views for a list of desired database objects and then use the functions available in dbms_metadatapackage to get the DDL for each object. Alternative is to investigate the support in dbms_metadatato export DDLs for a collection of objects.
在 Oracle 中，包含有关所有数据库对象（包括表和存储过程）的信息的位置称为数据字典。它是一组视图，可让您访问定义数据库的元数据。您可以查询数据字典视图以获取所需数据库对象的列表，然后使用dbms_metadata包中可用的函数来获取每个对象的 DDL。另一种方法是研究dbms_metadata对导出对象集合的 DDL的支持。
For a few pointers, for example to get a list of tables you can use the following Data Dictionary views
对于一些指针，例如要获取表列表，您可以使用以下数据字典视图
user_tablescontains all tables owned by the userall_tablescontains all tables that are accessible by the user- and so on...
user_tables包含用户拥有的所有表all_tables包含用户可以访问的所有表- 等等...
回答by theSTIG
This worked for me:
这对我有用：
PL SQL Developer -> Tools -> Export User Objects
PL SQL Developer -> 工具 -> 导出用户对象
Select checkboxes: Include privilege and Include storage
选择复选框：包括权限和包括存储
Select your file name. Hit export.
选择您的文件名。命中出口。
You can later use generated export file to create table in another schema.
您可以稍后使用生成的导出文件在另一个模式中创建表。
回答by shital
step 1. select * from <tablename>;
第 1 步。select * from <tablename>;
step 2. just right click on your output(t.e data) then go to last option export it will give u some extension then click on your required extension then apply u will get new file including data.
第 2 步。只需右键单击您的输出（数据），然后转到最后一个选项导出，它会给您一些扩展名，然后单击您需要的扩展名，然后应用您将获得包括数据的新文件。
回答by Jonathan Leffler
The basic answer appears to be 'use the dbms_metadata package'. The axuilliary question is:
基本答案似乎是“使用 dbms_metadata 包”。辅助问题是：
But what if I want to generate a script for all the tables at a time?
但是如果我想一次为所有表生成一个脚本怎么办？
And the answer, presumably, is to interrogate the system catalog for the names and owners of all the tables:
答案大概是查询系统目录以获取所有表的名称和所有者：
SELECT dbms_metadata.get_ddl('TABLE', s.tabname, s.tabowner)
FROM system_catalog_describing_tables AS s
WHERE ...any conditions that are needed...
I'm not sufficiently familiar with Oracle to know the system catalog. In Informix, which I do know, assuming that there was a procedure dbms_metadata.get_ddl, the query would be:
我对 Oracle 不够熟悉，无法了解系统目录。在我知道的 Informix 中，假设有一个 procedure dbms_metadata.get_ddl，查询将是：
SELECT dbms_metadata.get_ddl('TABLE', s.tabname, s.owner)
FROM "informix".systables AS s
WHERE tabid >= 100 AND tabtype = 'T';
In Informix, tabids less than 100 are reserved for the system catalog, and non-tables (views, synonyms, sequences and a few other esoteric things) are excluded by requiring the right 'tabtype'.
在 Informix 中，小于 100 的 tabid 被保留给系统目录，非表（视图、同义词、序列和一些其他深奥的东西）通过要求正确的“tabtype”被排除在外。
回答by devio
I did not know about DMBS_METADATA, but your answers prompted me to create a utility to script all objects owned by an Oracle user.
我不知道 DMBS_METADATA，但您的回答促使我创建一个实用程序来编写 Oracle 用户拥有的所有对象的脚本。
回答by Vu Nguyen
Oracle SQL Developer > View > DBA > Select your connection > Expand > Security > Users > Right click your user > Create like > Fill in fields > Copy SQL script > Close
Oracle SQL Developer > 查看 > DBA > 选择您的连接 > 展开 > 安全性 > 用户 > 右键单击您的用户 > 创建类似 > 填写字段 > 复制 SQL 脚本 > 关闭
If your user has object privileges, do this also
如果您的用户具有对象权限，也请执行此操作
Oracle SQL Developer > View > DBA > Select your connection > Expand > Security > Users > Double click your user > Object Privs > Select all data > Right click > Export > Export as text file
Oracle SQL Developer > 查看 > DBA > 选择您的连接 > 展开 > 安全 > 用户 > 双击您的用户 > 对象权限 > 选择所有数据 > 右键单击 > 导出 > 导出为文本文件
Edit that text file to grant object privileges to your user.
编辑该文本文件以向您的用户授予对象权限。
