1. 控制文件的查询
set echo off
set feedback off
set linesize 512
column name format a60
SELECT NAME, DECODE (STATUS, '', 'VALID', 'INVALID') "DEC"
FROM V$CONTROLFILE
ORDER BY NAME;
2. prompt Top Sessions by CPU consumption
set echo off
set feedback off
set linesize 512
column sid format 999 heading "SID"
column username format a20 heading "User Name"
column command format a20 heading "Command"
column osuser format a20 heading "OS User"
column process format a20 heading "OS Process"
column machine format a20 heading "Machine"
column value format 99,999 heading "CPU Time"
select
s.sid sid,
s.username username,
UPPER(DECODE(command,
1,'Create Table',2,'Insert',3,'Select',
4,'Create Cluster',5,'Alter Cluster',6,'Update',
7,'Delete', 8,'Drop Cluster', 9,'Create Index',
10,'Drop Index', 11,'Alter Index', 12,'Drop Table',
13,'Create Sequencfe', 14,'Alter Sequence', 15,'Alter Table',
16,'Drop Sequence', 17,'Grant', 18,'Revoke',
19,'Create Synonym', 20,'Drop Synonym', 21,'Create View',
22,'Drop View', 23,'Validate Index', 24,'Create Procedure',
25,'Alter Procedure', 26,'Lock Table', 27,'No Operation',
28,'Rename', 29,'Comment', 30,'Audit',
31,'NoAudit', 32,'Create Database Link', 33,'Drop Database Link',
34,'Create Database', 35,'Alter Database', 36,'Create Rollback Segment',
37,'Alter Rollback Segment', 38,'Drop Rollback Segment', 39,'Create Tablespace',
40,'Alter Tablespace', 41,'Drop Tablespace', 42,'Alter Sessions',
43,'Alter User', 44,'Commit', 45,'Rollback',
46,'Savepoint', 47,'PL/SQL Execute', 48,'Set Transaction',
49,'Alter System Switch Log', 50,'Explain Plan', 51,'Create User',
52,'Create Role', 53,'Drop User', 54,'Drop Role',
55,'Set Role', 56,'Create Schema', 57,'Create Control File',
58,'Alter Tracing', 59,'Create Trigger', 60,'Alter Trigger',
61,'Drop Trigger', 62,'Analyze Table', 63,'Analyze Index',
64,'Analyze Cluster', 65,'Create Profile', 66,'Drop Profile',
67,'Alter Profile', 68,'Drop Procedure', 69,'Drop Procedure',
70,'Alter Resource Cost', 71,'Create Snapshot Log', 72,'Alter Snapshot Log',
73,'Drop Snapshot Log', 74,'Create Snapshot', 75,'Alter Snapshot',
76,'Drop Snapshot', 79,'Alter Role', 85,'Truncate Table',
86,'Truncate Cluster', 88,'Alter View', 91,'Create Function',
92,'Alter Function', 93,'Drop Function', 94,'Create Package',
95,'Alter Package', 96,'Drop Package', 97,'Create Package Body',
98,'Alter Package Body', 99,'Drop Package Body')) command,
s.osuser osuser,
s.machine machine,
s.process process,
t.value value
from
v$session s,
v$sesstat t,
v$statname n
where
s.sid = t.sid
and
t.statistic# = n.statistic#
and
n.name = 'CPU used by this session'
and
t.value > 0
and
audsid > 0
order by
t.value desc;
3. prompt Database Basic Info
set echo off
set feedback off
set linesize 512
SELECT NAME, CREATED, LOG_MODE
FROM V$DATABASE;
4. prompt All Directories in Database
column directory_path format a80
SELECT OWNER, DIRECTORY_NAME, DIRECTORY_PATH
FROM DBA_DIRECTORIES
ORDER BY OWNER, DIRECTORY_NAME;
5.prompt Database INIT.ORA Info
set echo off
set feedback off
set linesize 512
column value format a80
SELECT NAME,
DECODE (TYPE, 1, 'Boolean', 2, 'String', 3, 'INTEGER', 4, 'FILE', 5, 'RESERVED', 6, 'BIG INTEGER') TYPE,
VALUE, DESCRIPTION, ISSES_MODIFIABLE, ISSYS_MODIFIABLE, ISDEFAULT,
ISMODIFIED, ISADJUSTED
FROM V$PARAMETER
ORDER BY NAME;
6. prompt Top Tablespaces by IO consumption
set echo off
set feedback off
set linesize 512
column file_name format a60 heading "Data-File Name"
column ts_name format a32 heading "Tablespace Name"
column stat_reads format 999,999,999,999 heading "Physical Reads"
column stat_writes format 999,999,999,999 heading "Physical Writes"
column stat_breads format 999,999,999,999 heading "Physical Blk-Reads"
column stat_bwrites format 999,999,999,999 heading "Physical Blk-Writes"
break on ts_name
select
t.name ts_name,
f.name file_name,
s.phyrds stat_reads,
s.phyblkrd stat_breads,
s.phywrts stat_writes,
s.phyblkwrt stat_bwrites
from v$tablespace t, v$datafile f, v$filestat s
where t.ts# = f.ts# and f.file# = s.file#
order by s.phyrds desc, s.phywrts desc;
7. prompt Top IO Waits in Database
column event format a30
column segment_type format a10
column segment_name format a20
select event,segment_type,segment_name,file_id,block_id,blocks
from dba_extents, v$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;
8. prompt Database Locks Being Held
column sid format 999 heading "SID"
column username format a10 heading "User Name"
column machine format a20 heading "Machine Name"
column object_name format a20 heading "Object Name"
column type format a4 heading "Type"
column lmode format a20 heading "Current Lock Mode"
column request format 9999999 heading "Request Mode"
column block format 9999999 heading "Lock Blocking"
select
s.sid sid,
s.username username,
s.machine machine,
l.type type,
o.object_name object_name,
DECODE(l.lmode,
0,'None',
1,'Null',
2,'Row Share',
3,'Row Exlusive',
4,'Share',
5,'Sh/Row Exlusive',
6,'Exclusive') lmode,
DECODE(l.request,
0,'None',
1,'Null',
2,'Row Share',
3,'Row Exlusive',
4,'Share',
5,'Sh/Row Exlusive',
6,'Exclusive') request,
l.block block
from v$lock l, v$session s, dba_objects o
where l.sid = s.sid and username != 'SYSTEM' and o.object_id(+) = l.id1;
SELECT DISTINCT PROFILE FROM DBA_PROFILES;
10. prompt Database Rollback Segments
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;
11. prompt Rollback Segment Activity in Database
SELECT A.NAME, B.XACTS,
C.SID, C.SERIAL#,
C.USERNAME, D.SQL_TEXT
FROM V$ROLLNAME A, V$ROLLSTAT B, V$SESSION C, V$SQLTEXT D, V$TRANSACTION E
WHERE A.USN = B.USN
AND B.USN = E.XIDUSN
AND C.TADDR = E.ADDR
AND C.SQL_ADDRESS = D.ADDRESS
AND C.SQL_HASH_VALUE = D.HASH_VALUE
ORDER BY A.NAME, C.SID, D.PIECE;
12.prompt Alter All Rollback Segments Offline（有点问题）
set echo off
set heading off
set feedback off
set term off
spool rbs_off.tmp
select 'set echo on' from dual;
select 'set feedback on' from dual;
select 'alter rollback segment '||segment_name||' offline;'
from dba_rollback_segs
where segment_name != 'SYSTEM'
and status = 'ONLINE';
spool off
set term on
@rbs_off.tmp
set echo off
set heading off
set feedback off
set term off
spool rbs_on.tmp
select 'set echo on' from dual;
select 'set feedback on' from dual;
select 'alter rollback segment '||segment_name||' online;'
from dba_rollback_segs
where segment_name != 'SYSTEM'
and status = 'OFFLINE';
spool off
set term on
@rbs_on.tmp
14. prompt All Redo Logs in Database
column member format a60
SELECT A.GROUP#, B.MEMBER, A.THREAD#, A.SEQUENCE#, A.BYTES, A.MEMBERS,
A.ARCHIVED, A.STATUS, A.FIRST_CHANGE#, A.FIRST_TIME
FROM V$LOG A, V$LOGFILE B
WHERE A.GROUP# = B.GROUP#
ORDER BY A.GROUP#;
15.prompt All Roles in Database
SELECT ROLE, PASSWORD_REQUIRED FROM DBA_ROLES;
16. prompt All Granted Roles in Database
break on GRANTED_ROLE skip 1
SELECT GRANTED_ROLE, GRANTEE, ADMIN_OPTION, DEFAULT_ROLE
FROM DBA_ROLE_PRIVS
WHERE GRANTEE IN (SELECT USERNAME FROM SYS.DBA_USERS)
ORDER BY GRANTED_ROLE, ADMIN_OPTION;
17. prompt All Roles Granted to Roles in Database
break on GRANTED_ROLE skip 1
SELECT GRANTED_ROLE, GRANTEE, ADMIN_OPTION, DEFAULT_ROLE
FROM SYS.DBA_ROLE_PRIVS
WHERE GRANTEE IN (SELECT ROLE FROM DBA_ROLES)
ORDER BY GRANTED_ROLE, ADMIN_OPTION;
18.prompt All Granted User Roles in Database
break on GRANTED_ROLE skip 1
SELECT GRANTED_ROLE, GRANTEE, ADMIN_OPTION, DEFAULT_ROLE
FROM SYS.DBA_ROLE_PRIVS
WHERE GRANTEE IN (SELECT USERNAME FROM SYS.DBA_USERS)
AND GRANTED_ROLE NOT IN (
'AQ_ADMINISTRATOR_ROLE',
'AQ_USER_ROLE',
'CONNECT',
'CTXAPP',
'DBA',
'DELETE_CATALOG_ROLE',
'EXECUTE_CATALOG_ROLE',
'EXP_FULL_DATABASE',
'GLOBAL_AQ_USER_ROLE',
'HS_ADMIN_ROLE',
'IMP_FULL_DATABASE',
'JAVADEBUGPRIV',
'JAVAIDPRIV',
'JAVASYSPRIV',
'JAVAUSERPRIV',
'JAVA_ADMIN',
'JAVA_DEPLOY',
'OEM_MONITOR',
'OLAP_DBA',
'RECOVERY_CATALOG_OWNER',
'RESOURCE',
'SELECT_CATALOG_ROLE',
'SNMPAGENT',
'WKADMIN',
'WKUSER'
)
ORDER BY GRANTED_ROLE, ADMIN_OPTION;
19.prompt Session Events
column event format a32
SELECT B.USERNAME, B.SID, B.SERIAL#, A.EVENT, A.TOTAL_WAITS,
A.TOTAL_TIMEOUTS, A.TIME_WAITED, A.AVERAGE_WAIT
FROM V$SESSION_EVENT A, V$SESSION B
WHERE A.SID = B.SID ORDER BY 1;
20. prompt SGA Memory Map (overall)
column dummy noprint
column area format a20 heading 'Main SGA Areas'
column name format a20
column pool format a20
column bytes format 999,999,999,999
column sum(bytes) format 999,999,999,999
break on report
compute sum of sum(bytes) on report
SELECT 1 dummy, 'DB Buffer Cache' area, name, sum(bytes) FROM v$sgastat
WHERE pool is null and name = 'db_block_buffers' group by name
union all
SELECT 2, 'Shared Pool', pool, sum(bytes) FROM v$sgastat WHERE pool = 'shared pool' group by pool
union all
SELECT 3, 'Large Pool', pool, sum(bytes) FROM v$sgastat WHERE pool = 'large pool' group by pool
union all
SELECT 4, 'Java Pool', pool, sum(bytes) FROM v$sgastat WHERE pool = 'java pool' group by pool
union all
SELECT 5, 'Redo Log Buffer', name, sum(bytes) FROM v$sgastat WHERE pool is null and name = 'log_buffer' group by name
union all
SELECT 6, 'Fixed SGA', name, sum(bytes) FROM v$sgastat WHERE pool is null and name = 'fixed_sga' group by name ORDER BY 4 desc;
21. prompt SGA Memory Map (shared pool)
column area format a20 heading 'Shared Pool Areas'
SELECT 'Shared Pool' area, name, sum(bytes) 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', sum(bytes) 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;
22. prompt System Events
column event format a32
column total_waits format 999,999,999,999 heading "Total Waits"
column total_timeouts format 999,999,999,999 heading "Total Timeouts"
column time_waited format 999,999,999,999 heading "Time Waited"
column average_wait format 999,999,999,999 heading "Average Wait"
SELECT EVENT, TOTAL_WAITS, TOTAL_TIMEOUTS, TIME_WAITED, AVERAGE_WAIT
FROM V$SYSTEM_EVENT
ORDER BY 4 DESC;
23.prompt Tablespace Basic Information
set echo off
set feedback off
set verify off
set linesize 512
set term off
COLUMN block_size NOPRINT new_value block_size
SELECT value block_size from v$parameter
where name='db_block_size';
col Tablespace_name Heading 'Tablespace'
col Megs_Alloc Heading 'Megs Alloc'
col Megs_Free Heading 'Megs Free'
col Megs_Used Heading 'Megs Used'
col Pct_Free Heading 'Pct Free'
col Pct_Used Heading 'Pct Used'
col Init_Ext Heading 'Init Ext'
col Next_Ext Heading 'Next Ext'
col Min_Ext Heading 'Min Ext'
col Max_Ext Heading 'Max Ext'
col Num_Segs Heading 'Num Segs'
col Num_Exts Heading 'Num Exts'
select c.tablespace_name,
round(a.bytes/1048576) Megs_Alloc,
round(b.bytes/1048576) Megs_Free,
round((a.bytes-b.bytes)/1048576) Megs_Used,
round(b.bytes/a.bytes * 100) Pct_Free,
round((a.bytes-b.bytes)/a.bytes * 100) Pct_Used,
round(c.initial_extent/1048576) Init_Ext,
round(c.next_extent/1048576) Next_Ext,
round(a.minbytes/1048576) Min_Ext,
round(a.maxbytes/1048576) Max_Ext,
nvl(d.num_segs,0) Num_segs,
nvl(d.num_exts,0) Num_Exts
from (select tablespace_name,
sum(a.bytes) bytes,
min(a.bytes) minbytes,
max(a.bytes) maxbytes
from sys.dba_data_files a
group by tablespace_name) a,
(select a.tablespace_name,
nvl(sum(b.bytes),0) bytes
from sys.dba_data_files a,
sys.dba_free_space b
where a.tablespace_name = b.tablespace_name (+)
and a.file_id = b.file_id (+)
group by a.tablespace_name) b,
sys.dba_tablespaces c,
(select tablespace_name,
count(distinct segment_name) num_segs,
count(extent_id) num_exts
from sys.dba_extents
group by tablespace_name) d
where a.tablespace_name = b.tablespace_name(+)
and a.tablespace_name = c.tablespace_name
and a.tablespace_name = d.tablespace_name(+)
order by c.tablespace_name;
24. prompt Alter All Tablespaces Coalesce
set heading off
set feedback off
set term off
spool tspc_coal.tmp
select 'set echo on' from dual;
select 'set feedback on' from dual;
select 'alter tablespace '||tablespace_name||' coalesce;'
from dba_tablespaces
where contents not in ('TEMPORARY','UNDO');
spool off
set term on
@tspc_coal.tmp
25. prompt Tablspace Free Space Breakdown
col tablespace_name Heading 'Tablespace Name'
col over_100m Heading '# > 100M'
col over_25m Heading '# > 25M'
col over_10m Heading '# > 10M'
col over_5m Heading '# > 5M'
col over_1m Heading '# > 1M'
col over_512k Heading '# > 512K'
col under_512k Heading '# < 521k' ;
select tablespace_name,
sum(decode(sign(bytes-1024*1024*100),-1,0,1)) over_100m,
(sum(decode(sign(bytes-1024*1024*025),-1,0,1)) -
sum(decode(sign(bytes-1024*1024*100),-1,0,1))) over_25m,
(sum(decode(sign(bytes-1024*1024*010),-1,0,1)) -
sum(decode(sign(bytes-1024*1024*25),-1,0,1))) over_10m,
(sum(decode(sign(bytes-1024*1024*005),-1,0,1)) -
sum(decode(sign(bytes-1024*1024*10),-1,0,1))) over_5m,
(sum(decode(sign(bytes-1024*1024*001),-1,0,1)) -
sum(decode(sign(bytes-1024*1024*5),-1,0,1))) over_1m,
(sum(decode(sign(bytes-1024*512*001),-1,0,1)) -
sum(decode(sign(bytes-1024*1024*1),-1,0,1))) over_512k,
sum(decode(sign(bytes-1024*512*001),-1,1,0)) under_512k
from dba_free_space
group by tablespace_name;
26.prompt Temporary Tablespace User Counts
prompt Default Tablespace User Counts
set echo off
set feedback off
set linesize 512
column count(username) heading '# USERS'
select default_tablespace, count(username)
from dba_users
where username not in ('PUBLIC','_NEXT_USER')
group by default_tablespace;
select temporary_tablespace, count(username)
from dba_users
where username not in ('PUBLIC','_NEXT_USER')
group by temporary_tablespace;
27.prompt Alter All Tablespaces Offline
set echo off
set heading off
set feedback off
set term off
spool tspc_off.tmp
select 'set echo on' from dual;
select 'set feedback on' from dual;
select 'alter tablespace '||tablespace_name||' offline;'
from dba_tablespaces
where tablespace_name not in ('SYSTEM','OUTLN')
and status = 'ONLINE';
spool off
set term on
@tspc_off.tmp
28.prompt Alter All Tablespaces Online
set echo off
set heading off
set feedback off
set term off
spool tspc_on.tmp
select 'set echo on' from dual;
select 'set feedback on' from dual;
select 'alter tablespace '||tablespace_name||' online;'
from dba_tablespaces
where tablespace_name not in ('SYSTEM','OUTLN')
and status = 'OFFLINE';
spool off
set term on
@tspc_on.tmp
29. prompt All Users in Database
set echo off
set feedback off
set linesize 512
column count(b.object_id) heading '# OBJECTS'
select a.username,
a.account_status,
a.default_tablespace,
a.temporary_tablespace,
count(b.object_id)
from dba_users a, dba_objects b
where a.username = b.owner(+)
group by a.username,
a.account_status,
a.default_tablespace,
a.temporary_tablespace;
30. prompt Database Instance and Version Info
set echo off
set feedback off
set heading off
select 'Database Instance = '||name from v$database;
select 'Database Version = '||banner from v$version where rownum = 1;
select 'Database Block Size = '||value from v$parameter where name = 'db_block_size';
31. prompt Tablespace Object Breakdown Information
column count(*) heading '# OBJECTS'
column sum(bytes) heading 'BYTES'
select tablespace_name, owner, segment_type, count(*), sum(bytes) from sys.dba_extents
group by tablespace_name, owner, segment_type
order by tablespace_name, owner, segment_type;
32.prompt Current Date and Time
select '*** Time = '||to_char(sysdate,'DD-MON-YY HH:MI:SS')|| ' ***' from dual;
33.prompt Database Sessions Active
column machine format a20
column osuser format a20
column module format a20
select machine, process, osuser, username,
schemaname, status, lockwait, sid,
serial#, module, action
from v$session
where username is not null and osuser is not null order by machine, osuser, username, schemaname, status, module;
34.prompt User Connected As
select '*** User = '||user||' ***' from dual;