oracle常用视图汇总

agangliu0400

　　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>selectusername,default_tablespacefromuser_users;
　　查看当前用户的角色
　　SQL>select*fromuser_role_privs;
　　查看当前用户的系统权限和表级权限
　　SQL>select*fromuser_sys_privs;
　　SQL>select*fromuser_tab_privs;
　　显示当前会话所具有的权限
　　SQL>select*fromsession_privs;
　　显示指定用户所具有的系统权限
　　SQL>select*fromdba_sys_privswheregrantee='GAME';
　　显示特权用户
　　select*fromv$pwfile_users;
　　显示用户信息(所属表空间)
　　selectdefault_tablespace,temporary_tablespace
　　fromdba_userswhereusername='GAME';
　　显示用户的PROFILE
　　selectprofilefromdba_userswhereusername='GAME';
　　2、表
　　查看用户下所有的表
　　SQL>select*fromuser_tables;
　　查看名称包含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');
　　查看索引的大小
　　SQL>selectsum(bytes)/(1024*1024)as"size(M)"fromuser_segments
　　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_remaining0
　　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'
　　orderbys.username,s.osuser
　　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)
　　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
　　groupbytablespace_name;
　　26。查询有哪些数据库实例在运行
　　selectinst_namefromv$active_instances;
　　//取得服务器的IP地址
　　selectutl_inaddr.get_host_addressfromdual
　　//取得客户端的IP地址
　　selectsys_context('userenv','host'),sys_context('userenv','ip_address')fromdual