DBA 常用SQL

1. 如何查找数据对象  如package

select object_name, OWNER from all_objects where object_name like '%GSB%' and  object_type='PACKAGE';

SQL> select distinct object_type from all_objects;

OBJECT_TYPE
------------------
CLUSTER
CONSUMER GROUP
EVALUATION CONTEXT
FUNCTION
INDEX
INDEX PARTITION
LIBRARY
LOB
OPERATOR
PACKAGE
PACKAGE BODY
PROCEDURE
QUEUE
RESOURCE PLAN
SEQUENCE
SYNONYM
TABLE
TABLE PARTITION
TRIGGER
TYPE
TYPE BODY
VIEW

2. tablespace 剩余空间查询

SELECT F.TABLESPACE_NAME,

TO_CHAR ((T.TOTAL_SPACE - F.FREE_SPACE),'999,999') "USED (MB)",

TO_CHAR (F.FREE_SPACE, '999,999') "FREE (MB)",

TO_CHAR (T.TOTAL_SPACE, '999,999') "TOTAL (MB)",

TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),'999')||' %' PER_FREE

FROM (

SELECT TABLESPACE_NAME,

ROUND (SUM (BLOCKS*(SELECT VALUE/1024

FROM V$PARAMETER

WHERE NAME = 'db_block_size')/1024)

) FREE_SPACE

FROM DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME

) F,

(

SELECT TABLESPACE_NAME,

ROUND (SUM (BYTES/1048576)) TOTAL_SPACE

FROM DBA_DATA_FILES

GROUP BY TABLESPACE_NAME

) T

WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME；

3.0检查无效的对象

 

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE STATUS = 'INVALID' ORDER BY OWNER, OBJECT_TYPE, OBJECT_NAME;

4. 检查死锁对象：

SELECT SID, DECODE(BLOCK, 0, 'NO', 'YES' ) BLOCKER,
DECODE(REQUEST, 0, 'NO','YES' ) WAITER
FROM V$LOCK
WHERE REQUEST > 0 OR BLOCK > 0
ORDER BY block D

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/223555/viewspace-498112/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/223555/viewspace-498112/