数据库内置表常见SQL记录

学习数据库内置的一些视图或者表来查询数据库的具体信息。

比如

用户，表空间，表，字段，主外键，索引，数据文件，日志文件，控制文件

耗时SQL

客户端连接

死锁的SQL等

通过这些内置数据了解oracle，进而对其优化。

==========================================================

SELECT * FROM user_tables --查询表 --all_tables dba_tables 

SELECT * FROM user_tab_columns --查询表的字段 --all_tab_columns dba_tab_columns 

SELECT * FROM user_col_comments --查询表字段的注释 --dba_tab_comments all_tab_comments

SELECT * FROM user_tab_comments --查询表的注释 --dba_tab_comments，all_tab_comments

SELECT * FROM user_constraints --查找表的主键

SELECT * FROM user_cons_columns --查找表的主键所在的列

SELECT * FROM user_indexes --查找表的索引

SELECT * FROM user_ind_columns --查找表索引所在的列名称

查看表空间的名称及大小 

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; 

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

SELECT tablespace_name, 
file_id, 
file_name, 
round(bytes / (1024 * 1024), 0) total_space 
FROM dba_data_files 
ORDER BY tablespace_name; 

查看回滚段名称及大小

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; 

查看控制文件 

ELECT NAME FROM v$controlfile;

查看日志文件

SELECT MEMBER FROM v$logfile; 

查看表空间的使用情况 

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 owner, object_type, status, COUNT(*) count# 
FROM all_objects 
GROUP BY owner, object_type, status; 

查看数据库的版本

SELECT version 
FROM product_component_version 
WHERE substr(product, 1, 6) = ‘Oracle‘;

查看数据库的创建日期和归档方式 

SELECT created, log_mode, log_mode FROM v$database; 

查询最慢的sql

select * from (
select parsing_user_id,executions,sorts
command_type,disk_reads,sql_text from v$sqlarea order by disk_reads desc
)where rownum<10

查询对应session

select SE.SID,SE.SERIAL#,PR.SPID,
SE.USERNAME,SE.STATUS,SE.TERMINAL,
SE.PROGRAM,SE.MODULE,
SE.SQL_ADDRESS,ST.EVENT,
ST.P1TEXT,SI.PHYSICAL_READS,SI.BLOCK_CHANGES from v$session se,v$session_wait st,
v$sess_io si,v$process pr
where st.SID=se.SID and st.SID=si.SID
AND SE.PADDR=PR.ADDR
AND SE.SID>6
AND ST.WAIT_TIME=0
AND ST.EVENT NOT LIKE ‘%SQL%‘
ORDER BY PHYSICAL_READS DESC;
SELECT sql_address FROM V$SESSION SS,V$SQLTEXT TT
WHERE SS.SQL_HASH_VALUE=TT.HASH_VALUE AND SID=439;

select a.CPU_TIME,--CPU时间 百万分之一（微秒）
       a.OPTIMIZER_MODE,--优化方式
       a.EXECUTIONS,--执行次数
       a.DISK_READS,--读盘次数
       a.SHARABLE_MEM,--占用shared pool的内存多少
       a.BUFFER_GETS,--读取缓冲区的次数
       a.COMMAND_TYPE,--命令类型(3:select,2:insert;6:update;7delete;47:pl/sql程序单元)
       a.SQL_TEXT,--Sql语句
       a.SHARABLE_MEM,
       a.PERSISTENT_MEM,
       a.RUNTIME_MEM,
       a.PARSE_CALLS,
       a.DISK_READS,
       a.DIRECT_WRITES,
       a.CONCURRENCY_WAIT_TIME,
       a.USER_IO_WAIT_TIME
  from SYS.V_$SQLAREA a
 WHERE PARSING_SCHEMA_NAME = ‘CHEA_FILL‘--表空间
 order by a.CPU_TIME desc