Oracle维护常用SQL语句汇总

如何远程判断Oracle数据库的安装平台

Java代码
  1. select * from v$version;  


查看表空间的使用情况

Java代码
  1. select sum(bytes)/(1024*1024) as free_space,tablespace_name   
  2. from dba_free_space   
  3. group by tablespace_name;   
  4.   
  5.   
  6. SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,   
  7. (B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"  
  8. FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C   
  9. WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;  



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

Java代码
  1. select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size   
  2. from dba_tablespaces t, dba_data_files d   
  3. where t.tablespace_name = d.tablespace_name   
  4. group by t.tablespace_name;  



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

Java代码
  1. select tablespace_name, file_id, file_name,   
  2. round(bytes/(1024*1024),0) total_space   
  3. from dba_data_files   
  4. order by tablespace_name;  



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

Java代码
  1. select segment_name, tablespace_name, r.status,   
  2. (initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,   
  3. max_extents, v.curext CurExtent   
  4. From dba_rollback_segs r, v$rollstat v   
  5. Where r.segment_id = v.usn(+)   
  6. order by segment_name ;  


4、查看控制文件

Java代码
  1. select name from v$controlfile;  



5、查看日志文件

Java代码
  1. select member from v$logfile;  



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

Java代码
  1. select sum(bytes)/(1024*1024) as free_space,tablespace_name   
  2. from dba_free_space   
  3. group by tablespace_name;   
  4.   
  5.   
  6. SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,   
  7. (B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"  
  8. FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C   
  9. WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;  



7、查看数据库库对象

Java代码
  1. select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;  



8、查看数据库的版本　

Java代码
  1. Select version FROM Product_component_version   
  2. Where SUBSTR(PRODUCT,1,6)='Oracle';  


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

Java代码
  1. Select Created, Log_Mode, Log_Mode From V$Database;  


10、如何远程判断Oracle数据库的安装平台

Java代码
  1. select * from v$version;  


11、查看数据表的参数信息

Java代码
  1. SELECT partition_name, high_value, high_value_length, tablespace_name,   
  2. pct_free, pct_used, ini_trans, max_trans, initial_extent,   
  3. next_extent, min_extent, max_extent, pct_increase, FREELISTS,   
  4. freelist_groups, LOGGING, BUFFER_POOL, num_rows, blocks,   
  5. empty_blocks, avg_space, chain_cnt, avg_row_len, sample_size,   
  6. last_analyzed   
  7. FROM dba_tab_partitions   
  8. --WHERE table_name = :tname AND table_owner = :towner   
  9. ORDER BY partition_position  



12、查看还没提交的事务
Java代码
  1. select * from v$locked_object;   
  2. select * from v$transaction;  



14、回滚段查看

Java代码
  1. select rownum, sys.dba_rollback_segs.segment_name Name, v$rollstat.extents   
  2. Extents, v$rollstat.rssize Size_in_Bytes, v$rollstat.xacts XActs,   
  3. v$rollstat.gets Gets, v$rollstat.waits Waits, v$rollstat.writes Writes,   
  4. sys.dba_rollback_segs.status status from v$rollstat, sys.dba_rollback_segs,   
  5. v$rollname where v$rollname.name(+) = sys.dba_rollback_segs.segment_name and   
  6. v$rollstat.usn (+) = v$rollname.usn order by rownum  


15、捕捉运行很久的SQL

Java代码
  1. column username format a12   
  2. column opname format a16   
  3. column progress format a8   
  4.   
  5. select username,sid,opname,   
  6.       round(sofar*100 / totalwork,0) || '%' as progress,   
  7.       time_remaining,sql_text   
  8. from v$session_longops , v$sql   
  9. where time_remaining <> 0  
  10. and sql_address = address   
  11. and sql_hash_value = hash_value  

/
16。查看数据表的参数信息
Java代码
  1. SELECT   partition_name, high_value, high_value_length, tablespace_name,   
  2.         pct_free, pct_used, ini_trans, max_trans, initial_extent,   
  3.         next_extent, min_extent, max_extent, pct_increase, FREELISTS,   
  4.         freelist_groups, LOGGING, BUFFER_POOL, num_rows, blocks,   
  5.         empty_blocks, avg_space, chain_cnt, avg_row_len, sample_size,   
  6.         last_analyzed   
  7.    FROM dba_tab_partitions   
  8.   --WHERE table_name = :tname AND table_owner = :towner   
  9. ORDER BY partition_position  


17。查找object为哪些进程所用
Java代码
  1. select   
  2. p.spid,   
  3. s.sid,   
  4. s.serial# serial_num,   
  5. s.username user_name,   
  6. a.type  object_type,   
  7. s.osuser os_user_name,   
  8. a.owner,   
  9. a.object object_name,   
  10. decode(sign(48 - command),   
  11. 1,   
  12. to_char(command), 'Action Code #' || to_char(command) ) action,   
  13. p.program oracle_process,   
  14. s.terminal terminal,   
  15. s.program program,   
  16. s.status session_status    
  17. from v$session s, v$access a, v$process p    
  18. where s.paddr = p.addr and   
  19.      s.type = 'USER' and      
  20.      a.sid = s.sid   and   
  21.   a.object='SUBSCRIBER_ATTR'  
  22. order by s.username, s.osuser  


18。耗资源的进程（top session）
Java代码
  1. select s.schemaname schema_name,    decode(sign(48 - command), 1,   
  2. to_char(command), 'Action Code #' || to_char(command) ) action,    status   
  3. session_status,   s.osuser os_user_name,   s.sid,         p.spid ,         s.serial# serial_num,    
  4. nvl(s.username, '[Oracle process]') user_name,   s.terminal terminal,      
  5. s.program program,   st.value criteria_value  from v$sesstat st,   v$session s  , v$process p    
  6. where st.sid = s.sid and   st.statistic# = to_number('38') and   ('ALL' = 'ALL'  
  7. or s.status = 'ALL') and p.addr = s.paddr order by st.value desc,  p.spid asc, s.username asc, s.osuser asc  


19。查看锁（lock）情况
Java代码
  1. select ls.osuser os_user_name,   ls.username user_name,    
  2. decode(ls.type, 'RW', 'Row wait enqueue lock', 'TM', 'DML enqueue lock', 'TX',   
  3. 'Transaction enqueue lock', 'UL', 'User supplied lock') lock_type,    
  4. o.object_name object,   decode(ls.lmode, 1, null, 2, 'Row Share', 3,   
  5. 'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive', null)   
  6. lock_mode,    o.owner,   ls.sid,   ls.serial# serial_num,   ls.id1,   ls.id2      
  7. from sys.dba_objects o, (   select s.osuser,    s.username,    l.type,      
  8. l.lmode,    s.sid,    s.serial#,    l.id1,    l.id2   from v$session s,      
  9. v$lock l   where s.sid = l.sid ) ls  where o.object_id = ls.id1 and    o.owner   
  10. <> 'SYS'   order by o.owner, o.object_name  



根据sid查是哪台电脑的链接
Java代码
  1. column osuser format a15   
  2. column username format a10   
  3. column machine format a30   
  4. select osuser,machine,username,sid,serial# from v$session where sid='128';  


根据sid查对应的sql
Java代码
  1. select SID,SQL_TEXT from v$open_cursor where SID='128';  


20。查看等待（wait）情况
Java代码
  1. SELECT v$waitstat.class, v$waitstat.count count, SUM(v$sysstat.value) sum_value   
  2. FROM v$waitstat, v$sysstat WHERE v$sysstat.name IN ('db block gets',   
  3. 'consistent gets') group by v$waitstat.class, v$waitstat.count  


21。查看sga情况
Java代码
  1. SELECT NAME, BYTES FROM SYS.V_$SGASTAT ORDER BY NAME ASC  


22。查看catched object
Java代码
  1. SELECT owner,              name,              db_link,              namespace,    
  2.            type,              sharable_mem,              loads,              executions,    
  3.           locks,              pins,              kept        FROM v$db_object_cache  

       
23。查看V$SQLAREA
Java代码
  1. SELECT SQL_TEXT, SHARABLE_MEM, PERSISTENT_MEM, RUNTIME_MEM, SORTS,   
  2. VERSION_COUNT, LOADED_VERSIONS, OPEN_VERSIONS, USERS_OPENING, EXECUTIONS,   
  3. USERS_EXECUTING, LOADS, FIRST_LOAD_TIME, INVALIDATIONS, PARSE_CALLS, DISK_READS,   
  4. BUFFER_GETS, ROWS_PROCESSED FROM V$SQLAREA  


24。查看object分类数量
Java代码
  1. select decode (o.type#,1,'INDEX' , 2,'TABLE' , 3 , 'CLUSTER' , 4, 'VIEW' , 5 ,   
  2. 'SYNONYM' , 6 , 'SEQUENCE' , 'OTHER' ) object_type , count(*) quantity from   
  3. sys.obj$ o where o.type# > 1 group by decode (o.type#,1,'INDEX' , 2,'TABLE' , 3  
  4. , 'CLUSTER' , 4, 'VIEW' , 5 , 'SYNONYM' , 6 , 'SEQUENCE' , 'OTHER' ) union select   
  5. 'COLUMN' , count(*) from sys.col$ union select 'DB LINK' , count(*) from  


25。按用户查看object种类
Java代码
  1. select u.name schema,   sum(decode(o.type#, 1, 1, NULL)) indexes,    
  2. sum(decode(o.type#, 2, 1, NULL)) tables,   sum(decode(o.type#, 3, 1, NULL))   
  3. clusters,   sum(decode(o.type#, 4, 1, NULL)) views,   sum(decode(o.type#, 5, 1,   
  4. NULL)) synonyms,   sum(decode(o.type#, 6, 1, NULL)) sequences,    
  5. sum(decode(o.type#, 1, NULL, 2, NULL, 3, NULL, 4, NULL, 5, NULL, 6, NULL, 1))   
  6. others   from sys.obj$ o, sys.user$ u   where o.type# >= 1 and    u.user# =   
  7. o.owner# and   u.name <> 'PUBLIC'   group by u.name    order by   
  8. sys.link$ union select 'CONSTRAINT' , count(*) from sys.con$  


26。有关connection的相关信息
1）查看有哪些用户连接
Java代码
  1. select s.osuser os_user_name,    decode(sign(48 - command), 1, to_char(command),   
  2. 'Action Code #' || to_char(command) ) action,     p.program oracle_process,      
  3. status session_status,    s.terminal terminal,    s.program program,      
  4. s.username user_name,    s.fixed_table_sequence activity_meter,    '' query,      
  5. 0 memory,    0 max_memory,     0 cpu_usage,    s.sid,   s.serial# serial_num      
  6. from v$session s,    v$process p   where s.paddr=p.addr and    s.type = 'USER'    
  7. order by s.username, s.osuser  

2）根据v.sid查看对应连接的资源占用等情况
Java代码
  1. select n.name,   
  2.  v.value,   
  3.  n.class,   
  4.  n.statistic#    
  5. from  v$statname n,   
  6.  v$sesstat v   
  7. where v.sid = 71 and   
  8.  v.statistic# = n.statistic#   
  9. order by n.class, n.statistic#  

3）根据sid查看对应连接正在运行的sql
Java代码
  1. select   
  2.  command_type,   
  3.  sql_text,   
  4.  sharable_mem,   
  5.  persistent_mem,   
  6.  runtime_mem,   
  7.  sorts,   
  8.  version_count,   
  9.  loaded_versions,   
  10.  open_versions,   
  11.  users_opening,   
  12.  executions,   
  13.  users_executing,   
  14.  loads,   
  15.  first_load_time,   
  16.  invalidations,   
  17.  parse_calls,   
  18.  disk_reads,   
  19.  buffer_gets,   
  20.  rows_processed,   
  21.  sysdate start_time,   
  22.  sysdate finish_time,   
  23.  '>' || address sql_address,   
  24.  'N' status   
  25. from v$sqlarea   
  26. where address = (select sql_address from v$session where sid = 71)  


27．查询表空间使用情况
Java代码
  1. select a.tablespace_name "表空间名称",   
  2. 100-round((nvl(b.bytes_free,0)/a.bytes_alloc)*100,2) "占用率(%)",   
  3. round(a.bytes_alloc/1024/1024,2) "容量(M)",   
  4. round(nvl(b.bytes_free,0)/1024/1024,2) "空闲(M)",   
  5. round((a.bytes_alloc-nvl(b.bytes_free,0))/1024/1024,2) "使用(M)",   
  6. Largest "最大扩展段(M)",   
  7. to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "采样时间"  
  8. from  (select f.tablespace_name,   
  9.    sum(f.bytes) bytes_alloc,   
  10.    sum(decode(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes)) maxbytes   
  11. from dba_data_files f   
  12. group by tablespace_name) a,   
  13. (select  f.tablespace_name,   
  14.     sum(f.bytes) bytes_free   
  15. from dba_free_space f   
  16. group by tablespace_name) b,   
  17. (select round(max(ff.length)*16/1024,2) Largest,   
  18.    ts.name tablespace_name   
  19. from sys.fet$ ff, sys.file$ tf,sys.ts$ ts   
  20. where ts.ts#=ff.ts# and ff.file#=tf.relfile# and ts.ts#=tf.ts#   
  21. group by ts.name, tf.blocks) c   
  22. where a.tablespace_name = b.tablespace_name and a.tablespace_name = c.tablespace_name  


28. 查询表空间的碎片程度

Java代码
  1. select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name   
  2. having count(tablespace_name)>10;   
  3.   
  4. alter tablespace name coalesce;   
  5. alter table name deallocate unused;   
  6.   
  7. create or replace view ts_blocks_v as   
  8. select tablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_space   
  9. union all   
  10. select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents;   
  11.   
  12. select * from ts_blocks_v;   
  13.   
  14. select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space   
  15. group by tablespace_name;  


29。查询有哪些数据库实例在运行
Java代码
  1. select inst_name from v$active_instances;  


30. 查找oracle性能瓶颈sql

Java代码
  1. select sql_text,spid,v$session.program,process from   
  2. v$sqlarea,v$session,v$process   
  3. where v$sqlarea.address=v$session.sql_address   
  4. and v$sqlarea.hash_value=v$session.sql_hash_value   
  5. and v$session.paddr=v$process.addr   
  6. and v$process.spid in (操作系统PID);   
  7.   
  8.   
  9. select sid,event,p1,p1text from v$session_wait;  


31. 找出最耗资源的sql

Java代码
  1. select * from v$process where spid='2796';   
  2.   
  3. select sql_hash_value,machine,username,program from v$session where PAddr='63B7A584';   
  4.   
  5. select * from v$sqltext where hash_value='833203018';   
  6.   
  7. select * from v$sql where hash_value='833203018';   
  8.   
  9. select * from v$sqlarea where hash_value='833203018';  


Java代码
  1. SELECT a.username,a.machine,a.program,a.sid,a.serial#,a.status,c.piece,c.sql_text FROM v$session a,v$process b,v$sqltext c WHERE b.spid='2796' AND b.addr=a.paddr AND a.sql_address=c.address(+) ORDER BY c.piece  
要找出最耗资源的sql，我们可以首先使用top等工具，找到最好资源的进程（记住进程号），例如，操作系统进程号为2796，然后根据这个进程号(v$process.spid)在v$process中找到进程地址(v$process.addr)，然后根据这个地址在 v$session中找到相应的sid(v$session.sid)，然后根据这个sid找到相应的hash alue（v$session. sql_hash_value），然后根据这个hash alue在v$sqltext，$sql，v$sqlarea等视图中找到对应的sql语句（sql_text）。

Java代码
  1. select * from v$process where spid='2796';   
  2.   
  3. select sql_hash_value,machine,username,program from v$session where PAddr='63B7A584';   
  4.   
  5. select * from v$sqltext where hash_value='833203018';   
  6.   
  7. select * from v$sql where hash_value='833203018';   
  8.   
  9. select * from v$sqlarea where hash_value='833203018';   
  10.   
  11. SELECT a.username,a.machine,a.program,a.sid,a.serial#,a.status,c.piece,c.sql_text FROM v$session a,v$process b,v$sqltext c WHERE b.spid='2796' AND b.addr=a.paddr AND a.sql_address=c.address(+) ORDER BY c.piece  
posted @ 2010-07-07 10:47  chinaifne  阅读(616)  评论(0)  编辑  收藏  举报