PACKAGE的get_ddl过程将返回spec AND body,因此最好更改all_objects上的查询,以便select不返回包体 .
到目前为止,我将查询更改为:
SELECT DBMS_METADATA.GET_DDL(REPLACE(object_type, ' ', '_'), object_name, owner)
FROM all_OBJECTS
WHERE (OWNER = 'OWNER1')
and object_type not like '%PARTITION'
and object_type not like '%BODY'
order by object_type, object_name;
sqlplus<<EOF
set long 100000
set head off
set echo off
set pagesize 0
set verify off
set feedback off
spool schema.out
select dbms_metadata.get_ddl(object_type, object_name, owner)
from
(
--Convert DBA_OBJECTS.OBJECT_TYPE to DBMS_METADATA object type:
select
owner,
--Java object names may need to be converted with DBMS_JAVA.LONGNAME.
--That code is not included since many database don't have Java installed.
object_name,
decode(object_type,
'DATABASE LINK', 'DB_LINK',
'JOB', 'PROCOBJ',
'RULE SET', 'PROCOBJ',
'RULE', 'PROCOBJ',
'EVALUATION CONTEXT', 'PROCOBJ',
'CREDENTIAL', 'PROCOBJ',
'CHAIN', 'PROCOBJ',
'PROGRAM', 'PROCOBJ',
'PACKAGE', 'PACKAGE_SPEC',
'PACKAGE BODY', 'PACKAGE_BODY',
'TYPE', 'TYPE_SPEC',
'TYPE BODY', 'TYPE_BODY',
'MATERIALIZED VIEW', 'MATERIALIZED_VIEW',
'QUEUE', 'AQ_QUEUE',
'JAVA CLASS', 'JAVA_CLASS',
'JAVA TYPE', 'JAVA_TYPE',
'JAVA SOURCE', 'JAVA_SOURCE',
'JAVA RESOURCE', 'JAVA_RESOURCE',
'XML SCHEMA', 'XMLSCHEMA',
object_type
) object_type
from dba_objects
where owner in ('OWNER1')
--These objects are included with other object types.
and object_type not in ('INDEX PARTITION','INDEX SUBPARTITION',
'LOB','LOB PARTITION','TABLE PARTITION','TABLE SUBPARTITION')
--Ignore system-generated types that support collection processing.
and not (object_type = 'TYPE' and object_name like 'SYS_PLSQL_%')
--Exclude nested tables, their DDL is part of their parent table.
and (owner, object_name) not in (select owner, table_name from dba_nested_tables)
--Exclude overflow segments, their DDL is part of their parent table.
and (owner, object_name) not in (select owner, table_name from dba_tables where iot_type = 'IOT_OVERFLOW')
)
order by owner, object_type, object_name;
spool off
quit
EOF
cat schema.out|sed 's/OWNER1/MYOWNER/g'>schema.out.change.sql
SELECT OBJECT_TYPE, OBJECT_NAME,DBMS_METADATA.GET_DDL(OBJECT_TYPE, OBJECT_NAME, OWNER)
FROM ALL_OBJECTS
WHERE (OWNER = 'XYZ') AND OBJECT_TYPE NOT IN('LOB','MATERIALIZED VIEW', 'TABLE PARTITION') ORDER BY OBJECT_TYPE, OBJECT_NAME;
2
PACKAGE_BODY等对象存在问题:
SELECT DBMS_METADATA.get_ddl(object_Type, object_name, owner) FROM ALL_OBJECTS WHERE OWNER = 'WEBSERVICE';
ORA-31600 invalid input value PACKAGE BODY parameter OBJECT_TYPE in function GET_DDL
ORA-06512: на "SYS.DBMS_METADATA", line 4018
ORA-06512: на "SYS.DBMS_METADATA", line 5843
ORA-06512: на line 1
31600. 00000 - "invalid input value %s for parameter %s in function %s"
*Cause: A NULL or invalid value was supplied for the parameter.
*Action: Correct the input value and try the call again.
SELECT DBMS_METADATA.GET_DDL(REPLACE(object_type,' ','_'), object_name, owner)
FROM all_OBJECTS
WHERE (OWNER = 'OWNER1');
set long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on
column ddl format a1000
begin
dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
end;
/
variable v_username VARCHAR2(30);
exec:v_username := upper('&1');
select dbms_metadata.get_ddl('USER', u.username) AS ddl
from dba_users u
where u.username = :v_username
union all
select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', tq.username) AS ddl
from dba_ts_quotas tq
where tq.username = :v_username
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee) AS ddl
from dba_role_privs rp
where rp.grantee = :v_username
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', sp.grantee) AS ddl
from dba_sys_privs sp
where sp.grantee = :v_username
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', tp.grantee) AS ddl
from dba_tab_privs tp
where tp.grantee = :v_username
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('DEFAULT_ROLE', rp.grantee) AS ddl
from dba_role_privs rp
where rp.grantee = :v_username
and rp.default_role = 'YES'
and rownum = 1
union all
select to_clob('/* Start profile creation script in case they are missing') AS ddl
from dba_users u
where u.username = :v_username
and u.profile <> 'DEFAULT'
and rownum = 1
union all
select dbms_metadata.get_ddl('PROFILE', u.profile) AS ddl
from dba_users u
where u.username = :v_username
and u.profile <> 'DEFAULT'
union all
select to_clob('End profile creation script */') AS ddl
from dba_users u
where u.username = :v_username
and u.profile <> 'DEFAULT'
and rownum = 1
/
set linesize 80 pagesize 14 feedback on trimspool on verify on
6 回答
PACKAGE的get_ddl过程将返回spec AND body,因此最好更改all_objects上的查询,以便select不返回包体 .
到目前为止,我将查询更改为:
虽然可能需要进行其他更改,具体取决于您获得的对象类型...
您可以通过SQL * Plus和dbms_metadata包将模式假脱机到文件 . 然后通过sed将模式名称替换为另一个模式名称 . 这适用于Oracle 10及更高版本 .
将所有内容放在脚本中并通过cron(scheduler)运行 . 使用高级功能时,导出对象可能会非常棘手 . 如果您需要为上述代码添加更多例外,请不要感到惊讶 .
如果要为每个对象单独生成ddl,
查询是:
--GENERATE DDL FOR ALL USER OBJECTS
--1. FOR ALL TABLES
--2. FOR ALL INDEXES
--3. FOR ALL VIEWS
要么
--4. FOR ALL MATERILIZED VIEWS
--5. FOR ALL FUNCTION
================================================== =============================================
GET_DDL 函数不支持某些object_type,如LOB,MATERIALIZED VIEW,TABLE PARTITION
因此,生成DDL的合并查询将是:
PACKAGE_BODY等对象存在问题:
首先导出模式元数据,然后使用sqlfile选项导入(它不会导入数据,只是它会将模式的ddl写入该文件)
示例: -
expdp dumpfile=filename logfile=logname directory=dir name schemas=schema name
username:/ as sysdba
impdp dumpfile=filename logfile=logname directory=dir sqlfile=sql.log
username:/ as sysdba
要为整个 SCHEMA (即 USER )生成DDL脚本,可以使用 dbms_metadata.get_ddl .
在Tim Hall创建的 SQL*Plus 中执行以下脚本:
出现提示时提供 username .