DM8日常运维必须要懂的几个命令

检查表空间

1. SQL>SELECT a.tablespace_name "表空间名称",
   
2. total / (1024 * 1024) "表空间大小(M)",
   
3. free / (1024 * 1024) "表空间剩余大小(M)",
   
4. (total - free) / (1024 * 1024 ) "表空间使用大小(M)",
   
5. total / (1024 * 1024 * 1024) "表空间大小(G)",
   
6. free / (1024 * 1024 * 1024) "表空间剩余大小(G)",
   
7. (total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
   
8. round((total - free) / total, 4) * 100 "使用率 %"
   
9. FROM (SELECT tablespace_name, SUM(bytes) free
   
10. FROM dba_free_space
    
11. GROUP BY tablespace_name) a,
    
12. (SELECT tablespace_name, SUM(bytes) total
    
13. FROM dba_data_files
    
14. GROUP BY tablespace_name) b
    
15. WHERE a.tablespace_name = b.tablespace_name;
    

检查临时表空间

1. SQL>SELECT a.tablespace_name "表空间名称",
   
2. total / (1024 * 1024) "表空间大小(M)",
   
3. free / (1024 * 1024) "表空间剩余大小(M)",
   
4. (total - free) / (1024 * 1024 ) "表空间使用大小(M)",
   
5. total / (1024 * 1024 * 1024) "表空间大小(G)",
   
6. free / (1024 * 1024 * 1024) "表空间剩余大小(G)",
   
7. (total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
   
8. round((total - free) / total, 4) * 100 "使用率 %"
   
9. FROM (SELECT tablespace_name, SUM(bytes) free
   
10. FROM dba_free_space
    
11. GROUP BY tablespace_name) a,
    
12. (SELECT tablespace_name, SUM(bytes) total
    
13. FROM dba_data_files
    
14. GROUP BY tablespace_name) b
    
15. WHERE a.tablespace_name = b.tablespace_name;
    

查看数据文件信息

1. SQL>select * from V$DATAFILE;
   

查看表空间与数据文件关系

1. SQL>SELECT
   
2.        TS.NAME,
   
3.        DF.PATH
   
4. FROM
   
5.        V$TABLESPACE AS TS,
   
6.        V$DATAFILE   AS DF
   
7. WHERE
   
8.        TS.ID = DF.GROUP_ID;
   

查看是否有死锁

1. SQL> select * from V$DEADLOCK_HISTORY;
   

查看数据库是否存在阻塞

1. SQL> WITH TRX_TAB AS
   
2. (SELECT O1.NAME,L1.TRX_ID FROM V$LOCK L1,SYSOBJECTS O1 WHERE L1.TABLE_ID=O1.ID AND O1.ID<>0),
   
3. TRX_SESS AS (
   
4. SELECT L.TRX_ID WT_TRXID, L.ROW_IDX BLK_TRXID,L.BLOCKED,(SELECT NAME TABLE_NAME FROM TRX_TAB A WHERE A.TRX_ID=L.TRX_ID) WT_TABLE,
   
5. S1.SESS_ID WT_SESS,S2.SESS_ID BLK_SESS,
   
6. 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
   
7. FROM V$LOCK L,V$SESSIONS S1,V$SESSIONS S2
   
8. WHERE L.TRX_ID=S1.TRX_ID AND L.ROW_IDX=S2.TRX_ID)
   
9. SELECT SYSDATE STATTIME,* FROM TRX_SESS where BLOCKED=1;
   

内存池信息检查

1. SQL> select * from V$DB_CACHE;
   

1. SQL> select * from V$BUFFERPOOL;
   

1. SQL> select * from V$MEM_POOL;
   

数据库统计信息检查

1. SQL> select * from V$SYSSTAT where classid in (11,5) order by classid desc;
   

会话统计

1. SQL> SELECT STATE,CLNT_IP,CLNT_TYPE,CURR_SCH,USER_NAME,COUNT(*)
   
2. COUNTS FROM V$SESSIONS GROUP BY STATE,CLNT_IP,CLNT_TYPE,CURR_SCH,USER_NAME
   
3. ORDER BY STATE;
   

对象统计

1. SQL>select tablespace_name,'TABLE_OF_TS' OBJTYPE,count(*) COUNTS from all_tables group by tablespace_name union all
   
2. select * from ( select owner,object_type,count(*) from all_objects
   
3. where owner not in ('SYS','SYSTEM','SYSAUDITOR','SYSSSO','CTISYS') group by object_type,owner
   
4. order by 1,2);
   

表行数统计

1. SQL>begin
   
2. for rec in (select owner,table_name from all_tables where owner not in ('SYS','SYSAUDITOR','SYSSSO','CTISYS')) loop
   
3. call SP_TAB_STAT_INIT(rec.owner,rec.table_name);
   
4. end loop;
   
5. end;
   
6. /
   
7. select owner,table_name,tablespace_name,status,num_rows from all_tables where owner not in ('SYS','SYSAUDITOR','SYSSSO','CTISYS')
   
8. order by owner,num_rows desc;
   

dblink检查

1. SQL> select * from DBA_DB_LINKS;
   

最慢的20条SQL统计

1. SQL>SELECT TOP 20 START_TIME,TIME_USED/1000 TIME_USED,TOP_SQL_TEXT FROM V$SQL_HISTORY ORDER BY TIME_USED DESC;
   
2. SELECT * FROM V$SYSTEM_LONG_EXEC_SQLS ORDER BY EXEC_TIME DESC;
   

内存高的20条SQL统计

1. SQL>select * from  V$SYSTEM_LARGE_MEM_SQLS order by mem_used_by_k desc;
   

前 20 条长耗时等待事件统计

1. SQL> SELECT top 20 * FROM V$SYSTEM_EVENT ORDER BY TOTAL_WAITS DESC;
   

查看所有作业信息

1. --查看所有作业信息
   
2. SELECT A.ID,A.NAME,A."ENABLE",A.USERNAME,A.CREATETIME,A.MODIFYTIME,A.DESCRIBE,
   
3. B.LAST_DATE||' '||B.LAST_SEC LAST_TIME,B.NEXT_DATE||' '||B.NEXT_SEC NEXT_TIME,B.WHAT
   
4. FROM SYSJOB.SYSJOBS A,SYSJOB.USER_JOBS B
   
5. WHERE A.ID=B.JOB;
   
6. --查看所有作业调度信息
   
7. SELECT * FROM SYSJOB.SYSJOBSCHEDULES;
   

查看用户信息，包括用户名、用户密码、默认表空间、索引表空间、数据文件等信息

1. SQL> select * from DBA_USERS;
   

查看密码策略

1. SQL> Select * from v$dm_ini where para_name='PWD_POLICY';
   

查看 redo 日志信息，包括路径，大小，可用空间，总空间

1. SQL>SELECT
   
2.        A.FILE_ID    ,
   
3.        A.PATH       ,
   
4.        A.CLIENT_PATH,
   
5.        A.RLOG_SIZE  ,
   
6.        B.FREE_SPACE ,
   
7.        B.TOTAL_SPACE,
   
8.        B.CUR_FILE
   
9. from
   
10.        (
    
11.                select * from V$RLOGFILE where true
    
12.        )  A,
    
13.        (
    
14.                select * from V$RLOG where true
    
15.        )  B;
    

查看归档日志是否打开

1. SQL> select name,arch_mode from v$database;
   

SQL 命令方式开启归档

1. SQL> ALTER DATABASE MOUNT;
   
2. executed successfully
   
3. used time: 1.439(ms). Execute id is 0.
   
4. SQL> ALTER DATABASE ARCHIVELOG;
   
5. executed successfully
   
6. used time: 5.425(ms). Execute id is 0.
   
7. SQL> ALTER DATABASE ADD ARCHIVELOG 'DEST=/dm/arch, TYPE=LOCAL, FILE_SIZE=2048, SPACE_LIMIT=10240';
   
8. executed successfully
   
9. used time: 1.101(ms). Execute id is 0.
   
10. SQL> ALTER DATABASE OPEN;
    
11. executed successfully
    
12. used time: 8.692(ms). Execute id is 0.
    
13. SQL> select name,arch_mode from v$database;
    
14. 
    
15. LINEID     NAME ARCH_MODE
    
16. ---------- ---- ---------
    
17. 1          PROD Y
    

检查 SVR_LOG 参数

1. SQL> select * from v$parameter where name like 'SVR_LOG%';
   

开启日志

1. SQL> SP_REFRESH_SVR_LOG_CONFIG();
   
2. DMSQL executed successfully
   
3. used time: 0.435(ms). Execute id is 64434.
   
4. SQL> sp_set_para_value(1,'SVR_LOG',1);
   
5. DMSQL executed successfully
   
6. used time: 8.896(ms). Execute id is 64435.
   
7. SQL> select * from v$parameter where name like 'SVR_LOG%';
   
8. 
   
9. LINEID     ID          NAME         TYPE VALUE    SYS_VALUE FILE_VALUE DESCRIPTION                                                                                                                 DEFAULT_VALUE ISDEFAULT  
   
10. ---------- ----------- ------------ ---- -------- --------- ---------- --------------------------------------------------------------------------------------------------------------------------- ------------- -----------
    
11. 1          550         SVR_LOG_NAME SYS  SLOG_ALL SLOG_ALL  SLOG_ALL   Using which sql log sys in sqllog.ini                                                                                       NULL          1
    
12. 2          553         SVR_LOG      SYS  1        1         1          Whether the Sql Log sys Is open or close. 1:open, 0:close, 2:use switch and detail mode. 3:use not switch and simple mode.  0             0
    
13. 
    
14. used time: 5.476(ms). Execute id is 64436.
    

开启AWR报告

检查数据库DBMS_WORKLOAD_REPOSITORY系统包的启用状态（0：未启用；1：已启用）以及awr快照信息和表空间信息

1. 
   
2. SQL> select sf_check_awr_sys;
   
3. SQL> select * from sys.wrm$_snapshot;
   
4. SQL> select tablespace_name from dba_tablespaces;
   

开启AWR功能

SQL> DBMS_WORKLOAD_REPOSITORY.AWR_SET_INTERVAL(10);

创建awr报告（可以手动创建，也可以等待，数据库会按照间隔时间自动创建）

1. SQL> DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
   

查看已经生成的快照信息

1. SQL> select * from sys.wrm$_snapshot;
   

生成html报告

1. SQL> SYS.AWR_REPORT_HTML(1,2,'/home/dmdba/','dm_awr_html.html');
   

清理AWR快照

例1：清理快照id为1-2（包含快照号为1和2的快照）之间的快照：

1. SQL> DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(1,2);
   

例2：清理所有的历史快照信息

1. SQL> DBMS_WORKLOAD_REPOSITORY.AWR_CLEAR_HISTORY;
   
2. SQL> select count(*) from sys.wrm$_snapshot;
   

2 、修改快照配置信息，包括修改快照生成的时间间隔、快照保留时间等

例1：先确认当前数据库环境的快照保存信息（查询WRM$_WR_CONTROL表），然后再修改快照生成的时间间隔。

1. SQL> select * from sys.wrm$_wr_control;
   

例如设置快照生成时间间隔为30分钟

1. SQL> DBMS_WORKLOAD_REPOSITORY.AWR_SET_INTERVAL(30);
   

开启日志挖掘

1. SQL> SP_CREATE_SYSTEM_PACKAGES (1,'DBMS_LOGMNR');
   
2. DMSQL executed successfully
   
3. SQL> select SF_CHECK_SYSTEM_PACKAGE('DBMS_LOGMNR');
   
4. LINEID     SF_CHECK_SYSTEM_PACKAGE('DBMS_LOGMNR')
   
5. ---------- --------------------------------------
   
6. 1          1
   
7. used time: 0.942(ms). Execute id is 78402.
   

确认参数

1. SQL> select para_name, para_value from v$dm_ini where para_name in ('ARCH_INI','RLOG_APPEND_LOGIC');
   
2. 
   
3. LINEID     PARA_NAME         PARA_VALUE
   
4. ---------- ----------------- ----------
   
5. 1          RLOG_APPEND_LOGIC 0
   
6. 2          ARCH_INI          1
   
7. 
   
8. used time: 5.339(ms). Execute id is 78404.
   

切换归档日志

1. SQL> alter system archive log current;
   
2. executed successfully
   
3. used time: 4.457(ms). Execute id is 0.
   
4. SQL> select name , first_time , next_time , first_change# , next_change# from v$archived_log;
   
5. 
   
6. LINEID     NAME                                                           FIRST_TIME                 NEXT_TIME                  FIRST_CHANGE#        NEXT_CHANGE#        
   
7. ---------- -------------------------------------------------------------- -------------------------- -------------------------- -------------------- --------------------
   
8. 1          /dm/arch/ARCHIVE_LOCAL1_0x71597500_EP0_2024-08-08_15-40-08.log 2024-08-08 15:40:08.729349 2024-08-08 16:31:23.311618 43481                85293
   
9. 
   
10. used time: 0.352(ms). Execute id is 78405.
    

添加一个或多个需要分析的归档日志文件

1. SQL> dbms_logmnr.add_logfile('/dm/arch/ARCHIVE_LOCAL1_0x71597500_EP0_2024-08-08_15-40-08.log');
   
2. DMSQL executed successfully
   
3. used time: 1.740(ms). Execute id is 78406.
   
4. SQL>
   

查看通过 ADD_LOGFILE 添加的归档日志文件，可以通过动态视图 V$LOGMNR_LOGS 进行查询

1. SQL> select low_scn, next_scn, low_time, high_time, log_id, filename from v$logmnr_logs;
   
2. 
   
3. LINEID     LOW_SCN              NEXT_SCN             LOW_TIME                   HIGH_TIME                  LOG_ID      FILENAME                                                      
   
4. ---------- -------------------- -------------------- -------------------------- -------------------------- ----------- --------------------------------------------------------------
   
5. 1          43481                85293                1900-01-01 00:00:00.000000 1900-01-01 00:00:00.000000 0           /dm/arch/ARCHIVE_LOCAL1_0x71597500_EP0_2024-08-08_15-40-08.log
   
6. 
   
7. used time: 0.614(ms). Execute id is 78407.
   
8. SQL>dbms_logmnr.start_logmnr(
   
9. Options=>2128,
   
10. Starttime=>to_date('2024-08-08 15:00:00','YYYY-MM-DD HH24:MI:SS'),
    
11. Endtime=>to_date('2024-08-08 16:33:00','YYYY-MM-DD HH24:MI:SS'));
    

查看归档日志文件的分析结果

1. SQL>select TIMESTAMP ,START_TIMESTAMP ,COMMIT_TIMESTAMP ,OPERATION ,OPERATION_CODE ,ROLL_BACK ,TABLE_NAME ,ROW_ID ,USERNAME ,DATA_OBJ# , DATA_OBJV# , SQL_REDO, REDO_VALUE, UNDO_VALUE
   
2. from V$LOGMNR_CONTENTS;
   
3. SQL>select operation_code , scn, sql_redo , timestamp ,seg_owner, table_name from
   
4. V$LOGMNR_CONTENTS where seg_owner = 'SYSDBA' and operation_code in (3,1,2);
   

终止日志挖掘

1. SQL> DBMS_LOGMNR.END_LOGMNR();
   
2. DMSQL executed successfully
   
3. used time: 0.720(ms). Execute id is 78426.