Oracle 12cR1 模式DDL与数据按序导出方案咨询

Got it, since you're working with an Oracle 12cR1 schema and only have regular schema-level permissions (no SYS or elevated access), here's how you can generate DDL and data copies using SQL Developer or other tools, along with some manual fallback options:

一、使用SQL Developer（推荐） #

SQL Developer is the easiest way to handle this without extra tools, and it works perfectly with your permission set.

1. 导出模式DDL #

• Fire up SQL Developer and connect to your schema using your credentials.
• In the left Navigator pane, find your schema, right-click it, and select Export.
• In the Export Wizard, switch to the DDL tab:
  • Check the object types you need to export (tables, views, procedures, functions, packages, constraints, indexes, etc.).
  • Head to the Options section to tweak settings: include comments, generate DROP statements before CREATE, or exclude storage clauses if you don't need them.
  • Choose your export destination (local file, clipboard, or even another database if you have access) and click Finish. You’ll get a complete DDL script for your schema objects.

2. 导出数据副本 #

• Still in SQL Developer, right-click your schema again and select Export.
• Switch to the Data tab:
  • Check the tables you want to export data from.
  • Pick your export format:
    • SQL Insert Statements: Great for small datasets—generates INSERT commands you can run directly in another schema. You can enable batch inserts or add COMMIT statements here for efficiency.
    • CSV/JSON: Better for large datasets; you can import these files later using SQL Developer’s Import tool or SQL*Loader.
  • Configure any additional settings (like filtering rows if you don’t need all data) and click Finish to get your data export.

二、第三方工具替代方案（比如PL/SQL Developer） #

If you prefer another tool, PL/SQL Developer works similarly with your permissions:

• Connect to your schema, right-click the schema name, and select Export User Objects to generate full DDL.
• To export data, right-click individual tables (or use the Export Data wizard from the Tools menu) and choose to generate INSERT scripts or other formats like Excel/CSV.

三、手动生成DDL（无工具时的 fallback） #

If you can’t use GUI tools, you can query Oracle’s data dictionary views or use the DBMS_METADATA package (you have execute permissions for schema-level objects, so this should work):

1. 生成表、约束、索引的DDL #

-- Get DDL for all tables in your schema
SELECT DBMS_METADATA.GET_DDL('TABLE', table_name) FROM USER_TABLES;

-- Get DDL for all constraints
SELECT DBMS_METADATA.GET_DDL('CONSTRAINT', constraint_name) FROM USER_CONSTRAINTS;

-- Get DDL for all indexes
SELECT DBMS_METADATA.GET_DDL('INDEX', index_name) FROM USER_INDEXES;

2. 生成存储过程、函数、包的DDL #

-- Procedures
SELECT DBMS_METADATA.GET_DDL('PROCEDURE', object_name) 
FROM USER_PROCEDURES 
WHERE object_type = 'PROCEDURE';

-- Functions
SELECT DBMS_METADATA.GET_DDL('FUNCTION', object_name) 
FROM USER_PROCEDURES 
WHERE object_type = 'FUNCTION';

-- Packages (spec and body)
SELECT DBMS_METADATA.GET_DDL('PACKAGE', object_name) 
FROM USER_OBJECTS 
WHERE object_type = 'PACKAGE';

SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY', object_name) 
FROM USER_OBJECTS 
WHERE object_type = 'PACKAGE BODY';

3. 手动导出数据（小表适用） #

For small tables, you can generate INSERT statements manually with a query like this (replace YOUR_TABLE with your table name):

SELECT 'INSERT INTO YOUR_TABLE (' || LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY column_id) || ') VALUES (' ||
       LISTAGG(CASE 
                 WHEN data_type IN ('VARCHAR2', 'CHAR', 'DATE') THEN '''' || REPLACE(column_value, '''', '''''') || ''''
                 ELSE column_value
               END, ', ') WITHIN GROUP (ORDER BY column_id) || ');'
FROM (
  SELECT column_name, column_id, 
         TO_CHAR(COLUMN_VALUE) AS column_value
  FROM YOUR_TABLE t,
       TABLE(DBMS_XMLGEN.GETXMLTYPE('SELECT * FROM YOUR_TABLE WHERE ROWID = ''' || t.ROWID || '''').EXTRACT('/ROWSET/ROW/*'))
)
GROUP BY column_id;

Note: This works best for small datasets—for large tables, stick to GUI tool exports.

四、关键注意事项 #

• Avoid including cross-schema objects (like synonyms to other schemas or grants from high-level users) in your DDL—you won’t have permission to create these in a new environment.
• When importing, run the DDL script first to create all objects, then run the data import script to populate tables.
• If you’re migrating to a different Oracle version, double-check for compatibility (e.g., 12c-specific features that might not work in older versions).

内容的提问来源于stack exchange，提问作者Duncan_McCloud