oracle数据库视图总结,Oracle基本视图总结

当我们基本熟悉了Oracle体系架构后，可能需要将这些信息从数据库字典中查出来看以便诊断和分析。如下是根据Oracle的体系架构整理出来基本视图分类列表：

一、数据库和实例信息

select * from

gV$INSTANCE

select * from

V$DATABASE

select * from

V$version

select * from

V$option

--select

s.NAME,s.BYTES/1024/1024 bytesM, s.RESIZEABLE fromv$sgainfo s

select * from

v$sgainfo[@more@]

二、物理结构相关的信息

select * from

v$parameter p where p.ISDEFAULT='FALSE'

select * from

v$SPPARAMETER sp wheresp.ORDINAL=1

select * from

v$pwfile_users

select * from

V$CONTROLFILE

select * from

V$CONTROLFILE_RECORD_SECTION

select * from

V$DATAFILE

select * from

V$TABLESPACE

select * from

DBA_DATA_FILES

select * from

DBA_TABLESPACES

select * from v$log

select * from

v$logfile

select * from

V$THREAD

select * from

DBA_ROLLBACK_SEGS

select * from

DBA_UNDO_EXTENTS

select * from

V$ROLLNAME

select * from

V$ROLLSTAT

select * from

V$UNDOSTAT

select * from

V$TRANSACTION

select * from

DBA_HIST_UNDOSTAT

select * from

DBA_TEMP_FILES

select * from

v$sort_segment

select * from

V$TEMPFILE

select * from

v$sort_usage

select * from

v$tempseg_usage;

三、存储相关信息：

DBA_EXTENTS

DBA_SEGMENTS

DBA_TABLESPACES

DBA_DATA_FILES

DBA_FREE_SPACE

四、数据库段和对象信息：

select * from

DBA_OBJECTS

select * from

DBA_TABLES

select * from

DBA_INDEXES

select * from

DBA_IND_COLUMNS

select * from

V$OBJECT_USAGE

五、进程、回话、SQL等信息：

select * from

V$process

select * from

V$session

select * from V$sql

select * from

V$NLS_PARAMETERS

六、用户和资源限定信息

DBA_USERS

DBA_PROFILES

DBA_USERS

DBA_TS_QUOTAS

七、综合应用案例：

1、表空使用情况

select

substr(a.TABLESPACE_NAME,1,30) TablespaceName,

sum(a.bytes/1024/1024)

as "Totle_size(M)",

sum(nvl(b.free_space1/1024/1024,0))

as "Free_space(M)",

sum(a.bytes/1024/1024)-sum(nvl(b.free_space1/1024/1024,0))

as "Used_space(M)",

round((sum(a.bytes/1024/1024)-sum(nvl(b.free_space1/1024/1024,0)))

*100/sum(a.bytes/1024/1024),2)

as "Used_percent%" from dba_data_files

a,

(select

sum(nvl(bytes,0)) free_space1,file_id from dba_free_space

group by file_id) b

where a.file_id = b.file_id(+) group by a.TABLESPACE_NAME

order by

"Used_percent%";

2、锁表情况查询：

select /*+ RULE */

ls.osuser os_user_name, ls.username user_name,

decode(ls.type,

'RW', 'Row wait enqueue lock', 'TM', 'DML enqueue lock', 'TX',

'Transaction enqueue

lock', 'UL', 'User supplied lock') lock_type,

o.owner,o.object_id,o.object_name

object,o.subobject_name, decode(ls.lmode, 1, null, 2, 'Row Share', 3,

'Row Exclusive', 4,

'Share', 5, 'Share Row Exclusive', 6, 'Exclusive', null)

lock_mode, o.owner,

ls.sid, ls.serial# seria ,ls.serial# serial_num, ls.id1, ls.id2

from sys.dba_objects o, ( select s.osuser,

s.username, l.type,

l.lmode, s.sid,

s.serial#, l.id1, l.id2 from v$session s,

v$lock l where s.sid = l.sid ) ls where

o.object_id = ls.id1 and o.owner

<> 'SYS' order

by o.owner, o.object_name

--alter system kill

session 'sid,serial';

3、操作系统的PID至数据库的Session的关联

SELECT s.SID, p.pid,

p.spid signaled, s.osuser, s.program

FROM v$process p,

v$session s

WHERE p.addr =

s.paddr;

--还可以通过和 v$bgprocess

连接查询到后台进程的名字：

SELECT s.SID SID,

p.spid threadid, p.program processname, bg.NAME NAME

FROM v$process p,

v$session s, v$bgprocess bg

WHERE p.addr =

s.paddr

AND p.addr =

bg.paddr

AND bg.paddr

<> '00';

4、根据操作系统PID定位SQL文本：

SELECT /*+ ORDERED

*/

sql_text

FROM v$sqltext a

WHERE (a.hash_value,

a.address) IN (

SELECT DECODE

(sql_hash_value,

0, prev_hash_value,

sql_hash_value

),

DECODE

(sql_hash_value, 0, prev_sql_addr, sql_address)

FROM v$session b

WHERE b.paddr =

(SELECT addr

FROM v$process c

WHERE c.spid =

TO_NUMBER ('&pid', 'xxxx')))

ORDER BY piece ASC

5、查询执行是将较长(比例为100毫秒)的SQL及Session

select

s.SID,s.SERIAL#,w.EVENT,s.PADDR,s.USERNAME,w.SECONDS_IN_WAIT,sql.SQL_TEXT,sql.SQL_FULLTEXT,

s.OSUSER,s.MACHINE,s.PROCESS,s.WAIT_CLASS,s.WAIT_TIME,s.STATE,s.ROW_WAIT_OBJ#,s.ROW_WAIT_ROW#,s.LAST_CALL_ET

,s.ROW_WAIT_BLOCK#,s.BLOCKING_SESSION

from v$session_wait

w ,v$session s,v$sql sql

where w.SID=s.SID

and s.SQL_ID=sql.SQL_ID

and w.EVENT not in('SQL*Net message from client')

and s.LAST_CALL_ET>100;--100毫秒以上的等待

6、查看临时表空间使用情况

select

SEGBLK#*8192/1024/1024/1024 from v$sort_usage

select

USERNAME,SQLHASH,SEGTYPE,SEGFILE#,SEGBLK# as size_GB from v$sort_usage

select * from

v$session where prev_hash_value in(select sqlhash from v$sort_usage)

7、UNDO表空间及回滚段信息的查询

select

file_name,bytes/1024/1024/1024 from dba_data_files

where

tablespace_name like 'UNDOTBS1'

select

usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks

from v$rollstat

order by rssize;