Oracle 数据库对象小结

得到创建数据对象的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; 

--检查对象的依赖及被依赖性

--数据库日常操作中，经常要对object做操作，但是数据库中object是有依赖性的，

--如果一个不小心，就可能导致对象失效，所以在做这种操作的时候，要特别注意，

--下面提供两个脚本，来检测对象的依赖性以及被依赖性：

    

--查出某个object依赖的其他对象,比如一个VIEW,用该SQL可以查出这个视图所依赖的表啊什么的

SELECT a.referenced_type AS type,

SUBSTR(a.referenced_owner,1,10) AS ref_owner,

a.referenced_name AS ref_name,

SUBSTR(a.referenced_link_name,1,20) AS ref_link_name

FROM all_dependencies a

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

AND a.owner = DECODE(UPPER('&owner'), 'ALL', a.referenced_owner, UPPER('&owner'))

ORDER BY 1,2,3;

--查出某个object被哪些对象依赖,就是检查你要修改的object被哪些对象引用了等等.

SELECT a.type,

SUBSTR(a.owner,1,10) AS owner,

a.name

FROM  all_dependencies a

WHERE  a.referenced_name  = UPPER('&object_name')

AND a.referenced_owner = DECODE(UPPER('&owner'), 'ALL', a.referenced_owner, UPPER('&owner'))

ORDER BY 1,2,3;

--查看数据库对象是否被锁

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

set wrap off

set linesize 200

col username for a10

col logon_time for a20

col lock_level for a10

col owner for a10

col object_name for a30

col object_type for a15

col status for a10

col program for a30

col osuser for a10

SELECT /*+ rule */ s.username,s.logon_time,

decode(l.type,'TM','TABLE LOCK','TX','ROW LOCK',NULL) "LOCK_LEVEL",

o.owner,o.object_name,o.object_type,

s.sid,s.serial#,p.spid,s.status,s.program,s.osuser

FROM v$session s,v$lock l,dba_objects o,v$process p

WHERE l.sid=s.sid AND p.addr=s.paddr and o.owner='&object_owner' and o.object_name='&object_name' and l.id1=o.object_id(+) AND s.username is NOT Null;