一、常用的系统视图：
dba_objects：显示数据库中所有的对象，例如想查询数据库中有没有某个对象
v$sessions：显示会话的具体信息，如执行的 sql 语句、主库名、当前会话状态、用户名等等
v$lock：查看当前数据库中锁的信息
v$mem_pool：显示所有的内存池信息
V$deadlock_histor：:记录死锁的历史信息
V$TABLESPACE：显示表空间信息，不包括回滚表空间信息
V$TRX：显示所有活动事务的信息。通过该视图可以查看所有系统中所有的事务以及相关信息，如锁信息等。
二、常用查询语句
1、查询数据库在线实例信息
select distinct NAME, HOST_NAME, SVR_VERSION, DB_VERSION, START_TIME, STATUS$, MODE$ from V$INSTANCE;
2、查看数据库常用参数值
select PARA_NAME,PARA_VALUE FROM V$DM_INI WHERE PARA_NAME IN('MEMORY_POOL','BUFFER','PORT_NUM','MAX_SESSIONS','MAX_SESSION_STATEMENT','INSTANCE_NAME','BAK_PATH','SYSTEM_PATH','ARCH_INI');
将需要查询的参数名称放在括号内
3、查询数据库初始化配置
select SF_GET_PAGE_SIZE() page_size, SF_GET_EXTENT_SIZE() extent_size, SF_GET_UNICODE_FLAG() unicode_flag, SF_GET_CASE_SENSITIVE_FLAG() case_sensitive_flag, SF_GET_SYSTEM_PATH() system_path;
4、查询数据库名称、数据库总大小、数据库是否启用归档
select NAME,STATUS$,ARCH_MODE, TOTAL_SIZE from SYS.V$DATABASE;
5、查询数据库连续运行时间
select (SYSDATE-START_TIME)*24 FROM V$INSTANCE;
6、查询数据库管理用户状态，默认表空间，是否存在被锁定
select D.USERNAME,A.CREATED,D.ACCOUNT_STATUS,D.DEFAULT_TABLESPACE,D.EXPIRY_DATE,D.LOCK_DATE FROM DBA_USERS D,ALL_USERS A;
7、查询当前数据库的日志分组情况
select GROUP_ID,FILE_ID,PATH,CLIENT_PATH,RLOG_SIZE FROM SYS.V$RLOGFILE;
8、查询表空间信息
select
T.NAME 表空间名称,
D.PATH 表空文件路径,
T.TYPE$ 表空间类型,
T.STATUS$ 表空间状态,
T. FILE_NUM 包含的文件数,
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;
9、查询数据表所分配的空间大小，辅助查询表用户使用情况
select OWNER,TABLESPACE_NAME,SEGMENT_TYPE,SEGMENT_NAME,BLOCKS,BYTES/1024/1024 FROM DBA_SEGMENTS ORDER BY OWNER,SEGMENT_NAME;
10、查询表索引状态，便于确认表索引是否可用
select I.TABLE_OWNER,I.TABLE_NAME,O.OBJECT_NAME,O.OBJECT_TYPE,O.STATUS FROM USER_INDEXES I,USER_OBJECTS O WHERE O.OBJECT_NAME=I.INDEX_NAME AND O.STATUS='INVALID';
11、查询数据库归档信息
select ARCH_TYPE,ARCH_DEST FROM V$DM_ARCH_INI;
select ARCH_NAME, ARCH_TYPE, ARCH_DEST, ARCH_FILE_SIZE, ARCH_SPACE_LIMIT, ARCH_TIMER_NAME, ARCH_IS_VALID from SYS.V$DM_ARCH_INI;
12、查询会话信息
select A.SESS_ID,A.SQL_TEXT,A.STATE,A.N_USED_STMT,A.CURR_SCH,
A.USER_NAME,A.TRX_ID,A.CREATE_TIME,A.CLNT_TYPE,A.TIME_ZONE,A.OSNAME,A.CONN_TYPE, B.PROTOCOL_TYPE,B.IP_ADDR FROM SYS.V$SESSIONS A,SYS.V$CONNECT B where A.Sess_id= B.SADDR ORDER BY SF_GET_EP_SEQNO(A.rowid),A.Sess_id;
13、查询会话数量
（1）当前活动会话数：
select COUNT(*) FROM V$SESSIONS WHERE STATE='ACTIVE';
（2）当前总会话数：
select COUNT(*) FROM V$SESSIONS;
（3）可用会话数=系统允许最大并发会话数-数据库当前会话数；
select PARA_VALUE-(SELECT COUNT(*) FROM V$SESSIONS) FROM V$DM_INI WHERE PARA_NAME='MAX_SESSIONS';
（4）会话使用率：
select (SELECT COUNT(*) FROM V$SESSIONS)/PARA_VALUE * 100||'%' FROM V$DM_INI WHERE PARA_NAME='MAX_SESSIONS';
14、事务监控
Select ID, STATUS,ISOLATION,READ_ONLY,SESS_ID,INS_CNT,DEL_CNT,UPD_CNT,UPD_INS_CNT,UREC_SEQNO,WAITING FROM SYS.V$TRX;
15、查询等待事件的具体信息
select THREAD_ID,TRX_ID,WAIT_CLASS,WAIT_OBJECT,WAIT_START,WAIT_TIME, SPACE_ID,FILE_ID,PAGE_NO FROM V$WAIT_HISTORY;
16、查询作业信息
select J.NAME,J.ENABLE,J.USERNAME,J.CREATETIME,S.DURING_START_DATE FROM SYSJOB.SYSJOBSCHEDULES S,SYSJOB.SYSJOBS J WHERE S.JOBID=J.ID;
17、查询数据库作业任务的执行历史
select NAME,STEPNAME,STATUS,ERRCODE,ERRINFO,CUR_TIME,RETRY_ATTEMPTS FROM SYSJOB.SYSJOBHISTORIES;
18、显示系统统计信息，查看数据库性能数据
select NAME,STAT_VAL FROM V$SYSSTAT WHERE NAME IN('logic read count','physical read count','physical write count');
19、查询缓冲区命中率信息
select NAME,N_PAGES,N_LOGIC_READS, N_PHY_READS ,RAT_HIT FROM V$BUFFERPOOL;
20、查询数据字典命中率
select USED_SIZE,TOTAL_SIZE,(USED_SIZE/TOTAL_SIZE)*100 as HIT FROM V$DICT_CACHE;
21、显示日志文件刷新的SLOT对象信息
select CKPT_RLOG_SIZE,CKPT_LSN,CKPT_INTERVAL,CKPT_FILE,LAST_BEGIN_TIME,LAST_BEGIN_TIME,(LAST_END_TIME-LAST_BEGIN_TIME)AS LAST_USED_TIME FROM V$CKPT;
22、显示执行SQL的历史记录信息
select SQL_ID,SESS_ID,SESS_SEQ,TRX_ID,TOP_SQL_TEXT,START_TIME,TIME_USED,N_LOGIC_READ,N_PHY_READ,HARD_PARSE_FLAG FROM V$SQL_HISTORY;
23、监控运行时错误
Select DISTINCT A.SEQNO,A.SESS_ID,A.TRX_ID, A.SQL_TEXT,A.SU_FLAG,A.ECPT_CODE,A.ECPT_DESC,A.ERR_TIME,B.USER_NAME,B.CLNT_IP,B.APPNAME FROM SYS.V$RUNTIME_ERR_HISTORY A, SYS.V$SESSION_HISTORY B；
24、查询环境运行慢于5秒的SQL
select * 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' and sess_id <> sessid())where ss>=1;
--ss>=1表示1秒及以上的慢SQL，可以替换成其他时间；
25、出现死锁的解决办法
Select O.OBJECT_NAME ,S.SESS_ID,S.SQL_TEXT,L.ADDR,L.TRX_ID,L.LTYPE,L.LMODE,L.BLOCKED,L.TABLE_ID,L.ROW_IDXFROM V$LOCK L,DBA_OBJECTS O,V$SESSIONS S
WHERE L.TABLE_ID=O.OBJECT_ID AND L.TRX_ID=S.TRX_ID AND L.BLOCKED=1;
--清除阻塞SQL；
SP_CLOSE_SESSION(上述查到的SESS_ID);
26、查看所占字节长度
-- 建表
create table t2(c1 varchar2(10));
-- 插入数据
insert into t2 values('a');
insert into t2 values('啊');
insert into t2 values('㺉');
commit;
-- 查看所占字节长度
select c1,lengthb(c1),(ascii(c1)) from t2;
27、查看字符集
字符集在创建实例时指定，设定后不可更改，如果需要变更字符集，只能重建实例。
select sf_get_unicode_flag();
--返回字符集对应的编号: 0 表示 GB18030，1 表示 UTF-8，2 表示 EUC-KR (指韩文字符集)。
28、查看数据库状态
-- 查看数据库名和启动状态
select NAME,CREATE_TIME,ARCH_MODE,STATUS$,ROLE$ from v$database;
select NAME,CREATE_TIME,ARCH_MODE,case STATUS$ when '1' then 'starting' when '2' then 'redo comp' when '3' then 'MOUNT' when '4' then 'OPEN' when '5' then 'hold' when '6' then 'close' end STATUS,ROLE$ from v$database;
STATUS$实例状态：1：启动；2：启动，redo 完成；3：MOUNT； 4：打开；5：挂起；6：关闭
26、数据库运行时长
SELECT (SYSDATE-START_TIME)*24 db_lifetime FROM V$INSTANCE;
27、表空间使用率
-- 查看表空间使用情况
select a.tablespace_name,
a.total_mb,
round(b.free_mb, 2) free_mb,
TO_CHAR(ROUND((a.total_mb - b.free_mb) / a.total_mb * 100, 2),
'990.99') || '%' "usage %"
from (select tablespace_name, sum(bytes) / 1024 / 1024 total_mb
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) / 1024 / 1024 free_mb
from dba_free_space
group by tablespace_name) b,
dba_tablespaces d
where a.tablespace_name = b.tablespace_name(+)
and a.tablespace_name = d.tablespace_name(+)
order by 4 desc;
28、数据文件路径
SELECT tablespace_name
,file_name
,AUTOEXTENSIBLE
,bytes / 1024 / 1024 || 'M' total_space
,user_bytes / 1024 / 1024 || 'M' user_space
,user_bytes * 100 / bytes "% FREE"
FROM dba_data_files
29、redo日志文件
select * from v$rlogfile;
-- 增加redo日志组
SQL> alter database add logfile '/dm8/data/DAMENG/DAMENG04.log' size 256;
-- 调整redo日志大小（只能减小，不能增大）
alter database resize logfile '/dm8/data/DAMENG/DAMENG04.log' to 128;
30、执行超过2s的活动SQL
select * from (
SELECT sess_id,sql_text,datediff(ss,last_recv_time,sysdate) Y_EXETIME,
SF_GET_SESSION_SQL(SESS_ID) fullsql,clnt_ip
FROM V$SESSIONS WHERE STATE='ACTIVE')
where Y_EXETIME>=2;
31、锁查询
select o.name,l.* from v$lock l,sysobjects o where l.table_id=o.id and blocked=1;
32、阻塞查询
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;
33、查看表上的锁是哪个会话导致
select a.*,b.NAME,c.SESS_ID from v$lock a
left join sysobjects b on b.ID=a.TABLE_ID
left join v$sessions c on a.TRX_ID=c.TRX_ID;
-- sp_close_session(sess_id); -- kill对应的session
34、审计日志
-- 显示审计记录，用来查询当前系统默认路径下的审计文件信息。此动态性能视图只有在审计开关打开时才有内容，且只有审计用户可以查询。DM 的 INI 参数 ENABLE_AUDIT 控制
SELECT PARA_VALUE FROM V$PARAMETER WHERE PARA_NAME='ENABLE_AUDIT';
disql SYSAUDITOR/SYSAUDITOR
select * from V$AUDITRECORDS;
select * from SYSAUDITOR.SYSAUDIT;
35、作业调度信息
--作业调度信息
SELECT * FROM SYSJOB.SYSJOBSCHEDULES;
--作业运行历史信息
SELECT * FROM SYSJOB.SYSSTEPHISTORIES2 A WHERE (SELECT COUNT(*) FROM SYSJOB.SYSSTEPHISTORIES2 B WHERE B.NAME = A.NAME AND B.EXEC_ID >= A.EXEC_ID) <= 1 ORDER BY A.START_TIME DESC,A.NAME;
-- 用户定义的作业信息
select * from SYSJOB.SYSJOBS;
-- 作业包括的所有步骤信息
select * from SYSJOB.SYSJOBSTEPS;
select j.NAME, js.NAME, j.ENABLE, j.VALID, j.DESCRIBE, js.SEQNO, js.COMMAND, js.OUTPUT_FILE_PATH
from SYSJOB.SYSJOBS j left join SYSJOB.SYSJOBSTEPS js on j.ID = js.JOBID
order by j.id;
-- 作业步骤的执行情况的日志
select * from SYSJOB.SYSJOBHISTORIES;
-- 作业的执行情况的日志
select * from SYSJOB.SYSJOBHISTORIES2
-- 作业步骤的执行情况的日志
select * from SYSJOB.SYSSTEPHISTORIES2;
-- 作业管理系统中所有已定义操作员的信息
select * from SYSJOB.SYSOPERATORS;
-- 作业管理系统中所有已定义的警报信息
select * from SYSJOB.SYSALERTS;
-- 存储警报需要通知的操作员的信息，即警报和操作员的关联信息
select * from SYSJOB.SYSALERTNOTIFICATIONS;
-- 存储警报发生的历史记录的日志
select * from SYSJOB.SYSALERTHISTORIES;
-- SYSMAILINFO 存储作业管理系统管理员的信息
select * from SYSJOB.SYSMAILINFO;
注意：需要先开启作业调度代理，否则抛出不存在SYSJOB schema对象数据。
-- 开启代理作业
SP_INIT_JOB_SYS(1);
36、创建作业调度的步骤
初始化，创建系统表
SP_INIT_JOB_SYS(1);
创建job名称
call SP_CREATE_JOB('job_1',1,0,'',0,0,'',0,'');
开启作业配置选项
call SP_JOB_CONFIG_START('job_1');
37、创建job作业步骤
-- 新增
call SP_ADD_JOB_STEP('job_1', '统计数据更新情况', 0, 'begin PKG_SYN_DATA.P_HGZX_SYN_DATA; end;', 1, 2, 0, 0, NULL, 0);
/*
-- 修改
call SP_ALTER_JOB_STEP('job_1', '统计数据更新情况', 0, 'begin PKG_SYN_DATA.P_HGZX_SYN_DATA; end;', 1, 2, 0, 0, NULL, 0);
/
38、创建调度步骤
-- 新增（每天下午14:00执行1次）
call SP_ADD_JOB_SCHEDULE('job_1', '调度测试', 1, 1, 1, 0, 0, '14:30:00', NULL, '2019-06-21 13:51:58', NULL, '');
/
-- 修改
call SP_ALTER_JOB_SCHEDULE('job_1', '调度测试', 1, 1, 1, 0, 0, '14:30:00', NULL, '2019-06-21 13:51:58', NULL, '');
39、作业调度配置提交
call SP_JOB_CONFIG_COMMIT('job_1');
查询确认创建的JOB调度任务
select * from dba_jobs;
40、使用作业备份
---开启代理作业
SP_INIT_JOB_SYS(1);
--定时每周六运行，进行全量备份
call SP_CREATE_JOB ('JOB_DB_FULL_BAK',1,0,'',0,0,'',0,'定时全库备份');
call SP_JOB_CONFIG_START('JOB_DB_FULL_BAK');
call SP_ADD_JOB_STEP('JOB_DB_FULL_BAK','STEP_FULL_BAK',6,'01010/home/dmdba/dmbak',1,2,0,0,'/home/dmdba/dmbak/job_db_full_bak.log',0);
call SP_ADD_JOB_SCHEDULE('JOB_DB_FULL_BAK','SCHEDULE_FULL_BAK',1,2,1,64,0,'22:00:00',NULL,'2020-12-16 12:00:00',NULL,'每周六晚 22:00:00 全备');
call SP_JOB_CONFIG_COMMIT('JOB_DB_FULL_BAK');
--定时每天运行，进行增量备份(晚上)
call SP_CREATE_JOB('JOB_DB_INCREMENT_BAK',1,0,'',0,0,'',0,'定时增量备份');
call SP_JOB_CONFIG_START('JOB_DB_INCREMENT_BAK');
call SP_ADD_JOB_STEP('JOB_DB_INCREMENT_BAK','STEP_INCREMENT_BAK',6,'11020000/home/dmdba/dmbak',1,2,0,0,'/home/dmdba/dmbak/job_incr.log',1);
call SP_ADD_JOB_SCHEDULE('JOB_DB_INCREMENT_BAK','SCHEDULE_INCREMENT_BAK',1,1,1,0,0,'02:00:00',NULL,'2020-12-16 12:00:00',NULL,'');
call SP_JOB_CONFIG_COMMIT('JOB_DB_INCREMENT_BAK');
--定时每周日运行，删除前20天的备份，包括全量和增量
call SP_CREATE_JOB('JOB_DB_DEL_BAK',1,0,'',0,0,'',0,'定时删除备份');
call SP_JOB_CONFIG_START('JOB_DB_DEL_BAK');
call SP_ADD_JOB_STEP('JOB_DB_DEL_BAK','STEP_DEL_BAK',0,'SP_DB_BAKSET_REMOVE_BATCH(NULL,SYSDATE-20);',0,0,0,0,'/home/dmdba/dmbak/job_del_bak.log',1);
call SP_ADD_JOB_SCHEDULE('JOB_DB_DEL_BAK','SCHEDULE_DEL_BAK',1,2,1,1,0,'00:00:00',NULL,'2020-12-16 12:00:00',NULL,'');
call SP_JOB_CONFIG_COMMIT('JOB_DB_DEL_BAK');
--定时每周日运行，删除前3天的归档日志
call SP_CREATE_JOB('JOB_DEL_ARCH',1,0,'',0,0,'',0,'定时删除归档');
call SP_JOB_CONFIG_START('JOB_DEL_ARCH');
call SP_ADD_JOB_STEP('JOB_DEL_ARCH','STEP_DEL_ARCH',0,'SP_ARCHIVELOG_DELETE_BEFORE_TIME(SYSDATE-3);',0,0,0,0,'/home/dmdba/dmbak/job_del_arch.log',1);
call SP_ADD_JOB_SCHEDULE('JOB_DEL_ARCH','SCHEDULE_DEL_ARCH',1,2,1,1,0,'00:00:00',NULL,'2020-12-16 12:00:00',NULL,'');
call SP_JOB_CONFIG_COMMIT('JOB_DEL_ARCH');
41、统计信息
--创建视图，统计所有的模式和表
CREATE OR REPLACE VIEW V_ALL_TAB As
SELECT
A.NAME SCH_NAME,
B.NAME TAB_NAME
FROM
SYS.SYSOBJECTS A JOIN SYS.SYSOBJECTS B
ON
A.ID = B.SCHID
AND A.TYPE$ = 'SCH'
AND A.NAME NOT IN ('SYS','CTISYS','SYSAUDITOR','SYSSSO','SYSJOB','SYSDBA')
AND B.SUBTYPE$ = 'UTAB'
AND B.PID = -1
AND B.NAME NOT LIKE '%$AUX' ORDER BY 1,2;
--创建记录统计信息的表
CREATE TABLE T_STAT_TAB
(
SCHE_NAME VARCHAR(20),
TAB_NAME VARCHAR(50),
TAB_CNT INT,
STAT_TIME DATETIME(0) DEFAULT SYSDATE
);
--存储过程，统计所有表的数量，或者指定用户下的表的数量
CREATE OR REPLACE PROCEDURE P_STAT_TABLE (SCHEMA_NAME VARCHAR(50)) AS
V_CNT INT;
BEGIN
IF UCASE(SCHEMA_NAME)== 'ALL' THEN
SCHEMA_NAME := '';
END IF;
FOR REC IN (SELECT SCH_NAME,TAB_NAME FROM V_ALL_TAB WHERE SCH_NAME LIKE '%'||SCHEMA_NAME||'%') LOOP
BEGIN
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM "'||REC.SCH_NAME||'"."'||REC.TAB_NAME||'"' INTO V_CNT;
EXCEPTION WHEN OTHERS THEN
V_CNT := -1;
END;
EXECUTE IMMEDIATE 'INSERT INTO T_STAT_TAB(SCH_NAME,TAB_NAME,TAB_CNT)
VALUES('''||REC.SCH_NAME||''','''||REC.TAB_NAME||''','||V_CNT||')';
END LOOP;
COMMIT;
END;
P_START_TABLE('ALL');
--定时每周日运行，统计本周所有表数据
call SP_CREATE_JOB('JOB_START_TABLE_COUNT_TIMELY',1,0,'',0,0,'',0,'定时统计表数据量');
call SP_JOB_CONFIG_START('JOB_START_TABLE_COUNT_TIMELY');
call SP_ADD_JOB_STEP('JOB_START_TABLE_COUNT_TIMELY','STEP_START_TABLE_COUNT',0,'P_START_TABLE("ALL");',0,0,0,0,'',1);
call SP_ADD_JOB_SCHEDULE('JOB_START_TABLE_COUNT_TIMELY','SCHEDULE_START_TABLE_COUNT',1,2,1,1,0,'00:00:00',NULL,'2020-05-20 22:22:22',NULL,'');
call SP_JOB_CONFIG_COMMIT('JOB_START_TABLE_COUNT_TIMELY');
--创建统计每周总量和增量
CREATE OR REPLACE VIEW V_SEL_TAB_SUM
AS
SELECT *,TOTAL_ROWS - PRV_TOTAL_ROWS INCREMENT_ROWS
FROM(
SELECT STAT_TIME,TOTAL_ROWS,LEAD(TOTAL_ROWS) OVER(ORDER BY STAT_TIME DESC) PRV_TOTAL_ROWS
FROM (
SELECT LEFT(STAT_TIME,10) STAT_TIME,SUM(TAB_CNT) TOTAL_ROWS
FROM T_STAT_TAB
GROUP BY LEFT(STAT_TIME,10)
)
)
WHERE PRV_TOTAL_ROWS IS NOT NULL;
--查看每周总量和增量
SELECT * FROM V_SEL_TAB_SUM;
--统计每个表每周总量和增量
CREATE OR REPLACE VIEW V_SEL_TAB_INCR
AS
SELECT STAT_TIME,TAB_ROWS,PRV_TAB_ROWS,TAB_ROWS - PRV_TAB_ROWS INCREMENT_ROWS,SCHE_NAME,TAB_NAME
FROM(
SELECT STAT_TIME,TAB_ROWS,LEAD(TAB_ROWS) OVER(PARTITION BY SCHE_NAME,TAB_NAME ORDER BY STAT_TIME DESC) PRV_TAB_ROWS,SCHE_NAME,TAB_NAME
FROM (
SELECT LEFT(STAT_TIME,10) STAT_TIME,TAB_CNT TAB_ROWS,SCHE_NAME,TAB_NAME
FROM T_STAT_TAB
)
)
WHERE PRV_TAB_ROWS IS NOT NULL;
--查看每个表每周总量和增量
SELECT * FROM V_SEL_TAB_INCR;
-- 查看指定表每周总量和增量
SELECT * FROM V_SEL_TAB_INCR WHERE TAB_NAME='STAT_TAB';
-- 查看每个表最新周总量和增量
SELECT * FROM V_SEL_TAB_INCR
WHERE STAT_TIME=(
SELECT MAX(STAT_TIME)
FROM V_SEL_TAB_INCR)
ORDER BY TAB_ROWS DESC;
-- 查看增量较大的表数据信息
SELECT * FROM V_SEL_TAB_INCR ORDER BY INCREMENT_ROWS DESC NULLS LAST;
-- 统计数据库存储空间和表空间存储空间
CREATE TABLE T_STAT_DB_SIZE
(
TBS_NAME VARCHAR(20),
TOTAL_SIZE_MB INT,
STAT_TIME DATETIME(0) DEFAULT SYSDATE
);
CREATE OR REPLACE PROCEDURE P_STAT_SIZE AS
BEGIN
INSERT INTO T_STAT_DB_SIZE(TAB_NAME,TOTAL_SIZE_MB)
SELECT 'DB' TBS_NAME,TOTAL_SIZE*PAGE/1024/1024 FROM V$DATABASE;
INSERT INTO T_STAT_DB_SIZE(TBS_NAME,TOTAL_SIZE_MB)
SELECT ISNULL(NAME,'ALL_TBS') NAME,SUM(TOTAL_SIZE*PAGE/1024/1024) TOTAL_SIZE_MB
FROM V$TABLESPACE
GROUP BY ROLLUP(NAME);
END;
CALL SP_STAT_SIZE;
--定时每周日运行，统计本周表空间存储
call SP_CREATE_JOB('JOP_STAT_DB_SIZE_TIMELY',1,0,'',0,0,'',0,'定时统计存储容量');
call SP_JOB_CONFIG_START('JOP_STAT_DB_SIZE_TIMELY');
call SP_ADD_JOB_STEP('JOP_STAT_DB_SIZE_TIMELY','STEP_STAT_DB_SIZE',0,'P_STAT_SIZE',0,0,0,0,'',1);
call SP_ADD_JOB_SCHEDULE('JOP_STAT_DB_SIZE_TIMELY','SCHEDULE_STAT_DB_SIZE',1,2,1,1,0,'00:00:00',NULL,'2020-05-20 22:22:22',NULL,'');
call SP_JOB_CONFIG_COMMIT('JOP_STAT_DB_SIZE_TIMELY');
-- 统计每个表空间增量
CREATE OR REPLACE VIEW V_SEL_DB_INCR
AS
--统计每个表空间增量
SELECT STAT_TIME,TBS_NAME,TOTAL_SIZE_MB,PRV_TOTAL_SIZE_MB,
TOTAL_SIZE_MB - PRV_TOTAL_SIZE_MB INCREMENT_ROWS
FROM (
SELECT STAT_TIME,TBS_NAME,TOTAL_SIZE_MB,
LEAD(TOTAL_SIZE_MB) OVER(PARTITION BY TBS_NAME ORDER BY STAT_TIME DESC) PRV_TOTAL_SIZE_MB
FROM(
SELECT LEFT(STAT_TIME,19) STAT_TIME,TBS_NAME,TOTAL_SIZE_MB
FROM T_STAT_DB_SIZE)
)
WHERE PRV_TOTAL_SIZE_MB IS NOT NULL;
--统计每个表空间增量
SELECT * FROM V_SEL_TAB_INCR;
42、会话统计
--统计会话并发情况
CREATE TABLE T_STAT_SESS_COUNT
(
SESS_TYPE VARCHAR(20),
SESS_COUNT INT,
SESS_TIME DATETIME(0)
);
--统计会话并发情况
CREATE OR REPLACE PROCEDURE P_STAT_SESS(STAT_CNT INT) AS
BEGIN
INSERT INTO T_STAT_SESS_COUNT
SELECT 'SESS' SESS_TYPE,COUNT(*) SESS_COUNT,LEFT(CREATE_TIME,19) SESS_TIME
FROM V$SESSION_HISTORY
WHERE CREATE_TIME > (
SELECT IFNULL(MAX(SESS_TIME),'2020-01-01 01:01:01')
FROM T_STAT_SESS_COUNT
WHERE SESS_TYPE = 'SESS')
GROUP BY LEFT(CREATE_TIME,19)
HAVING COUNT(*) > STAT_CNT;
INSERT INTO T_STAT_SESS_COUNT
SELECT 'SQL' SESS_TYPE,COUNT(*) SESS_COUNT,LEFT(START_TIME,19) SESS_TIME
FROM V$SQL_HISTORY
WHERE START_TIME > (
SELECT IFNULL(MAX(SESS_TIME),'2020-01-01 01:01:01')
FROM T_STAT_SESS_COUNT
WHERE SESS_TYPE = 'SQL')
GROUP BY LEFT(START_TIME,19)
HAVING COUNT(*) > STAT_CNT;
END;
CALL P_STAT_SESS(2);
--定时每小时运行，统计时间段内并发情况
call SP_CREATE_JOB('JOP_STAT_SESS_COUNT_TIMELY',1,0,'',0,0,'',0,'定时统计会话并发量');
call SP_JOB_CONFIG_START('JOP_STAT_SESS_COUNT_TIMELY');
call SP_ADD_JOB_STEP('JOP_STAT_SESS_COUNT_TIMELY','STEP_STAT_SESS_SCOUNT',0,'CALL P_STAT_SESS(5);',0,0,0,0,'',1);
call SP_ADD_JOB_SCHEDULE('JOP_STAT_SESS_COUNT_TIMELY','SCHEDULE_STAT_SESS_COUNT',1,1,1,0,60,'00:00:00','23:59:59','2020-05-20 22:22:22',NULL,'');
call SP_JOB_CONFIG_COMMIT('JOP_STAT_SESS_COUNT_TIMELY');
--查询会话并发统计，按并发量排序。
SELECT * FROM T_STAT_SESS_COUNT WHERE SESS_TYPE = 'SESS' ORDER BY SESS_COUNT DESC;
--查询会话并发统计，按并发时间排序。
SELECT * FROM T_STAT_SESS_COUNT WHERE SESS_TYPE = 'SESS' ORDER BY SESS_TIME DESC;
--按照分钟统计会话并发
SELECT LEFT(SESS_TIME,16),SUM(SESS_COUNT)
FROM T_STAT_SESS_COUNT
WHERE SESS_TYPE='SESS'
GROUP BY LEFT(SESS_TIME,16)
ORDER BY 2 DESC ,1 DESC;
--查询SQL并发统计，按并发量排序。
SELECT * FROM T_STAT_SESS_COUNT WHERE SESS_TYPE = 'SQL' ORDER BY SESS_COUNT DESC;
--查询SQL并发统计，按并发时间排序。
SELECT * FROM T_STAT_SESS_COUNT WHERE SESS_TYPE = 'SQL' ORDER BY SESS_TIME DESC;
--按照分钟统计SQL并发
SELECT LEFT(SESS_TIME,16),SUM(SESS_COUNT)
FROM T_STAT_SESS_COUNT
WHERE SESS_TYPE='SQL'
GROUP BY LEFT(SESS_TIME,16)
ORDER BY 2 DESC ,1 DESC;
通过V$ERR_INFO 视图查看对应的错误代号
select top 5 * from V$ERR_INFO;
LINEID CODE errinfo
---------- ----------- --------------------
1 100 Empty result
2 101 String truncated
3 102 NULL in set function
4 103 Invalid table name
5 104 Delete 0 row
43、查看自带函数
-- 统计信息相关函数
select name, CLASS$ from v$ifun where name like '%SP%STAT%INIT';
44、执行计划缓存
-- 创建系统包
CALL SP_CREATE_SYSTEM_PACKAGES(1);
-- 收集SYSDBA 模式下所有索引的统计信息
DBMS_STATS.GATHER_SCHEMA_STATS('SYSDBA',100,FALSE,'FOR ALL COLUMNS SIZE AUTO');
-- 清理执行计划缓存
CALL SP_CLEAR_PLAN_CACHE();
45、统计存储过程数量
-- 统计存储过程数量
SELECT sch.name, proc.name, proc.crtdate
FROM sysobjects sch, sysobjects proc
WHERE proc.schid = sch.id
AND sch.name IN ('MID', 'APP')
AND proc.subtype$ = 'PROC';
AND proc.crtdate > SYSDATE - 1;
46、清空模式下所有表数据
DECLARE
BEGIN
FOR rs IN (SELECT *
FROM all_tables
WHERE "OWNER" IN ('MID', 'BAS'))
LOOP
DECLARE
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || rs.owner || '.' || rs.table_name;
EXCEPTION
WHEN OTHERS THEN
print('TRUNCATE TABLE' || rs.owner || '.' || rs.table_name);
END;
END LOOP;
END;
47、查看数据库使用内存
SELECT NAME
, -- 内存池描述名称
SUM(org_size)
, -- 内存池原始大小，sum 函数：总数
sf_get_ep_seqno(ROWID)
, --rowid 数据对象编号
SUM(total_size) / 1024.0 / 1024 / 1024 total_gb
, -- 内存池目前大小
SUM(reserved_size)
, -- 内存池内已经分配掉的空间
COUNT(*)
,AVG(total_size) / 1024.0 / 1024 avg_size_mb -- 内存池目前大小，avg 函数：平均值
FROM v$mem_pool
GROUP BY NAME, sf_get_ep_seqno(ROWID)
ORDER BY 4 DESC;
674
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
