表：
　　select * from cat;
　　select * from tab;
　　select table_name from user_tables;
視圖：
　　select text from user_views where view_name=upper('&view_name');
索引：
　　select index_name,table_owner,table_name,tablespace_name,status from user_indexes order by table_name;
觸發(fā)器：
　　select trigger_name,trigger_type,table_owner,table_name,status from user_triggers;
快照：
　　select owner,name,master,table_name,last_refresh,next from user_snapshots order by owner,next;
同義詞：
　　select * from syn;
序列：
　　select * from seq;
數(shù)據(jù)庫鏈路：
　　select * from user_db_links;
約束限制：
　　select TABLE_NAME,CONSTRAINT_NAME,SEARCH_CONDITION,STATUS
    from user_constraints WHERE TABLE_name=upper('&TABLE_Name');
本用戶讀取其他用戶對象的權限：
　　select * from user_tab_privs;
本用戶所擁有的系統(tǒng)權限：
　　select * from user_sys_privs;
用戶：
　　select * from all_users order by user_id;
表空間剩余自由空間情況：
　　select tablespace_name,sum(bytes) 總字節(jié)數(shù),max(bytes),count(*) from dba_free_space group by tablespace_name；
數(shù)據(jù)字典：
　　select table_name from dict order by table_name;
鎖及資源信息：
　　select * from v$lock;不包括DDL鎖
數(shù)據(jù)庫字符集：
　　select name,value$ from props$ where name='NLS_CHARACTERSET';
inin.ora參數(shù)：
　　select name,value from v$parameter order by name;
SQL 共享池：
　　select sql _text from v$sqlarea;
數(shù)據(jù)庫：
　　select * from v$database
控制文件：
　　select * from V$controlfile;
重做日志文件信息：
　　select * from V$logfile;
來自控制文件中的日志文件信息：
　　select * from V$log;
來自控制文件中的數(shù)據(jù)文件信息：
　　select * from V$datafile;
NLS參數(shù)當前值：
　　select * from V$nls_parameters;
ORACLE 版本信息：
　　select * from v$version;
描述后臺進程：
　　select * from v$bgprocess;
查看版本信息：
　　select * from product_component_version;
Oracle -常用監(jiān)控SQL
1.監(jiān)控事例的等待：
         select event,sum(decode(wait_time,0,0,1)) prev, sum(decode(wait_time,0,1,0)) curr,count(*)
from v$session_wait
group by event order by 4;
2.回滾段的爭用情況：
select name,waits,gets,waits/gets ratio from v$rollstat a,v$rollname b where a.usn=b.usn;
3.監(jiān)控表空間的I/O比例：
select df.tablespace_name name,df.file_name "file",f.phyrds pyr,f.phyblkrd pbr,f.phywrts pyw,
f.phyblkwrt pbw
from v$filestat f,dba_data_files df
where f.file#=df.file_id
4.監(jiān)空文件系統(tǒng)的I/O比例：
select substr(a.file#,1,2) "#",substr(a.name,1,30) "name",a.status,a.bytes,
b.phyrds,b.phywrts
from v$datafile a,v$filestat b
where a.file#=b.file#
5.在某個用戶下找所有的索引：
select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name
from user_ind_columns, user_indexes
where user_ind_columns.index_name = user_indexes.index_name
and user_ind_columns.table_name = user_indexes.table_name
order by user_indexes.table_type, user_indexes.table_name,
user_indexes.index_name, column_position;
6. 監(jiān)控 SGA 的命中率
select a.value + b.value "logical_reads", c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where a.statistic# = 38 and b.statistic# = 39
and c.statistic# = 40;
7. 監(jiān)控 SGA 中字典緩沖區(qū)的命中率
select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio",
(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"
from v$rowcache
where gets+getmisses <>0
group by parameter, gets, getmisses;
8. 監(jiān)控 SGA 中共享緩存區(qū)的命中率,，應該小于1%
select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
sum(reloads)/sum(pins) *100 libcache
from v$librarycache;
select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent"
from v$librarycache;
9. 顯示所有數(shù)據(jù)庫對象的類別和大小
select count(name) num_instances ,type ,sum(source_size) source_size ,
sum(parsed_size) parsed_size ,sum(code_size) code_size ,sum(error_size) error_size,
sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_required
from dba_object_size
group by type order by 2;
10. 監(jiān)控 SGA 中重做日志緩存區(qū)的命中率,，應該小于1%
SELECT name, gets, misses, immediate_gets, immediate_misses,
Decode(gets,0,0,misses/gets*100) ratio1,
Decode(immediate_gets+immediate_misses,0,0,
immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
FROM v$latch WHERE name IN ('redo allocation', 'redo copy');
11. 監(jiān)控內(nèi)存和硬盤的排序比率,，最好使它小于 .10,，增加 sort_area_size
SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');
12. 監(jiān)控當前數(shù)據(jù)庫誰在運行什么SQL 語句
SELECT osuser, username, sql _text from v$session a, v$sqltext b
where a.sql _address =b.address order by address, piece;
13. 監(jiān)控字典緩沖區(qū)
SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE" FROM V$LIBRARYCACHE;
SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;
SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE;
后者除以前者,此比率小于1%,接近0%為好,。
SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES"
FROM V$ROWCACHE
14. 找ORACLE 字符集
select * from sys.props$ where name='NLS_CHARACTERSET';
15. 監(jiān)控 MTS
select busy/(busy+idle) "shared servers busy" from v$dispatcher;
此值大于0.5時,，參數(shù)需加大
select sum(wait)/sum(totalq) "dispatcher waits" from v$queue where type='dispatcher';
select count(*) from v$dispatcher;
select servers_highwater from v$mts;
servers_highwater接近mts_max_servers時，參數(shù)需加大
16. 碎片程度
select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name
having count(tablespace_name)>10;
alter tablespace name coalesce;
alter table name deallocate unused;
create or replace view ts_blocks_v as
select tablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_space
union all
select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents;
select * from ts_blocks_v;
select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space
group by tablespace_name;
查看碎片程度高的表
SELECT segment_name table_name , COUNT(*) extents
FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name
HAVING COUNT(*) = (SELECT MAX( COUNT(*) ) FROM dba_segments GROUP BY segment_name);
17. 表,、索引的存儲情況檢查
select segment_name,sum(bytes),count(*) ext_quan from dba_extents where
tablespace_name='&tablespace_name' and segment_type='TABLE' group by tablespace_name,segment_name;
select segment_name,count(*) from dba_extents where segment_type='INDEX' and owner='&owner'
group by segment_name;
18,、找使用CPU多的用戶session
12是cpu used by this session
select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value
from v$session a,v$process b,v$sesstat c
where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc;
20.監(jiān)控log_buffer的使用情況：(值最好小于1%，否則增加log_buffer 的大小)
select rbar.name,rbar.value,re.name,re.value,(rbar.value*100)/re.value||'%' "radio"
from v$sysstat rbar,v$sysstat re
where rbar.name='redo buffer allocation retries'
and re.name='redo entries';
19,、查看運行過的SQL 語句：
SELECT SQL _TEXT
FROM V$SQL
Oracle 一些常用的SQL
查詢表結(jié)構
select substr(table_name,1,20) tabname,
substr(column_name,1,20)column_name,
rtrim(data_type)||'('||data_length||')' from system.dba_tab_columns
where owner='username'
表空間使用狀態(tài)
select a.file_id "FileNo",a.tablespace_name "Tablespace_name",
round(a.bytes/1024/1024,4) "Total MB",
round((a.bytes-sum(nvl(b.bytes,0)))/1024/1024,4) "Used MB",
round(sum(nvl(b.bytes,0))/1024/1024,4) "Free MB",
round(sum(nvl(b.bytes,0))/a.bytes*100,4)  "%Free"
from dba_data_files a, dba_free_space b
where a.file_id=b.file_id(+)
group by a.tablespace_name,
a.file_id,a.bytes order by a.tablespace_name
查詢某個模式下面數(shù)據(jù)不為空的表
declare
Cursor c is select TNAME from tab;
vCount Number;
table_nm Varchar2(100);
sq varchar2(300);
begin
for r in c loop
table_nm:=r.TNAME;
sq:='select  count(*)  from '|| table_nm;
execute immediate sq into vCount;
if vCount>0 then
dbms_output.put_line(r.tname);
end if;
end loop;
end;
客戶端主機信息
SELECT
SYS_CONTEXT('USERENV','TERMINAL') TERMINAL,
SYS_CONTEXT('USERENV','HOST') HOST,
SYS_CONTEXT('USERENV','OS_USER') OS_USER,
SYS_CONTEXT('USERENV','IP _ADDRESS') IP _ADDRESS
FROM DUAL
查看回滾段名稱及大小
COLUMN roll_name   FORMAT a13          HEADING 'Rollback Name'
COLUMN tablespace  FORMAT a11          HEADING 'Tablspace'
COLUMN in_extents  FORMAT a20          HEADING 'Init/Next Extents'
COLUMN m_extents&

nbsp;  FORMAT a10          HEADING 'Min/Max Extents'
COLUMN status      FORMAT a8           HEADING 'Status'
COLUMN wraps       FORMAT 999          HEADING 'Wraps'
COLUMN shrinks     FORMAT 999          HEADING 'Shrinks'
COLUMN opt         FORMAT 999,999,999  HEADING 'Opt. Size'
COLUMN bytes       FORMAT 999,999,999  HEADING 'Bytes'
COLUMN extents     FORMAT 999          HEADING 'Extents'
SELECT
    a.owner || '.' || a.segment_name          roll_name
  , a.tablespace_name                         tablespace
  , TO_CHAR(a.initial_extent) || ' / ' ||
    TO_CHAR(a.next_extent)                    in_extents
  , TO_CHAR(a.min_extents)    || ' / ' ||
    TO_CHAR(a.max_extents)                    m_extents
  , a.status                                  status
  , b.bytes                                   bytes
  , b.extents                                 extents
  , d.shrinks                                 shrinks
  , d.wraps                                   wraps
  , d.optsize                                 opt
FROM
    dba_rollback_segs a
  , dba_segments b
  , v$rollname c
  , v$rollstat d
WHERE
       a.segment_name = b.segment_name
  AND  a.segment_name = c.name (+)
  AND  c.usn          = d.usn (+)
ORDER BY a.segment_name;

本篇文章來源于GIS動力站|www. 原文鏈接：http://www./article/db/2007/925/079251397I7DFC178AF6GJ4H8GIBF_2.html

本篇文章來源于GIS動力站|www. 原文鏈接：http://www./article/db/2007/925/079251397I7DFC178AF6GJ4H8GIBF.html