oracle 查看有哪些系统视图,Oracle系统视图及应用场景总结

一：查看Oracle数据库中对象时用到的视图

总结查看数据库对象,主要视图包括:

1.基本数据字典

字典名称            说明

dba_tables               所有用户的所有表信息

dba_tab_columns     所有用户的表的字段信息

dba_views                所有用户的所有视图信息

dba_synonyms         所有用户的所有同义词信息

dba_sequences       所有用户的所有序列信息

dba_constraints       所有用户的表的约束信息

dba_ind_columns     所有用户的表的索引的字段信息

dba_triggers             所有用户的触发器信息

dba_sources            所有用户的存储过程信息

dba_segments         所有用户的段的使用空间信息

dba_extents             所有用户的段的扩展信息

dba_objects             所有用户对象的基本信息

cat                 当前用户可以访问的所有基表

tab                 但前用户创建的所有基表、视图、同义词等

dict                构成数据字典的所有表的信息

基本语句如下:

select  *  from  v$version;

select * from user_sys_privs;

select * from user_role_privs;

select username,default_tablespace from user_users;

select * from user_tables;

select object_name,object_id from user_objects

select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('&table_name');

查看放在ORACLE的内存区里的表

select table_name,cache from user_tables where instr(cache,'Y')>0;

select index_name,index_type,table_name from user_indexes order by table_name;

select * from user_ind_columns where index_name=upper('&index_name');

select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('&index_name');

select * from user_sequences;

select view_name from user_views;

select * from user_synonyms;

select constraint_name, constraint_type,search_condition, r_constraint_name  from user_constraints where table_name = upper('&table_name');

select object_name,status from user_objects where object_type='FUNCTION';

select object_name,status from user_objects where object_type='PROCEDURE';

select text from all_source where owner=user and name=upper('&plsql_name');

二:查看数据库:

数据库组件相关的数据字典

数据库组件   数据字典中的表或视图  说明

数据库       v$datafile                   记录系统的运行情况

表空间       dba_tablespaces       记录系统表空间的基本信息

dba_free_space        记录系统表空间的空闲空间信息

控制文件     v$controlfile         记录系统控制文件的基本信息

v$control_record_section 记录系统控制文件中记录文档段的信息

v$parameter 记录系统个参数的基本信息

数据文件     dba_data_files 记录系统数据文件及表空间的基本信息

v$filestat 记录来自控制文件的数据文件信息

v$datafile_header 记录数据文件头部的基本信息

段           dba_segments 记录段的基本信息

区           dba_extents 记录数据区的基本信息

日志          v$thread 记录日志线程的基本信息

v$log 记录日志文件的基本信息

v$logfile 记录日志文件的概要信息

归档   v$archived_log 记录归档日志文件的基本信息

v$archived_dest 记录归档日志文件的路径信息

数据库实例 v$instance 记录实例的基本信息

v$system_parameter 记录实例当前有效的参数信息

内存结构  v$sga 记录sga区的信息

v$sgastat 记录sga的详细信息

v$db_object_cache 记录对象缓存的大小信息

v$sql      记录sql语句的详细信息

v$sqltext  记录sql语句的语句信息

v$sqlarea  记录sql区的sql基本信息

后台进程  v$bgprocess 显示后台进程信息

v$session   显示当前会话信息

1、查看表空间的名称及大小

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

from dba_tablespaces t, dba_data_files d

where t.tablespace_name = d.tablespace_name

group by t.tablespace_name;

2、查看表空间物理文件的名称及大小

select tablespace_name, file_id, file_name,

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

from dba_data_files

order by tablespace_name;

3、查看回滚段名称及大小

select segment_name, tablespace_name, r.status,

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

max_extents, v.curext CurExtent

From dba_rollback_segs r, v$rollstat v

Where r.segment_id = v.usn(+)

order by segment_name ;

4、查看控制文件

select name from v$controlfile;

5、查看日志文件

select member from v$logfile;

6、查看表空间的使用情况

select sum(bytes)/(1024*1024) as free_space,tablespace_name

from dba_free_space group by tablespace_name;

SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,

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

FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C

WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;

Select Created, Log_Mode, Log_Mode From V$Database;

select username,sid,serial# from v$session;

根据sid找到pid

select pro.spid from v$session ses,v$process pro where ses.sid=21 and ses.paddr=pro.addr;

常用动态性能视图

视图名称       说明

v$fixed_table     显示当前发行的固定对象的说明

v$instance         显示当前实例的信息

v$latch               显示锁存器的统计数据

v$librarycache   显示有关库缓存性能的统计数据

v$rollstat            显示联机的回滚段的名字

v$rowcache       显示活动数据字典的统计

v$sag                记录sga区的信息

v$sgastat          记录sga的详细信息

v$sort_usage    显示临时段的大小及会话

v$sqltext            记录sql语句的语句信息

v$sqlarea          记录sql区的sql基本信息

v$stsstat            显示基本的实例统计信息

v$system_event 显示一个事件的总计等待时间

v$waitstat           显示

优化所涉及到的SQL语句:

1: 根据等待事件找到对应sql

select sql_text from V$sqlarea  where (address,hash_value) in

(select sql_address,sql_hash_value from v$session  where event like 'library%');

2: 根据sql找到对应信息:

根据sql找出对应的信息

select sql_id,HASH_VALUE,  ADDRESS from v$sql where sql_text like 'SELECT %';

根据v$sql_plan查看sql的执行计划

select ADDRESS ,HASH_VALUE,SQL_ID,PLAN_HASH_VALUE,COST,CPU_COST, ACCESS_PREDICATES  from v$sql_plan where SQL_ID='46n9n9d019tp2';

根据pid找出负载过高的sql及执行对应sql的主机和Ip

(1)：

根据pid找到对应的sql

select sql_text,spid,v$session.program,process  from

v$sqlarea,v$session,v$process

where v$sqlarea.address=v$session.sql_address

and v$sqlarea.hash_value=v$session.sql_hash_value

and v$session.paddr=v$process.addr

and v$process.spid in (55509026);

根据sid查是哪台电脑的链接

column osuser format a15

column username format a10

column machine format a30

select LOGON_TIME,osuser,machine,username,sid,serial#,CLIENT_INFO  from v$session where sid='1626';

要对自己维护的主要数据库的各个指标心中有数，比如每秒钟的事务数是多少，硬解析多少，软解析又是多少，这些心中有数，就能对每次报表的变化非常敏感，从而可以更敏锐的发现问题。

select ADDRESS  ,HASH_VALUE,SQL_ID,PLAN_HASH_VALUE,COST,CPU_COST, TIMESTAMP , ACCESS_PREDICATES  from v$sql_plan where SQL_ID='46n9n9d019tp2';

实际上简单的方法是看v$sql_plan 知道hash_value 就可以了。这样比较准确一些

看执行计划，可以用dbms_xplan程序包中的多个函数……

可结合AWR的数据字典，如dba_hist_sqlstat之类，或者查询ash信息，应可以确定具体时间段，使用的执行计划

查看当前状态下存在的等待事件

topas

select sid,event,p1,p2,seconds_in_wait from v$session_wait where event not like '%SQL%' and event not like 'rdbms%' and event not like '%message%' and event not like '%Streams AQ%' AND EVENT NOT LIKE '%slave%' order by event;

根据占cpu高的进程抓sql

通过sid找出对应系统的pid

SQL> select spid from v$process where addr in (select paddr from v$session where sid=1351);

通过spid 找到sid

select sid from v$session where paddr in (select addr from v$process where spid=29156);

通过sid找出执行的sql语句

select sql_address,sql_hash_value from v$session where sid=2609;

select table_name from dba_tables where buffer_pool='KEEP';

查看一下keep池的大小是不是合适：

oracle10g中才可以使用：那些连接导致了等待

select a.sample_time,b.event_name,a.wait_time,a.time_waited,a.program,a.module

from WRH$_ACTIVE_SESSION_HISTORY a,WRH$_EVENT_NAME b

where a.event_id=b.event_id and a.instance_number=1

and trunc(sample_time,'DD')=to_date('2011-05-4','yyyy-mm-dd')

and b.event_name='PX Deq Credit: need buffer';

1.2.4 检查问题语句的执行计划

set autotrace traceonly

set autotrace on

SQL> explain plan for SELECT COUNT(ID) FROM TABLE_A WHERE COL0107 =” || :B1;

Explained.

SQL> select * from table(DBMS_XPLAN.display);

show parameter keep

select sum(blocks) from dba_tables where buffer_pool='KEEP';

select 11821*8/1024 from dual;

查看热表的pctfree，发现都是默认的10：

select table_name,pct_free from dba_tables where buffer_pool='KEEP';

表空间是否自动扩展:

select file_name,autoextensible,increment_by from dba_data_files

检查问题表上是否存在索引

SQL>

select index_name,column_name,column_position from dba_ind_columns where table_name='TEMP_QUOTE' order by 1,3;

select index_name,column_name,column_position from user_ind_columns where table_name='TEMP_QUOTE' order by 1,3;

检查出现问题的语句

SQL> @sql_sid

Enter value for sid: 1413

old 1:

select sql_text from v$sqltext where hash_value in(select decode(sql_hash_value,0,prev_hash_value,sql_hash_value) from v$session where sid='&sid') order by piece;

new 1: select sql_text from v$sqltext where hash_value in(select decode(sql_hash_value,0,prev_hash_value,sql_hash_value) from v$session where sid=’1413′) order by piece;

查看表空间及碎片情况

select a.tablespace_name,

round( sum(a.tots)/1024/1024,0) Tot_Size_M,

round(sum(a.sumb)/1024/1024,0) Tot_Free_M,

round(sum(a.sumb)*100/sum(a.tots),0) Pct_Free,

round(sum(a.largest)/1024/1024,0) Max_Free_M,

sum(a.chunks) Chunks_Free

from

(

select tablespace_name,0 tots,sum(bytes) sumb,

max(bytes) largest,count(*) chunks

from dba_free_space a

group by tablespace_name

union

select tablespace_name,sum(bytes) tots,0,0,0 from

dba_data_files

group by tablespace_name) a

group by a.tablespace_name order by 2 desc;

2: 查看表空间大小

col used% for a10

col free% for a10

col tablespace_name for a20

SELECT A.TS1"tablespace_name",

ROUND((1-(B.SUM_B2/A.SUM_B1))*100,2)||'%' "used%",

ROUND((B.SUM_B2/A.SUM_B1)*100,2)||'%'  "free%",

A.SUM_B1/1024/1024"all_tablespace(M)",

(A.SUM_B1 - B.SUM_B2)/1024/1024 "used(M)",

B.SUM_B2/1024/1024 "free(M)"

FROM

(SELECT TABLESPACE_NAME TS1,

SUM(BYTES) SUM_B1

FROM DBA_DATA_FILES

GROUP BY TABLESPACE_NAME) A,

(SELECT TABLESPACE_NAME TS2,

SUM(BYTES) SUM_B2,

MAX(BYTES) MAX_B2,

COUNT(BYTES) CNT_B2

FROM DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME) B

WHERE A.TS1=B.TS2;

3：查看大小在前十位的对象

Select * from (select substr(segment_name,1,20),segment_type,sum(bytes)/1024/1024 M from dba_segments group by substr(segment_name,1,20),segment_type order by 3 desc) where rownum < 10;

临时表使用情况查看:

临时表空间分配

select file#,name,bytes/1024/1024 from v$tempfile;

select (sum (blocks))*8/1000 "MB" from v$sort_usage --当前有多少临时表空间被使用

select * from V$TEMP_SPACE_HEADER;

4:临时表空间分配

select file#,name,bytes/1024/1024 from v$tempfile;

select (sum (blocks))*8/1000 "MB" from v$sort_usage --当前有多少临时表空间被使用

select * from V$TEMP_SPACE_HEADER;