查询sql

（1）查看数据库的实例：

select instance_name,status,version,database_status from v$instance;

 （2）查看数据库的日志模式，打开模式

select name,log_mode,open_mode from v$database;

 （3）检查控制文件状态：

select status,name from v$controlfile;

 （4）查询日志状态

select group#,status,member from v$logfile;

  （5）数据库是否处于归档模式

Archive log list

   （6）文件系统的使用情况

Df -k检查有没有使用率超过80%的文件系统，特别是存放归档日志的文件系统

（7）检查sga使用情况

Select * from v$sga

检查sga各部分的分配情况，与时间内存比较是否合理

（8）检查sga各部分占用内存情况

Select * from v$sgastat;

检查有无占用大量sharaed pool的对象，及是否有内存浪费情况

（9）检查sequence的使用情况

Select sequence_owner,sequence_name,min_value.max_value,increment_by,

Last_number,cache_Size,cycle_flag from dba_sequence;

检查是否存在即将达到max_Value的sequence

查看序列号，last_number是当前值
select * from user_sequences;

（10）检查有无运行失败的job 

select job,this_date,this_sec,next_date,next_sec,failures,What

from dba_jobs where failures !=0 or failures is not null;

只知道job id，要查看執行錯誤的job的 sid 和 serial#

    Step1： select sid from v$lock where TYPE = 'JQ';

Step2： select sid,serial# from v$session where sid = :sid;

Step3： select ADDR,PID,SPID,SERIAL# from v$process where pid = :sid;

如果数据库使用了Oracle的JOB来完成一些定时作业，要对这些JOB的运行情况进行检查：select job,log_user,last_date,failures from dba_jobs;

如果FAILURES列是一个大于0的数的话，说明JOB运行失败，要进一步的检查。

检查无效的trigger 

SELECT owner,trigger_name,table_name,status FROM dba_triggers WHERE 

status ='DISABLED’;

（11） 检查Oracle初始化文件中相关的参数值

 Select resource_name,current_utilization,max_utilization,initial_allocation,

limit_value from v$resource_limit

（12）查找未断连接

select process,osuser,username,machine,logon_time ,sql_textfrom v$session a,v$sqltext b where a.sql_address=b.address;

对象

检查是否有无效的对象

Select object_name,object_type,owner,status from dba_object where 

Status!=’VALID’ and owner not in(‘SYS’,’SYSTEM’) and object_type in

(‘’TRIGGER,’VIEW’,’PROCEDURE’,’FUNCTION’)

如果存在无效的对象，手工重新编译一下

检查无效的数据库对象:

SELECT owner, object_name, object_type FROM dba_objects WHERE 

status='INVALID';

数据库对象的存储参数设置

Select segment_name,next_extent,tablespace_name from dba_segments where next_extent>[上一个检查中的最小的max_chunk]

如果有结果返回，说明有些对象的下一次扩展（从表空间的空闲区中分配空间的操作）会败

检查是否有超过200个extent的对象

Select segment_name,tablespace_name,extents from dba_segmnet

Where owner not in (‘SYS’,’SYSTEM’) and extents>200;

如果有结果返回，说明这些对象分配了太多的extent，可以考虑重建这些对象。

检查一些扩展异常的对象

select segment_name,segment_type,tablespace_name, (extents/max_extents)*100 Percent from sys.DBA_segments     where max_extents!=0 and (extents/max_extents)*100>=95

 order by percent;

no rows selected

如果有记录返回，则这些对象的扩展已经快达到它定义时的最大扩展值。对于这些对象

要修改它的存储结构参数.

查看当前所有对象

 select * from tab;

查出当前用户所有表名。

select unique tname from col;

查询当前用户对象? 

select * from user_objects; 

select * from dba_segments; 

select owner, object_type, status, count(*) count# from all_objects

group by owner, object_type, status;

表：select * from cat;select * from tab;select table_name from user_tables;

看名称包含log字符的表
 select object_name,object_id from user_objects
 where instr(object_name,'LOG')>0;
查看某表的创建时间
select object_name,created from user_objects where object_name=upper('&table_name');
查看某表的大小
 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 substr(table_name,1,20) tabname,substr(column_name,1,20)column_name,

rtrim(data_type)||'('||data_length||')' from system.dba_tab_columns

where owner='username'

如何知道数据库中某个表所在的tablespace? 

select tablespace_name from user_tables where table_name='test'; 

select * from user_tables中有个字段tablespace_name，（oracle）; 

select * from dba_segments where …; 

 

 

索引

检查是否有失效的索引

Select index_name,owner,table_name ,tablespace_name from dba_indexs

Where owner not in (‘’SYS,’SYSTEM’) and status!=’VALID’;

如果有记录返回，考虑重建这些索引

查看索引个数和类别
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 index_name,table_owner,table_name,tablespace_name,status from user_indexes order by table_name;

 

视图

select text from user_views where view_name=upper('&view_name');

select view_name from user_views;
查看创建视图的select语句
 set view_name,text_length from user_views;
 set long 2000;     

说明：可以根据视图的text_length值设定set long 的大小  

select text from user_views where view_name=upper('&view_name'); 

 

表空间

查询表空间信息

select * from dba_data_files; 

查询表空间状态：

select tablespace_name,status from dba_tablespaces;

查看表空间的名称及大小
    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 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 tablespace_name,max_m,count_blocks 

free_blk_cnt,sum_free_m,to_char(100*sum_free_m/sum_m, '99.99') || 

'%' AS pct_free FROM (SELECT tablespace_name,sum(bytes)/1024/1024 

AS sum_m FROM dba_data_files GROUP BY tablespace_name),(SELECT tablespace_name AS fs_ts_name, max(bytes)/1024/1024 AS 

max_m,count(blocks) AS count_blocks,sum(bytes/1024/1024) AS 

sum_free_m FROM dba_free_space GROUP BY tablespace_name) WHERE 

tablespace_name=fs_ts_name; 

表空间剩余自由空间情况：

select tablespace_name,sum(bytes) 总字节数,max(bytes),count(*) from dba_free_space group by tablespace_name；

回收表空間:

a、使用alter table ... deallocate unused 命令回收表的空间 

例如： alter table AA.TEST deallocate unused keep 1k;

b、alter tablespace TABLESPACENAME coalesce 命令回收表空间的空间

查看表空间物理文件的名称及大小
 select tablespace_name, file_id, file_name,
 round(bytes/(1024*1024),0) total_space
 from dba_data_files  order by tablespace_name;
检查system表空间内的内容

select distinct(owner) from dba_tables where tablespace_name='SYSTEM' and

 owner!='SYS' and owner!='SYSTEM' union select distinct(owner) from dba_indexes

where tablespace_name='SYSTEM' and owner!='SYS' and owner!='SYSTEM';

OWNER

------------------------------

MDSYS

OLAPSYS

OUTLN

如果有记录返回，则表明system表空间内存在一些非system和sys用户的对象。应该进一

步检查这些对象是否与我们应用相关。如果相关请把这些对象移到非System表空间，同时应该检查这些对象属主的缺省表空间值，

检查剩余表空间: 

SELECT tablespace_name,sum(blocks) as 

free_blk,trunc(sum(bytes)/(1024*1024)) as free_m, max(bytes)/(1024) 

as big_chunk_k,count(*) as num_chunks FROM dba_free_space GROUP BY 

tablespace_name; 

检查Oracle各个表空间的增长情况

 select A.tablespace_name,(1-(A.total)/B.total)*100 used_percent from

 (select tablespace_name,sum(bytes) total  from dba_free_space group by tablespace_name) A,(select tablespace_name,sum(bytes) total from dba_data_files group by tablespace_name) B where A.tablespace_name=B.tablespace_name

检查对象的下一扩展与表空间的最大扩展值

select a.table_name,a.next_extent,a.tablespace_name from all_tables a, 

 (select tablespace_name,max(bytes) as big_chunk

     from dba_free_space group by tablespace_name) f

      where f.tablespace_name=a.tablespace_name

      and a.next_extent>f.big_chunk union

     select a.index_name,a.next_extent,a.tablespace_name

      from all_indexes a,

   (seect tablespace_name,max(bytes) as big_chunk

      from dba_free_space

      group by tablespace_name) f

      where f.tablespace_name=a.tablespace_name

     and a.next_extent>f.big_chunk;

 

no rows selected

如果有记录返回，则表明这些对象的下一个扩展大于该对象所属表空间的最大扩展值，

需调整相应表空间的存储参数

 

数据文件

检查数据文件的自动增长是否关闭

select file_name,autoextensible from dba_data_files where autoextensible='YES';

如果存在这样的数据文件就要关闭自动增长

查询数据状态：

select file#,status,name from v$datafile;

检查数据文件的状态记录状态不是“online”的数据文件，并做恢复:

Select file_name from dba_data_files where status='OFFLINE';

查看数据文件放置的路径

select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id;

查詢oracle的datafile基本信息

SELECT FILE_NAME,BYTES/1024/1024,AUTOEXTENSIBLE,MAXBYTES/1024/1024 FROM DBA_DATA_FILES

查看数据库的大小，和空间使用情况

select b.file_id　　文件ID,　

b.tablespace_name　　表空间,　

b.file_name　　　　　物理文件名,　

b.bytes　　　　　　　总字节数,　　

(b.bytes-sum(nvl(a.bytes,0)))　　　已使用,　　

sum(nvl(a.bytes,0))　　　　　　　　剩余,　　

sum(nvl(a.bytes,0))/(b.bytes)*100　剩余百分比　　

from dba_free_space a,dba_data_files b　　

where a.file_id=b.file_id　

group by b.tablespace_name,b.file_name,b.file_id,b.bytes　　

order by b.tablespace_name　　/　　

dba_free_space --表空间剩余空间状况　

dba_data_files --数据文件空间占用情况 

 

权限

本用户读取其他用户对象的权限：select * from user_tab_privs;

本用户所拥有的系统权限：select * from user_sys_privs;

怎样查看哪些用户拥有sysdba、sysoper权限？ 

conn sys/change_on_install 

select * from v_$pwfile_users; 

 97. 如何知道用户拥有的权限? 

select * from dba_sys_privs ;

约束限制：

select TABLE_NAME,CONSTRAINT_NAME,SEARCH_CONDITION,STATUS

from user_constraints WHERE TABLE_name=upper('&TABLE_Name');

 查看某表的约束条件
select constraint_name, constraint_type,search_condition, r_constraint_name
 from user_constraints where table_name = upper('&table_name');
 select c.constraint_name,c.constraint_type,cc.column_name
 from user_constraints c,user_cons_columns cc
  where c.owner = upper('&table_owner') and c.table_name = upper('&table_name') and c.owner = cc.owner and c.constraint_name = cc.constraint_name order by cc.position;

 

 查看当前用户的缺省表空间
  select username,default_tablespace from user_users;
 查看当前用户的角色select * from user_role_privs;
  查看当前用户的系统权限和表级权限
select * from user_sys_privs; select * from user_tab_privs;

检查不起作用的约束 

SELECT owner, constraint_name, table_name,constraint_type,status 

FROM dba_constraints 

WHERE status = 'DISABLED’ AND constraint_type = 'P'; 

锁

察看oracle中被鎖的對象

select a.object_name objectname,b.session_id,c.serial#,c.program 

program,c.username username,c.command,c.machine machine,c.lockwait 

from all_objects a,v$locked_object b,v$session c where a.object_id =b.object_id and c.sid=b.session_id

如何对锁住的帐户进行解锁

show user

select username,account_status,expiry_date from dba_users;

alter user OUTLN account unlock;

grant connect to outln identified by outln;

 

如何查看系统被锁的事务时间？ 

select * from v$locked_object ; 

查询锁的状况的对象有? 

v$lock, v$locked_object, v$session, v$sqlarea, v$process ; 

如何解锁? 

alter system kill session ‘sid,serir#’; 

用户

检查数据库用户情况

select username,default_tablespace,temporary_tablespacefrom dba_users;

用户：select * from all_users order by user_id;

回滚

如何查看现有回滚段及其状态 ? 

Select segment_name,ccbzzp,tablespace_name,segment_id,file_id,status

 from dba_rollback_segs 

8.检查回滚段使用情况

select n.name,wraps,extends,shrinks,optsize,waits,xacts,aveactive,hwmsize

from v$rollstat r, v$rollname n where r.usn=n.usn;

检查回滚段的shrink 和extends 次数是否过多。

检查 optimal 设置是否合理，是否占用了过多的回滚段表空间

查看回滚段名称及大小
    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 ;
检查Oracle所有回滚段的状态

select segment_name,status from dba_rollback_segs;

触发器：select trigger_name,trigger_type,table_owner,table_name,status from user_triggers;

快照：select owner,name,master,table_name,last_refresh,next from user_snapshots order by owner,next;

同义词：select * from syn;

查看同义词select * from user_synonyms;

序列：select * from seq;

数据字典：select table_name from dict order by table_name;

锁及资源信息：select * from v$lock;不包括DDL 锁

SQL 共享池：select sql_text from v$sqlarea;

NLS 参数当前值：select * from V$nls_parameters;

描述后台进程：select * from v$bgprocess;

查看版本信息：select * from product_component_version;

53、察看sql历史记录

 SELECT   osuser, username, sql_text

          FROM v$session a, v$sqltext b

          WHERE a.sql_address = b.address 

          and sql_text like '%delete%'

          AND username IS NOT NULL

ORDER BY address, piece;

 

104、根据用户的PID找出正在做何sql操作。

SELECT sql_text FROM v$sqlarea WHERE address IN (SELECT sql_address FROM v$session WHERE SID IN (SELECT SID FROM v$session WHERE paddr IN (SELECT addr FROM v$process WHERE pid = 121)));

 

 

 

Oracle常用性能监控SQL语句

--查看表锁 

select * from sys.v_$sqlarea where disk_reads>100

 

--监控事例的等待 

select event,sum(decode(wait_Time,0,0,1)) "Prev", 

sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot" 

from v$session_Wait 

group by event order by 4 

 

--回滚段的争用情况

select name, waits, gets, waits/gets "Ratio" 

from v$rollstat a, v$rollname b 

where a.usn = b.usn

 

--查看前台正在发出的SQL语句

select user_name,sql_text

　　 from v$open_cursor

　　 where sid in (select sid from (select sid,serial#,username,program

　　 from v$session

　　 where status='ACTIVE'))

 

--数据表占用空间大小情况

select segment_name,tablespace_name,bytes,blocks 

from user_segments 

where segment_type='TABLE' 

ORDER BY bytes DESC ,blocks DESC

 

--查看表空间碎片大小

    select tablespace_name,round(sqrt(max(blocks)/sum(blocks))*

           (100/sqrt(sqrt(count(blocks)))),2) FSFI

    from dba_free_space

    group by tablespace_name order by 1

    

--查看表空间占用磁盘情况

     select 

             b.file_id                                 文件ID号,

             b.tablespace_name                         表空间名,

             b.bytes                                 字节数,

             (b.bytes-sum(nvl(a.bytes,0)))                 已使用,

             sum(nvl(a.bytes,0))                         剩余空间,

             sum(nvl(a.bytes,0))/(b.bytes)*100         剩余百分比 

             from dba_free_space a,dba_data_files b 

             where a.file_id=b.file_id 

             group by b.tablespace_name,b.file_id,b.bytes 

             order by b.file_id

 

--查看session使用回滚段

SELECT  r.name 回滚段名,

        s.sid,

        s.serial#,

        s.username 用户名,

        t.status,

        t.cr_get,

        t.phy_io,

        t.used_ublk,

        t.noundo,

        substr(s.program, 1, 78) 操作程序

FROM   sys.v_$session s,sys.v_$transaction t,sys.v_$rollname r

WHERE  t.addr = s.taddr and t.xidusn = r.usn

ORDER  BY t.cr_get,t.phy_io

 

--查看SGA区剩余可用内存

select name,

　　　　　　sgasize/1024/1024"Allocated(M)",

　　　　　　bytes/1024　　　　　　　　　　　 "**空间(K)",

　　　　　　round(bytes/sgasize*100, 2)"**空间百分比(%)"

　　 from   (select sum(bytes) sgasize from sys.v_$sgastat) s, sys.v_$sgastat f

　　 where  f.name = 'free memory'

 

--监控表空间I/O比例

select df.tablespace_name name,df.file_name "file",f.phyrds pyr, 

       f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw 

from v$filestat f, dba_data_files df 

where f.file# = df.file_id 

order by df.tablespace_name

 

--监控SGA命中率

select a.value + b.value "logical_reads", 

       c.value "phys_reads", 

       round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO" 

from v$sysstat a, v$sysstat b, v$sysstat c 

where a.statistic# = 38 and 

      b.statistic# = 39 and 

      c.statistic# = 40 

 

--监控 SGA 中字典缓冲区的命中率

select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio", 

(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio" 

from v$rowcache 

where gets+getmisses <>0 

group by parameter, gets, getmisses 

 

--监控 SGA **享缓存区的命中率，应该小于1%

select sum(pins) "Total Pins", sum(reloads) "Total Reloads", 

sum(reloads)/sum(pins) *100 libcache 

from v$librarycache

 

--监控 SGA 中重做日志缓存区的命中率，应该小于1%

SELECT name, gets, misses, immediate_gets, immediate_misses, 

Decode(gets,0,0,misses/gets*100) ratio1, 

Decode(immediate_gets+immediate_misses,0,0, 

immediate_misses/(immediate_gets+immediate_misses)*100) ratio2 

FROM v$latch WHERE name IN ('redo allocation', 'redo copy')

 

--监控内存和硬盘的排序比率，最好使它小于 .10

SELECT name, value 

FROM v$sysstat 

WHERE name IN ('sorts (memory)', 'sorts (disk)') 

 

--监控字典缓冲区

SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES" 

FROM V$ROWCACHE 

 

--非系统用户建在SYSTEM表空间中的表

SELECT owner,table_name 

FROM DBA_TABLES

WHERE tablespace_name in('SYSTEM','USER_DATA') AND 

      owner NOT IN('SYSTEM','SYS','OUTLN', 'ORDSYS','MDSYS','SCOTT', 'HOSTEAC')

 

--性能最差的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<100

                             

--读磁盘数超100次的sql

select * from sys.v_$sqlarea where disk_reads>100

 

--最频繁执行的sql

select * from sys.v_$sqlarea where executions>100

 

--查询使用CPU多的用户session

select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value 

from v$session a,v$process b,v$sesstat c 

where c.statistic#=12 and 

      c.sid=a.sid and 

      a.paddr=b.addr 

order by value desc

 

--当前每个会话使用的对象数

SELECT a.sid,s.terminal,s.program,count(a.sid) 

FROM V$ACCESS a,V$SESSION s

WHERE a.owner <> 'SYS'AND s.sid = a.sid 

GROUP BY a.sid,s.terminal,s.program

ORDER BY count(a.sid)