当我们基本熟悉了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 from v$sgainfo s

select * from v$sgainfo

[@more@]

二、物理结构相关的信息

select * from v$parameter p where p.ISDEFAULT='FALSE'

select * from v$SPPARAMETER sp where sp.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;