柚子快報(bào)邀請(qǐng)碼778899分享：8.達(dá)夢(mèng)數(shù)據(jù)庫(kù)常用SQL
柚子快報(bào)邀請(qǐng)碼778899分享：8.達(dá)夢(mèng)數(shù)據(jù)庫(kù)常用SQL
文章目錄
前言1. 服務(wù)器資源1.1 CPU使用率1.2 內(nèi)存使用率
2 數(shù)據(jù)庫(kù)實(shí)例管理2.1 查詢版本號(hào)2.2 查詢ini配置2.3 查詢歸檔配置2.4 數(shù)據(jù)庫(kù)實(shí)例初始化參數(shù)2.5 查看數(shù)據(jù)庫(kù)信息2.6 查看數(shù)據(jù)庫(kù)實(shí)例信息2.7 查看數(shù)據(jù)庫(kù)實(shí)例信息2.8 查看授權(quán)信息2.9 查詢頁(yè)大小，字符集大小2.10 檢查點(diǎn)配置信息
3 數(shù)據(jù)對(duì)象管理3.1 查看系統(tǒng)所有的動(dòng)態(tài)視圖名稱3.2 查看系統(tǒng)提供的所有函數(shù)名稱3.3 查看表中數(shù)據(jù)的變化信息3.4 查看單個(gè)表占用空間大小3.5 查看數(shù)據(jù)對(duì)象的定義語(yǔ)句3.6 查看表的主鍵列3.7 查看數(shù)據(jù)庫(kù)中所有的索引信息3.8 統(tǒng)計(jì)數(shù)據(jù)庫(kù)中的所有的表數(shù)據(jù)量和DDL語(yǔ)句3.9 統(tǒng)計(jì)數(shù)據(jù)庫(kù)中所有的索引信息和DDL語(yǔ)句3.10 統(tǒng)計(jì)數(shù)據(jù)庫(kù)中所有的存儲(chǔ)過(guò)程和函數(shù)及DDL語(yǔ)句3.11 統(tǒng)計(jì)超過(guò)15%變化數(shù)據(jù)的表3.12 統(tǒng)計(jì)數(shù)據(jù)庫(kù)中所有的觸發(fā)器3.12.1 庫(kù)級(jí)觸發(fā)器3.12.2 模式級(jí)觸發(fā)器3.12.3 視圖級(jí)觸發(fā)器3.12.4 表級(jí)觸發(fā)器
4 用戶管理4.1 實(shí)例中用戶信息
5 表空間與數(shù)據(jù)文件管理5.1 回滾表空間5.1.1 顯示當(dāng)前 PURGE 回滾段信息5.1.2 查看回滾段信息5.1.3 查看每個(gè)回滾項(xiàng)信息5.1.4 查看回滾項(xiàng)中已提交但未 PURGE 的事務(wù)信息
5.2 普通表空間5.2.1 查看所有的數(shù)據(jù)文件使用情況5.2.2 查看表空間信息5.3 查看表空間中數(shù)據(jù)文件信息
6 日志與備份管理6.1 做數(shù)據(jù)庫(kù)的全量備份6.2 做數(shù)據(jù)庫(kù)的增量備份6.3 備份歷史信息6.4 對(duì)備份集進(jìn)行校驗(yàn)6.5 查看備份集及備份鏈信息6.6 刪除備份集6.7 批量刪除數(shù)據(jù)庫(kù)備份集6.8 查看歸檔配置信息6.9 查看redo日志文件信息6.10 查看redo日志使用信息6.11 查看歸檔日志文件的具體信息6.12 查看歸檔任務(wù)隊(duì)列情況
7 會(huì)話與事務(wù)管理7.1 查看活動(dòng)會(huì)話信息7.2 查看會(huì)話統(tǒng)計(jì)信息7.3 查看當(dāng)前活動(dòng)事務(wù)數(shù)量7.4 查看阻塞與被阻塞信息7.5 查看會(huì)話統(tǒng)計(jì)信息7.6 數(shù)據(jù)庫(kù)啟動(dòng)時(shí)的回滾的事務(wù)信息7.7 關(guān)閉一個(gè)活動(dòng)會(huì)話7.8 查詢已執(zhí)行完畢但未提交事務(wù)7.9 臟數(shù)據(jù)占比統(tǒng)計(jì)7.10 查看死鎖信息
8 調(diào)度作業(yè)管理8.1 正在運(yùn)行的作業(yè)信息8.2 執(zhí)行失敗的定時(shí)任務(wù)統(tǒng)計(jì)
9 性能診斷9.1 查看字典緩沖區(qū)使用情況9.2 查看數(shù)據(jù)緩沖區(qū)使用情況9.3 查看SQL緩沖區(qū)的使用情況9.4 查看內(nèi)存池的使用情況9.5 查看數(shù)據(jù)庫(kù)內(nèi)存的申請(qǐng)信息9.6 顯示支持的hint信息9.7 查看數(shù)據(jù)庫(kù)讀寫(xiě)統(tǒng)計(jì)數(shù)據(jù)9.8 收集統(tǒng)計(jì)信息9.8.1 收集user_name用戶下所有表的統(tǒng)計(jì)信息9.8.2 收集user_name用戶下某個(gè)表的統(tǒng)計(jì)信息9.8.3 收集某個(gè)表某列的統(tǒng)計(jì)信息9.8.4 收集user_name用戶下所有索引統(tǒng)計(jì)信息9.8.5 收集數(shù)據(jù)庫(kù)中某個(gè)索引的統(tǒng)計(jì)信息
10 SQL執(zhí)行相關(guān)10.1 SQL執(zhí)行歷史信息10.2 SQL語(yǔ)句執(zhí)行歷史報(bào)錯(cuò)信息10.3 DDL SQL執(zhí)行歷史信息10.4 SQL等待信息10.5 開(kāi)啟/關(guān)閉SQL日志10.6 查詢當(dāng)前數(shù)據(jù)庫(kù)中執(zhí)行時(shí)間超過(guò)1秒的SQL
11 MPP相關(guān)11.1 檢查各節(jié)點(diǎn)參數(shù)一致性
12 數(shù)據(jù)庫(kù)巡檢專用12.1 dm.ini關(guān)鍵參數(shù)12.2 授權(quán)信息12.3 數(shù)據(jù)庫(kù)實(shí)例信息12.4 表空間12.5 數(shù)據(jù)文件12.6 會(huì)話統(tǒng)計(jì)12.7 用戶對(duì)象信息12.8 最慢20條SQL
參考內(nèi)容
前言
本篇博客主要講一些 DM 數(shù)據(jù)庫(kù)常用 SQL
1. 服務(wù)器資源
1.1 CPU使用率
SELECT
(STAT_VAL/100.0) AS RATIO
FROM
SYS.V$SYSSTAT
WHERE
UPPER(NAME) ='OS DM DATABASE CPU RATE';
1.2 內(nèi)存使用率
SELECT
A.TOTAL_SIZE_BYTES/1024/1024 "總計(jì)分配內(nèi)存大小/GB",
B.USED_SIZE_BYTES/1024/1024 "當(dāng)前使用的內(nèi)存大小/GB",
(B.USED_SIZE_BYTES/CAST(decode(A.TOTAL_SIZE_BYTES, 0, 1, A.TOTAL_SIZE_BYTES) AS DOUBLE))*100 "使用率（%）"
FROM
(
SELECT
STAT_VAL TOTAL_SIZE_BYTES
FROM
SYS.V$SYSSTAT
WHERE
UPPER(NAME)='MEMORY POOL SIZE IN BYTES'
)
A,
(
SELECT
STAT_VAL USED_SIZE_BYTES
FROM
SYS.V$SYSSTAT
WHERE
UPPER(NAME)='MEMORY USED BYTES'
)
B;
2 數(shù)據(jù)庫(kù)實(shí)例管理
2.1 查詢版本號(hào)
select id_code from dual;
2.2 查詢ini配置
SELECT
NAME "參數(shù)名" ,
(CASE WHEN TYPE IN ('SYS','SESSION') THEN '動(dòng)態(tài)參數(shù)' ELSE '靜態(tài)參數(shù)' END CASE) "類型" ,
VALUE "當(dāng)前會(huì)話值" ,
SYS_VALUE "系統(tǒng)值",
FILE_VALUE "INI文件值",
DESCRIPTION
FROM
V$PARAMETER;
常用的參數(shù) name 為：
SYSTEM_PATH-------實(shí)例數(shù)據(jù)存放目錄MAX_SESSIONS------會(huì)話最大連接數(shù)PORT_NUM----------數(shù)據(jù)庫(kù)端口號(hào)BUFFER------------系統(tǒng)緩沖區(qū)大小ARCH_INI----------是否開(kāi)啟歸檔
2.3 查詢歸檔配置
SELECT
ARCH_TYPE "歸檔類型" ,
ARCH_DEST "歸檔保存路徑" ,
ARCH_FILE_SIZE "歸檔文件大小",
ARCH_SPACE_LIMIT "歸檔上限",
ARCH_INCOMING_PATH "遠(yuǎn)程歸檔保存路徑",
ARCH_LOCAL_SHARE "共享標(biāo)記"
FROM
V$DM_ARCH_INI
2.4 數(shù)據(jù)庫(kù)實(shí)例初始化參數(shù)
SELECT *FROM V$OPTION
2.5 查看數(shù)據(jù)庫(kù)信息
SELECT
NAME "數(shù)據(jù)庫(kù)名" ,
CREATE_TIME "創(chuàng)建時(shí)間" ,
(CASE ARCH_MODE WHEN 'N' THEN '未開(kāi)歸檔' ELSE THEN '已開(kāi)歸檔' END CASE) "歸檔模式",
LAST_CKPT_TIME "最后一次檢查點(diǎn)時(shí)間" ,
(CASE ROLE$ WHEN 0 THEN '普通' WHEN 1 THEN '主庫(kù)' WHEN 2 THEN '備庫(kù)' END CASE) "數(shù)據(jù)庫(kù)模式",
TOTAL_SIZE*SF_GET_PAGE_SIZE()/1024/1024||'MB' "總大小 " ,
(CASE DSC_NODES WHEN 1 THEN '非DSC' END CASE) "DSC節(jié)點(diǎn)數(shù)"
FROM
V$DATABASE
2.6 查看數(shù)據(jù)庫(kù)實(shí)例信息
SELECT
INSTANCE_NAME "實(shí)例名稱",
STATUS$ "系統(tǒng)狀態(tài)" ,
MODE$ "模式" ,
HOST_NAME "服務(wù)器名稱" ,
DAYS_BETWEEN(SYSDATE,START_TIME) "持續(xù)運(yùn)行時(shí)間(天)" ,
OGUID ,
(SELECT DECODE(UNICODE,0,'GB18030',1,'UNICODE')) "字符集",
(SELECT PARA_VALUE FROM V$DM_INI WHERE PARA_NAME = 'PORT_NUM') "訪問(wèn)端口",
(SELECT (CASE ARCH_MODE WHEN 'N' THEN '未開(kāi)歸檔' ELSE THEN '已開(kāi)歸檔' END CASE) FROM V$DATABASE) "歸檔模式",
(CASE DSC_ROLE WHEN 'NULL' THEN '非DSC' END CASE) "是否DSC"
FROM
V$INSTANCE
2.7 查看數(shù)據(jù)庫(kù)實(shí)例信息
select '版本',SVR_VERSION from v$instance union all
select '數(shù)據(jù)庫(kù)名', NAME from v$database union all
select '實(shí)例名',INSTANCE_NAME from v$instance union all
select '系統(tǒng)狀態(tài)',DECODE(STATUS$,'OPEN','打開(kāi)','MOUNT','配置','SUSPEND','掛起') from v$instance union all
select '實(shí)例模式',DECODE(MODE$,'NORMAL','普通模式','PRIMARY','主機(jī)模式','STANDBY','備機(jī)模式') from v$instance union all
select '是否啟用歸檔',DECODE(ARCH_MODE,'Y','是','N','否') from v$database union all
select '數(shù)據(jù)文件總大小', TOTAL_SIZE*PAGE/1024/1024||'MB' from v$database union all
select '數(shù)據(jù)庫(kù)打開(kāi)次數(shù)', OPEN_COUNT||'' from v$database union all
select '創(chuàng)建時(shí)間', cast(CREATE_TIME as varchar(50)) from v$database union all
select '啟動(dòng)時(shí)間',START_TIME||'' from v$instance union all
select '最后檢查點(diǎn)時(shí)間', cast(LAST_CKPT_TIME as varchar(50)) from v$database;
2.8 查看授權(quán)信息
SELECT '許可證版本' AS 名稱, 'V'||LIC_VERSION AS 信息 FROM V$LICENSE
UNION ALL
SELECT '序列號(hào)', SERIES_NO FROM V$LICENSE
UNION ALL
SELECT '校驗(yàn)碼', CHECK_CODE FROM V$LICENSE
UNION ALL
SELECT '制作日期', CAST(DATE_GEN AS VARCHAR(30)) FROM V$LICENSE
UNION ALL
SELECT '最終用戶', AUTHORIZED_CUSTOMER FROM V$LICENSE
UNION ALL
SELECT '項(xiàng)目名稱', PROJECT_NAME FROM V$LICENSE
UNION ALL
SELECT
'產(chǎn)品名稱',
PRODUCT_TYPE
||'('
||DECODE(SERVER_SERIES, 'P', '個(gè)人版', 'S', '標(biāo)準(zhǔn)版', 'E', '企業(yè)版', 'A', '安全版', 'D', '開(kāi)發(fā)版')
||')'
FROM
V$LICENSE
UNION ALL
SELECT
'產(chǎn)品類型',
DECODE(SERVER_TYPE, '1', '正式版', '2', '測(cè)試版', '3', '試用版')
FROM
V$LICENSE
UNION ALL
SELECT '有效日期', CAST(EXPIRED_DATE AS VARCHAR(30)) FROM V$LICENSE
UNION ALL
SELECT
'授權(quán)系統(tǒng)',
OS_TYPE
||' '
||DECODE(CPU_TYPE, 'X86', 'X86', 'X64', 'X64')
FROM
V$LICENSE
UNION ALL
SELECT '授權(quán)用戶數(shù)', CAST(AUTHORIZED_USER_NUMBER AS VARCHAR(30)) FROM V$LICENSE
UNION ALL
SELECT '授權(quán)并發(fā)數(shù)', CAST(CONCURRENCY_USER_NUMBER AS VARCHAR(30)) FROM V$LICENSE
UNION ALL
SELECT '授權(quán)CPU個(gè)數(shù)', CAST(MAX_CPU_NUM AS VARCHAR(30)) FROM V$LICENSE
UNION ALL
SELECT '授權(quán)CPU核數(shù)', CAST(MAX_CORE_NUM AS VARCHAR(30)) FROM V$LICENSE
UNION ALL
SELECT
'授權(quán)集群',
DECODE(CLUSTER_TYPE, '0000', '無(wú)', '0001', 'RAC', '0010', '讀寫(xiě)分離', '0011', '讀寫(xiě)分離、RAC', '0100', 'MPP', '0101', 'MPP、RAC', '0110', 'MPP、讀寫(xiě)分離', '0111', 'MPP、讀寫(xiě)分離、RAC', '1000', '主備', '1001', '主備、RAC', '1010', '主備、讀寫(xiě)分離', '1011', '主備、讀寫(xiě)分離、RAC', '1100', '主備、MPP', '1101', '主備、MPP、RAC', '1110', '主備、MPP、讀寫(xiě)分離', '1111', '主備、MPP、讀寫(xiě)分離、RAC')
FROM
V$LICENSE
UNION ALL
SELECT '未激活狀態(tài)截止日期', CAST(NOACTIVE_DEADLINE AS VARCHAR(30)) FROM V$LICENSE;
2.9 查詢頁(yè)大小，字符集大小
SELECT
SF_GET_PAGE_SIZE()/1024 "頁(yè)大小(KB)" ,
(CASE SF_GET_UNICODE_FLAG() WHEN 0 THEN 'GB18030' WHEN 1 THEN 'UFT-8' END CASE) "字符集",
(CASE SF_GET_CASE_SENSITIVE_FLAG() WHEN 1 THEN '敏感' WHEN 0 THEN '不敏感' END CASE) "大小寫(xiě)是否敏感",
SF_GET_SYSTEM_PATH() "數(shù)據(jù)庫(kù)路徑"
FROM DUAL
2.10 檢查點(diǎn)配置信息
SELECT
CKPT_RLOG_SIZE "檢查點(diǎn)日志大小",
CKPT_DIRTY_PAGES "產(chǎn)生多少臟頁(yè)刷檢查點(diǎn)",
CKPT_FLUSH_RATE "檢查點(diǎn)刷盤(pán)比例",
CKPT_INTERVAL "檢查點(diǎn)間隔時(shí)間" ,
CKPT_FLUSH_PAGES "每次檢查點(diǎn)至少刷臟頁(yè)數(shù)",
LAST_BEGIN_TIME "最近一次檢查點(diǎn)開(kāi)始時(shí)間",
LAST_END_TIME "最近一次檢查點(diǎn)結(jié)束時(shí)間",
CKPT_TIME_USED "檢查點(diǎn)使用時(shí)間"
FROM
V$CKPT;
3 數(shù)據(jù)對(duì)象管理
3.1 查看系統(tǒng)所有的動(dòng)態(tài)視圖名稱
SELECT * FROM V$DYNAMIC_TABLES
3.2 查看系統(tǒng)提供的所有函數(shù)名稱
SELECT NAME "名稱",CLASS$ "類別" FROM V$IFUN
3.3 查看表中數(shù)據(jù)的變化信息
-要求AUTO_STAT_OBJ =1
SELECT
B.NAME "表名",
TOTAL_ROWS "總行數(shù)",
INSERT_ROWS "已插入行數(shù)",
DELETE_ROWS "已刪除行數(shù)",
UPDATE_ROWS "已更新行數(shù)"
FROM
V$AUTO_STAT_TABLE_IDU A
LEFT JOIN SYSOBJECTS B
ON
A.ID = B.ID
3.4 查看單個(gè)表占用空間大小
SELECT
TABLE_USED_PAGES('USER_NAME', 'TB_CNB_PAY_TEMP_EMP')*PAGE/1024 "使用空間",
TABLE_USED_SPACE('USER_NAME', 'TB_CNB_PAY_TEMP_EMP')*PAGE/1024 "占用空間"
FROM
DUAL;
3.5 查看數(shù)據(jù)對(duì)象的定義語(yǔ)句
SELECT
DBMS_METADATA.GET_DDL('INDEX', 'IDX_T2_ID', 'SYSDBA'),--索引
DBMS_METADATA.GET_DDL('TABLE', 'T1', 'SYSDBA') --表
DBMS_METADATA.GET_DDL('FUNCTION', 'FUNC_NAME', 'SYSDBA') --函數(shù)
DBMS_METADATA.GET_DDL('PROCEDURE', 'PROC_NAME', 'SYSDBA') --存儲(chǔ)過(guò)程
DBMS_METADATA.GET_DDL('PACKAGE', 'PGK_NAME', 'SYSDBA') --存儲(chǔ)過(guò)程
FROM
DUAL
3.6 查看表的主鍵列
select
COLS.NAME
from
SYS.SYSCOLUMNS COLS,
(
select * from SYS.SYSCONS WHERE TYPE$ = 'P'
)
CONS ,
SYS.SYSINDEXES INDS,
(
select ID, PID from SYS.SYSOBJECTS where SUBTYPE$='INDEX' AND VALID = 'Y'
)
IND_OBJ
where
CONS.TABLEID =
(
select
ID
from
SYS.SYSOBJECTS
where
SUBTYPE$ like '_TAB'
and SCHID =
(
select
id
from
sys.sysobjects
where
type$ = 'SCH'
and name = 'SYSDBA' --替換為模式名
)
and NAME = 'T1' --替換為表名
)
and INDS.ID = IND_OBJ.ID
and COLS.ID = CONS.TABLEID
and CONS.INDEXID = INDS.ID
and SF_COL_IS_IDX_KEY(INDS.KEYNUM, INDS.KEYINFO, COLS.COLID) = 1;
3.7 查看數(shù)據(jù)庫(kù)中所有的索引信息
SELECT
I.TABLE_OWNER,
I.TABLE_NAME ,
O.OBJECT_NAME,
O.STATUS
FROM
USER_INDEXES I,
USER_OBJECTS O
WHERE
O.OBJECT_NAME=I.INDEX_NAME
AND O.STATUS ='VALID' ;
3.8 統(tǒng)計(jì)數(shù)據(jù)庫(kù)中的所有的表數(shù)據(jù)量和DDL語(yǔ)句
DECLARE
TYPE TYPE_TAB_RECORD IS RECORD(ID SYSOBJECTS.ID%TYPE,USER_NAME SYSOBJECTS.NAME%TYPE,SCH_NAME SYSOBJECTS.NAME%TYPE,TAB_NAME SYSOBJECTS.NAME%TYPE,TAB_ROWNUM BIGINT,USED_PAGES INT,USED_SPACE INT,DDL_STR CLOB,DIS_TYPE ALL_TABLES_DIS_INFO.DIS_TYPE%TYPE,DIS_COLS ALL_TABLES_DIS_INFO.DIS_COLS%TYPE);
TYPE TAB_REC_ARR IS TABLE OF TYPE_TAB_RECORD;
V_TAB_ARR TAB_REC_ARR;
CURSOR TB_CUR IS
SELECT
TAB_OBJ.ID ,
USER_OBJ.NAME USER_NAME,
SCH_OBJ.NAME SCH_NAME ,
TAB_OBJ.NAME TAB_NAME ,
0 TAB_ROWNUM ,
0 USED_PAGES ,
0 USED_SPACE ,
'' DDL_STR ,
DIS_INFO.DIS_TYPE ,
DIS_INFO.DIS_COLS
FROM
SYSOBJECTS USER_OBJ
INNER JOIN SYSOBJECTS SCH_OBJ
ON
SCH_OBJ.PID = USER_OBJ.ID
INNER JOIN SYSOBJECTS TAB_OBJ
ON
SCH_OBJ.ID = TAB_OBJ.SCHID
LEFT JOIN ALL_TABLES_DIS_INFO DIS_INFO
ON
DIS_INFO.SCHEMA_NAME = SCH_OBJ.NAME
AND DIS_INFO.TABLE_NAME = TAB_OBJ.NAME
WHERE
USER_OBJ.TYPE$ = 'UR'
AND USER_OBJ.SUBTYPE$ ='USER'
AND SCH_OBJ.TYPE$ = 'SCH'
AND SCH_OBJ.SUBTYPE$ IS NULL
AND TAB_OBJ.TYPE$ = 'SCHOBJ'
AND TAB_OBJ.SUBTYPE$ = 'UTAB'
AND TAB_OBJ.PID = -1
AND SCH_OBJ.NAME NOT IN ('DMHS', 'SYSDBA', 'SYS');
BEGIN
OPEN TB_CUR;
FETCH TB_CUR BULK COLLECT INTO V_TAB_ARR;
FOR I IN V_TAB_ARR.FIRST..V_TAB_ARR.COUNT
LOOP
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||V_TAB_ARR(I).SCH_NAME||'.'||V_TAB_ARR(I).TAB_NAME INTO V_TAB_ARR(I).TAB_ROWNUM;
EXECUTE IMMEDIATE 'SELECT TABLEDEF('''||V_TAB_ARR(I).SCH_NAME||''','''||V_TAB_ARR(I).TAB_NAME||''')' INTO V_TAB_ARR(I).DDL_STR;
EXECUTE IMMEDIATE 'SELECT TABLE_USED_PAGES('''||V_TAB_ARR(I).SCH_NAME||''','''||V_TAB_ARR(I).TAB_NAME||''')*'||'(PAGE/1024)' INTO V_TAB_ARR(I).USED_PAGES;
EXECUTE IMMEDIATE 'SELECT TABLE_USED_SPACE('''||V_TAB_ARR(I).SCH_NAME||''','''||V_TAB_ARR(I).TAB_NAME||''')*'||'(PAGE/1024)' INTO V_TAB_ARR(I).USED_SPACE;
END LOOP;
SELECT ID,USER_NAME "用戶名",SCH_NAME "模式名",TAB_NAME "表名",TAB_ROWNUM "表行數(shù)",USED_PAGES||'KB/'||USED_SPACE||'KB' "空間使用", DDL_STR "DDL語(yǔ)句",DIS_TYPE "分布類型",TO_CHAR(DIS_COLS) "分布列" FROM TABLE(V_TAB_ARR);
END;
3.9 統(tǒng)計(jì)數(shù)據(jù)庫(kù)中所有的索引信息和DDL語(yǔ)句
DECLARE
TYPE TYPE_IDX_RECORD IS RECORD(ID SYSOBJECTS.ID%TYPE,SCH_NAME SYSOBJECTS.NAME%TYPE,TAB_NAME SYSOBJECTS.NAME%TYPE,IDX_NAME SYSOBJECTS.NAME%TYPE,CREATE_TIME TIMESTAMP(6),IS_VALID CHAR(1),USED_PAGES VARCHAR2(22),USED_SPACE VARCHAR2(22),IDX_TYPE$ VARCHAR2(20),CONS_TYPE$ VARCHAR2(20),DDL_STR CLOB);
TYPE IDX_REC_ARR IS TABLE OF TYPE_IDX_RECORD;
V_IDX_ARR IDX_REC_ARR;
CURSOR IDX_CUR IS
SELECT
I.ID,
S.NAME,
T.NAME,
I.NAME,
I.CRTDATE,
I.VALID,
INDEX_USED_PAGES(SI.ID)*(PAGE/1024)||'KB',
INDEX_USED_SPACE(SI.ID)*(PAGE/1024)||'KB',
SI.XTYPE,
SC.TYPE$,
'' DDL_STR
FROM
SYS.SYSOBJECTS I
LEFT JOIN SYS.SYSOBJECTS T
ON
I.PID = T.ID
LEFT JOIN SYS.SYSOBJECTS S
ON
T.SCHID = S.ID
LEFT JOIN SYS.SYSINDEXES SI
ON
I.ID = SI.ID
LEFT JOIN SYS.SYSCONS SC
ON
SC.TABLEID = T.ID
AND SC.INDEXID = I.ID
WHERE
T.TYPE$ ='SCHOBJ'
AND T.SUBTYPE$ = 'UTAB'
AND I.SUBTYPE$ = 'INDEX';
BEGIN
OPEN IDX_CUR;
FETCH IDX_CUR BULK COLLECT INTO V_IDX_ARR;
FOR I IN V_IDX_ARR.FIRST..V_IDX_ARR.COUNT
LOOP
EXECUTE IMMEDIATE 'SELECT INDEXDEF('||V_IDX_ARR(I).ID||','||'1)' INTO V_IDX_ARR(I).DDL_STR;
END LOOP;
SELECT ID,SCH_NAME "模式名",TAB_NAME "表名",IDX_NAME "索引名",CREATE_TIME "創(chuàng)建時(shí)間",IS_VALID "是否有效",USED_PAGES||'/'||USED_SPACE "空間使用",
(CASE IDX_TYPE$ WHEN 0 THEN '聚集索引' WHEN 1 THEN '二級(jí)索引'
ELSE (CASE CONS_TYPE$ WHEN 'P' THEN '主鍵索引' WHEN 'U' THEN '唯一索引' WHEN 'F' THEN '外鍵約束' ELSE '無(wú)' END CASE)
END CASE) "索引類型",
(CASE CONS_TYPE$ WHEN 'P' THEN '主鍵約束' WHEN 'U' THEN '唯一約束' WHEN 'F' THEN '外鍵約束' ELSE '無(wú)' END CASE) "約束類型",
DDL_STR "DDL定義"
FROM TABLE(V_IDX_ARR);
END;
3.10 統(tǒng)計(jì)數(shù)據(jù)庫(kù)中所有的存儲(chǔ)過(guò)程和函數(shù)及DDL語(yǔ)句
DECLARE
TYPE TYPE_PROC_RECORD IS RECORD(ID SYSOBJECTS.ID%TYPE,USER_NAME SYSOBJECTS.NAME%TYPE,SCH_NAME SYSOBJECTS.NAME%TYPE,PROC_NAME SYSOBJECTS.NAME%TYPE,TYPE$ VARCHAR2(20),CREATE_TIME TIMESTAMP(6),IS_VALID CHAR(1),DDL_STR CLOB);
TYPE PROC_REC_ARR IS TABLE OF TYPE_PROC_RECORD;
V_PROC_ARR PROC_REC_ARR;
CURSOR PROC_CUR IS
SELECT DISTINCT
PROC_OBJ.ID,
USER_OBJ.NAME,
SCH_OBJ.NAME,
PROC_OBJ.NAME,
(CASE PROC_OBJ.INFO1 WHEN 0 THEN '函數(shù)' WHEN 1 THEN '存儲(chǔ)過(guò)程' END CASE) ,
PROC_OBJ.CRTDATE,
PROC_OBJ.VALID,
'' DDL_STR
FROM
SYS.SYSOBJECTS PROC_OBJ,
SYS.SYSOBJECTS SCH_OBJ,
SYS.SYSOBJECTS USER_OBJ
WHERE
PROC_OBJ.SUBTYPE$ ='PROC'
AND PROC_OBJ.INFO1 IN (0,1)
AND USER_OBJ.SUBTYPE$ = 'USER'
AND SCH_OBJ.ID = PROC_OBJ.SCHID
AND SCH_OBJ.PID = USER_OBJ.ID;
BEGIN
OPEN PROC_CUR;
FETCH PROC_CUR BULK COLLECT INTO V_PROC_ARR;
FOR I IN V_PROC_ARR.FIRST..V_PROC_ARR.COUNT
LOOP
EXECUTE IMMEDIATE 'SELECT TXT FROM SYS.SYSTEXTS WHERE ID = '||V_PROC_ARR(I).ID INTO V_PROC_ARR(I).DDL_STR;
END LOOP;
SELECT ID,USER_NAME "用戶名",SCH_NAME "模式名",PROC_NAME "過(guò)程名",TYPE$ "類型",CREATE_TIME "創(chuàng)建時(shí)間",IS_VALID "是否有效",DDL_STR "DDL語(yǔ)句" FROM TABLE(V_PROC_ARR);
END;
3.11 統(tǒng)計(jì)超過(guò)15%變化數(shù)據(jù)的表
-- 要求AUTO_STAT_OBJ =1
DECLARE
TYPE T_NAME IS TABLE OF VARCHAR2(10);
CURSOR CUR_TAB
IS
SELECT
O2.NAME SCH_NAME,
O1.NAME TAB_NAME,
CONVERT(NUMBER(5, 2), INSERT_ROWS+DELETE_ROWS+UPDATE_ROWS)/CONVERT(NUMBER(5, 2), TOTAL_ROWS) PERC
FROM
V$AUTO_STAT_TABLE_IDU I
LEFT JOIN SYSOBJECTS O1
ON
O1.TYPE$ ='SCHOBJ'
AND O1.SUBTYPE$='UTAB'
AND I.ID = O1.ID
LEFT JOIN SYSOBJECTS O2
ON
O2.ID = O1.SCHID;
TAB_RECORD CUR_TAB%ROWTYPE;
BEGIN
OPEN CUR_TAB;
LOOP
FETCH CUR_TAB INTO TAB_RECORD;
EXIT WHEN CUR_TAB%NOTFOUND;
IF TAB_RECORD.PERC >= 0.15 THEN
PRINT(TAB_RECORD.SCH_NAME||'--'||TAB_RECORD.TAB_NAME);
END IF;
END LOOP;
END;
3.12 統(tǒng)計(jì)數(shù)據(jù)庫(kù)中所有的觸發(fā)器
3.12.1 庫(kù)級(jí)觸發(fā)器
select
*
from
(
(
select
TRIG_OBJ.ID ,
TRIG_OBJ.NAME TRIG_NAME ,
TRIG_OBJ.CRTDATE TRIG_CRTDATE,
TRIG_OBJ.INFO1 ,
TRIG_OBJ.VALID TRIG_VALID ,
SCH_OBJ.ID ,
SCH_OBJ.NAME SCH_OBJ_NAME ,
'D' TRIG_TYPE ,
NULL ,
NULL OBJ_SCH_OBJ_NAME ,
NULL ,
NULL OBJ_TV_NAME ,
TRIG_OBJ.TRIG_EVENT
from
(
select
DBTRIG_OBJ_INNER.ID ,
DBTRIG_OBJ_INNER.NAME ,
DBTRIG_OBJ_INNER.CRTDATE,
DBTRIG_OBJ_INNER.INFO1 ,
DBTRIG_OBJ_INNER.VALID ,
DBTRIG_OBJ_INNER.SCHID ,
SF_GET_TRI_EVENT_FROM_INFO3(DBTRIG_OBJ_INNER.INFO3, DBTRIG_OBJ_INNER.INFO1) TRIG_EVENT
from
SYS.SYSOBJECTS DBTRIG_OBJ_INNER,
SYS.SYSOBJECTS SCH_OBJ_INNER ,
SYS.SYSOBJECTS USER_OBJ_INNER
where
DBTRIG_OBJ_INNER.SUBTYPE$ ='TRIG'
and DBTRIG_OBJ_INNER.INFO1 & 0x00000002 = 0
and DBTRIG_OBJ_INNER.INFO1 & 0x000000C0 = 64
and USER_OBJ_INNER.SUBTYPE$ = 'USER'
and SCH_OBJ_INNER.ID = DBTRIG_OBJ_INNER.SCHID
and SCH_OBJ_INNER.PID = USER_OBJ_INNER.ID
and SF_CHECK_PRIV_OPT(UID(), CURRENT_USERTYPE(), DBTRIG_OBJ_INNER.PID, USER_OBJ_INNER.ID, USER_OBJ_INNER.INFO1, DBTRIG_OBJ_INNER.ID) = 1
)
TRIG_OBJ,
(
select ID, NAME from SYS.SYSOBJECTS where TYPE$='SCH' and ID = 150994945
)
SCH_OBJ
where
TRIG_OBJ.SCHID = SCH_OBJ.ID
)
)
order by
TRIG_TYPE,
TRIG_NAME
3.12.2 模式級(jí)觸發(fā)器
select
*
from
(
(
select
TRIG_OBJ.ID ,
TRIG_OBJ.NAME TRIG_NAME ,
TRIG_OBJ.CRTDATE TRIG_CRTDATE ,
TRIG_OBJ.INFO1 ,
TRIG_OBJ.VALID TRIG_VALID ,
SCH_OBJ.ID ,
SCH_OBJ.NAME SCH_OBJ_NAME ,
'S' TRIG_TYPE ,
OBJ_SCH_OBJ.ID ,
OBJ_SCH_OBJ.NAME OBJ_SCH_OBJ_NAME,
NULL ,
NULL OBJ_TV_NAME ,
TRIG_OBJ.TRIG_EVENT
from
(
select
SCHTRIG_OBJ_INNER.ID ,
SCHTRIG_OBJ_INNER.NAME ,
SCHTRIG_OBJ_INNER.CRTDATE,
SCHTRIG_OBJ_INNER.INFO1 ,
SCHTRIG_OBJ_INNER.VALID ,
SCHTRIG_OBJ_INNER.SCHID ,
SCHTRIG_OBJ_INNER.PID ,
SF_GET_TRI_EVENT_FROM_INFO3(SCHTRIG_OBJ_INNER.INFO3, SCHTRIG_OBJ_INNER.INFO1) TRIG_EVENT
from
SYS.SYSOBJECTS SCHTRIG_OBJ_INNER,
SYS.SYSOBJECTS SCH_OBJ_INNER ,
SYS.SYSOBJECTS USER_OBJ_INNER
where
SCHTRIG_OBJ_INNER.SUBTYPE$ ='TRIG'
and SCHTRIG_OBJ_INNER.INFO1 & 0x000000C0 = 0
and USER_OBJ_INNER.SUBTYPE$ = 'USER'
and SCH_OBJ_INNER.ID = SCHTRIG_OBJ_INNER.SCHID
and SCH_OBJ_INNER.PID = USER_OBJ_INNER.ID
and SF_CHECK_PRIV_OPT(UID(), CURRENT_USERTYPE(), SCHTRIG_OBJ_INNER.PID, USER_OBJ_INNER.ID, USER_OBJ_INNER.INFO1, SCHTRIG_OBJ_INNER.ID) = 1
)
TRIG_OBJ,
(
select ID, NAME from SYS.SYSOBJECTS where TYPE$='SCH' and ID = 150994945
)
SCH_OBJ,
(
select ID, NAME from SYS.SYSOBJECTS where TYPE$='SCH'
)
OBJ_SCH_OBJ
where
TRIG_OBJ.SCHID = SCH_OBJ.ID
and TRIG_OBJ.PID = OBJ_SCH_OBJ.ID
)
)
order by
TRIG_TYPE,
TRIG_NAME
3.12.3 視圖級(jí)觸發(fā)器
select
*
from
(
(
select
TRIG_OBJ.ID ,
TRIG_OBJ.NAME TRIG_NAME ,
TRIG_OBJ.CRTDATE TRIG_CRTDATE ,
TRIG_OBJ.INFO1 ,
TRIG_OBJ.VALID TRIG_VALID ,
SCH_OBJ.ID ,
SCH_OBJ.NAME SCH_OBJ_NAME ,
'V' TRIG_TYPE ,
OBJ_SCH_OBJ.ID ,
OBJ_SCH_OBJ.NAME OBJ_SCH_OBJ_NAME,
OBJ_VIEW_OBJ.ID ,
OBJ_VIEW_OBJ.NAME OBJ_TV_NAME ,
TRIG_OBJ.TRIG_EVENT
from
(
select
VIEWTRIG_OBJ_INNER.ID ,
VIEWTRIG_OBJ_INNER.NAME ,
VIEWTRIG_OBJ_INNER.CRTDATE,
VIEWTRIG_OBJ_INNER.INFO1 ,
VIEWTRIG_OBJ_INNER.VALID ,
VIEWTRIG_OBJ_INNER.SCHID ,
VIEWTRIG_OBJ_INNER.PID ,
SF_GET_TRI_EVENT_FROM_INFO3(VIEWTRIG_OBJ_INNER.INFO3, VIEWTRIG_OBJ_INNER.INFO1) TRIG_EVENT
from
SYS.SYSOBJECTS VIEWTRIG_OBJ_INNER,
SYS.SYSOBJECTS SCH_OBJ_INNER ,
SYS.SYSOBJECTS USER_OBJ_INNER
where
VIEWTRIG_OBJ_INNER.SUBTYPE$ ='TRIG'
and VIEWTRIG_OBJ_INNER.INFO1 & 0x00000002 = 2
and USER_OBJ_INNER.SUBTYPE$ = 'USER'
and SCH_OBJ_INNER.ID = VIEWTRIG_OBJ_INNER.SCHID
and SCH_OBJ_INNER.PID = USER_OBJ_INNER.ID
and SF_CHECK_PRIV_OPT(UID(), CURRENT_USERTYPE(), VIEWTRIG_OBJ_INNER.PID, USER_OBJ_INNER.ID, USER_OBJ_INNER.INFO1, VIEWTRIG_OBJ_INNER.ID) = 1
)
TRIG_OBJ,
(
select ID, NAME from SYS.SYSOBJECTS where TYPE$='SCH' and ID = 150994945
)
SCH_OBJ,
(
select ID, NAME from SYS.SYSOBJECTS where TYPE$='SCH'
)
OBJ_SCH_OBJ,
(
select
ID ,
NAME,
SCHID
from
SYS.SYSOBJECTS
where
TYPE$ = 'SCHOBJ'
and SUBTYPE$ like 'VIEW'
)
OBJ_VIEW_OBJ
where
TRIG_OBJ.SCHID = SCH_OBJ.ID
and TRIG_OBJ.PID = OBJ_VIEW_OBJ.ID
and OBJ_VIEW_OBJ.SCHID = OBJ_SCH_OBJ.ID
)
)
order by
TRIG_TYPE,
TRIG_NAME
3.12.4 表級(jí)觸發(fā)器
select
*
from
(
(
select
TRIG_OBJ.ID ,
TRIG_OBJ.NAME TRIG_NAME ,
TRIG_OBJ.CRTDATE TRIG_CRTDATE ,
TRIG_OBJ.INFO1 ,
TRIG_OBJ.VALID TRIG_VALID ,
SCH_OBJ.ID ,
SCH_OBJ.NAME SCH_OBJ_NAME ,
'T' TRIG_TYPE ,
OBJ_SCH_OBJ.ID ,
OBJ_SCH_OBJ.NAME OBJ_SCH_OBJ_NAME,
OBJ_TAB_OBJ.ID ,
OBJ_TAB_OBJ.NAME OBJ_TV_NAME ,
TRIG_OBJ.TRIG_EVENT
from
(
select
TABTRIG_OBJ_INNER.ID ,
TABTRIG_OBJ_INNER.NAME ,
TABTRIG_OBJ_INNER.CRTDATE,
TABTRIG_OBJ_INNER.INFO1 ,
TABTRIG_OBJ_INNER.VALID ,
TABTRIG_OBJ_INNER.SCHID ,
TABTRIG_OBJ_INNER.PID ,
SF_GET_TRI_EVENT_FROM_INFO3(TABTRIG_OBJ_INNER.INFO3, TABTRIG_OBJ_INNER.INFO1) TRIG_EVENT
from
SYS.SYSOBJECTS TABTRIG_OBJ_INNER,
SYS.SYSOBJECTS SCH_OBJ_INNER ,
SYS.SYSOBJECTS USER_OBJ_INNER
where
TABTRIG_OBJ_INNER.SUBTYPE$ ='TRIG'
and TABTRIG_OBJ_INNER.INFO1 & 0x00000002 = 2
and USER_OBJ_INNER.SUBTYPE$ = 'USER'
and SCH_OBJ_INNER.ID = TABTRIG_OBJ_INNER.SCHID
and SCH_OBJ_INNER.PID = USER_OBJ_INNER.ID
and SF_CHECK_PRIV_OPT(UID(), CURRENT_USERTYPE(), TABTRIG_OBJ_INNER.PID, USER_OBJ_INNER.ID, USER_OBJ_INNER.INFO1, TABTRIG_OBJ_INNER.ID) = 1
)
TRIG_OBJ,
(
select ID, NAME from SYS.SYSOBJECTS where TYPE$='SCH' and ID = 150994945
)
SCH_OBJ,
(
select ID, NAME from SYS.SYSOBJECTS where TYPE$='SCH'
)
OBJ_SCH_OBJ,
(
select
ID ,
NAME,
SCHID
from
SYS.SYSOBJECTS
where
TYPE$ = 'SCHOBJ'
and SUBTYPE$ like '_TAB'
)
OBJ_TAB_OBJ
where
TRIG_OBJ.SCHID = SCH_OBJ.ID
and TRIG_OBJ.PID = OBJ_TAB_OBJ.ID
and OBJ_TAB_OBJ.SCHID = OBJ_SCH_OBJ.ID
)
)
order by
TRIG_TYPE,
TRIG_NAME
4 用戶管理
4.1 實(shí)例中用戶信息
SELECT
D.USERNAME "用戶名",
D.ACCOUNT_STATUS "用戶狀態(tài)",
D.CREATED "創(chuàng)建時(shí)間",
S.CONN_IDLE_TIME "最大空閑時(shí)間",
S.FAILED_NUM "登錄失敗次數(shù)限制" ,
D.EXPIRY_DATE "過(guò)期時(shí)間",
S.LIFE_TIME "口令剩余有效時(shí)間" ,
D.DEFAULT_TABLESPACE "默認(rèn)表空間",
(CASE S.LOCKED_STATUS WHEN 2 THEN 'UNLOCKED' WHEN 1 THEN 'LOCKED' END) "用戶鎖定狀態(tài)"
FROM
SYSUSERS S,DBA_USERS D
WHERE S.ID = D.USER_ID
5 表空間與數(shù)據(jù)文件管理
5.1 回滾表空間
5.1.1 顯示當(dāng)前 PURGE 回滾段信息
SELECT OBJ_NUM "待PURGE個(gè)數(shù)", IS_RUNNING "是否正在PURGE" FROM V$PURGE
5.1.2 查看回滾段信息
SELECT
N_ITEM "回滾項(xiàng)目個(gè)數(shù)",
ALLOC_PAGES*SF_GET_PAGE_SIZE()/1024/1024
||'MB' "分配空間" ,
TAB_ITEMS "表個(gè)數(shù)",
OBJ_COUNT "對(duì)象個(gè)數(shù)"
FROM
V$PSEG_SYS
5.1.3 查看每個(gè)回滾項(xiàng)信息
SELECT
N_PAGES*SF_GET_PAGE_SIZE()/1024/1024
||'MB' "回滾頁(yè)數(shù)",
N_PURGED_PAGES*SF_GET_PAGE_SIZE()/1024/1024
||'MB' "已經(jīng)PURGE的頁(yè)數(shù)",
N_COMMIT_TRX*SF_GET_PAGE_SIZE()/1024/1024
||'MB' "已提交但未PURGE的頁(yè)數(shù)" ,
RESERVE_TIME "事務(wù)提交后最長(zhǎng)保留時(shí)間",
N_PURGING_TRX "正在PURGE的事務(wù)數(shù)"
FROM
V$PSEG_ITEMS
5.1.4 查看回滾項(xiàng)中已提交但未 PURGE 的事務(wù)信息
SELECT TRX_ID "事務(wù)ID",CMT_TIME "事務(wù)提交時(shí)間" FROM V$PSEG_COMMIT_TRX
5.2 普通表空間
5.2.1 查看所有的數(shù)據(jù)文件使用情況
SELECT
PATH "路徑" ,
CREATE_TIME "創(chuàng)建時(shí)間",
TOTAL_SIZE*PAGE_SIZE/1024/1024||'MB' "總大小",
(TOTAL_SIZE-FREE_SIZE)*PAGE_SIZE/1024/1024||'MB' "已使用大小",
(CASE AUTO_EXTEND WHEN 1 THEN '自動(dòng)擴(kuò)展' WHEN 0 THEN '不自動(dòng)擴(kuò)展' END CASE)
FROM
V$DATAFILE
5.2.2 查看表空間信息
SELECT
NAME "名稱" ,
FILE_NUM "數(shù)據(jù)文件個(gè)數(shù)",
(CASE TYPE$ WHEN 1 THEN '普通表空間' ELSE THEN '臨時(shí)表空間' END CASE),
TOTAL_SIZE*SF_GET_PAGE_SIZE()/1024/1024||'MB' "大小"
FROM
V$TABLESPACE
5.3 查看表空間中數(shù)據(jù)文件信息
SELECT
T.NAME 表空間名稱,
D.PATH 表空文件路徑,
T.TYPE$ 表空間類型,
T.STATUS$ 表空間狀態(tài),
T. FILE_NUM 包含的文件數(shù),
D.TOTAL_SIZE*16/1024 總大小,
D.FREE_SIZE*16/1024 空閑大小,
TRUNC((TRUNC(D.TOTAL_SIZE-D.FREE_SIZE, 4)/D.TOTAL_SIZE)*100, 2) 使用率
FROM V$TABLESPACE T, V$DATAFILE D WHERE "GROUP_ID"=T.ID;
6 日志與備份管理
6.1 做數(shù)據(jù)庫(kù)的全量備份
BACKUP DATABASE TO FULL_BAK_HAND BACKUPSET 'd:\dmdbms\data\DAMENG\bak\db_full_bak_hand';
6.2 做數(shù)據(jù)庫(kù)的增量備份
BACKUP DATABASE INCREMENT WITH BACKUPDIR 'd:\dmdbms\data\DAMENG\bak\' to incre_bak_hand BACKUPSET 'd:\dmdbms\data\DAMENG\bak\db_incre_bak_hand';
6.3 備份歷史信息
SELECT
PATH "備份集路徑" ,
START_TIME "開(kāi)始時(shí)間" ,
END_TIME "結(jié)束時(shí)間" ,
DATEDIFF(SS, START_TIME, END_TIME) "備份時(shí)間(秒)",
READ_SIZE /1024/1024 "讀取數(shù)據(jù)文件大小(MB)" ,
WRITE_SIZE/1024/1024 "備份集大小(MB)" ,
AVG_READ "平均讀速度" ,
AVG_WRITE "平均寫(xiě)速度"
FROM
V$BACKUP_HISTORY
--備注：只會(huì)記錄100條記錄
6.4 對(duì)備份集進(jìn)行校驗(yàn)
SF_BAKSET_CHECK('DISK','/home/dm_bak/db_bak_for_check')
--備注：返回1為合法
6.5 查看備份集及備份鏈信息
SELECT
LEVEL ,
TT.DEVICE_TYPE "介質(zhì)類型" ,
TT.BACKUP_NAME "備份名稱" ,
TT.BASE_NAME "基備份名稱" ,
TT.BACKUP_PATH "備份路徑" ,
(CASE TT.TYPE WHEN 0 THEN '基備份' WHEN 1 THEN '增量備份' WHEN 2 THEN '表備份' WHEN 3 THEN '歸檔備份' END CASE) "備份類型",
(CASE TT."LEVEL#" WHEN 0 THEN '聯(lián)機(jī)備份' WHEN 1 THEN '脫機(jī)備份' END CASE) "聯(lián)機(jī)/脫機(jī)備份",
(CASE TT."RANGE#" WHEN 1 THEN '庫(kù)備份' WHEN 2 THEN '表空間備份' WHEN 3 THEN '表備份' WHEN 4 THEN '歸檔備份' END CASE) "備份內(nèi)容",
TT.OBJECT_NAME "備份對(duì)象名稱" ,
TT.BACKUP_TIME "備份時(shí)間" ,
(CASE WHEN TT.TYPE=1 AND TT.CUMULATIVE =1 THEN '累積增量備份' WHEN TT.TYPE=1 AND TT.CUMULATIVE =0 THEN '差異增量備份' END CASE) "增備類型"
FROM
(
SELECT
DEVICE_TYPE ,
BACKUP_ID ,
BACKUP_NAME ,
BASE_NAME ,
BACKUP_PATH ,
TYPE ,
LEVEL "LEVEL#",
"RANGE#" ,
OBJECT_NAME ,
BACKUP_TIME ,
CUMULATIVE
FROM
V$BACKUPSET
)
TT START
WITH TYPE=0 CONNECT BY PRIOR BACKUP_NAME = BASE_NAME
-- 備注：只會(huì)搜索V$BACKUPSET_SEARCH_DIRS顯示的路徑下的備份；如果有備份集在其他路徑下例如：d:\dmdbms\data\，需要執(zhí)行SELECT SF_BAKSET_BACKUP_DIR_ADD('DISK','d:\dmdbms\data\');將路徑納入進(jìn)來(lái)才能查到對(duì)應(yīng)的備份集信息，返回1為執(zhí)行成功。
--同樣的，移除可以使用SELECT SF_BAKSET_BACKUP_DIR_REMOVE('DISK',' d:\dmdbms\data\');返回1為執(zhí)行成功?；蛘邎?zhí)行移除全部目錄SELECT SF_BAKSET_BACKUP_DIR_REMOVE_ALL();
6.6 刪除備份集
SELECT SF_BAKSET_REMOVE('DISK','d:\dmdbms\data\DAMENG\bak\DB_DAMENG_FULL_2021_12_23_09_46_01');
--返回1表示執(zhí)行成功
--備注：如果要?jiǎng)h除的備份集是基備份，那么需要執(zhí)行級(jí)聯(lián)刪除命令，將依賴該備份集的備份一起刪除：
SELECT SF_BAKSET_REMOVE('DISK','d:\dmdbms\data\DAMENG\bak\DB_DAMENG_FULL_2021_12_23_09_46_01',1); 返回1表示執(zhí)行成功
6.7 批量刪除數(shù)據(jù)庫(kù)備份集
CALL SP_DB_BAKSET_REMOVE_BATCH('DISK', ADD_DAYS(NOW(), -7));
--備注：刪除距今7天之前生成的備份集
--只會(huì)搜索V$BACKUPSET_SEARCH_DIRS顯示的路徑下的備份；如果有備份集在其他路徑下例如：d:\dmdbms\data\，需要執(zhí)行SELECT SF_BAKSET_BACKUP_DIR_ADD('DISK','d:\dmdbms\data\');將路徑納入進(jìn)來(lái)才能查到對(duì)應(yīng)的備份集信息。
6.8 查看歸檔配置信息
SELECT
ARCH_NAME "配置名稱",
ARCH_TYPE "歸檔類型",
ARCH_DEST "歸檔目的地",
ARCH_FILE_SIZE "單個(gè)歸檔文件大小",
ARCH_SPACE_LIMIT "歸檔空間上限",
ARCH_TIMER_NAME "定時(shí)器名稱(ASYNC歸檔有效)",
ARCH_IS_VALID "歸檔配置是否有效",
ARCH_INCOMING_PATH "遠(yuǎn)程歸檔保存路徑（REMOTE歸檔有效）"
FROM
SYS.V$DM_ARCH_INI;
6.9 查看redo日志文件信息
SELECT
FILE_ID "ID編號(hào)" ,
PATH "路徑" ,
CREATE_TIME "創(chuàng)建時(shí)間",
RLOG_SIZE/1024/1024 "文件大小（MB）"
FROM
V$RLOGFILE
6.10 查看redo日志使用信息
SELECT
CKPT_LSN "最近一次檢查點(diǎn)LSN" ,
FILE_LSN "已經(jīng)到盤(pán)上的最大LSN",
FLUSH_LSN "準(zhǔn)備刷盤(pán)的LSN" ,
CUR_LSN "當(dāng)前LSN" ,
FLUSH_PAGES*SF_GET_PAGE_SIZE()/1024/1024||'MB' "Flush鏈中的數(shù)據(jù)量",
FLUSHING_PAGES*SF_GET_PAGE_SIZE()/1024/1024||'MB' "正在刷盤(pán)的數(shù)據(jù)量",
FREE_SPACE/1024/1024||'MB' "可用日志空間",
TOTAL_SPACE/1024/1024||'MB' "日志總空間"
FROM
V$RLOG
6.11 查看歸檔日志文件的具體信息
SELECT
NAME "名稱" ,
SEQUENCE# "序號(hào)" ,
FIRST_CHANGE# "最小LSN",
NEXT_CHANGE# "最大LSN" ,
FIRST_TIME "開(kāi)始時(shí)間" ,
NEXT_TIME "結(jié)束時(shí)間"
FROM
V$ARCHIVED_LOG
6.12 查看歸檔任務(wù)隊(duì)列情況
SELECT
ARCH_TYPE "歸檔類型" ,
WAITING "等待處理任務(wù)數(shù)" ,
CUR_WAIT_TIME/1000||'S' "當(dāng)前任務(wù)等待時(shí)間",
MAX_WAIT_TIME/1000||'S' "最大任務(wù)等待時(shí)間",
TOTAL_WAIT_TIME/1000||'S' "總等待時(shí)間",
AVERAGE_WAIT_TIME/1000||'S' "平均等待時(shí)間"
FROM
V$ARCH_QUEUE
7 會(huì)話與事務(wù)管理
7.1 查看活動(dòng)會(huì)話信息
SELECT
SESS_ID "會(huì)話ID" ,
STATE "狀態(tài)" ,
SQL_TEXT "SQL語(yǔ)句" ,
USER_NAME "用戶" ,
TRX_ID "事務(wù)ID" ,
CREATE_TIME "創(chuàng)建時(shí)間",
CLNT_TYPE "客戶端類型" ,
AUTO_CMT "是否自動(dòng)提交" ,
CLNT_IP "客戶端IP" ,
THRD_ID "線程ID"
FROM
V$SESSIONS
WHERE
STATE='ACTIVE'
7.2 查看會(huì)話統(tǒng)計(jì)信息
--當(dāng)前活動(dòng)會(huì)話數(shù)：
SELECT COUNT(*) FROM V$SESSIONS WHERE STATE='ACTIVE';
--當(dāng)前總會(huì)話數(shù)：
SELECT COUNT(*) FROM V$SESSIONS;
--可用會(huì)話數(shù)=系統(tǒng)允許最大并發(fā)會(huì)話數(shù)-數(shù)據(jù)庫(kù)當(dāng)前會(huì)話數(shù)；
SELECT
PARA_VALUE-
(
SELECT COUNT(*) FROM V$SESSIONS
)
FROM
V$DM_INI
WHERE
PARA_NAME='MAX_SESSIONS';
7.3 查看當(dāng)前活動(dòng)事務(wù)數(shù)量
--當(dāng)前活動(dòng)事務(wù)數(shù)量，動(dòng)態(tài)監(jiān)控：
SELECT COUNT(*) FROM V$TRX WHERE STATUS='ACTIVE';
7.4 查看阻塞與被阻塞信息
SELECT
DS.SESS_ID "被阻塞的會(huì)話ID",
DS.SQL_TEXT "被阻塞的SQL",
DS.TRX_ID "被阻塞的事務(wù)ID",
(CASE L.LTYPE WHEN 'OBJECT' THEN '對(duì)象鎖' WHEN 'TID' THEN '事務(wù)鎖' END CASE ) "被阻塞的鎖類型",
DS.CREATE_TIME "開(kāi)始阻塞時(shí)間",
SS.SESS_ID "占用鎖的會(huì)話ID",
SS.SQL_TEXT "占用鎖的SQL",
SS.CLNT_IP "占用鎖的IP",
L.TID "占用鎖的事務(wù)ID"
FROM
V$LOCK L
LEFT JOIN V$SESSIONS DS
ON
DS.TRX_ID = L.TRX_ID
LEFT JOIN V$SESSIONS SS
ON
SS.TRX_ID = L.TID
WHERE
L.BLOCKED = 1
-- 備注：“占用鎖的會(huì)話ID”表示該會(huì)話占用這個(gè)對(duì)象的鎖，且事務(wù)一直沒(méi)有提交。導(dǎo)致“被阻塞的會(huì)話ID”無(wú)法對(duì)該對(duì)象上鎖。有兩種解決方式：
--（1）SP_CLOSE_SESSION(占用鎖的會(huì)話ID);--殺掉占用鎖的會(huì)話，讓該不提交的事務(wù)回滾，釋放鎖；
--（2）SP_CLOSE_SESSION(被阻塞的會(huì)話ID);--殺掉被阻塞的會(huì)話；
7.5 查看會(huì)話統(tǒng)計(jì)信息
SELECT
SESSID "會(huì)話ID" ,
HARD_PARSE_CNT "硬解析次數(shù)",
LOGIC_READ_CNT "邏輯讀次數(shù)",
PHY_READ_CNT "物理讀次數(shù)" ,
PHY_MULTI_READ_CNT "物理讀多頁(yè)次數(shù)",
IO_WAIT_TIME "IO等待時(shí)間",
TAB_SCAN_CNT "全表掃描次數(shù)",
HASH_JOIN_CNT "HASH連接次數(shù)"
FROM
V$SESSION_STAT
7.6 數(shù)據(jù)庫(kù)啟動(dòng)時(shí)的回滾的事務(wù)信息
select TRX_ID "事務(wù)號(hào)", N_UPAGES "回滾頁(yè)總數(shù)"，N_URECS "回滾記錄總數(shù)" from V$RECV_ROLLBACK_TRX
7.7 關(guān)閉一個(gè)活動(dòng)會(huì)話
sp_close_session(session_id);
7.8 查詢已執(zhí)行完畢但未提交事務(wù)
SELECT
T1.SQL_TEXT,
T1.STATE ,
T1.TRX_ID,
T1.SESS_ID
FROM
V$SESSIONS T1,
V$TRX T2
WHERE
T1.TRX_ID=T2.ID
AND T1.STATE ='IDLE'
AND T2.STATUS='ACTIVE';
7.9 臟數(shù)據(jù)占比統(tǒng)計(jì)
SELECT
(
(
SELECT
ISNULL(MAX(PARA_VALUE), '')
FROM
V$DM_INI
WHERE
UPPER(PARA_NAME)='INSTANCE_NAME'
)
||'_'
||NAME) "內(nèi)存池名稱" ,
(CAST(N_PAGES AS NUMERIC(22, 0)) * CAST(PAGE_SIZE AS NUMERIC(22, 0)))/1024/1024 "分配內(nèi)存大小(MB)",
(CAST(N_DIRTY AS NUMERIC(22, 0)) * CAST(PAGE_SIZE AS NUMERIC(22, 0)))/1024/1024 "臟數(shù)據(jù)大小(MB)" ,
(N_DIRTY /CAST(DECODE(N_PAGES, 0, 1, N_PAGES) AS DOUBLE)) "臟數(shù)據(jù)比例"
FROM
SYS.V$BUFFERPOOL
ORDER BY
NAME;
7.10 查看死鎖信息
SELECT
TRX_ID "事務(wù)ID" ,
SESS_ID "會(huì)話ID" ,
SQL_TEXT "產(chǎn)生死鎖的SQL語(yǔ)句",
HAPPEN_TIME "死鎖時(shí)間"
FROM
V$DEADLOCK_HISTORY;
8 調(diào)度作業(yè)管理
8.1 正在運(yùn)行的作業(yè)信息
SELECT
SID "SESSIONID" ,
LAST_DATE "上一次成功運(yùn)行的時(shí)間",
THIS_DATE "本次運(yùn)行開(kāi)始時(shí)間"
FROM
V$JOBS_RUNNING
8.2 執(zhí)行失敗的定時(shí)任務(wù)統(tǒng)計(jì)
SELECT
NAME AS JOB_NAME,
ERRCODE AS ERR_CODE,
ERRINFO AS ERR_INFO,
START_TIME AS CUR_TIME
FROM
SYSJOB.SYSJOBHISTORIES2
WHERE
ERRCODE < 0
ORDER BY
START_TIME;
9 性能診斷
9.1 查看字典緩沖區(qū)使用情況
SELECT
TOTAL_SIZE/1024/1024||'MB' "總大小",
USED_SIZE/1024/1024||'MB' "已使用大小" ,
DICT_NUM "緩存字典個(gè)數(shù)",
LRU_DISCARD "被淘汰次數(shù)"
FROM
V$DB_CACHE
9.2 查看數(shù)據(jù)緩沖區(qū)使用情況
SELECT /*+GROUP_OPT_FLAG(1)*/
NAME "緩沖區(qū)名稱",
SUM(N_PAGES)*PAGE_SIZE/1024/1024||'MB' "總大小(不含擴(kuò)展池)",
SUM(N_TOTAL_PAGES)*PAGE_SIZE/1024/1024||'MB' "總大小(含擴(kuò)展池)",
SUM(FREE)*PAGE_SIZE/1024/1024||'MB' "空閑大小",
SUM(N_DIRTY)*PAGE_SIZE/1024/1024||'MB' "臟數(shù)據(jù)大小",
SUM(N_CLEAR)*PAGE_SIZE/1024/1024||'MB' "干凈數(shù)據(jù)大小",
AVG(RAT_HIT) "命中率"
FROM
V$BUFFERPOOL
GROUP BY
NAME
9.3 查看SQL緩沖區(qū)的使用情況
SELECT TYPE$ "類型",COUNT(*)"申請(qǐng)次數(shù)",SUM(ITEM_SIZE)/1024/1024||'MB' "占用緩存大小" FROM V$CACHEITEM GROUP BY TYPE$
9.4 查看內(nèi)存池的使用情況
SELECT
NAME "內(nèi)存池名稱",
ORG_SIZE/1024/1024||'MB' "初始大小",
TOTAL_SIZE/1024/1024||'MB' "當(dāng)前總大小",
RESERVED_SIZE/1024/1024||'MB' "已分配出去的大小",
TARGET_SIZE/1024/1024||'MB' "擴(kuò)展目標(biāo)大小" ,
N_EXTEND_NORMAL "TARGET范圍內(nèi)累計(jì)擴(kuò)展次數(shù)" ,
N_EXTEND_EXCLUSIVE "超過(guò) TARGET累計(jì)擴(kuò)展次數(shù)",
N_FREE "釋放的次數(shù)"
FROM
V$MEM_POOL WHERE NAME LIKE '%SHARE%' OR NAME LIKE '%DICT%' OR NAME LIKE '%SQL%';
9.5 查看數(shù)據(jù)庫(kù)內(nèi)存的申請(qǐng)信息
--注：當(dāng)動(dòng)態(tài)參數(shù)MEMORY_LEAK_CHECK 為 1 時(shí)有效
SELECT
RG.POOL ,
SUM(RG.REFNUM) "申請(qǐng)發(fā)起次數(shù)",
SUM(RG.RESERVED_SIZE)/1024/1024||'MB' "占用的內(nèi)存",
SUM(RG.DATA_SIZE)/1024/1024||'MB' "數(shù)據(jù)實(shí)際大小",
RG.FNAME
FROM
V$MEM_REGINFO RG
GROUP BY
POOL
9.6 顯示支持的hint信息
SELECT *FROM V$HINT_INI_INFO
9.7 查看數(shù)據(jù)庫(kù)讀寫(xiě)統(tǒng)計(jì)數(shù)據(jù)
SELECT
NAME "名稱",
STAT_VAL "統(tǒng)計(jì)值"
FROM
V$SYSSTAT
WHERE
NAME IN('logic read count', 'physical read count', 'physical write count');
9.8 收集統(tǒng)計(jì)信息
9.8.1 收集user_name用戶下所有表的統(tǒng)計(jì)信息
DBMS_STATS.GATHER_SCHEMA_STATS('NW_TEST',100,TRUE,'FOR ALL COLUMNS SIZE AUTO');
9.8.2 收集user_name用戶下某個(gè)表的統(tǒng)計(jì)信息
DBMS_STATS.GATHER_TABLE_STATS('USER_NAME','GL_PREBALANCE',NULL,100,TRUE,'FOR ALL COLUMNS SIZE AUTO');
9.8.3 收集某個(gè)表某列的統(tǒng)計(jì)信息
STAT 100 ON T1(ID)
9.8.4 收集user_name用戶下所有索引統(tǒng)計(jì)信息
DBMS_STATS.GATHER_SCHEMA_STATS('用戶名',1.0,TRUE,'FOR ALL INDEXED SIZE AUTO');
9.8.5 收集數(shù)據(jù)庫(kù)中某個(gè)索引的統(tǒng)計(jì)信息
DBMS_STATS.GATHER_INDEX_STATS(USER,'IDX_T2_X');
10 SQL執(zhí)行相關(guān)
10.1 SQL執(zhí)行歷史信息
--要求ENABLE_MONITOR=1;保存條數(shù)依賴于dm.ini參數(shù)SQL_HISTORY_CNT設(shè)置
SELECT
SESS_ID "會(huì)話ID" ,
TOP_SQL_TEXT "SQL語(yǔ)句" ,
START_TIME "SQL開(kāi)始執(zhí)行時(shí)間" ,
TIME_USED "SQL執(zhí)行時(shí)間（毫秒）",
N_LOGIC_READ "邏輯讀次數(shù)" ,
N_PHY_READ "物理讀次數(shù)" ,
(CASE HARD_PARSE_FLAG WHEN 0 THEN '軟解析' WHEN 1 THEN '語(yǔ)義解析' WHEN 2 THEN '硬解析' END CASE) "SQL解析方式"
FROM
V$SQL_HISTORY;
10.2 SQL語(yǔ)句執(zhí)行歷史報(bào)錯(cuò)信息
--要求ENABLE_MONITOR=1;
SELECT DISTINCT
A.SESS_ID "會(huì)話ID",
A.SQL_TEXT "SQL語(yǔ)句",
A.ECPT_DESC "錯(cuò)誤描述",
A.ERR_TIME "時(shí)間",
B.USER_NAME "用戶名",
B.CLNT_IP "IP地址",
B.APPNAME "客戶端"
FROM
SYS.V$RUNTIME_ERR_HISTORY A,
SYS.V$SESSION_HISTORY B
10.3 DDL SQL執(zhí)行歷史信息
SELECT
SESS_ID "會(huì)話ID" ,
TRX_ID "事務(wù)ID" ,
SQL_TEXT "DDL SQL語(yǔ)句",
DDL_TIME "執(zhí)行時(shí)間"
FROM
V$PLSQL_DDL_HISTORY
10.4 SQL等待信息
SELECT
S1.SQL_TEXT "SQL語(yǔ)句",
S2.SQL_TEXT "等待的SQL語(yǔ)句",
W.WAIT_TIME "等待時(shí)間"
FROM
V$TRXWAIT W
LEFT JOIN V$SESSIONS S1
ON
W.ID = S1.TRX_ID
LEFT JOIN V$SESSIONS S2
ON
W.WAIT_FOR_ID = S2.TRX_ID
10.5 開(kāi)啟/關(guān)閉SQL日志
--開(kāi)啟跟蹤日志
SP_SET_PARA_VALUE(1,'SVR_LOG',1);
--關(guān)閉跟蹤日志
SP_SET_PARA_VALUE(1,'SVR_LOG',0);
在DM_HOME/log/下生成dmsql****.log的文本文件，里面記錄每條SQL的執(zhí)行情況
10.6 查詢當(dāng)前數(shù)據(jù)庫(kù)中執(zhí)行時(shí)間超過(guò)1秒的SQL
SELECT
SESS_ID "會(huì)話ID",
SQL_TEXT "SQL語(yǔ)句",
SS "執(zhí)行時(shí)間（秒）",
FULLSQL "完整SQL語(yǔ)句"
FROM
(
SELECT
SESS_ID ,
SQL_TEXT ,
DATEDIFF(SS, LAST_SEND_TIME, SYSDATE) SS,
SF_GET_SESSION_SQL(SESS_ID) FULLSQL
FROM
V$SESSIONS
WHERE
STATE='ACTIVE'
)
WHERE
SS>=1;
11 MPP相關(guān)
11.1 檢查各節(jié)點(diǎn)參數(shù)一致性
SELECT
*
FROM
V$PARAMETER
WHERE
ID IN
(
SELECT
ID
FROM
(
SELECT DISTINCT * FROM V$PARAMETER
)
GROUP BY
ID
HAVING
COUNT(*)>1
)
ORDER BY
ID;
12 數(shù)據(jù)庫(kù)巡檢專用
12.1 dm.ini關(guān)鍵參數(shù)
SELECT
NAME as 名稱 ,
DECODE(TYPE, 'READ ONLY', '手動(dòng)', 'IN FILE', '靜態(tài)', 'SYS', '系統(tǒng)級(jí)動(dòng)態(tài)', 'SESSION', '會(huì)話級(jí)動(dòng)態(tài)')as 類型 ,
VALUE as 當(dāng)前會(huì)話值 ,
SYS_VALUE as 系統(tǒng)值 ,
FILE_VALUE as ini文件值,
DESCRIPTION as 參數(shù)描述
FROM
V$PARAMETER
WHERE
NAME IN ('INSTANCE_NAME', 'MAX_OS_MEMORY', 'MEMORY_POOL', 'BUFFER', 'BUFFER_POOLS', 'MAX_BUFFER', 'RECYCLE', 'RECYCLE_POOLS', 'HJ_BUF_GLOBAL_SIZE', 'HJ_BUF_SIZE', 'DICT_BUF_SIZE', 'TEMP_SIZE', 'VM_POOL_SIZE', 'SESS_POOL_SIZE', 'CACHE_POOL_SIZE', 'WORKER_THREADS', 'TASK_THREADS', 'MAX_SESSION_STATEMENT', 'OPTIMIZER_MODE', 'VIEW_PULLUP_FLAG', 'COMPATIBLE_MODE', 'SVR_LOG', 'MAX_SESSIONS', 'USE_PLN_POOL', 'ENABLE_MONITOR', 'OLAP_FLAG', 'ARCH_INI');
12.2 授權(quán)信息
SELECT '許可證版本' AS 名稱,'V'||LIC_VERSION AS 信息 FROM V$LICENSE UNION ALL
SELECT '序列號(hào)',SERIES_NO FROM V$LICENSE UNION ALL
SELECT '校驗(yàn)碼',CHECK_CODE FROM V$LICENSE UNION ALL
SELECT '制作日期',CAST(DATE_GEN AS VARCHAR(30)) FROM V$LICENSE UNION ALL
SELECT '最終用戶',AUTHORIZED_CUSTOMER FROM V$LICENSE UNION ALL
SELECT '項(xiàng)目名稱',PROJECT_NAME FROM V$LICENSE UNION ALL
SELECT '產(chǎn)品名稱',PRODUCT_TYPE||'('||DECODE(SERVER_SERIES,'P','個(gè)人版','S','標(biāo)準(zhǔn)版','E','企業(yè)版','A','安全版','D','開(kāi)發(fā)版')||')' FROM V$LICENSE UNION ALL
SELECT '產(chǎn)品類型',DECODE(SERVER_TYPE,'1','正式版','2','測(cè)試版','3','試用版') FROM V$LICENSE UNION ALL
SELECT '有效日期',CAST(EXPIRED_DATE AS VARCHAR(30)) FROM V$LICENSE UNION ALL
SELECT '授權(quán)系統(tǒng)',OS_TYPE||' '||DECODE(CPU_TYPE,'X86','X86','X64','X64') FROM V$LICENSE UNION ALL
SELECT '授權(quán)用戶數(shù)',CAST(AUTHORIZED_USER_NUMBER AS VARCHAR(30)) FROM V$LICENSE UNION ALL
SELECT '授權(quán)并發(fā)數(shù)',CAST(CONCURRENCY_USER_NUMBER AS VARCHAR(30)) FROM V$LICENSE UNION ALL
SELECT '授權(quán)CPU個(gè)數(shù)',CAST(MAX_CPU_NUM AS VARCHAR(30)) FROM V$LICENSE UNION ALL
SELECT '授權(quán)CPU核數(shù)',CAST(MAX_CORE_NUM AS VARCHAR(30)) FROM V$LICENSE UNION ALL
SELECT '授權(quán)集群',DECODE(CLUSTER_TYPE,'0000','無(wú)','0001','RAC','0010','讀寫(xiě)分離','0011','讀寫(xiě)分離、RAC','0100','MPP','0101','MPP、RAC','0110','MPP、讀寫(xiě)分離','0111','MPP、讀寫(xiě)分離、RAC','1000','主備','1001','主備、RAC','1010','主備、讀寫(xiě)分離','1011','主備、讀寫(xiě)分離、RAC','1100','主備、MPP','1101','主備、MPP、RAC','1110','主備、MPP、讀寫(xiě)分離','1111','主備、MPP、讀寫(xiě)分離、RAC') FROM V$LICENSE UNION ALL
SELECT '未激活狀態(tài)截止日期',CAST(NOACTIVE_DEADLINE AS VARCHAR(30)) FROM V$LICENSE;
12.3 數(shù)據(jù)庫(kù)實(shí)例信息
SELECT '版本',SVR_VERSION FROM V$INSTANCE UNION ALL
SELECT '數(shù)據(jù)庫(kù)名', NAME FROM V$DATABASE UNION ALL
SELECT '實(shí)例名',INSTANCE_NAME FROM V$INSTANCE UNION ALL
SELECT '系統(tǒng)狀態(tài)',DECODE(STATUS$,'OPEN','打開(kāi)','MOUNT','配置','SUSPEND','掛起') FROM V$INSTANCE UNION ALL
SELECT '實(shí)例模式',DECODE(MODE$,'NORMAL','普通模式','PRIMARY','主機(jī)模式','STANDBY','備機(jī)模式') FROM V$INSTANCE UNION ALL
SELECT '是否啟用歸檔',DECODE(ARCH_MODE,'Y','是','N','否') FROM V$DATABASE UNION ALL
SELECT '數(shù)據(jù)文件總大小', TOTAL_SIZE*PAGE/1024/1024||'MB' FROM V$DATABASE UNION ALL
SELECT '數(shù)據(jù)庫(kù)打開(kāi)次數(shù)', OPEN_COUNT||'' FROM V$DATABASE UNION ALL
SELECT '創(chuàng)建時(shí)間', CAST(CREATE_TIME AS VARCHAR(50)) FROM V$DATABASE UNION ALL
SELECT '啟動(dòng)時(shí)間',START_TIME||'' FROM V$INSTANCE UNION ALL
SELECT '最后檢查點(diǎn)時(shí)間', CAST(LAST_CKPT_TIME AS VARCHAR(50)) FROM V$DATABASE;
12.4 表空間
SELECT
NAME AS 名稱,
DECODE(TYPE$, '1', 'DB類型', '2', '臨時(shí)表空間') AS 類型,
DECODE(STATUS$, '0', 'ONLINE', '1', 'OFFLINE', '2', 'RES_OFFLINE', '3', 'CORRUPT')AS 狀態(tài),
TOTAL_SIZE*PAGE/1024/1024
||'MB' AS 總大小,
FILE_NUM AS 包含文件數(shù)
FROM
V$TABLESPACE;
12.5 數(shù)據(jù)文件
SELECT
PATH AS 路徑,
(TOTAL_SIZE*PAGE/1024/1024
||'MB')AS 總大小,
(FREE_SIZE*PAGE/1024/1024
||'MB')AS 剩余大小,
(CAST((TOTAL_SIZE-FREE_SIZE)*100/TOTAL_SIZE AS NUMERIC(4, 2))
||'%') AS 使用比例 ,
MAX_SIZE AS 擴(kuò)充上限 ,
CREATE_TIME AS 創(chuàng)建日期 ,
MODIFY_TIME AS 修改時(shí)間 ,
LAST_CKPT_TIME AS 最后一次檢查點(diǎn)時(shí)間,
STATUS$ ,
DECODE(RW_STATUS, '1', '讀', '2', '寫(xiě)')AS 讀寫(xiě)狀態(tài)
FROM
V$DATAFILE;
12.6 會(huì)話統(tǒng)計(jì)
SELECT
STATE as 狀態(tài) ,
CLNT_IP as 連接IP,
COUNT(*)as 數(shù)量
FROM
V$SESSIONS
GROUP BY
STATE,
CLNT_IP
ORDER BY
STATE;
12.7 用戶對(duì)象信息
SELECT
OBJECT_TYPE AS 類型 ,
OWNER AS 所屬用戶,
COUNT(*) AS 數(shù)量
FROM
ALL_OBJECTS
WHERE
OWNER NOT IN ('SYS', 'SYSTEM', 'SYSAUDITOR', 'SYSJOB', 'SYSSSO', 'CTISYS')
GROUP BY
OBJECT_TYPE,
OWNER;
12.8 最慢20條SQL
SELECT
TOP 20 TO_DATE(START_TIME, 'YYYY-MM-DD HH24:MI:SS') AS 執(zhí)行起始時(shí)間,
(TIME_USED/1000)
||'MS' AS 執(zhí)行時(shí)間,
TOP_SQL_TEXT AS SQL內(nèi)容
FROM
V$SQL_HISTORY
ORDER BY
TIME_USED DESC;
參考內(nèi)容
達(dá)夢(mèng)社區(qū)
柚子快報(bào)邀請(qǐng)碼778899分享：8.達(dá)夢(mèng)數(shù)據(jù)庫(kù)常用SQL
推薦鏈接
本文內(nèi)容根據(jù)網(wǎng)絡(luò)資料整理，出于傳遞更多信息之目的，不代表金鑰匙跨境贊同其觀點(diǎn)和立場(chǎng)。
轉(zhuǎn)載請(qǐng)注明，如有侵權(quán)，聯(lián)系刪除。
敦煌網(wǎng)行者
