oracle常用視圖彙總

Oracle常用資料字典表

檢視目前使用者的預設表空間

　　SQL>selectusername,default_tablespacefromuser_users;

　　檢視目前使用者的角色

　　SQL>select*fromuser_role_privs;

　　檢視目前使用者的系統權限和表級權限

　　SQL>select*fromuser_sys_privs;

　　SQL>select*fromuser_tab_privs;

　　檢視使用者下所有的表

　　SQL>select*fromuser_tables;

檢視使用者下所有的表的列屬性

SQL>select*fromUSER_TAB_COLUMNSwheretable_name=:table_Name;

　　顯示使用者資訊(所屬表空間)

　　selectdefault_tablespace,temporary_tablespace

　　fromdba_userswhereusername='GAME';

　　1、使用者

　　檢視目前使用者的預設表空間

　　顯示目前會話所具有的權限

　　SQL>select*fromsession_privs;

　　顯示指定使用者所具有的系統權限

　　SQL>select*fromdba_sys_privswheregrantee='GAME';

　　顯示特權使用者

　　select*fromv$pwfile_users;

　　顯示使用者的PROFILE

　　selectprofilefromdba_userswhereusername='GAME';

　　

　　2、表

　　檢視名稱包含log字元的表

　　SQL>selectobject_name,object_idfromuser_objects

　　whereinstr(object_name,'LOG')>0;

　　檢視某表的建立時間

　　SQL>selectobject_name,createdfromuser_objectswhereobject_name=upper('&table_name');

　　檢視某表的大小

　　SQL>selectsum(bytes)/(1024*1024)as"size(M)"fromuser_segments

　　wheresegment_name=upper('&table_name');

　　檢視放在Oracle的記憶體區裡的表

　　SQL>selecttable_name,cachefromuser_tableswhereinstr(cache,'Y')>0;

　　3、索引

　　檢視索引個數和類别

　　SQL>selectindex_name,index_type,table_namefromuser_indexesorderbytable_name;

　　檢視索引被索引的字段

　　SQL>select*fromuser_ind_columnswhereindex_name=upper('&index_name');

　　檢視索引的大小

　　wheresegment_name=upper('&index_name');

　　4、序列号

　　檢視序列号，last_number是目前值

　　SQL>select*fromuser_sequences;

　　5、視圖

　　檢視視圖的名稱

　　SQL>selectview_namefromuser_views;

　　檢視建立視圖的select語句

　　SQL>setview_name,text_lengthfromuser_views;

　　SQL>setlong2000;說明：可以根據視圖的text_length值設定setlong的大小

　　SQL>selecttextfromuser_viewswhereview_name=upper('&view_name');

　　6、同義詞

　　檢視同義詞的名稱

　　SQL>select*fromuser_synonyms;

　　7、限制條件

　　檢視某表的限制條件

　　SQL>selectconstraint_name,constraint_type,search_condition,r_constraint_name

　　fromuser_constraintswheretable_name=upper('&table_name');

　　SQL>selectc.constraint_name,c.constraint_type,cc.column_name

　　fromuser_constraintsc,user_cons_columnscc

　　wherec.owner=upper('&table_owner')andc.table_name=upper('&table_name')

　　andc.owner=cc.ownerandc.constraint_name=cc.constraint_name

　　orderbycc.position;

　　8、存儲函數和過程

　　檢視函數和過程的狀态

　　SQL>selectobject_name,statusfromuser_objectswhereobject_type='FUNCTION';

　　SQL>selectobject_name,statusfromuser_objectswhereobject_type='PROCEDURE';

　　檢視函數和過程的源代碼

　　SQL>selecttextfromall_sourcewhereowner=userandname=upper('&plsql_name');

常用的資料字典：

　　dba_data_files:通常用來查詢關于資料庫檔案的資訊

　　dba_db_links:包括資料庫中的所有資料庫鍊路，也就是databaselinks。

　　dba_extents:資料庫中所有分區的資訊

　　dba_free_space:所有表空間中的自由分區

　　dba_indexs:關于資料庫中所有索引的描述

　　dba_ind_columns:在所有表及聚集上壓縮索引的列

　　dba_objects:資料庫中所有的對象

　　dba_rollback_segs:復原段的描述

　　dba_segments:所有資料庫段分段的存儲空間

　　dba_synonyms:關于同義詞的資訊查詢

　　dba_tables:資料庫中所有資料表的描述

　　dba_tabespaces:關于表空間的資訊

　　dba_tab_columns:所有表描述、視圖以及聚集的列

　　dba_tab_grants/privs:對象所授予的權限

　　dba_ts_quotas:所有使用者表空間限額

　　dba_users:關于資料的所有使用者的資訊

　　dba_views:資料庫中所有視圖的文本

常用的動态性能視圖：

　　v$datafile：資料庫使用的資料檔案資訊

　　v$librarycache：共享池中SQL語句的管理資訊

　　v$lock：通過通路資料庫會話，設定對象鎖的所有資訊

　　v$log：從控制檔案中提取有關重做日志組的資訊

　　v$logfile有關執行個體重置日志組檔案名及其位置的資訊

　　v$parameter：初始化參數檔案中所有項的值

　　v$process：目前程序的資訊

　　v$rollname：復原段資訊

　　v$rollstat：聯機復原段統計資訊

　　v$rowcache：記憶體中資料字典活動/性能資訊

　　v$session:有關會話的資訊

　　v$sesstat：在v$session中報告目前會話的統計資訊

　　v$sqlarea：共享池中使用目前光标的統計資訊，光标是一塊記憶體區域，有Oracle處理SQL語句時打開。

　　v$statname：在v$sesstat中報告各個統計的含義

　　v$sysstat：基于目前操作會話進行的系統統計

　　v$waitstat：出現一個以上會話通路資料庫的資料時的詳細情況。當有一個以上的會話通路同一資訊時，可出現等待情況。

　　總結了一下這些，徹底差別了視圖與資料字典，也不那麼容易混淆。嘿嘿!!!

常用SQL查詢：

1、檢視表空間的名稱及大小

selectt.tablespace_name,round(sum(bytes/(1024*1024)),0)ts_size

fromdba_tablespacest,dba_data_filesd

wheret.tablespace_name=d.tablespace_name

groupbyt.tablespace_name;

2、檢視表空間實體檔案的名稱及大小

selecttablespace_name,file_id,file_name,

round(bytes/(1024*1024),0)total_space

fromdba_data_files

orderbytablespace_name;

3、檢視復原段名稱及大小

selectsegment_name,tablespace_name,r.status,

(initial_extent/1024)InitialExtent,(next_extent/1024)NextExtent,

max_extents,v.curextCurExtent

Fromdba_rollback_segsr,v$rollstatv

Wherer.segment_id=v.usn(+)

orderbysegment_name;

4、檢視控制檔案

selectnamefromv$controlfile;

5、檢視日志檔案

selectmemberfromv$logfile;

6、檢視表空間的使用情況

selectsum(bytes)/(1024*1024)asfree_space,tablespace_name

fromdba_free_space

groupbytablespace_name;

SELECTA.TABLESPACE_NAME,A.BYTESTOTAL,B.BYTESUSED,C.BYTESFREE,

(B.BYTES*100)/A.BYTES"%USED",(C.BYTES*100)/A.BYTES"%FREE"

FROMSYS.SM$TS_AVAILA,SYS.SM$TS_USEDB,SYS.SM$TS_FREEC

WHEREA.TABLESPACE_NAME=B.TABLESPACE_NAMEANDA.TABLESPACE_NAME=C.TABLESPACE_NAME;

7、檢視資料庫庫對象

selectowner,object_type,status,count(*)count#fromall_objectsgroupbyowner,object_type,status;

8、檢視資料庫的版本　

SelectversionFROMProduct_component_version

WhereSUBSTR(PRODUCT,1,6)='Oracle';

9、檢視資料庫的建立日期和歸檔方式

SelectCreated,Log_Mode,Log_ModeFromV$Database;

10、捕捉運作很久的SQL

columnusernameformata12

columnopnameformata16

columnprogressformata8

selectusername,sid,opname,

round(sofar*100/totalwork,0)||'%'asprogress,

time_remaining,sql_text

fromv$session_longops,v$sql

wheretime_remaining<>0

andsql_address=address

andsql_hash_value=hash_value

11。檢視資料表的參數資訊

SELECTpartition_name,high_value,high_value_length,tablespace_name,

pct_free,pct_used,ini_trans,max_trans,initial_extent,

next_extent,min_extent,max_extent,pct_increase,FREELISTS,

freelist_groups,LOGGING,BUFFER_POOL,num_rows,blocks,

empty_blocks,avg_space,chain_cnt,avg_row_len,sample_size,

last_analyzed

FROMdba_tab_partitions

--WHEREtable_name=:tnameANDtable_owner=:towner

ORDERBYpartition_position

12.檢視還沒送出的事務

select*fromv$locked_object;

select*fromv$transaction;

13。查找object為哪些程序所用

select

p.spid,

s.sid,

s.serial#serial_num,

s.usernameuser_name,

a.typeobject_type,

s.osuseros_user_name,

a.owner,

a.objectobject_name,

decode(sign(48-command),

1,

to_char(command),'ActionCode#'||to_char(command))action,

p.programoracle_process,

s.terminalterminal,

s.programprogram,

s.statussession_status

fromv$sessions,v$accessa,v$processp

wheres.paddr=p.addrand

s.type='USER'and

a.sid=s.sidand

a.object='SUBSCRIBER_ATTR'

orderbys.username,s.osuser

14。復原段檢視

selectrownum,sys.dba_rollback_segs.segment_nameName,v$rollstat.extents

Extents,v$rollstat.rssizeSize_in_Bytes,v$rollstat.xactsXActs,

v$rollstat.getsGets,v$rollstat.waitsWaits,v$rollstat.writesWrites,

sys.dba_rollback_segs.statusstatusfromv$rollstat,sys.dba_rollback_segs,

v$rollnamewherev$rollname.name(+)=sys.dba_rollback_segs.segment_nameand

v$rollstat.usn(+)=v$rollname.usnorderbyrownum

15。耗資源的程序（topsession）

selects.schemanameschema_name,decode(sign(48-command),1,

to_char(command),'ActionCode#'||to_char(command))action,status

session_status,s.osuseros_user_name,s.sid,p.spid,s.serial#serial_num,

nvl(s.username,'[Oracleprocess]')user_name,s.terminalterminal,

s.programprogram,st.valuecriteria_valuefromv$sesstatst,v$sessions,v$processp

wherest.sid=s.sidandst.statistic#=to_number('38')and('ALL'='ALL'

ors.status='ALL')andp.addr=s.paddrorderbyst.valuedesc,p.spidasc,s.usernameasc,s.osuserasc

16。檢視鎖（lock）情況

select/*+RULE*/ls.osuseros_user_name,ls.usernameuser_name,

decode(ls.type,'RW','Rowwaitenqueuelock','TM','DMLenqueuelock','TX',

'Transactionenqueuelock','UL','Usersuppliedlock')lock_type,

o.object_nameobject,decode(ls.lmode,1,null,2,'RowShare',3,

'RowExclusive',4,'Share',5,'ShareRowExclusive',6,'Exclusive',null)

lock_mode,o.owner,ls.sid,ls.serial#serial_num,ls.id1,ls.id2

fromsys.dba_objectso,(selects.osuser,s.username,l.type,

l.lmode,s.sid,s.serial#,l.id1,l.id2fromv$sessions,

v$locklwheres.sid=l.sid)lswhereo.object_id=ls.id1ando.owner

<>'SYS'orderbyo.owner,o.object_name

17。檢視等待（wait）情況

SELECTv$waitstat.class,v$waitstat.countcount,SUM(v$sysstat.value)sum_value

FROMv$waitstat,v$sysstatWHEREv$sysstat.nameIN('dbblockgets',

'consistentgets')groupbyv$waitstat.class,v$waitstat.count

18。檢視sga情況

SELECTNAME,BYTESFROMSYS.V_$SGASTATORDERBYNAMEASC

19。檢視catchedobject

SELECTowner,name,db_link,namespace,

type,sharable_mem,loads,executions,

locks,pins,keptFROMv$db_object_cache

20。檢視V$SQLAREA

SELECTSQL_TEXT,SHARABLE_MEM,PERSISTENT_MEM,RUNTIME_MEM,SORTS,

VERSION_COUNT,LOADED_VERSIONS,OPEN_VERSIONS,USERS_OPENING,EXECUTIONS,

USERS_EXECUTING,LOADS,FIRST_LOAD_TIME,INVALIDATIONS,PARSE_CALLS,DISK_READS,

BUFFER_GETS,ROWS_PROCESSEDFROMV$SQLAREA

21。檢視object分類數量

selectdecode(o.type#,1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,

'SYNONYM',6,'SEQUENCE','OTHER')object_type,count(*)quantityfrom

sys.obj$owhereo.type#>1groupbydecode(o.type#,1,'INDEX',2,'TABLE',3

,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE','OTHER')unionselect

'COLUMN',count(*)fromsys.col$unionselect'DBLINK',count(*)from

22。按使用者檢視object種類

selectu.nameschema,sum(decode(o.type#,1,1,NULL))indexes,

sum(decode(o.type#,2,1,NULL))tables,sum(decode(o.type#,3,1,NULL))

clusters,sum(decode(o.type#,4,1,NULL))views,sum(decode(o.type#,5,1,

NULL))synonyms,sum(decode(o.type#,6,1,NULL))sequences,

sum(decode(o.type#,1,NULL,2,NULL,3,NULL,4,NULL,5,NULL,6,NULL,1))

othersfromsys.obj$o,sys.user$uwhereo.type#>=1andu.user#=

o.owner#andu.name<>'PUBLIC'groupbyu.nameorderby

sys.link$unionselect'CONSTRAINT',count(*)fromsys.con$

23。有關connection的相關資訊

1）檢視有哪些使用者連接配接

selects.osuseros_user_name,decode(sign(48-command),1,to_char(command),

'ActionCode#'||to_char(command))action,p.programoracle_process,

statussession_status,s.terminalterminal,s.programprogram,

s.usernameuser_name,s.fixed_table_sequenceactivity_meter,''query,

0memory,0max_memory,0cpu_usage,s.sid,s.serial#serial_num

fromv$sessions,v$processpwheres.paddr=p.addrands.type='USER'

2）根據v.sid檢視對應連接配接的資源占用等情況

selectn.name,

v.value,

n.class,

n.statistic#

fromv$statnamen,

v$sesstatv

wherev.sid=71and

v.statistic#=n.statistic#

orderbyn.class,n.statistic#

3）根據sid檢視對應連接配接正在運作的sql

select/*+PUSH_SUBQ*/

command_type,

sql_text,

sharable_mem,

persistent_mem,

runtime_mem,

sorts,

version_count,

loaded_versions,

open_versions,

users_opening,

executions,

users_executing,

loads,

first_load_time,

invalidations,

parse_calls,

disk_reads,

buffer_gets,

rows_processed,

sysdatestart_time,

sysdatefinish_time,

'>'||addresssql_address,

'N'status

fromv$sqlarea

whereaddress=(selectsql_addressfromv$sessionwheresid=71)

24．查詢表空間使用情況

selecta.tablespace_name"表空間名稱",

100-round((nvl(b.bytes_free,0)/a.bytes_alloc)*100,2)"占用率(%)",

round(a.bytes_alloc/1024/1024,2)"容量(M)",

round(nvl(b.bytes_free,0)/1024/1024,2)"空閑(M)",

round((a.bytes_alloc-nvl(b.bytes_free,0))/1024/1024,2)"使用(M)",

Largest"最大擴充段(M)",

to_char(sysdate,'yyyy-mm-ddhh24:mi:ss')"采樣時間"

from(selectf.tablespace_name,

sum(f.bytes)bytes_alloc,

sum(decode(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes))maxbytes

fromdba_data_filesf

groupbytablespace_name)a,

(selectf.tablespace_name,

sum(f.bytes)bytes_free

fromdba_free_spacef

groupbytablespace_name)b,

(selectround(max(ff.length)*16/1024,2)Largest,

ts.nametablespace_name

fromsys.fet$ff,sys.file$tf,sys.ts$ts

wherets.ts#=ff.ts#andff.file#=tf.relfile#andts.ts#=tf.ts#

groupbyts.name,tf.blocks)c

wherea.tablespace_name=b.tablespace_nameanda.tablespace_name=c.tablespace_name

25.查詢表空間的碎片程度

selecttablespace_name,count(tablespace_name)fromdba_free_spacegroupbytablespace_name

havingcount(tablespace_name)>10;

altertablespacenamecoalesce;

altertablenamedeallocateunused;

createorreplaceviewts_blocks_vas

selecttablespace_name,block_id,bytes,blocks,'freespace'segment_namefromdba_free_space

unionall

selecttablespace_name,block_id,bytes,blocks,segment_namefromdba_extents;

select*fromts_blocks_v;

selecttablespace_name,sum(bytes),max(bytes),count(block_id)fromdba_free_space

26。查詢有哪些資料庫執行個體在運作

selectinst_namefromv$active_instances;

//取得伺服器的IP位址

selectutl_inaddr.get_host_addressfromdual

//取得用戶端的IP位址

selectsys_context('userenv','host'),sys_context('userenv','ip_address')fromdual