十、序列/索引差异 比对结果后的创建语句 (例如:将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'-->存储状态'无效';
十三、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;
--> 等同于
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恢复进度