select a.name "DB Name",
e.global_name "Global Name",
c.host_name "Host Name",
c.instance_name "Instance Name" ,
DECODE(c.logins,'RESTRICTED','YES','NO') "Restricted Mode",
a.log_mode "Archive Log Mode"
FROM v$database a, v$version b, v$instance c,global_name e
WHERE b.banner LIKE '%Oracle%';
参数文件
select nvl(value,'pfile') "Parameter_File" from v$parameter where Name='spfile';
非默认的参数
select name, rtrim(value) "pvalue"
from v$parameter
where isdefault = 'FALSE'
order by name;
控制文件及其状态
select Name,Status from v$controlfile;
数据库版本信息
select * from v$version;
数据库组件（true：已安装，false：未安装）
SELECT PARAMETER, VALUE FROM V$OPTION;
实例信息
select instance_name,host_name,version,status,database_status from v$instance;
NLS参数设置
SELECT * FROM NLS_Database_Parameters;
已装载的产品选项
select COMP_ID, COMP_NAME, VERSION,STATUS from dba_registry;
数据库的并发数
select count(*) as "并发数" from v$session where status='ACTIVE';
数据库Session连接数
select count(*) as "连接数" from v$session;
数据库总大小（GB）
select round(sum(space)) "总容量/Gb"
from (select sum(bytes) / 1024 / 1024 / 1024 space
from dba_data_files
union all
select nvl(sum(bytes) / 1024 / 1024 / 1024, 0) space
from dba_temp_files
union all
select sum(bytes) / 1024 / 1024 / 1024 space from v$log);
数据库服务器运行的操作系统
select PLATFORM_NAME from v$database;
DBID
select dbid from v$database;
Flashback是否启动
select decode(flashback_on,'NO','未启用','启用') as "闪回模式" from v$database;
存储结构、表空间、数据文件
表空间及数据文件
select tablespace_name,file_name,
bytes/1024/1024 "Total Size(MB)",autoExtensible "Auto"
from dba_data_files
order by tablespace_name,file_id;
表空间状态及其大小使用情况
SELECT d.tablespace_name "Name", d.status "Status", d.contents "Type",
ROUND(NVL(a.bytes / 1024 / 1024, 0), 2) "Size (MB)",
ROUND(NVL(a.bytes - NVL(f.bytes, 0), 0) / 1024 /1024, 2) "Used (MB)",
ROUND(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), 2) "Used%",
ROUND(NVL(a.maxbytes / 1024 / 1024, 0), 2) "Max Size (MB)",
DECODE(NVL(a.maxbytes,0), 0, 0, ROUND(NVL(a.maxbytes - a.bytes, 0) / 1024 / 1024, 2)) "Unused (MB)",
DECODE(NVL(a.maxbytes,0), 0, 0, ROUND((1 - NVL(a.bytes / a.maxbytes, 0))*100, 2)) "Unused%"
FROM sys.dba_tablespaces d,
(SELECT tablespace_name, SUM(bytes) bytes, SUM(maxbytes) maxbytes
FROM dba_data_files GROUP BY tablespace_name
UNION ALL
SELECT tablespace_name, SUM(bytes) bytes, SUM(maxbytes) maxbytes
FROM dba_temp_files GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) bytes
FROM dba_free_space GROUP BY tablespace_name
UNION ALL
SELECT tablespace_name, SUM(bytes_free) bytes
FROM gv$temp_space_header GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+);
数据文件状态及其大小使用情况
SELECT a.tablespace_name "TableSpace Name", a.File_Name "File Name",
a.status "Status", a.AutoExtensible "Auto",
round(NVL(a.bytes / 1024 / 1024, 0),1) "Size (MB)",
round(NVL(a.bytes - NVL(f.bytes, 0),0)/1024/1024, 1) "Used (MB)",
round(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), 2) "Used %"
FROM dba_data_files a,
(select file_id, sum(bytes) bytes
from dba_free_space group by File_id) f
WHERE a.file_id=f.file_id(+)
order by a.tablespace_name,a.File_id;
不使用临时文件的临时表空间
select tablespace_name,contents from dba_tablespaces
where contents='TEMPORARY' and tablespace_name not in
(select tablespace_name from dba_temp_files);
无效的数据文件(offline)
select f.tablespace_name,f.file_name,d.status
from dba_data_files f,v$datafile d
where d.status='OFFLINE' and f.file_id=File#(+);
处于恢复模式的文件
select f.tablespace_name,f.file_name
from dba_data_files f, v$recover_file r
where f.file_id=r.file#;
含有50个以上的Extent且30%以上碎片的表空间
select s.tablespace_name,
round(100 * f.hole_count / (f.hole_count + s.seg_count)) pct_fragmented,
s.seg_count segments, f.hole_count holes
from (Select tablespace_name, count(*) seg_count
from dba_segments group by tablespace_name) s,
(Select tablespace_name, count(*) hole_count
from dba_free_space group by tablespace_name) f
where s.tablespace_name = f.tablespace_name
and s.tablespace_name in (Select tablespace_name
from dba_tablespaces where contents = 'PERMANENT')
And s.tablespace_name not in ('SYSTEM')
and 100 * f.hole_count / (f.hole_count + s.seg_count) > 30
and s.seg_count > 50;
表空间上的I/O分布
SELECT t.name ts_name,
f.name file_name,
s.phyrds phy_reads,
s.phyblkrd phy_blockreads,
s.phywrts phy_writes,
s.phyblkwrt phy_blockwrites
FROM gv$tablespace t,
gv$datafile f,
gv$filestat s
WHERE t.ts# = f.ts#
and
f.file# = s.file#
ORDER BY s.phyrds desc, s.phywrts desc;
Select InitCap(SEGMENT_TYPE) "Type", OWNER, SEGMENT_NAME, BYTES, NEXT_EXTENT,
ROUND(100 * NEXT_EXTENT / BYTES) "Percent(Next/Bytes)"
FROM DBA_SEGMENTS
WHERE ((ROUND(100 * NEXT_EXTENT / BYTES) < 10) OR
(ROUND(100 * NEXT_EXTENT / BYTES) >= 200))
AND SEGMENT_TYPE NOT IN ('ROLLBACK', 'TEMPORARY', 'CACHE', 'TYPE2 UNDO')
order by 2,3,1;
Max Extents(>1)已经有90%被使用了的Segments
Select segment_type, owner, Segment_name, Tablespace_name,
partition_name, round(bytes /1024/1024) "Size(MB)", extents, max_extents
From dba_segments
where segment_type not in ('ROLLBACK', 'TEMPORARY', 'CACHE', 'TYPE2 UNDO')
and extents >= (1 - ( 10 / 100)) * max_extents and max_extents > 1
order by bytes / max_extents desc;
已经分配超过100 Extents的Segments
Select segment_type, owner, segment_name, extents, partition_name
from dba_segments
where segment_type not in ('ROLLBACK', 'TEMPORARY', 'CACHE', 'TYPE2 UNDO')
and owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS', 'ORDPLUGINS',
'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB')
and extents > 100;
因表空间空间不够将导致不能扩展的Objects
Select a.tablespace_name, a.owner,
decode(a.partition_name, null, a.segment_name,
a.segment_name || '.' || a.partition_name) "Segment Name",
a.extents, round(next_extent/1024) next_extent_kb,
round(b.free / 1024) ts_free_kb,
round(c.morebytes / 1024 / 1024) ts_growth_mb
from dba_segments a,
(Select df.tablespace_name, nvl(max(fs.bytes), 0) free
from dba_data_files df,
dba_free_space fs
where df.file_id = fs.file_id (+)
group by df.tablespace_name) b,
(Select tablespace_name, max(maxbytes - bytes) morebytes,
sum(decode(AUTOEXTENSIBLE, 'YES', 1, 0)) autoextensible
from dba_data_files
group by tablespace_name) c
where a.tablespace_name = b.tablespace_name
and a.tablespace_name = c.tablespace_name
and ((c.autoextensible = 0) or ((c.autoextensible > 0)
and (a.next_extent > c.morebytes)))
and a.next_extent > b.free
order by 1;
表空间碎片化程度分析（FSFI<30,破碎化程度高）
select tablespace_name,
round(sqrt(max(blocks) / sum(blocks)) *
(100 / sqrt(sqrt(count(blocks)))),
2) FSFI,
(case
when sqrt(max(blocks) / sum(blocks)) *
(100 / sqrt(sqrt(count(blocks)))) > = 30 then
'正常'
when sqrt(max(blocks) / sum(blocks)) *
(100 / sqrt(sqrt(count(blocks)))) < 30 then
'表空间破碎化程度高，请整理'
end) Prompt
from dba_free_space
group by tablespace_name
order by 2;
select h.tablespace_name,
round(sum(h.bytes_free+h.bytes_used)/1048576, 2) "MB_Alloc",
round(sum((h.bytes_free+h.bytes_used)-nvl(p.bytes_used, 0))/1048576, 2) "MB_free",
round(sum(nvl(p.bytes_used, 0))/ 1048576, 2) "MB_Used",
round((sum((h.bytes_free + h.bytes_used)-nvl(p.bytes_used, 0))/sum(h.bytes_used + h.bytes_free)) * 100,2) "Pct_Free%",
100 - round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100,2)
"pct_used%"
from sys.v_$TEMP_SPACE_HEADER h, sys.v_$Temp_extent_pool p, dba_temp_files f
where p.file_id(+) = h.file_id
and p.tablespace_name(+) = h.tablespace_name
and f.file_id = h.file_id
and f.tablespace_name = h.tablespace_name
group by h.tablespace_name, f.maxbytes
ORDER BY 4;
使用最多临时表空间的SQL
SELECT SE.USERNAME,
SE.SID,
SU.EXTENTS,
(SU.BLOCKS * TO_NUMBER(RTRIM(P.VALUE)))/(1024*1024) AS "SPACE",
TABLESPACE,
SEGTYPE,
SQL_TEXT
FROM V$SORT_USAGE SU, V$PARAMETER P, V$SESSION SE, V$SQL S
WHERE P.NAME = 'DB_BLOCK_SIZE'
AND SU.SESSION_ADDR = SE.SADDR
AND S.HASH_VALUE = SU.SQLHASH
AND S.ADDRESS = SU.SQLADDR
ORDER BY SU.BLOCKS * TO_NUMBER(RTRIM(P.VALUE)) DESC, SE.SID;
重做日志
重做日志文件信息
select f.group#, f.member "Redo File", f.Type,
l.Status,l.bytes/1024/1024 "Size(MB)"
from v$log l,v$logfile f
where l.group#=f.group#;
最近7天归档日志的生成频率
select a.recid,
to_char(a.first_time, 'yyyy-mm-dd hh24:mi:ss') begin_time,
b.recid,
to_char(b.first_time, 'yyyy-mm-dd hh24:mi:ss') end_time,
round((b.first_time - a.first_time) * 24 * 60, 2) minutes
from v$log_history a, v$log_history b
where b.recid = a.recid + 1
and a.first_time > sysdate - 7;
监控当前重做日志文件使用情况（as sysdba）
select le.leseq "Current log sequence No",
100 * cp.cpodr_bno / le.lesiz "Percent Full",
(cpodr_bno - 1) * 512 "bytes used exclude header",
le.lesiz * 512 - cpodr_bno * 512 "Left space",
le.lesiz * 512 "logfile size"
from x$kcccp cp, x$kccle le
where LE.leseq = CP.cpodr_seq
and bitand(le.leflg, 24) = 8;
select name,to_char(value) "value(Byte)"
from v$sga
union all
select name,value
from v$parameter
where name in
('shared pool_size','large_pool_size',
'java_pool_size','lock_sga');
Library Cache Reload Ratio(<1%)
Select round((Sum(Reloads) / Sum (Pins)) * 100, 4) "LC_Reload_Ratio%"
From V$Librarycache;
Data Dictionary Miss Ratio(<15%)
Select Round((((sum(GetMisses)) / sum(Gets)) * 100),4) "DC_Miss_Ratio%"
From V$rowcache;
共享池使用概况
Select round(sum(a.bytes)/(1024*1024), 2) "Used(MB)",
round(max(p.value)/(1024*1024), 2) "Size(MB)",
round((max(p.value)/(1024*1024))-(sum(a.bytes)/(1024*1024)), 2) "Avail(MB)",
round((sum(a.bytes)/max(p.value))*100, 2) "Used(%)"
from V$sgastat a,
(select decode(sign(instr(upper(value), 'K') + instr(upper(value),'M')),
0, value,
1, decode(sign(instr(upper(value), 'K')),
1, to_number(1024 * rtrim(substr(value, 1, instr(upper(value), 'K') - 1))),
to_number(1024 * 1024 * rtrim(substr(value, 1,instr(upper(value), 'M') - 1))))) value
from v$parameter
where name like 'shared_pool_size') p
where a.name in
( 'reserved stopper', 'table definiti', 'dictionary cache',
'library cache', 'sql area', 'PL/SQL DIANA', 'SEQ S.O.');
共享池建议
select shared_pool_size_for_estimate "Shared Pool Size(estimate)",
SHARED_POOL_SIZE_FACTOR "Factor",
estd_lc_size "Libarary Cache Size",
estd_lc_time_saved "time Saved"
from v$shared_pool_advice;
DB Buffer Cache(Default) Hit Ratio(>90%)
Select round(100 * (1-(physical_reads/(db_block_gets+consistent_gets))), 4) "BC_Hit _Ratio"
FROM v$buffer_pool_statistics
WHERE name = 'DEFAULT';
DB Buffer Cache Advice
select Name "Pool Name",Block_size,SIZE_FOR_ESTIMATE "Buffer Size",
SIZE_FACTOR "Factor",ESTD_PHYSICAL_READ_FACTOR "Phy_Read_Factor",
ESTD_PHYSICAL_READS "ESTD_PHY_READS"
from v$db_cache_advice where ADVICE_STATUS='ON';
磁盘排序(<5%)
select a.value "Sort(Disk)", b.value "Sort(Memory)",
round(100*(a.value/decode((a.value+b.value), 0,1,
(a.value+b.value))),2) "Disk_Sort_Ratio%"
from v$sysstat a, v$sysstat b
where a.name = 'sorts (disk)' and b.name = 'sorts (memory)';
Log Buffer latch Contention(<1%)
SELECT name "Redo Name", gets, misses, immediate_gets, immediate_misses,
Decode(gets,0,0,round(misses/gets*100,3)) "Miss_Ratio%",
Decode(immediate_gets+immediate_misses,0,0,
round( immediate_misses/(immediate_gets+immediate_misses)*100,3)) "Immediate Misses Ratio%"
FROM v$latch WHERE name IN ('redo allocation', 'redo copy');
SELECT a.VALUE redo_entries,
b.VALUE redo_buffer_allocation_retries,
ROUND((1 - b.VALUE / a.VALUE) * 100, 4) log_buffer_ratio
FROM v$sysstat a, v$sysstat b
WHERE a.NAME = 'redo entries'
AND b.NAME = 'redo buffer allocation retries';
数据字典高速缓存
SELECT a.VALUE redo_entries,
b.VALUE redo_buffer_allocation_retries,
ROUND((1 - b.VALUE / a.VALUE) * 100, 4) log_buffer_ratio
FROM v$sysstat a, v$sysstat b
WHERE a.NAME = 'redo entries'
AND b.NAME = 'redo buffer allocation retries';
高速缓存
SELECT ROUND((1 - SUM(getmisses) / SUM(gets)) * 100, 1) "Dictionary Cache Hit Ratio"
FROM v$rowcache;
排序（磁盘/内存）
SELECT b.VALUE memory_sort,
a.VALUE disk_sort,
ROUND((1 - a.VALUE / (a.VALUE + b.VALUE)) * 100, 4) sort_ratio
FROM v$sysstat a, v$sysstat b
WHERE a.NAME = 'sorts (disk)'
AND b.NAME = 'sorts (memory)';
SGA Memory Map (overall)
SELECT 1 dummy, 'DB Buffer Cache' area, name, round(sum(bytes)/1024/1024,2) "Size/Mb"
FROM v$sgastat
WHERE pool is null
and name = 'db_block_buffers'
group by name
union all
SELECT 2, 'Shared Pool', pool, round(sum(bytes)/1024/1024,2)
FROM v$sgastat
WHERE pool = 'shared pool'
group by pool
union all
SELECT 3, 'Large Pool', pool, round(sum(bytes)/1024/1024,2)
FROM v$sgastat
WHERE pool = 'large pool'
group by pool
union all
SELECT 4, 'Java Pool', pool, round(sum(bytes)/1024/1024,2)
FROM v$sgastat
WHERE pool = 'java pool'
group by pool
union all
SELECT 5, 'Redo Log Buffer', name, round(sum(bytes)/1024/1024,2)
FROM v$sgastat
WHERE pool is null
and name = 'log_buffer'
group by name
union all
SELECT 6, 'Fixed SGA', name, round(sum(bytes)/1024/1024,2)
FROM v$sgastat
WHERE pool is null
and name = 'fixed_sga'
group by name
ORDER BY 4 desc;
SGA Memory Map (shared pool)
SELECT 'Shared Pool' area,
name,
round(sum(bytes) / 1024 / 1024, 2) "Size/Mb"
FROM v$sgastat
WHERE pool = 'shared pool'
and name in
('library cache', 'dictionary cache', 'free memory', 'sql area')
group by name
union all
SELECT 'Shared Pool' area,
'miscellaneous',
round(sum(bytes) / 1024 / 1024, 2) "Size/Mb"
FROM v$sgastat
WHERE pool = 'shared pool'
and name not in
('library cache', 'dictionary cache', 'free memory', 'sql area')
group by pool
order by 3 desc;
查看SGA的使用
select COMPONENT,CURRENT_SIZE from V$SGA_DYNAMIC_COMPONENTS;
数据库对象
没有主键的非系统表
Select owner, table_name
from dba_tables
where owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS',
'ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB','WMSYS','OLAPSYS','WKSYS')
minus
Select owner, table_name
from dba_constraints
where constraint_type = 'P'
and owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS',
'ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB','WMSYS','OLAPSYS','WKSYS');
没有索引的外键
SELECT acc.owner,acc.table_name,acc.constraint_name,acc.column_name
FROM all_cons_columns acc, all_constraints ac
WHERE ac.constraint_name = acc.constraint_name
AND ac.constraint_type = 'R'
and acc.owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS',
'ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB','WMSYS','OLAPSYS','WKSYS')
AND (acc.owner, acc.table_name, acc.column_name, acc.position)
IN
(SELECT acc.owner, acc.table_name, acc.column_name, acc.position
FROM all_cons_columns acc, all_constraints ac
WHERE ac.constraint_name = acc.constraint_name
AND ac.constraint_type = 'R'
MINUS
SELECT table_owner, table_name, column_name, column_position
FROM all_ind_columns)
ORDER BY acc.owner,acc.table_name, acc.constraint_name,acc.column_name;
建有6个以上索引的非系统表
Select table_owner, table_name, count(*) index_count
from dba_indexes
where table_owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS',
'ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB','WMSYS','OLAPSYS','WKSYS')
having count(*) > 6
group by table_owner, table_name
order by 1,3 desc;
指向对象不存在的Public同义词
Select s.synonym_name, s.table_owner, s.table_name
from sys.DBA_synonyms s
where not exists (Select 'x'
from sys.DBA_objects o
where o.owner = s.table_owner
and o.object_name = s.table_name)
and db_link is null and s.owner = 'PUBLIC'
and s.table_owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS',
'ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB','WMSYS','OLAPSYS','WKSYS')
order by 1;
指向对象不存在的非Public同义词
Select s.owner, s.synonym_name, s.table_owner, s.table_name
from sys.DBA_synonyms s
where not exists (Select 'x'
from sys.DBA_objects o
where o.owner = s.table_owner
and o.object_name = s.table_name)
and db_link is null and s.owner <> 'PUBLIC'
and s.owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS',
'ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB','WMSYS','OLAPSYS','WKSYS')
order by 1;
没有授予给任何角色和用户的角色
Select role
from dba_roles r
where
role not in (
'CONNECT','RESOURCE','DBA','SELECT_CATALOG_ROLE',
'EXECUTE_CATALOG_ROLE','DELETE_CATALOG_ROLE',
'EXP_FULL_DATABASE','WM_ADMIN_ROLE','IMP_FULL_DATABASE',
'RECOVERY_CATALOG_OWNER','AQ_ADMINISTRATOR_ROLE',
'AQ_USER_ROLE','GLOBAL_AQ_USER_ROLE','OEM_MONITOR','HS_ADMIN_ROLE')
and
not exists (Select 1
from dba_role_privs p
where p.granted_role = r.role);
将System表空间作为临时表空间的用户(除Sys外)
Select username
from dba_users
where temporary_tablespace = 'SYSTEM';
将System表空间作为默认表空间的用户(除Sys外)
Select username
from dba_users
where default_tablespace = 'SYSTEM'
and username <> 'SYS' ;
没有授予给任何用户的profiles
Select distinct profile
from dba_profiles
minus
Select distinct profile
from dba_users;
没有和Package相关联的Package Body
Select pb.owner, pb.object_name
from dba_objects pb
where pb.object_type = 'PACKAGE BODY'
and not exists (Select 1
from dba_objects p
where p.object_type = 'PACKAGE'
and p.owner = pb.owner
and p.object_name = pb.object_name)
and pb.owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS',
'ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB','WMSYS','OLAPSYS','WKSYS')
order by 1,2;
被Disabled的约束
Select owner, table_name, constraint_name, CONSTRAINT_TYPE
from dba_constraints
where status = 'DISABLED'
and owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS',
'ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB','WMSYS','OLAPSYS','WKSYS')
ORDER BY 1,2,3;
被Disabled的触发器
Select owner, nvl(table_name, '<system trigger>') table_name, trigger_name
from dba_triggers
where status = 'DISABLED'
and owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS',
'ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB','WMSYS','OLAPSYS','WKSYS')
ORDER BY 1,2,3;
Invalid Objects
Select OWNER, OBJECT_NAME, OBJECT_TYPE
from dba_objects
where status = 'INVALID'
and owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS',
'ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB','WMSYS','OLAPSYS','WKSYS')
ORDER BY 1,2,3;
执行失败或中断的Jobs
select job, to_char(last_date,'yyyy-mm-dd hh24:mi:ss') "Last Date",
to_char(this_date,'yyyy-mm-dd hh24:mi:ss') "This Date",
broken,failures, schema_user, what
from dba_jobs where broken='Y' or failures>0;
当前未执行且下一执行日期已经过去的Jobs
select job, to_char(last_date,'yyyy-mm-dd hh24:mi:ss') "Last Date",
to_char(this_date,'yyyy-mm-dd hh24:mi:ss') "This Date",
broken,failures, schema_user, what
from dba_jobs
where job not in (select job from dba_jobs_running)
and broken='N' and next_date<sysdate;
含有未分析的非系统表的Schemas
Select distinct owner "Schema"
from DBA_tables
where num_rows is null
and owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS',
'ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB','WMSYS','OLAPSYS','WKSYS')
order by 1;
含有未分析的非系统分区表的Schemas
Select distinct table_owner "Schema"
from DBA_tab_partitions
where num_rows is null
and table_owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS',
'ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB','WMSYS','OLAPSYS','WKSYS')
order by 1;
含有未分析的非系统索引的Schemas
Select distinct owner "Schema" from DBA_indexes
where leaf_blocks is null
and owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS',
'ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB','WMSYS','OLAPSYS','WKSYS')
order by 1;
含有未分析的非系统分区索引的Schemas
Select distinct index_owner "Schema"
from DBA_ind_partitions
where leaf_blocks is null
and index_owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS',
'ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB','WMSYS','OLAPSYS','WKSYS')
order by 1;
回滚段空间配置
select r.segment_name segment_name,
r.owner owner,
r.tablespace_name tablespace_name,
r.status status,
round(r.initial_extent / 1024 / 1024) initial_extent,
round(r.next_extent / 1024 / 1024) next_extent,
s.extents,
0 extents,
ROUND(s.rssize / 1024 / 1024) rssize,
s.xacts active_trans
from dba_rollback_segs r, v$rollname n, v$rollstat s
where r.segment_name = n.name
and n.usn = s.usn;
用户角色查询
select username,
ACCOUNT_STATUS,
default_tablespace,
temporary_tablespace,
granted_role
from dba_users u, dba_role_privs r
where u.username = r.grantee
order by username;
select a.owner,
a.tablespace_name tbsname,
a.table_name tname,
b.index_name iname
from dba_tables a,
dba_indexes b
where
a.tablespace_name = b.tablespace_name
and b.table_name = a.table_name
and a.owner = b.owner
and b.owner NOT in ('SYS', 'SYSTEM')
and a.tablespace_name not in ('USERS', 'SYSAUX', 'SYSMAN', 'SYSTEM', 'TEMP')
order by owner;
单个用户大小估算
select nvl(t.owner, 'total:') owner,
case
when (to_char(sum(bytes) / 1024 / 10241)) < 1 then
'0' || to_char(round(sum(bytes) / 1024 / 10241, 2))
else
to_char(round(sum(bytes) / 1024 / 10241, 2))
end "大小/Mb"
from dba_segments t
group by rollup(t.owner);
具有DBA角色的用户
select grantee,granted_role from dba_role_privs where granted_role='DBA';
具有SYSDBA权限的用户
SELECT * FROM v$pwfile_users;
系统表空间中非SYS对象
select OWNER,
SEGMENT_NAME,
SEGMENT_TYPE,
decode(segment_type,
'TABLE',
'alter table ' || OWNER || '.' || SEGMENT_NAME ||
' MOVE TABLESPACE &' || 'TABLESPACE;',
'INDEX',
'alter index ' || OWNER || '.' || SEGMENT_NAME ||
' REBUILD TABLESPACE &' || 'TABLESPACE NOLOGGING;',
null) SCRIPT
from dba_segments t
where t.tablespace_name = 'SYSTEM'
AND OWNER NOT IN ('SYS', 'OUTLN', 'SYSTEM', 'WMSYS');
检测SYSTEM表空间里的用户对象
select owner, segment_type, segment_name
from dba_segments
where owner not in ('SYS', 'SYSTEM')
and tablespace_name = 'SYSTEM'
order by 1;
SELECT owner,
segment_name,
segment_type,
tablespace_name,
TRUNC(BYTES / 1024 / 1024, 1) size_mb
FROM dba_segments t
WHERE NOT EXISTS
(SELECT 'x'
FROM dba_indexes i
WHERE t.owner = i.table_owner
AND t.segment_name = i.table_name)
AND t.segment_type IN ('TABLE', 'TABLE PARTITION')
AND t.owner IN (select username
from dba_users d
where d.default_tablespace not in
('SYSTEM', 'SYSAUX', 'SYSMAN', 'USERS', 'TEMP')
and d.account_status = 'OPEN')
ORDER BY 5 DESC;
sort_segment检查
select tablespace_name,extent_size db_blocks_per_extent,total_extents,
used_extents,free_extents from v$sort_segment;
超过2g的segment（单个表超过2g建议使用分区表）
select *
from (Select segment_name,
bytes / 1024 / 1024 size_M,
segment_type,
tablespace_name
from dba_segments
where bytes > 2 * 1024 * 1024 * 1024
order by bytes desc);
定时任务（JOB）
SELECT JOB,
LOG_USER,
PRIV_USER,
SCHEMA_USER,
LAST_DATE,
THIS_DATE,
NEXT_DATE,
TOTAL_TIME,
DECODE(BROKEN, 'Y', 'YES', 'N', 'NO') "JOB_BROKEN",
INTERVAL,
FAILURES,
TRANSLATE(WHAT, chr(10), ' ') WHAT
FROM DBA_JOBS
ORDER BY JOB;
Rollback信息
select substr(sys.dba_rollback_segs.SEGMENT_ID,1,5) "ID#",
substr(sys.dba_segments.OWNER,1,8) "Owner",
substr(sys.dba_segments.TABLESPACE_NAME,1,17) "Tablespace Name",
substr(sys.dba_segments.SEGMENT_NAME,1,12) "Rollback Name",
substr(sys.dba_rollback_segs.INITIAL_EXTENT,1,10) "INI_Extent",
substr(sys.dba_rollback_segs.NEXT_EXTENT,1,10) "Next Exts",
substr(sys.dba_segments.MIN_EXTENTS,1,5) "MinEx",
substr(sys.dba_segments.MAX_EXTENTS,1,5) "MaxEx",
substr(sys.dba_segments.PCT_INCREASE,1,5) "%Incr",
substr(sys.dba_segments.BYTES,1,15) "Size (Bytes)",
substr(sys.dba_segments.EXTENTS,1,6) "Extent#",
substr(sys.dba_rollback_segs.STATUS,1,10) "Status"
from sys.dba_segments, sys.dba_rollback_segs
where sys.dba_segments.segment_name = sys.dba_rollback_segs.segment_name and
sys.dba_segments.segment_type = 'ROLLBACK'
order by sys.dba_rollback_segs.segment_id;
查看表分区的信息
select t.table_name, kc.column_name, t.partitioning_type
from dba_part_key_columns kc, dba_part_tables t
where kc.owner = t.owner
and kc.name = t.table_name;
查看超过16G的索引对像
SELECT OWNER,
SEGMENT_NAME,
SEGMENT_TYPE,
TABLESPACE_NAME,
BYTES
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE='INDEX' AND BYTES>=17179869184;
--可以通过重建索引减少空间：
ALTER INDEX index_name REBUILD [ONLINE];
--REBUILD和REBUILD ONLINE的区别：
--ALTER INDEX REBUILD 只扫描现有的索引块来实现索引的重建。
--ALTER INDEX REBUILD ONLINE实质上是扫描表而不是扫描现有的索引块来实现索引的重建
性能
锁等待检测
SELECT substr(lpad('--->',DECODE(request,0,0,4))||sid,1,20) "SESSID", id1, id2, lmode, request, type
FROM V$LOCK WHERE id1 IN (SELECT id1 FROM V$LOCK WHERE lmode = 0)
ORDER BY id1,request;
死锁检测
SELECT dob.OBJECT_NAME Table_Name,lo.SESSION_ID,vss.SERIAL#,
vss.action Action,vss.osuser OSUSER,
vss.process AP_Process_ID,VPS.SPID DB_Process_ID
from v$locked_object lo, dba_objects dob, v$session vss, V$PROCESS VPS
where lo.OBJECT_ID = dob.OBJECT_ID
and lo.SESSION_ID = vss.SID
AND VSS.paddr = VPS.addr
order by 2,3,DOB.object_name;
select max_proc, sess_cnt, round((sess_cnt*100)/max_proc,2) "USED%"
from
(select to_number(value) max_proc from v$parameter where lower(name)='processes') a,
(select count(*) sess_cnt from v$session) b;
6.5 Top I/O Wait
SELECT /*+ rule */ event,segment_type,segment_name,file_id,block_id,blocks
FROM dba_extents, gv$session_wait
WHERE p1text='file#'
AND p2text='block#'
AND p1=file_id and
p2 between block_id AND block_id+blocks
ORDER BY segment_type,segment_name;
Top 10 Wait
select *
from (
select event,sum(decode(wait_Time,0,0,1)) "Prev",
sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Total"
from v$session_Wait
group by event
order by 4 desc
)
where rownum<=10;
Top 10 bad SQL
SELECT *
FROM (SELECT parsing_user_id executions,
sorts,
command_type,
disk_reads,
sql_text
FROM v$sqlarea
ORDER BY disk_reads DESC)
WHERE rownum < 10;
Top most expensive SQL (Buffer Gets by Executions)
select buffer_gets,
executions,
buffer_gets/ decode(executions,0,1, executions) gets_per_exec,
hash_value,
sql_text
from v$sqlarea
where buffer_gets > 50000
order by buffer_gets desc;
6.9 Top most expensive SQL (Physical Reads by Executions)
select disk_reads,
executions,
disk_reads / decode(executions,0,1, executions) reads_per_exec,
hash_value,
sql_text
from v$sqlarea
where disk_reads > 10000
order by disk_reads desc;
Top most expensive SQL (Rows Processed by Executions)
select rows_processed,
executions,
rows_processed / decode(executions,0,1, executions) rows_per_exec,
hash_value,
sql_text
from v$sqlarea
where rows_processed > 10000
order by rows_processed desc;
Top most expensive SQL (Buffer Gets vs Rows Processed)
select buffer_gets, lpad(rows_processed ||
decode(users_opening + users_executing, 0, ' ','*'),20) "rows_processed",
executions, loads,
(decode(rows_processed,0,1,1))*buffer_gets/ decode(rows_processed,0,1,rows_processed) avg_cost,
sql_text
from v$sqlarea
Where decode(rows_processed,0,1,1) * buffer_gets/
decode(rows_processed,0,1,rows_processed)>10000
order by 5 desc;
Top 10 等待事件
select EVENT, TOTAL_WAITS, TOTAL_TIMEOUTS, TIME_WAITED, AVERAGE_WAIT
from (
select *
from v$system_event
where event not like '%rdbms%'
and event not like '%message%'
and event not like 'SQL*Net%'
order by total_waits desc
)
WHERE ROWNUM <= 10;
数据库长事务（执行超过6S）
select s.username, q.sql_text, s.elapsed_seconds, opname
from v$session_longops s, v$sqlarea q
where s.sql_hash_value = q.hash_value
order by s.ELAPSED_SECONDS desc;
产生大量物理读的进程
select st.sid, st.value, sn.name, s.username
from v$sesstat st, v$statname sn, v$session s
where st.sid = s.sid
AND st.statistic# = sn.statistic#
and st.value > 100000
and s.username is not null
and sn.name like '%physical read%'
order by 2 desc;
产生归档日志过快的进程
select sysdate,
se.username,
se.sid,
se.serial#,
se.SQL_HASH_VALUE,
se.status,
se.machine,
se.osuser,
round(st.value / 1024 / 1024) redosize,
sa.sql_text
from v$session se, v$sesstat st, v$sqlarea sa
where se.sid = st.sid
and st.STATISTIC# =
(select STATISTIC# from v$statname where NAME = 'redo size')
and se.username is not null
and st.value > 10 * 1024 * 1024
and se.SQL_ADDRESS = sa.ADDRESS
and se.SQL_HASH_VALUE = sa.HASH_VALUE
order by redosize;
等待事件对应的SQL语句
select b.sql_text text,
a.sid sid,
a.serial# serial#,
a.username "user",
a.machine machine
from v$session a, v$sqltext b, v$session_wait c
where a.sid = c.sid
and b.address = a.sql_address
and b.hash_value = a.sql_hash_value
order by a.sid, a.serial#, b.piece;
占用大量temp表空间的session和sql监控
select su.extents, su.segtype, su.sqlhash, se.sid, se.serial#, se.last_call_et, se.username, se.machine ,sa.sql_text
from v$sort_usage su, v$session se ,v$sqlarea sa
where su.session_addr=se.saddr
and se.SQL_ADDRESS = sa.ADDRESS
and se.SQL_HASH_VALUE = sa.HASH_VALUE
and su.extents>10;
回滚段争用情况
select name ,waits ,gets ,waits/gets "Ratio" from v$rollstat a ,v$rollname b where a.usn=b.usn;
Session等待事件
select sid, event, p1, p1text from v$session_wait s;
Listing Memory Used By All Sessions
select se.sid, n.name, max(se.value) maxmem
from v$sesstat se, v$statname n
where n.statistic# = se.statistic#
and n.name in ('session pga memory',
'session pga memory max',
'session uga memory',
'session uga memory max')
group by n.name, se.sid
order by 1,3;
评论区