数据库常用查看命令

1。查看表空间的名称及大小
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;
　　
2。查看表空间物理文件的名称及大小
select tablespace_name,file_id,file_name,round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name;
　　
3。查看回滚段名称及大小
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;
　　
4。查看控制文件
select name from v$controlfile;
　　
5。查看日志文件
select member from v$logfile;
　　
6。查看表空间的使用情况
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;
　　
7。查看数据库库对象
select owner,object_type,status,count(*) count# from all_objects group by owner,object_type,status;
　　
8。查看数据库的版本　
select version from product_component_version where substr(product,1,6)='oracle';
　　
9。查看数据库的创建日期和归档方式
select created,log_mode,log_mode from v$database;　
　　
10。捕捉运行很久的SQL
column username format a12
column opname format a16
column progress format a8
select username,sid,opname,round(sofar*100 / totalwork,0) || '%' as progress,time_remaining,sql_text from v$session_longops,v$sql where time_remaining <> 0 and sql_address = address and sql_hash_value = hash_value;
　　
11。查看数据表的参数信息
select partition_name,high_value,high_value_length,tablespace_name,pct_free,pct_used,ini_trans,max_trans,initial_extent,next_extent,min_extent,max_extent,pct_increase,freelists,freelist_groups,logging,buffer_pool,num_rows,blocks,empty_blocks,avg_space,chain_cnt,avg_row_len,sample_size,last_analyzed from dba_tab_partitions --where table_name = :tname and table_owner = :towner order by partition_position;
　　
12。查看还没提交的事务
select * from v$locked_object;
select * from v$transaction;
　　
13。查找object为哪些进程所用
select p.spid,s.sid,s.serial# serial_num,s.username user_name,a.type object_type,s.osuser os_user_name,a.owner,a.object object_name,decode(sign(48 - command),1,to_char(command),'action code #' || to_char(command)) action,p.program oracle_process,s.terminal terminal,s.program program,s.status session_status from v$session s,v$access a,v$process p where s.paddr = p.addr and s.type = 'user' and a.sid = s.sid and a.object='subscriber_attr' order by s.username,s.osuser;
　　
14。回滚段查看
select rownum,sys.dba_rollback_segs.segment_name name,v$rollstat.extents extents,v$rollstat.rssize size_in_bytes,v$rollstat.xacts xacts,v$rollstat.gets gets,v$rollstat.waits waits,v$rollstat.writes writes,sys.dba_rollback_segs.status status from v$rollstat,sys.dba_rollback_segs,v$rollname where v$rollname.name(+) = sys.dba_rollback_segs.segment_name and v$rollstat.usn (+) = v$rollname.usn order by rownum;
　　
15。耗资源的进程（top session）
select s.schemaname schema_name,decode(sign(48 - command),1,to_char(command),'action code #' || to_char(command)) action,status session_status,s.osuser os_user_name,s.sid,p.spid,s.serial# serial_num,nvl(s.username,'[oracle process]') user_name,s.terminal terminal,s.program program,st.value criteria_value from v$sesstat st,v$session s,v$process p where st.sid = s.sid and st.statistic# = to_number('38') and ('all' = 'all' or s.status = 'all') and p.addr = s.paddr order by st.value desc,p.spid asc,s.username asc,s.osuser asc;
　　
16。查看锁（lock）情况
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.object_name object, 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# 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;
　　
17。查看等待（wait）情况
select v$waitstat.class,v$waitstat.count count,sum(v$sysstat.value) sum_value from v$waitstat,v$sysstat where v$sysstat.name in ('db block gets','consistent gets') group by v$waitstat.class,v$waitstat.count;
　　
18。查看sga情况
select name,bytes from sys.v_$sgastat order by name asc;
　　
19。查看catched object
select owner,name,db_link,namespace,type,sharable_mem,loads,executions,locks,pins,kept from v$db_object_cache;
　　　　　　　　
20。查看v$sqlarea
select sql_text,sharable_mem,persistent_mem,runtime_mem,sorts,version_count,loaded_versions,open_versions,users_opening,executions,users_executing,loads,first_load_time,invalidations,parse_calls,disk_reads,buffer_gets,rows_processed from v$sqlarea;
　　
22。有关connection的相关信息
1）查看有哪些用户连接
select s.osuser os_user_name,decode(sign(48 - command),1,to_char(command),'action code #' || to_char(command) ) action,p.program oracle_process,status session_status,s.terminal terminal,s.program program,s.username user_name,s.fixed_table_sequence activity_meter,'' query,0 memory,0 max_memory,0 cpu_usage,s.sid,s.serial# serial_num from v$session s,v$process p where s.paddr=p.addr and s.type = 'user' order by s.username,s.osuser;
　　
2）根据v.sid查看对应连接的资源占用等情况
select n.name,v.value,n.class,n.statistic# from v$statname n,v$sesstat v where v.sid = 71 and v.statistic# = n.statistic# order by n.class,n.statistic#;
　　
3）根据sid查看对应连接正在运行的sql
select /*+ push_subq */ command_type,sql_text,sharable_mem,persistent_mem,runtime_mem,sorts,version_count,loaded_versions,open_versions,users_opening,executions,users_executing,loads,first_load_time,invalidations,parse_calls,disk_reads,buffer_gets,rows_processed,sysdate start_time,sysdate finish_time,'>' || address sql_address,'n' status from v$sqlarea where address = (select sql_address from v$session where sid = 71);
　　
23。查询表空间的碎片程度　
select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name having count(tablespace_name)>10;
alter tablespace name coalesce;
alter table name deallocate unused;
create or replace view ts_blocks_v as select tablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_space union all select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents;
select * from ts_blocks_v;
select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space group by tablespace_name;
　　
24。查看有哪些实例在运行：
select * from v$active_instances;
25。查看临时表空间
select file_name,tablespace_name,bytes/1024/1024 MB,autoextensible from dba_temp_files;
26。查看快存命中率
SELECT 100*((sum(pins-reloads))/sum(pins)) from v$librarycache;
27。查看数据缓存命中率
SELECT round(1-SUM(PHYSICAL_READS)/(SUM(DB_BLOCK_GETS) + SUM(CONSISTENT_GETS)), 4) * 100 FROM (SELECT CASE WHEN NAME='physical reads' THEN VALUE END PHYSICAL_READS,CASE WHEN NAME = 'db block gets' THEN VALUE END   DB_BLOCK_GETS,CASE WHEN NAME = 'consistent gets' THEN VALUE END   CONSISTENT_GETS FROM V$SYSSTAT WHERE Name IN ('physical reads','db block gets','consistent gets'));
28。查看共享缓存命中率
Select round(sum(pins-reloads)/sum(pins) * 100, 2) from v$librarycache;
29。查看字典缓存命中率
Select round(sum(gets-getmisses-usage-fixed)/sum(gets) * 100, 2) from v$rowcache;
30。查看回滚段争用
select round(sum(waits)/sum(gets) * 100, 2) from v$rollstat;
31。查看数据库连接
select machine, count(*) from V$session group by machine;
(摘自别人)