查询所有模式：
SELECT DISTINCT object_name FROM ALL_OBJECTS WHERE OBJECT_TYPE = 'SCH'；
查询库：
select TABLE_NAME from all_tables where OWNER ='模式名';
1.管理
查询所有用户：
SELECT USERNAME FROM DBA_USERS;
查询数据库名:
select name,create_time from v$database;
查看最大连接数:
select SF_GET_PARA_VALUE(2,'MAX_SESSIONS');
查询大小写是否敏感:
SELECT CASE_SENSITIVE();
查询达梦数据库当前状态:
select status$ from v$instance;
查询达梦数据库授权截至有效期:
select EXPIRED_DATE FROM V$LICENSE;
查看授权信息:
select * FROM V$LICENSE;
查看数据库版本:
SELECT * FROM V$VERSION;
查看达梦数据库小版本号:
select *, id_code from v$version;
查看所有schema:
SELECT * FROM DBA_OBJECTS WHERE OBJECT_TYPE='SCH';
查看所有死锁:
SELECT * FROM V$DEADLOCK_history;
2.awr报告功能
查看awr报告功能是否开启
select sf_check_awr_sys;
0为关闭
查看表空间
select tablespace_name from dba_tablespaces;
awr报告功能开启自动创建临时表空间SYSAUX
开启awr报告监视功能
SP_INIT_AWR_SYS(1);
此sql为创建DBMS_WORKLOAD_REPOSITORY, (0)为关闭
开始AWR功能
（10）为间隔10分钟生成一次报告 可以根据需要修改
DBMS_WORKLOAD_REPOSITORY.AWR_SET_INTERVAL(10);
（10）为间隔10分钟生成一次报告 可以根据需要修改
查看快照信息
select * from sys.wrm$_snapshot;
将awr报告导出到本地
SYS.AWR_REPORT_HTML(1, 2, '/home/dmdba/', 'AWR1_2.HTML');
删除快照（按照编号）
CALL DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(1, 5);
清理全部快照
CALL DNMS_WORKLOAD_REPOSITORY.AWR_CLEAR_HISTORY();
3.会话信息
-- 连接总数
select count(1) from v$sessions ;
-- ip，用户名、连接数汇总
select CLNT_IP,USER_NAME,count(1) from v$sessions group by CLNT_IP,USER_NAME order by CLNT_IP;
select * from v$sessions where CLNT_IP='::ffff:190.xxxx' order by LAST_SEND_TIME;
select SESS_ID,USER_NAME,CREATE_TIME,CLNT_TYPE,CLNT_IP,LAST_RECV_TIME,LAST_SEND_TIME from v$sessions where CLNT_IP='::ffff:220.2.65.16';
select USER_NAME,CREATE_TIME,CLNT_TYPE,CLNT_IP,LAST_RECV_TIME,LAST_SEND_TIME,SQL_TEXT from v$sessions where SQL_TEXT is not null;
-- 某个ip的连接总数
select count(1) from v$sessions where CLNT_IP='::ffff:220.xxxxx' limit 1;
select SESS_ID,USER_NAME,CREATE_TIME,CLNT_TYPE,CLNT_IP,LAST_RECV_TIME,LAST_SEND_TIME from v$sessions where CLNT_IP='::ffff:220.2.65.78' order by LAST_SEND_TIME;
杀连接
call sp_close_session($进程id);
select 'call sp_close_session('||SESS_ID||');' from v$sessions where CLNT_IP='::ffff:220.xxxxxx';
4.慢SQL
select * from V$LONG_EXEC_SQLS t order by t.FINISH_TIME desc limit 50;
select * from V$LONG_EXEC_SQLS t order by exec_time desc limit 20;
select * from V$LONG_EXEC_SQLS t where sql_text not like 'UPDATE t_version%' order by exec_time desc limit 20;
select * from V$LONG_EXEC_SQLS limit 50;
5.事务
select VTW.ID AS TRX_ID, VS.SESS_ID,VS.SQL_TEXT,VS.APPNAME,VS.CLNT_IP FROM V$TRXWAIT VTW LEFT JOIN V$TRX VT ON (VTW.ID=VT.ID) LEFT JOIN v$SESSIONS VS ON (VT.SESS_ID=VS.SESS_ID);
6.索引
select * from dba_indexs limit 10;
select * from SYS.DBA_INDEXES ;
SELECT * FROM ALL_INDEXES WHERE TABLE_NAME = 'sys_org_addbook';
SELECT OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME FROM ALL_INDEXES WHERE TABLE_NAME = 'sys_org_addbook';
SELECT INDEX_NAME,COLUMN_NAME FROM ALL_IND_COLUMNS WHERE INDEX_NAME = 'wps_account_sync_sys_org_addbook_idx_oa_org_addbook_super_id';
SELECT INDEX_NAME,COLUMN_NAME FROM ALL_IND_COLUMNS WHERE INDEX_NAME = 'idx_sys_org_addbook_deptnumber ';
SELECT INDEX_NAME,COLUMN_NAME FROM ALL_IND_COLUMNS WHERE INDEX_NAME = 'INDEX33631015';
SELECT INDEX_NAME,COLUMN_NAME FROM ALL_IND_COLUMNS WHERE INDEX_NAME = 'INDEX33631016';
SELECT INDEX_NAME,COLUMN_NAME FROM ALL_IND_COLUMNS WHERE INDEX_NAME IN ('wps_account_sync_sys_org_addbook_idx_oa_org_addbook_super_id','idx_sys_org_addbook_deptnumber','INDEX33631015','INDEX33631016')
