银河麒麟V10一键安装DM8的脚本及高阶运维SQL分享

介质下载地址

一键安装

将iso镜像和安装脚本上传至任意目录下

[root@localhost iso]# chmod +x dm8_single_install.sh
[root@localhost iso]# ./dm8_single_install.sh

[root@localhost iso]# cat dm8_single_install.sh

#!/bin/bash
set -euo pipefailecho -e "\n\n****** 开始安装 DM8 ******\n\n"# 配置参数
ISO_NAME="dm8_20250506_x86_rh7_64.iso"
DM_BASE="/dm"
SYSDBA_PWD="Ceshi@5235"
PORT_NUM="5236"
DB_NAME="PROD"
INSTANCE_NAME="PROD"
INSTALL_LOG="/tmp/dm_install.log"
sudo mount -t tmpfs -o size=2G none /tmp
# 清理旧日志
> "$INSTALL_LOG"# 步骤1: 准备安装环境
echo -n "[1/9] 创建目录结构... "
mkdir -p ${DM_BASE}/{dbms,data,log,arch,bak,script} /dm8_setup/iso
echo "完成"echo -n "[2/9] 准备安装文件... "
mv -f ./* /dm8_setup/iso/ 2>/dev/null || true
echo "完成"# 步骤2: 挂载安装镜像
echo -n "[3/9] 挂载安装镜像... "
mount "/dm8_setup/iso/${ISO_NAME}" /mnt -o loop &>/dev/null
echo "完成"# 步骤3: 系统配置
echo -n "[4/9] 系统配置... "
public_ip=$(hostname -I | awk '{print $1}')
node_name=$(hostname)
grep -q "${public_ip} ${node_name}" /etc/hosts || echo "${public_ip} ${node_name}" >> /etc/hosts
hostnamectl set-hostname "${node_name}" &>/dev/nullgroupadd -g 50000 dinstall &>/dev/null || true
useradd -u 51000 -g dinstall dmdba &>/dev/null || true
echo "dmdba:${SYSDBA_PWD}" | chpasswd &>/dev/nullchown -R dmdba:dinstall "${DM_BASE}"
chmod -R 775 "${DM_BASE}"grep -q "dmdba soft nproc" /etc/security/limits.conf || cat <<EOF >> /etc/security/limits.conf
dmdba soft nproc 16384
dmdba hard nproc 16384
dmdba soft nofile 16384
dmdba hard nofile 65536
dmdba soft stack 16384
dmdba hard stack 32768
EOFsystemctl stop firewalld &>/dev/null || true
systemctl disable firewalld &>/dev/null || true
setenforce 0 &>/dev/null || true
sed -i 's/^SELINUX=.*/SELINUX=disabled/' /etc/selinux/config
echo "完成"# 步骤4: 生成安装配置
echo -n "[5/9] 生成安装配置... "
cat << EOF > "${DM_BASE}/script/silent_setup.xml"
<?xml version="1.0"?>
<DATABASE>
<LANGUAGE>en</LANGUAGE>
<TIME_ZONE>+08:00</TIME_ZONE>
<KEY></KEY>
<INSTALL_TYPE>0</INSTALL_TYPE>
<INSTALL_PATH>${DM_BASE}/dbms</INSTALL_PATH>
<INIT_DB>y</INIT_DB>
<DB_PARAMS>
<PATH>${DM_BASE}/data</PATH>
<DB_NAME>${DB_NAME}</DB_NAME>
<INSTANCE_NAME>${INSTANCE_NAME}</INSTANCE_NAME>
<PORT_NUM>${PORT_NUM}</PORT_NUM>
<CTL_PATH>${DM_BASE}/data/${DB_NAME}/dm.ctl</CTL_PATH>
<LOG_PATHS><LOG_PATH>${DM_BASE}/log/${DB_NAME}/redo01.log</LOG_PATH><LOG_PATH>${DM_BASE}/log/${DB_NAME}/redo02.log</LOG_PATH>
</LOG_PATHS>
<EXTENT_SIZE>16</EXTENT_SIZE>
<PAGE_SIZE>16</PAGE_SIZE>
<LOG_SIZE>256</LOG_SIZE>
<CASE_SENSITIVE>Y</CASE_SENSITIVE>
<CHARSET>0</CHARSET>
<SYSDBA_PWD>${SYSDBA_PWD}</SYSDBA_PWD>
<SYSAUDITOR_PWD>${SYSDBA_PWD}</SYSAUDITOR_PWD>
<SYSSSO_PWD>${SYSDBA_PWD}</SYSSSO_PWD>
<SYSDBO_PWD>${SYSDBA_PWD}</SYSDBO_PWD>
<TIME_ZONE>+08:00</TIME_ZONE>
</DB_PARAMS>
<CREATE_DB_SERVICE>n</CREATE_DB_SERVICE>
<STARTUP_DB_SERVICE>n</STARTUP_DB_SERVICE>
</DATABASE>
EOFchown dmdba:dinstall "${DM_BASE}/script/silent_setup.xml" &>/dev/null
echo "完成"# 步骤5: 执行数据库安装（添加进度指示）
echo -n "[6/9] 正在安装数据库 "
export DM_INSTALL_TMPDIR=/tmp
export LANG=en_US.UTF-8# 后台安装并显示进度指示器
(sudo -u dmdba /mnt/DMInstall.bin -q "${DM_BASE}/script/silent_setup.xml" &>> "$INSTALL_LOG"
) &
pid=$!# 显示旋转进度指示
spin='-\|/'
i=0
while kill -0 $pid 2>/dev/null; doi=$(( (i+1) %4 ))printf "\b${spin:$i:1}"sleep 0.5
done# 等待后台任务完成
wait $pid# 检查安装结果
if [ -d "${DM_BASE}/dbms/bin" ]; thenecho -e "\b完成"
elseecho -e "\n\n[错误] 数据库安装失败！请检查日志: $INSTALL_LOG"exit 1
fi# 步骤6: 安装后配置
echo -n "[7/9] 安装后配置... "
"${DM_BASE}/dbms/script/root/root_installer.sh" &>> "$INSTALL_LOG"
"${DM_BASE}/dbms/script/root/dm_service_installer.sh" -t dmserver -p "${DB_NAME}" -dm_ini "${DM_BASE}/data/${DB_NAME}/dm.ini" &>> "$INSTALL_LOG"
echo "完成"# 步骤7: 环境配置
echo -n "[8/9] 环境配置... "
cat << EOF >> /home/dmdba/.bashrc
export DM_HOME="${DM_BASE}/dbms"
export PATH="\$PATH:\${DM_HOME}/bin"
export LD_LIBRARY_PATH="\$LD_LIBRARY_PATH:\${DM_HOME}/bin"
EOF
echo "完成"# 步骤8: 启动服务
echo -n "[9/9] 启动数据库服务... "
sudo -u dmdba "${DM_BASE}/dbms/bin/DmService${DB_NAME}" start &>> "$INSTALL_LOG"
sleep 3  # 给服务启动时间# 检查服务状态
if sudo -u dmdba "${DM_BASE}/dbms/bin/DmService${DB_NAME}" status | grep -q "running"; thenecho "完成"
elseecho "失败"echo "[警告] 服务启动失败，请检查日志: $INSTALL_LOG"
fi# 清理工作
umount /mnt &>/dev/null || trueecho -e "\n\n****** DM8 安装成功完成！ ******"
echo -e "数据库端口: \033[32m${PORT_NUM}\033[0m"
echo -e "管理员账号: \033[32msysdba\033[0m"
echo -e "管理员密码: \033[32m${SYSDBA_PWD}\033[0m"
echo -e "安装目录: \033[32m${DM_BASE}/dbms\033[0m"
echo -e "数据目录: \033[32m${DM_BASE}/data\033[0m"
echo -e "服务状态: \033[32m$(sudo -u dmdba ${DM_BASE}/dbms/bin/DmService${DB_NAME} status | grep -o "running")\033[0m"
echo -e "安装日志: \033[34m${INSTALL_LOG}\033[0m\n\n"

过程截图

日常运维命令

-- 1. 数据库授权信息查询
SELECT LIC_VERSION AS "许可证版本",SERIES_NO AS "序列号",CHECK_CODE AS "校验码",AUTHORIZED_CUSTOMER AS "最终用户",PROJECT_NAME AS "项目名称",PRODUCT_TYPE AS "产品名称",CASE SERVER_TYPE WHEN '1' THEN '正式版' WHEN '2' THEN '测试版' WHEN '3' THEN '试用版' WHEN '4' THEN '其他' END AS "产品类型",TO_CHAR(EXPIRED_DATE) AS "有效日期",OS_TYPE AS "授权系统",TO_CHAR(AUTHORIZED_USER_NUMBER) AS "授权用户数",NVL(TO_CHAR(CONCURRENCY_USER_NUMBER),'') AS "授权并发数",NVL(TO_CHAR(MAX_CPU_NUM),'') AS "授权CPU个数",CLUSTER_TYPE AS "授权集群"
FROM V$LICENSE;
-- 注意事项：
-- 1）需关注数据库授权的有效日期，到期前需及时更换
-- 2）注意授权CPU个数字段值，曾出现因限制CPU使用个数导致DB性能下降的情况-- 2. 查询数据库的实例信息
SELECT '版本号',(SELECT id_code) FROM v$instance
UNION ALL
SELECT '数据库名',name FROM v$database
UNION ALL
SELECT '实例名',instance_name FROM v$instance
UNION ALL
SELECT '系统状态',status$ FROM v$instance
UNION ALL
SELECT '实例模式',mode$ FROM v$instance
UNION ALL
SELECT '是否启用归档',case arch_mode when 'Y' then '是' when 'N' then '否' end FROM v$database
UNION ALL
SELECT '页大小',cast(PAGE()/1024 as varchar)
UNION ALL
SELECT '大小写敏感',cast(case SF_GET_CASE_SENSITIVE_FLAG()when '1' then '是' when '0' then '否' 
end as varchar)
UNION ALL
SELECT '字符集',CASE SF_GET_UNICODE_FLAG() WHEN '0' THEN 'GBK18030' WHEN '1' then 'UTF-8' when '2' then 'EUC-KR' 
end
UNION ALL
SELECT '以字符为单位',cast(case SF_GET_LENGTH_IN_CHAR()when '1' then '是' when '0' then '否' 
end as varchar)
UNION ALL
SELECT '空白字符填充模式',cast(case (select BLANK_PAD_MODE()) when '1' then '是' when '0' then '否' 
end as varchar)
UNION ALL
SELECT '日志文件个数',to_char(count(*)) FROM v$rlogfile
UNION ALL
SELECT '日志文件大小',cast(RLOG_SIZE/1024/1024 as varchar) FROM v$rlogfile where rowid =1
UNION ALL
SELECT '创建时间',to_char(create_time) FROM v$database
UNION ALL
SELECT '启动时间',to_char(last_startup_time) FROM v$database;
-- 注意事项：
-- 1）该SQL查询数据库安装信息参数，实例迁移时需保障两端实例参数一致
-- 2）系统状态字段如为非OPEN状态，需检查数据库是否正常
-- 3）日志文件大小（redo日志）不建议低于2G，且建议大小一致-- 3. 查询数据库中语句统计信息
SELECT NAME, STAT_VAL 
FROM v$sysstat 
WHERE name IN ('select statements','insert statements','delete statements','update statements','ddl statements','transaction total count'
);
-- 注意事项：
-- 1）该值为数据库启动后从0递增的计数，重启后重置
-- 2）可用于监控特定时间段SQL执行情况-- 4. 数据库表空间的状态检查
SELECT NAME AS "NAME",CASE TYPE$ WHEN '1' THEN 'DB类型' WHEN '2' THEN '临时表空间' END AS "TYPE",CASE STATUS$ WHEN '0' THEN '联机' WHEN '1' THEN '脱机' WHEN '2' THEN '脱机' WHEN '3' THEN '损坏'END AS "STATUS",TOTAL_SIZE*PAGE/1024/1024 AS "TOTALSIZE",FILE_NUM AS "FILENUM"
FROM V$TABLESPACE;
-- 注意事项：
-- 1）需重点关注表空间类型非联机的情况，需重点监控-- 5. 查询数据库表空间的使用情况
SELECT F.TABLESPACE_NAME,ROUND((T.TOTAL_SPACE - F.FREE_SPACE) / 1024, 2) "USED",CASE WHEN H.TOTAL_MAX_SPACE == 0 THEN ROUND(F.FREE_SPACE / 1024, 2) ELSE ROUND((H.TOTAL_MAX_SPACE -(T.TOTAL_SPACE - F.FREE_SPACE)) / 1024, 2) END "FREE_MAX",CASE WHEN H.TOTAL_MAX_SPACE == 0 THEN ROUND(T.TOTAL_SPACE / 1024, 2) ELSE ROUND(H.TOTAL_MAX_SPACE / 1024, 2) END "TOTAL_MAX",CASE WHEN H.TOTAL_MAX_SPACE == 0 THEN ROUND((F.FREE_SPACE/1024)/(T.TOTAL_SPACE / 1024), 4)*100||'%' ELSE ROUND(((H.TOTAL_MAX_SPACE-(T.TOTAL_SPACE - F.FREE_SPACE))/1024)/(H.TOTAL_MAX_SPACE / 1024), 4)*100||'%' END PER_FREE_MAX,CASE WHEN H.TOTAL_MAX_SPACE == 0 THEN ROUND((((T.TOTAL_SPACE - F.FREE_SPACE))/1024)/(T.TOTAL_SPACE / 1024), 4)*100||'%' ELSE ROUND((((T.TOTAL_SPACE - F.FREE_SPACE))/1024)/(H.TOTAL_MAX_SPACE / 1024), 4)*100||'%' END PER_USED_MAX,ROUND(F.FREE_SPACE / 1024, 2) "FREE",ROUND(T.TOTAL_SPACE / 1024, 2) "TOTAL",CASE WHEN T.TOTAL_SPACE == 0 THEN '' ELSE (ROUND((F.FREE_SPACE / T.TOTAL_SPACE), 4)* 100) || '% ' END PER_FREE,CASE WHEN T.TOTAL_SPACE == 0 THEN '' ELSE (ROUND((T.TOTAL_SPACE - F.FREE_SPACE) / T.TOTAL_SPACE, 4) * 100)||'%' END PER_USED
FROM ( SELECT TABLESPACE_NAME,ROUND(SUM(BLOCKS * (SELECT PARA_VALUE / 1024 FROM V$DM_INI WHERE PARA_NAME = 'GLOBAL_PAGE_SIZE') / 1024)) FREE_SPACEFROM DBA_FREE_SPACEGROUP BY TABLESPACE_NAME 
) F, 
( SELECT TABLESPACE_NAME,ROUND(SUM(BYTES / 1048576)) TOTAL_SPACEFROM DBA_DATA_FILESGROUP BY TABLESPACE_NAME 
) T, 
( SELECT TABLESPACE_NAME,ROUND(SUM(MAXBYTES / 1048576)) TOTAL_MAX_SPACEFROM DBA_DATA_FILESGROUP BY TABLESPACE_NAME 
) H 
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME AND F.TABLESPACE_NAME = H.TABLESPACE_NAME;
-- 注意事项：
-- 1）MAIN|TEMP|ROLL|SYSTEM表空间由系统自动管理，无需关注使用率
-- 2）计算公式复杂原因：一个表空间可包含多个数据文件，若存在自动拓展且无上限的文件，使用率参考意义较低-- 6. 查询表空间的数据文件使用情况
SELECT PATH,TO_CHAR(TOTAL_SIZE*PAGE/1024/1024) AS TOTAL_SIZE,TO_CHAR(FREE_SIZE*PAGE/1024/1024) AS FREE_SIZE,(TO_CHAR(100-FREE_SIZE*100/TOTAL_SIZE)) AS REM_PER,CASE AUTO_EXTEND WHEN '0' THEN '未开启' WHEN '1' THEN '已开启' END AS AUTO_EXTEND,NEXT_SIZE,MAX_SIZE,b.TABLESPACE_NAME
FROM V$DATAFILE a,dba_data_files b 
WHERE b.file_name = a.PATH 
ORDER BY GROUP_ID;
-- 注意事项：
-- 1）MAIN|TEMP|ROLL|SYSTEM表空间由系统自动管理，无需关注-- 7. 查询数据库中的用户信息
SELECT A.USERNAME,CASE B.RN_FLAG WHEN '0' THEN '否' WHEN '1' THEN '是' END AS READ_ONLY,CASE A.ACCOUNT_STATUS WHEN 'LOCKED' THEN '锁定' WHEN 'OPEN' THEN '正常' END AS "状态",TO_CHAR(A.LOCK_DATE,'YYYY-MM-DD HH24:MI:SS') AS "锁定起始时间",TO_CHAR(A.EXPIRY_DATE,'YYYY-MM-DD HH24:MI:SS') AS "密码截止使用时间",TO_CHAR(round(datediff(DAY,TO_CHAR(sysdate,'YYYY-MM-DD HH24:MI:SS'),TO_CHAR(A.EXPIRY_DATE,'YYYY-MM-DD HH24:MI:SS')),2)) AS EXPIRY_DATE_DAY,TO_CHAR(round(datediff(DAY,TO_CHAR(sysdate,'YYYY-MM-DD HH24:MI:SS'),TO_CHAR(A.LOCK_DATE,'YYYY-MM-DD HH24:MI:SS')),2)) AS LOCK_DATE_DAY,A.DEFAULT_TABLESPACE,A.PROFILE,TO_CHAR(A.CREATED,'YYYY-MM-DD HH24:MI:SS') AS CREATE_TIME
FROM DBA_USERS A,SYSUSERS B 
WHERE A.USER_ID=B.ID;
-- 注意事项：
-- 1）需关注密码设置有效期后即将到期的用户
-- 2）需关注非预期情况下的用户账号锁定状态-- 8. 查询数据库中用户权限
SELECT USERNAME AS "用户名", WM_CONCAT(PRIVILEGE) AS "默认权限"
FROM(SELECT A.USERNAME,C.PRIVILEGE FROM DBA_USERS A,SYSUSERS B,(SELECT A.* FROM (SELECT GRANTEE,GRANTED_ROLE PRIVILEGE,'ROLE_PRIVS' PRIVILEGE_TYPE,CASE ADMIN_OPTION WHEN 'Y' THEN 'YES' ELSE 'NO' END ADMIN_OPTION FROM DBA_ROLE_PRIVSUNION SELECT GRANTEE,PRIVILEGE,'SYS_PRIVS' PRIVILEGE_TYPE,ADMIN_OPTION FROM DBA_SYS_PRIVSUNION SELECT GRANTEE,PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME PRIVILEGE,'TABLE_PRIVS' PRIVILEGE_TYPE,GRANTABLE FROM DBA_TAB_PRIVS) AWHERE GRANTEE IN (SELECT USERNAME FROM ALL_USERS WHERE USERNAME NOT IN ('SYS','SYSDBA','SYSSSO','SYSAUDITOR') ) ) C WHERE A.USER_ID=B.ID AND A.USERNAME = C.GRANTEE
)
GROUP BY USERNAME;
-- 注意事项：
-- 1）需关注是否给应用用户授予DBA权限等过高权限-- 9. 查询数据库中的对象是否无效（函数、存储过程、包等对象）
SELECT OWNER,OBJECT_NAME,OBJECT_TYPE,TO_CHAR(CREATED,'YYYY-MM-DD HH24:MI:SS'),TO_CHAR(LAST_DDL_TIME,'YYYY-MM-DD HH24:MI:SS')
FROM DBA_OBJECTS 
WHERE OWNER NOT IN('SYS','SYSJOB','SYSAUDITOR','CTISYS','SYSSSO'
)AND STATUS = 'INVALID';
-- 注意事项：
-- 1）需关注库中无效的函数、包、存储过程等对象，评估是否需要处理-- 10. 查询数据库中的大表信息
SELECT A.TABLE_NAME,A.TABLESPACE_NAME,B.OWNER,B.BYTES
FROM (SELECT TABLE_NAME,TABLESPACE_NAME FROM ALL_TABLES  GROUP BY TABLE_NAME,TABLESPACE_NAME
) AS A 
LEFT JOIN (SELECT OWNER,SEGMENT_NAME,SUM(BYTES) BYTES FROM DBA_SEGMENTS WHERE SEGMENT_TYPE='TABLE'GROUP BY OWNER,SEGMENT_NAME
) AS B 
ON A.TABLE_NAME=B.SEGMENT_NAME 
WHERE B.OWNER NOT IN ('SYS','SYSDBA','SYSAUDITOR','SYSSSO','CTISYS') 
ORDER BY BYTES DESC 
LIMIT 10;-- 11. 查询数据库中的分区大表信息
SELECT A.OWNER,A.TABLE_NAME,A.PARTITIONING_TYPE,TO_CHAR(ROUND(TABLE_USED_SPACE(A.OWNER, A.TABLE_NAME) * PAGE / 1024.0 / 1024, 2)) SIZEMB,A.PARTITION_COUNT as partition_num,table_rowcount(a.owner, a.table_name) as row_num
FROM DBA_PART_TABLES a;-- 12. 查询数据库中会话的使用情况
SELECT *
FROM (SELECT STATE,CASE WHEN INSTR(CLNT_IP, ':',8) > 0 THEN SUBSTR(CLNT_IP, 1, INSTR(CLNT_IP, ':',8) - 1) ELSE CLNT_IP END AS CLNT_IP,CLNT_TYPE,CURR_SCH,USER_NAME,COUNT(*) COUNTSFROM V$SESSIONSGROUP BY STATE,CASE WHEN INSTR(CLNT_IP, ':',8) > 0 THEN SUBSTR(CLNT_IP, 1, INSTR(CLNT_IP, ':',8) - 1) ELSE CLNT_IP END,CLNT_TYPE,CURR_SCH,USER_NAMEORDER BY STATE
);-- 13. 长时间空闲会话检查
SELECT SESS_ID,SESS_SEQ,USER_NAME,CREATE_TIME,CLNT_TYPE,CLNT_HOST,CLNT_IP,OSNAME,CONN_TYPE,CLNT_VER 
FROM SYS.V$SESSIONS 
WHERE STATE = 'IDLE'AND DATEDIFF(HH, LAST_SEND_TIME, SYSDATE) > 48AND DATEDIFF(HH, CREATE_TIME, SYSDATE) > 48;
-- 注意事项：
-- 1）达梦数据库默认不会自动断开会话连接
-- 2）曾出现因未提交事务导致其他事务等待的情况（如未提交的insert）
-- 3）建议定期清理长时间空闲会话-- 14. 查询数据库的redo日志大小
SELECT FILE_ID,PATH,CLIENT_PATH,RLOG_SIZE FROM V$RLOGFILE;
-- 注意事项：
-- 1）单个redo日志文件不建议低于2G，且建议所有redo日志大小一致-- 15. 查询数据库的定时任务信息
SELECT SYSJOB."NAME",SCHE."NAME" SCHENAME,SCHE."JOBID",SCHE."TYPE",SCHE."FREQ_INTERVAL",SCHE."FREQ_SUB_INTERVAL",SCHE."STARTTIME",STEPS."NAME" STEPSNAME,STEPS."SEQNO" STEPSSEQNO,STEPS."TYPE" STEPSTYPE,STEPS.COMMAND WHAT,STEPS.SUCC_ACTION,STEPS.FAIL_ACTION
FROM SYSJOB.SYSJOBSCHEDULES SCHE
LEFT JOIN SYSJOB.SYSJOBSTEPS STEPSON SCHE.JOBID = STEPS.JOBID
LEFT JOIN SYSJOB.SYSJOBS SYSJOBON SCHE.JOBID = SYSJOB.ID
WHERE SCHE.VALID == 'Y'
ORDER BY STEPS.JOBID, STEPS.SEQNO ASC;
-- 注意事项：
-- 1）检查是否配置定时数据备份任务，保障数据安全-- 16. 查询定时任务是否有错误
SELECT NAME,'' STEPNAME,MAX(START_TIME) START_TIME,ERRINFO 
FROM ( SELECT NAME,MAX(START_TIME) START_TIME,ERRINFO FROM SYSJOB.SYSSTEPHISTORIES2 WHERE ERRCODE !=0 GROUP BY NAME, ERRINFO UNION ALL SELECT NAME,MAX(START_TIME) START_TIME,ERRINFO FROM SYSJOB.SYSJOBHISTORIES2 WHERE ERRCODE !=0 GROUP BY NAME, ERRINFO 
) 
WHERE TO_CHAR(START_TIME,'YYYY-MM-DD HH24:MI:SS') >= TO_CHAR(TRUNC(ADD_DAYS(SYSDATE, -7)),'YYYY-MM-DD HH24:MI:SS')
GROUP BY NAME, ERRINFO
ORDER BY START_TIME DESC 
LIMIT 10;-- 17. 数据字典的淘汰情况
SELECT ROUND(TOTAL_SIZE/1024.0/1024, 2) TOTALSIZE,ROUND(USED_SIZE /1024.0/1024, 2) USEDSIZE,DICT_NUM DICTNUM,ROUND(SIZE_LRU_DISCARD/1024.0/1024, 2) SIZELRUDISCARD,LRU_DISCARD LRUDISCARD,ROUND((USED_SIZE/1024.0/1024)/(TOTAL_SIZE/1024.0/1024)*100, 2) CACHE_HIT
FROM V$DB_CACHE;
-- 注意事项：
-- 1）根据数据字典的使用/淘汰率，判断是否需要调整数据字典缓冲区参数-- 18. 查询数据库中的无效索引
SELECT owner, index_name,table_name,index_type,status 
FROM dba_indexes
WHERE status != 'VALID' AND owner NOT IN ('SYS', 'SYSAUDITOR', 'SYSSSO', 'SYSDBA', 'DEM', 'SYSJOB', 'SYSDBO')
ORDER BY 1,2,3;-- 19. 查询数据库分区表中的无效索引
SELECT * 
FROM (SELECT SCH_NAME, INDEX_NAME, PARTITION_NAME, SUBPARTITION_NAME,STATUS FROM DBA_IND_SUBPARTITIONSUNION SELECT SCH_NAME, INDEX_NAME, PARTITION_NAME, NULL,STATUS FROM DBA_IND_PARTITIONSUNION SELECT OWNER, INDEX_NAME, NULL, NULL,STATUS FROM DBA_INDEXES
) S 
WHERE S.STATUS = 'UNUSABLE'AND S.SCH_NAME NOT IN ('SYS', 'SYSAUDITOR', 'SYSSSO', 'SYSDBA', 'DEM', 'SYSJOB', 'SYSDBO')
ORDER BY 1, 2;-- 20. 查询数据库中的大索引信息
SELECT objname AS "对象名",objtype as "对象类型",TABLESPACE_NAME AS "表空间",to_char(round(TOT_BLOCKS/1024.0/1024.0*page(),2)) AS "大小(MB)"
FROM (SELECT objname,objtype,TABLESPACE_NAME,SUM(page_used) TOT_BLOCKS FROM (select * from (select owner||'.'||index_name objname, 'INDEX/INDEX PART' objtype, TABLESPACE_NAME, INDEX_USED_PAGES(owner,index_name) page_used from dba_indexes where tablespace_name not in ('TEMP','ROLL','SYSTEM')and owner not in ('SYS','SYSAUDITOR','SYSSSO','SCHEDULER')and temporary='N'and INDEX_TYPE != 'CLUSTER'and INDEX_USED_PAGES(owner,index_name)> (select sum(TOTAL_SIZE)* 0 from v$datafile)order by index_used_space(owner,table_name) desc)order by page_used desclimit 10)GROUP BY objname,objtype,TABLESPACE_NAMEorder by TOT_BLOCKS DESC limit 10
);-- 21. 查询监视器信息
SELECT TO_CHAR(DW_CONN_TIME, 'YYYY-MM-DD HH24:MI:SS') CONN_TIME,MON_CONFIRM,MON_IP,MON_ID,MON_TERM 
FROM v$dmmonitor;-- 22. 查询实例运行错误的日志
SELECT * 
FROM V$INSTANCE_LOG_HISTORY 
WHERE LEVEL$ NOT IN ('INFO','WARN');-- 23. 查询数据库中是否存在死锁
SELECT TO_CHAR(HAPPEN_TIME,'YYYY-MM-DD HH24:MI:SS') HAPPEN_TIME,SQL_TEXT 
FROM V$DEADLOCK_HISTORY 
WHERE HAPPEN_TIME > DATEADD(DAY,-30,SYSDATE);-- 24. 查询数据库中已经运行后的慢SQL
SELECT SQL_TEXT,EXEC_TIME,FINISH_TIME 
FROM V$SYSTEM_LONG_EXEC_SQLS 
ORDER BY EXEC_TIME DESC;-- 25. 查询数据库中运行报错的SQL语句
SELECT SQL_TEXT,ECPT_DESC,max(ERR_TIME)ERR_TIME 
FROM V$RUNTIME_ERR_HISTORY  
GROUP BY SQL_TEXT,ECPT_DESC 
LIMIT 10;-- 26. 查询数据库中正在运行的慢SQL
SELECT * 
FROM ( SELECT DATEDIFF(MS,LAST_RECV_TIME,SYSDATE) EXEC_TIME,DBMS_LOB.SUBSTR(SF_GET_SESSION_SQL(SESS_ID)) SLOW_SQL,SESS_ID,CURR_SCH,THRD_ID,LAST_RECV_TIME,SUBSTR(CLNT_IP,8,13) CONN_IPFROM V$SESSIONS WHERE 1=1AND STATE='ACTIVE'ORDER BY 1 DESC
) 
WHERE EXEC_TIME >= ? AND LAST_RECV_TIME > TO_TIMESTAMP('2000-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') 
LIMIT ?;
-- 说明：其中“?”为参数占位符，需根据实际情况替换（如执行时间阈值和返回条数限制）