oracle dba必会,Oracle DBA常用sql分享

十、序列/索引差异 比对结果后的创建语句 (例如：将A用户index和B用户对比，将A用户多B用户的在B用户创建)

【如下2个SQL都需要在 缺少sequence/index A用户执行】

--#SEQUENCE的创建语句：

SELECT 'CREATE SEQUENCE ' || SEQUENCE_NAME || ' MINVALUE ' || MIN_VALUE ||

' MAXVALUE ' || MAX_VALUE || ' START WITH ' || LAST_NUMBER ||

' INCREMENT BY ' || INCREMENT_BY || (CASE

WHEN CACHE_SIZE = 0 THEN

' NOCACHE'

ELSE

' CACHE ' || CACHE_SIZE

END ) || ';'

FROM USER_SEQUENCES W

WHERE --过滤掉登录用户存在的SEQUENCE

NOT EXISTS ( SELECT 1

FROM USER_SEQUENCES@DB_SINOSOFT W1

WHERE W.SEQUENCE_NAME = W1.SEQUENCE_NAME);

--#索引差异 结果的创建语句

SELECT 'CREATE ' || INDEX_TYPE || ' INDEX ' || INDEX_NAME || ' ON ' ||

TABLE_NAME || ' (' || LISTAGG(CNAME, ',' ) WITHIN GROUP (ORDER BY COLUMN_POSITION) || ');'

FROM (SELECT IC.INDEX_NAME,

IC.TABLE_NAME,

IC.COLUMN_NAME CNAME,

IC.COLUMN_POSITION,

COUNT(IC.INDEX_NAME) OVER ( PARTITION BY IC.INDEX_NAME, IC.TABLE_NAME) CON,

I.INDEX_TYPE

FROM USER_IND_COLUMNS@DB_SINOSOFT IC

JOIN USER_INDEXES@DB_SINOSOFT I

ON I.INDEX_NAME = IC.INDEX_NAME

WHERE

--过滤掉登录用户存在的INDEX

NOT EXISTS

( SELECT 1

FROM USER_IND_COLUMNS IC1

WHERE IC1.INDEX_OWNER = UPPER ( '&TO_USERNAME')

AND IC.INDEX_NAME = IC1.INDEX_NAME)

--过滤掉主键，避免索引创建，在创建主键报错 对象已存在

AND IC.INDEX_NAME NOT IN

( SELECT C.CONSTRAINT_NAME FROM USER_CONSTRAINTS@DB_SINOSOFT C)

ORDER BY IC.INDEX_NAME, IC.COLUMN_POSITION)

GROUP BY INDEX_TYPE, CON, INDEX_NAME, TABLE_NAME;

十一、查看热点块的对象

SELECT A.HLADDR, A.FILE#, A.DBABLK, A.TCH, A.OBJ, B.OBJECT_NAME

FROM X$BH A, DBA_OBJECTS B

WHERE (A.OBJ = B.OBJECT_ID OR A.OBJ = B.DATA_OBJECT_ID)

AND A.HLADDR = '0000000054435000' --V$SESSION_WAIT.P1RAW

UNION

SELECT HLADDR, FILE#, DBABLK, TCH, OBJ, NULL

FROM X$BH

WHERE OBJ IN ( SELECT OBJ

FROM X$BH

WHERE HLADDR = '0000000054435000'

MINUS

SELECT OBJECT_ID

FROM DBA_OBJECTS

MINUS

SELECT DATA_OBJECT_ID FROM DBA_OBJECTS)

AND HLADDR = '0000000054435000'

ORDER BY 4;

十一、查看某用户表大小/总数情况

SELECT T.TABLE_NAME,

TC.COMMENTS,

T.NUM_ROWS,

ROUND (SUM (S.BYTES / 1024 / 1024 / 1024 )) GB

FROM USER_TABLES T

JOIN USER_SEGMENTS S

ON S.SEGMENT_NAME = T.TABLE_NAME

JOIN USER_TAB_COMMENTS TC

ON TC.TABLE_NAME = T.TABLE_NAME

GROUP BY T.TABLE_NAME, TC.COMMENTS, T.NUM_ROWS

ORDER BY NUM_ROWS DESC  NULLS LAST ;

十二、 重新编译失效存储/包语句：

SELECT 'ALTER ' || (CASE

WHEN OBJECT_TYPE = 'PACKAGE BODY' THEN

'PACKAGE'ELSE OBJECT_TYPE

END) || ' ' || OWNER || '.' || OBJECT_NAME || ' COMPILE ' || (CASE

WHEN OBJECT_TYPE = 'PACKAGE BODY' THEN

'BODY;' ELSE ';' END), --除类型是PACKAGE BODY返回是PACKAGE，其他正常显示类型，是PACKAGE BODY显示COMPILE BODY 否则显示COMPILE

OWNER,

OBJECT_NAME,

OBJECT_TYPE,

STATUS,

O.CREATED,

LAST_DDL_TIME

FROM DBA_OBJECTS O

WHERE STATUS = 'INVALID'--&gt存储状态'无效';

十三、Oracle 查看各表空间使用情况和最大最小块：

SELECT UPPER (F.TABLESPACE_NAME) "表空间名",

D.TOT_GROOTTE_MB "表空间大小(M)",

D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",

TO_CHAR( ROUND ((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100 ,

2 ),

'990.99' ) "使用比",

F.TOTAL_BYTES "空闲空间(G)",

F.MAX_BYTES "最大块(G)"

FROM (SELECT TABLESPACE_NAME,

ROUND (SUM (BYTES) / 1024 / 1024 / 1024 , 2) TOTAL_BYTES,

ROUND (MAX (BYTES) / 1024 / 1024 / 1024 , 2) MAX_BYTES

FROM SYS.DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME) F,

( SELECT DD.TABLESPACE_NAME,

ROUND (SUM (DD.BYTES) / 1024 / 1024 / 1024 , 2) TOT_GROOTTE_MB

FROM SYS.DBA_DATA_FILES DD

GROUP BY DD.TABLESPACE_NAME) D

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME;

十四、Oracle 查看TEMP表空间使用情况 ：

SELECT F.BYTES_FREE + F.BYTES_USED TOTAL_BYTES,

F.BYTES_FREE + F.BYTES_USED - NVL (P.BYTES_USED, 0 ) FREE_BYTES,

D.FILE_NAME,

NVL (P.BYTES_USED, 0 ) USED_BYTES

FROM SYS.V_$TEMP_SPACE_HEADER F,

DBA_TEMP_FILES           D,

SYS.V_$TEMP_EXTENT_POOL  P

WHERE F.TABLESPACE_NAME(+) = D.TABLESPACE_NAME

AND F.FILE_ID(+) = D.FILE_ID

AND P.FILE_ID(+) = D.FILE_ID;

--&gt 等同于

SELECT TABLESPACE_NAME,

TF.TABLESPACE_SIZE,

TF.FREE_SPACE,

TF.TABLESPACE_SIZE - TF.FREE_SPACE

FROM DBA_TEMP_FREE_SPACE TF;

十五、 Oracle 查看回滚进度情况用的几个SQL：

SELECT DISTINCT KTUXESIZ FROM X$KTUXE WHERE KTUXESTA = 'ACTIVE' ;

SELECT USED_UBLK FROM V$TRANSACTION;

SELECT KTUXEUSN, KTUXESLT

FROM X$KTUXE

WHERE /*KTUXECFL = 'DEAD' AND*/

KTUXESTA = 'ACTIVE' ;

SELECT * FROM V_$FAST_START_TRANSACTIONS;

SELECT USED_UBLK, T.USED_UREC FROM V$TRANSACTION T;

--查询视图V$FAST_START_TRANSACTIONS中字段UNDOBLOCKSDONE，UNDOBLOCKSTOTAL估算SMON恢复进度