达梦数据库常用SQL

冯冯冯冯超宇

已于 2022-11-17 16:25:34 修改

阅读量3.2k

点赞数 6

分类专栏： DM8 达梦数据库文章标签：数据库 sql

于 2022-11-09 00:55:10 首次发布

本文链接：https://blog.csdn.net/qq_38114620/article/details/127761690

版权

DM8 同时被 2 个专栏收录

10 篇文章 0 订阅

订阅专栏

达梦数据库

10 篇文章 0 订阅

订阅专栏

达梦数据库常用SQL使用

1.DM常用SQL

注：以下测试用例模式为DMHR

1.DM常用SQL

1.1 表行数

--CALL SP_CREATE_SYSTEM_PACKAGES(1); --创建系统包
--CALL SP_DB_STAT_INIT (); --对库上所有表及索引 生成统计信息
SELECT TABLE_NAME AS 表名,OWNER AS 所属用户,TABLESPACE_NAME AS 所属表空间,NUM_ROWS AS 行数 FROM DBA_TABLES WHERE OWNER NOT IN ('SYS','SYSTEM','SYSAUDITOR','SYSJOB','SYSSSO','CTISYS') AND TABLESPACE_NAME != 'TEMP';

在这里插入图片描述

1.2 模式下所有表行数

--CALL SP_CREATE_SYSTEM_PACKAGES(1); --创建系统包
--CALL SP_DB_STAT_INIT (); --对库上所有表及索引 生成统计信息
SELECT OWNER,TABLE_NAME,NUM_ROWS,TABLESPACE_NAME FROM DBA_TABLES T WHERE T.OWNER = UPPER('DMHR');

在这里插入图片描述

统计所有模式下所有表的数据量：

SELECT OWNER,SUM(NUM_ROWS) FROM DBA_TABLES WHERE OWNER=’SYSDBA’ OR OWNER=’SCHEMA’ GROUP BY OWNER;

在这里插入图片描述

1.3 模式下表大小、表注释、表空间

SELECT A.OWNER AS "模式",A.SEGMENT_NAME AS "表名",A.BYTES/1024/1024 AS "大小(M)",A.TABLESPACE_NAME AS "所属表空间",B.COMMENTS AS "表注释" FROM DBA_SEGMENTS A,DBA_TAB_COMMENTS B WHERE A.OWNER=B.OWNER AND A.SEGMENT_NAME=B.TABLE_NAME AND A.OWNER='DMHR' ORDER BY SEGMENT_NAME ASC;

在这里插入图片描述

1.4 模式下表的列、列注释

SELECT A.OWNER AS "模式",A.TABLE_NAME  AS "表名",A.COLUMN_NAME AS "列名",A.DATA_TYPE AS "列类型",A.DATA_LENGTH AS "列长度",A.COLUMN_ID AS "列序号",DECODE(A.NULLABLE,'Y','是','N','否') AS "是否可为空",B.COMMENTS AS "注释" FROM DBA_TAB_COLUMNS A,DBA_COL_COMMENTS B WHERE A.OWNER=B.OWNER AND A.TABLE_NAME=B.TABLE_NAME AND A.COLUMN_NAME=B.COLUMN_NAME AND A.OWNER='DMHR';

在这里插入图片描述

1.5 模式下索引信息

SELECT TABLE_OWNER AS "表归属",TABLE_NAME AS "表名",OWNER AS "索引归属",INDEX_NAME AS "索引名",INDEX_TYPE AS "索引类型",TABLESPACE_NAME AS "索引所在表空间",JOIN_INDEX AS "是否组合索引",DECODE(VISIBILITY,'VISIBLE','可见','INVISIBLE','不可见') AS "索引是否可见" FROM DBA_INDEXES WHERE OWNER='DMHR';

在这里插入图片描述

1.6 模式下有哪些表

SELECT NAME AS "表名" FROM SYSOBJECTS WHERE SUBTYPE$='UTAB' AND SCHID IN (SELECT A.ID FROM SYSOBJECTS A,ALL_USERS B WHERE A.TYPE$='SCH' AND A.PID=B.USER_ID AND A.NAME='DMHR');

在这里插入图片描述

1.7 模式下表总数统计

select count(*) from all_all_tables where owner='DMHR';

在这里插入图片描述

1.8 当前用户下有哪些模式

普通用户(非DBA权限)根据当前用户ID，查询当前用户下有哪些模式

SELECT A.NAME AS "模式名",A.ID AS "模式ID" FROM SYSOBJECTS A,ALL_USERS B WHERE A.TYPE$='SCH' AND A.PID=B.USER_ID;

在这里插入图片描述

1.9 所有用户信息

SELECT USER_ID AS "用户ID",USERNAME AS "用户名",ACCOUNT_STATUS AS "状态",DEFAULT_TABLESPACE AS "默认表空间",CREATED AS "创建时间",LOCK_DATE AS "锁定开始时间",EXPIRY_DATE AS "密码有效期截止" FROM DBA_USERS;

在这里插入图片描述

1.10 整库对象信息

SELECT DECODE(OBJECT_TYPE,'SCH','模式','TABLE','表','VIEW','视图','MATERIALIZED VIEW','物化视图','PROCEDURE','存储过程','FUNCTION','函数','SEQUENCE','序列','TRIGGER','触发器','PACKAGE','包','PACKAGE BODY','包主体','CLASS','类','SYNONYM','同义词','DOMAIN','域','TYPE','自定义类型','INDEX','索引','CONSTRAINT','约束','SEQUENCE','序列','TABLE PARTITION','分区表') AS 对象名称,OWNER AS 所属用户,COUNT(*)AS 数量 FROM DBA_OBJECTS WHERE OWNER NOT IN ('SYS','SYSTEM','SYSAUDITOR','SYSJOB','SYSSSO','CTISYS') GROUP BY OBJECT_TYPE,OWNER ORDER BY OWNER ASC,OBJECT_TYPE DESC;

在这里插入图片描述

1.11 表结构

SELECT TABLEDEF('DMHR','CITY') FROM DUAL;  #查看DMHR模式下CITY表的结构

在这里插入图片描述

1.12 数据库版本、实例信息

SELECT B.SVR_VERSION AS "数据库版本", C.DB_MAGIC AS "数据库MAGIC",A.NAME AS "数据库名",B.INSTANCE_NAME AS "实例名",B.STATUS$ AS "系统状态",B.MODE$ AS "模式",DECODE(A.ARCH_MODE,'Y','是','N','否') AS "是否归档",B.START_TIME AS "服务启动时间" FROM V$DATABASE A,V$INSTANCE B,V$RLOG C;

在这里插入图片描述

1.13 DM8数据库版本

SELECT SUBSTR(SVR_VERSION,1,23)||REPLACE(REPLACE(REPLACE('v8.'||ID_CODE,'-','.'),'.21.','-Build(2021.'),'.ENT',')ent') FROM V$INSTANCE;
SELECT ID_CODE;
SELECT ID_CODE();
./disql -id

在这里插入图片描述

1.14 表空间信息

SELECT NAME AS 名称,DECODE(TYPE$,'1','DB类型','2','临时表空间')AS 类型,DECODE(STATUS$,'0','联机','1','脱机','2','RES_OFFLINE','3','CORRUPT')AS 状态,TOTAL_SIZE*PAGE/1024/1024 AS 总大小MB,FILE_NUM AS 包含文件数 FROM V$TABLESPACE;

在这里插入图片描述

1.15 Key信息

SELECT SERIES_NO AS "序列号",CHECK_CODE AS "校验码",DECODE(SERVER_SERIES,'P','个人版','S','标准版','E','企业版','A','安全版','D','开发版') as "系列名称",DECODE(SERVER_TYPE,'1','正式版','2','测试版','3','试用版') as "版本类型",AUTHORIZED_CUSTOMER AS "最终用户",PROJECT_NAME AS "项目名称",OS_TYPE AS "授权操作系统",CPU_TYPE AS "授权CPU类型",MAX_CPU_NUM AS "授权CPU个数",DECODE(CLUSTER_TYPE,'0000','无','0001','DSC','0010','RWC','0011','RWC、DSC','0100','MPP','0101','MPP、DSC','0110','MPP、RWC','0111','MPP、RWC、DSC','1000','DW','1001','DW、DSC','1010','DW、RWC','1011','DW、RWC、DSC','1100','DW、MPP','1101','DW、MPP、DSC','1110','DW、MPP、RWC','1111','DW、MPP、RWC、DSC') as "授权集群",EXPIRED_DATE AS "有效期",CONCURRENCY_USER_NUMBER AS "并发数" FROM V$LICENSE;

在这里插入图片描述

1.16 不重启数据库使Key生效

sp_load_lic_info();

在这里插入图片描述

1.17 数据文件信息

SELECT PATH as 文件路径,(TOTAL_SIZE*PAGE/1024/1024)as 文件大小MB,(FREE_SIZE*PAGE/1024/1024)as 剩余大小MB,(CAST((TOTAL_SIZE-FREE_SIZE)*100/TOTAL_SIZE AS NUMERIC(2,0))||'%') as 使用比例,DECODE(AUTO_EXTEND,'0','关闭','1','打开') as 自动扩展,NEXT_SIZE as 扩充尺寸MB,MAX_SIZE as 扩充上限MB,CREATE_TIME as 创建时间,MODIFY_TIME as 修改时间 FROM V$DATAFILE;

在这里插入图片描述

1.18 归档日志LSN

SELECT CUR_LSN AS "当前LSN",FILE_LSN AS "已经刷到盘上的LSN",FLUSH_LSN AS "准备刷到盘上的LSN",FLUSHING_PAGES AS "正在刷盘总页数",(TOTAL_SPACE/1024/1024) AS "归档日志总空间M",(FREE_SPACE/1024/1024) AS "归档日志剩余空间M" FROM V$RLOG;

在这里插入图片描述

1.19 REDO日志

SELECT CLIENT_PATH AS "日志名",PATH AS "路径",(RLOG_SIZE/1024/1024) AS "文件大小M",CREATE_TIME AS "创建时间" FROM V$RLOGFILE;

在这里插入图片描述

1.20 会话、连接信息

SELECT SESS_ID AS "会话ID",DECODE(STATE,'CREATE','创建','STARTUP','启动','IDLE','空闲','ACTIVE','活动','WAIT','等待','UNKNOWN','未知') AS "会话状态",CREATE_TIME AS "会话创建时间",TRX_ID AS "事务ID",SQL_TEXT AS "SQL",USER_NAME AS "当前用户",CURR_SCH AS "当前模式",CLNT_TYPE AS "连接类型",DECODE(AUTO_CMT,'Y','是','N','否') AS "是否自动提交",DECODE(DDL_AUTOCMT,'Y','是','N','否') AS "DDL是否自动提交",APPNAME AS "连接程序名",CLNT_IP AS "客户机IP",CLNT_HOST AS "客户机名",OSNAME AS "客户机系统" FROM V$SESSIONS;

在这里插入图片描述

SP_CLOSE_SESSION(157792224); --根据SESS_ID杀掉会话

在这里插入图片描述

1.21 会话统计

SELECT STATE AS 状态,CLNT_IP AS 连接IP,COUNT(*)AS 数量 FROM V$SESSIONS GROUP BY STATE,CLNT_IP ORDER BY STATE;

在这里插入图片描述

1.22 锁信息

SELECT ADDR AS "锁地址",TRX_ID AS "所属事务ID",LTYPE AS "锁类型",LMODE AS "锁模式",DECODE(BLOCKED,'1','是','0','否') AS "是否阻塞",TABLE_ID AS "对应表锁ID",ROW_IDX AS "TID锁事务ID" FROM V$LOCK;

在这里插入图片描述

1.23 死锁历史

SELECT SESS_ID AS "会话ID",TRX_ID AS "事务ID",SQL_TEXT AS "产生死锁的SQL",HAPPEN_TIME AS "死锁发生时间" FROM V$DEADLOCK_HISTORY;

1.24 锁阻塞

SELECT
        SYSDATE STATTIME                           ,
        DATEDIFF(SS, S1.LAST_SEND_TIME, SYSDATE) SS,
        '被阻塞的信息' WT，S1.SESS_ID WT_SESS_ID          ,
        S1.SQL_TEXT WT_SQL_TEXT                    ,
        S1.STATE WT_STATE                          ,
        S1.TRX_ID WT_TRX_ID                        ,
        S1.USER_NAME WT_USER_NAME                  ,
        S1.CLNT_IP WT_CLNT_IP                      ,
        S1.APPNAME WT_APPNAME                      ,
        S1.LAST_SEND_TIME WT_LAST_SEND_TIME        ,
        '引起阻塞的信息' FM                               ,
        S2.SESS_ID FM_SESS_ID                      ,
        S2.SQL_TEXT FM_SQL_TEXT                    ,
        S2.STATE FM_STATE                          ,
        S2.TRX_ID FM_TRX_ID                        ,
        S2.USER_NAME FM_USER_NAME                  ,
        S2.CLNT_IP FM_CLNT_IP                      ,
        S2.APPNAME FM_APPNAME                      ,
        S2.LAST_SEND_TIME FM_LAST_SEND_TIME
FROM
        V$SESSIONS S1,
        V$SESSIONS S2,
        V$TRXWAIT W
WHERE
        S1.TRX_ID=W.ID
    AND S2.TRX_ID=W.WAIT_FOR_ID;

1.25 阻塞

DM7：
WITH LOCKS AS(SELECT O.NAME,L.*,S.SESS_ID,S.SQL_TEXT,S.CLNT_IP,S.LAST_SEND_TIME FROM V$LOCK L,SYSOBJECTS O,V$SESSIONS S WHERE L.TABLE_ID=O.ID AND L.TRX_ID=S.TRX_ID),LOCK_TR AS(SELECT TRX_ID WT_TRXID,ROW_IDX BLK_TRXID FROM LOCKS WHERE BLOCKED=1),RES AS(SELECT SYSDATE STATTIME,T1.NAME,T1.SESS_ID WT_SESSID,S.WT_TRXID,T2.SESS_ID BLK_SESSID,S.BLK_TRXID,T2.CLNT_IP,SF_GET_SESSION_SQL(T1.SESS_ID)FULSQL,DATEDIFF(SS,T1.LAST_SEND_TIME,SYSDATE)SS,T1.SQL_TEXT WT_SQL FROM LOCK_TR S,LOCKS T1,LOCKS T2 WHERE T1.LTYPE='OBJECT' AND T1.TABLE_ID<>0 AND T2.LTYPE='OBJECT' AND T2.TABLE_ID<>0 AND S.WT_TRXID=T1.TRX_ID AND S.BLK_TRXID=T2.TRX_ID) SELECT DISTINCT WT_SQL,CLNT_IP,SS,WT_TRXID,BLK_TRXID FROM RES;

DM8：
WITH TRX_TAB AS(SELECT DISTINCT O1.NAME,L1.TRX_ID FROM V$LOCK L1,SYSOBJECTS O1 WHERE L1.TABLE_ID=O1.ID AND O1.ID<>0),
TRX_SESS AS (SELECT L.TRX_ID WT_TRXID, L.TID BLK_TRXID,L.BLOCKED,(SELECT NAME TABLE_NAME FROM TRX_TAB A WHERE A.TRX_ID=L.TRX_ID) WT_TABLE,
S1.SESS_ID WT_SESS,S2.SESS_ID BLK_SESS,S1.USER_NAME WT_USER_NAME,S2.USER_NAME BLK_USER_NAME,S1.SQL_TEXT,S1.CLNT_IP,DATEDIFF(SS, S1.LAST_SEND_TIME, SYSDATE) SS
FROM V$LOCK L,V$SESSIONS S1,V$SESSIONS S2 WHERE L.TRX_ID=S1.TRX_ID AND L.TID=S2.TRX_ID)SELECT SYSDATE STATTIME,* FROM TRX_SESS where BLOCKED=1;

1.26 强制终止阻塞源头

SP_CLOSE_SESSION(SESS_ID);

1.27 等待事件

SELECT THREAD_ID AS "线程ID",TRX_ID AS "事务ID",WAIT_CLASS AS "等待类型号",WAIT_OBJECT AS "等待对象",WAIT_START AS "等待开始时间",TRUNC(WAIT_TIME,6)/1000000 AS "等待时间" FROM V$WAIT_HISTORY;

1.28 历史运行错误

SELECT SESS_ID AS "会话ID",TRX_ID AS "事务ID",DECODE(SU_FLAG,'U','用户异常','S','系统异常','P','语法异常') AS "错误类型",SQL_TEXT AS "错误的SQL",ECPT_CODE AS "错误提示",ERR_TIME AS "产生时间" FROM V$RUNTIME_ERR_HISTORY;

在这里插入图片描述

1.29 作业清除当日之前的空闲会话

declare
  vsessid varchar(50);
  venddate varchar(8);
  isessioncnt int;
  cursor c1;
begin
  vsessid := '';
  select to_char(sysdate ,'yyyymmdd') into venddate from dual; --获取指定日期
  select count(1) into isessioncnt from v$sessions where state = 'IDLE' and to_char(last_recv_time,'yyyymmdd') < venddate; --查看会话数
  open c1 for select sess_id from v$sessions where state = 'IDLE' and to_char(create_time,'yyyymmdd') < venddate; --打开游标
    loop --循环获取会话ID
      if c1%NOTFOUND then exit;
      end if;
      fetch c1 into vsessid;
      sp_close_session(vsessid); --删除会话
    end loop; --关闭游标
  close c1;
end;

1.30 作业清除大于1800的空闲会话

declare
  vsessid varchar(50);
  venddate varchar(8);
  isessioncnt int;
  cursor c1;
begin
  vsessid := '';
  select to_char(sysdate + 1 ,'yyyymmdd') into venddate from dual; --获取指定日期
  select count(1) into isessioncnt from v$sessions where state = 'IDLE' and to_char(last_recv_time,'yyyymmdd') < venddate; --查看会话数
  if isessioncnt > 1800 then 	  
    open c1 for select sess_id from v$sessions where state = 'IDLE' and to_char(create_time,'yyyymmdd') < venddate; --打开游标
      loop --循环获取会话ID
        if c1%NOTFOUND then exit;
        end if;
        fetch c1 into  vsessid;
        sp_close_session(vsessid); --删除会话
      end loop;
    close c1; --关闭游标
  end if;
end;

1.31 编译模式下无效存储过程

CREATE OR REPLACE PROCEDURE PRO_INV(
    p_owner varchar2 -- 模式名
) as

--编译某个模式下的无效存储过程
    str_sql varchar2(200);
begin
    for invalid_procedures in (select object_name from all_objects
       where status = 'INVALID' and object_type = 'PROCEDURE' and owner=upper(p_owner))
    loop
        str_sql := 'alter procedure '||p_owner||'.'||invalid_procedures.object_name || ' compile';
        begin
            execute immediate str_sql;
        exception
            when OTHERS Then
                dbms_output.put_line(sqlerrm);
        end;
    end loop;
end;

call PRO_INV('SYSDBA');--SYSDBA指模式名称

1.32 缩小TEMP表空间

SP_TRUNC_TS_FILE(3,0,32); --缩小TEMP表空间大小为32M

1.33 清理执行计划缓存

SP_CLEAR_PLAN_CACHE();

1.34 删除归档

SF_ARCHIVELOG_DELETE_BEFORE_TIME(SYSDATE - 30); --保留30天归档

1.35 获取实例信息

SELECT SVR_VERSION AS "版本" FROM V$INSTANCE; --数据库版本
SELECT CUR_DATABASE() AS "数据库名"; --数据库名
SELECT INSTANCE_NAME AS "实例名" FROM V$INSTANCE;
SELECT PERMANENT_MAGIC() AS "永久魔术值"; --数据库永久魔术值
SELECT SF_GET_EXTENT_SIZE ()||'页' AS "簇大小"; --簇大小
SELECT PAGE()/1024||'K' AS "页大小"; --页大小
SELECT DECODE(UNICODE,'0','GB18030','1','UTF-8','2','EUC-KR') AS "字符集"; --字符集
SELECT DECODE(SF_GET_CASE_SENSITIVE_FLAG,'0','不敏感','1','敏感') AS "大小写"; --大小写
SELECT DECODE(BLANK_PAD_MODE,'0','否','1','是') AS "空格填充"; --空格填充模式
SELECT MAX(RLOG_SIZE/1024/1024)||'MB' AS "日志大小" FROM V$RLOGFILE;
SELECT DECODE(ARCH_MODE,'Y','启用','N','未启用') AS "归档状态" FROM V$DATABASE;
SELECT CAST(CREATE_TIME AS VARCHAR(50)) AS "创建时间" FROM V$DATABASE;
--统一查询
select '版本' as 名称,SVR_VERSION as 信息 from v$instance union all
select '数据库名' as 名称,CUR_DATABASE() union all
select '实例名',INSTANCE_NAME from v$instance union all
select '永久魔术值',to_char(PERMANENT_MAGIC()) union all
select '簇大小',SF_GET_EXTENT_SIZE ()||'页' union all
select '页大小',PAGE()/1024||'K' union all
select '字符集',DECODE(UNICODE,'0','GB18030','1','UTF-8','2','EUC-KR') union all
select '大小写',DECODE(SF_GET_CASE_SENSITIVE_FLAG,'0','不敏感','1','敏感') union all
select '空格填充',DECODE(BLANK_PAD_MODE,'0','否','1','是') union all
select '日志大小',MAX(RLOG_SIZE/1024/1024)||'MB' from v$rlogfile union all
select '归档状态',DECODE(ARCH_MODE,'Y','启用','N','未启用') from v$database union all
select '创建时间', cast(CREATE_TIME as varchar(50)) from v$database;

在这里插入图片描述

1.36 获取相关ID

SELECT SESSID (); --获取当前连接会话ID
SELECT UID(); --获取当前登录用户ID

在这里插入图片描述

1.37 备份相关

SELECT DECODE(SF_BAK_GET_TYPE('/opt/bak/all.bak'),'0','全量','1','增量','2','B树'); --备份文件类型
SELECT DECODE(SF_BAK_GET_LEVEL('/opt/bak/all.bak'),'0','联机备份','1','脱机备份'); --备份文件方式
SELECT SF_BAK_GET_TIME('/opt/bak/all.bak'); --备份文件时间
SELECT SF_BAK_GET_EXTENT_SIZE('/opt/bak/all.bak')||'页'; --备份文件簇大小;
SELECT SF_BAK_GET_PAGE_SIZE('/opt/bak/all.bak')/1024||'K'; --备份文件页大小
SELECT DECODE(SF_BAK_GET_CASE_SENSITIVE('/opt/bak/all.bak'),'0','不敏感','1','敏感'); --备份文件大小写
SELECT SF_BAK_GET_GLOBAL_VERSION('/opt/bak/all.bak'); --备份文件数据库版本
SELECT DECODE(SF_BAK_GET_ARCH_FLAG('/opt/bak/all.bak','0','未归档','1','归档'); --备份文件是否有归档
SELECT DECODE(SF_BAK_GET_ENCRYPT_TYPE ('/opt/bak/all.bak'),'0','未加密','1','加密'); --备份文件是否加密
SELECT DECODE(SF_BAK_GET_COMPRESSED('/opt/bak/all.bak'),'0','未压缩','1','压缩'); --备份文件是压缩

1.38 监控数据库内存

--创建基础表
CREATE TABLE MEM_POOL("CONTTIME" TIMESTAMP(0),"SUM(ORG)" VARCHAR2(20),"SUM(TOTAL)" VARCHAR2(20));
--插入当前内存信息
insert into MEM_POOL values (SYSDATE(),(select sum(ORG_SIZE)/1024/1024 from v$mem_pool),(select sum(TOTAL_SIZE)/1024/1024 from v$mem_pool));commit;

在这里插入图片描述

1.39 监控指定索引是否使用

CREATE TABLE T1("C1" CHAR(10)); --创建测试表
CREATE INDEX IDX_T1_C1 ON T1("C1" ASC); --创建IDX_T1_C1索引
ALTER INDEX SYSDBA."IDX_T1_C1" MONITORING USAGE; --监控IDX_T1_C1索引
SELECT * FROM T1 WHERE T1.C1 = '1'; --使用IDX_T1_C1索引
ALTER INDEX SYSDBA."IDX_T1_C1" NOMONITORING USAGE; --取消监控INDEX_T1_C1索引
SELECT SCH_NAME AS "所属模式",TABLE_NAME AS "所属表",INDEX_NAME AS "索引名称", MONITORING AS "是否监控",USED AS "是否使用",START_MONITORING AS "开始监控时间",END_MONITORING AS "停止监控时间" FROM V$OBJECT_USAGE; --查看监控结果

在这里插入图片描述

1.40 监控指定模式下索引是否使用

SELECT 'ALTER INDEX '||OWNER||'."'||OBJECT_NAME||'" '||'MONITORING USAGE;' FROM DBA_OBJECTS WHERE OBJECT_TYPE='INDEX' AND GENERATED='N' AND OWNER='SYSDBA'; --监控SYSDBA模式下的索引
SELECT 'ALTER INDEX '||OWNER||'."'||OBJECT_NAME||'" '||'NOMONITORING USAGE;' FROM DBA_OBJECTS WHERE OBJECT_TYPE='INDEX' AND GENERATED='N' AND OWNER='SYSDBA'; --取消监控SYSDBA模式下的索引
SELECT SCH_NAME AS "所属模式",TABLE_NAME AS "所属表",INDEX_NAME AS "索引名称", MONITORING AS "是否监控",USED AS "是否使用",START_MONITORING AS "开始监控时间",END_MONITORING AS "停止监控时间" FROM V$OBJECT_USAGE; --查看监控结果