Oracle关于对象 得到数据库对象的DDL语句& 编译失效的数据库对象

得到创建数据对象的DDL语句

--用户：得到创建用户的DDL语句

SELECT DBMS_METADATA.GET_DDL('USER',U.username) 

FROM DBA_USERS U;

Select dbms_metadata.get_dll('USER','USERNAME') from dual;

补充：

--输出为CLOB类型，请在PL\SQL里执行

--导出用户对象权限

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', '&USERNAME') FROM DUAL;

--导出用户系统权限

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', '&USERNAME') FROM DUAL;

--导出用户角色权限

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', '&USERNAME') FROM DUAL;

--导出用户默认角色权限

SELECT DBMS_METADATA.GET_GRANTED_DDL('DEFAULT_ROLE', '&USERNAME') FROM DUAL;

--导出用户表空间配额权限

SELECT DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA', '&USERNAME') FROM DUAL;

--导出用户代理权限

SELECT DBMS_METADATA.GET_GRANTED_DDL('PROXY', '&USERNAME') FROM DUAL;

--表:得到当前用户创建表的DDL语句

SET SERVEROUTPUT ON

SET LINESIZE 100 

SET FEEDBACK OFF 

set long 999999

SET PAGESIZE 0

--方式一

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name,'T2')

FROM dba_tables u

where u.table_name like 'QRY_MONI_LOG_2009%' or u.table_name like 'QRY_TOUCH_LOG%2009%';

--方式二

SQL> select dbms_metadata.get_ddl('TABLE','T2') from dual;

DBMS_METADATA.GET_DDL('TABLE','T2')

--------------------------------------------------------------------------------

  CREATE TABLE "SYS"."T2"

   (    "ID" NUMBER

   ) PCTFREE 10 PCTUSED 40 INITRANS

--索引:创建当前用户所有创建索引的的DDL语句

SET SERVEROUTPUT ON

SET LINESIZE 100 

SET FEEDBACK OFF 

set long 999999

SET PAGESIZE 0

SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name,'AICBS')

FROM dba_indexes u

where u.table_name like 'QRY_MONI_LOG_2009%' or u.table_name like 'QRY_TOUCH_LOG%2009%';

补充：去掉参数

exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PRETTY', true);

exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES', true);

exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE', false);

exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'TABLESPACE', true); 

exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'CONSTRAINTS', false);

exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'REF_CONSTRAINTS', false);

exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'CONSTRAINTS_AS_ALTER', false);

 编译失效的数据库对象

--批量编译失效的包或包体

select 'alter package '||'&owner'||'.'||object_name||' compile;' 

from dba_objects 

where owner='&owner' and object_type in ('PACKAGE BODY','PACKAGE') and status='INVALID';

--

alter package owner.object_name compile;

--批量编译失效的过程

select 'alter procedure '||'&owner'||'.'||object_name||' compile;' 

from dba_objects 

where owner='&owner' and object_type='PROCEDURE' and status='INVALID';

--

alter procedure owner.object_name compile;

--批量编译失效的视图

select 'alter view '||'&owner'||'.'||object_name||' compile;' 

from dba_objects 

where owner='&owner' and object_type='VIEW' and status='INVALID';

--

alter view owner.object_name compile;

---批量删除失效的视图

select 'drop view '||'&owner'||'.'||object_name||';' 

from dba_objects 

where owner='&owner' and object_type='VIEW' and status='INVALID';

--

drop view owner.object_name ;

补充：

--检查编译错误

--在PL/SQL下，对于失效存储过程等的编译，如果出错了，

--我们可以很方便的看到在错误在什么地方，

--但是，如果没有图形环境，仅在SQLPLUS下，知道对象名，

--要查它在什么地方出错，就要用到下面的脚本了

SELECT a.name,a.type,'Line :'||a.line||':'||a.text||' - '||b.text error    

FROM   dba_source a,

       dba_errors b

WHERE  a.name = Upper('&object_name')

AND    a.name = b.name

AND    a.type = b.type

AND    a.line = b.line

ORDER BY a.name, a.line;

--扩展异常的对象

select segment_name,segment_type,tablespace_name, 

(extents/max_extents)*100 Percent from sys.DBA_segments 

where max_extents!=0 and (extents/max_extents)*100>=95 

order by percent;