Oracle 第三天 DBA基本操作

Oracle DBA基本操作

--查看所有用户的连接数
select username,count(username) from v$session where username is not null group by username;

--查看当前的连接会话数
select count(*) from v$session;

--查看当前的连接进程数
select count(*) from v$process;

--数据库允许的最大连接数
select value from v$parameter where name = 'processes'

--并发连接数
select count(*) from v$session where status='ACTIVE'　

--查看当前的会话
select username,sid,serial#,paddr,status from v$session;

--使用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 tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name

--查看未使用的表空间
select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name

--查看表空间的汇总信息
select a.tablespace_name,total,free,total-free used from
( select tablespace_name,sum(bytes)/1024/1024 total from dba_data_files
   group by tablespace_name) a,
( select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space
   group by tablespace_name) b
where a.tablespace_name=b.tablespace_name;

---------------------------------------------------------
--PGA内存
---------------------------------------------------------
--查看PGA
show parameter pga;

--查看PGA的命中率
select value "命中率" from V$pgastat  where name = 'cache hit percentage';

--查看PGA的命中率
select (p.PGA_TARGET_FOR_ESTIMATE)/1024/1024,p.ESTD_PGA_CACHE_HIT_PERCENTAGE
from v$pga_target_advice p


--查看PGA使用情况
select spid,program,pga_max_mem,pga_alloc_mem,pga_used_mem,pga_freeable_mem
from v$process
where spid in (select spid from v$process where addr in
(select paddr from v$session where sid in
(select distinct sid from v$mystat)));


---------------------------------------------------------
--SGA内存
---------------------------------------------------------

--查看SGA
show parameter sga;
show parameter shared_pool_size;

--查看SGA
select * from v$sgastat;


--查看共享SQL区的使用率
select(sum(pins-reloads))/sum(pins) "Library cache" from v$librarycache;

--查看数据字典缓冲区的使用率
select (sum(gets-getmisses-usage-fixed))/sum(gets) "Data dictionary cache" from v$rowcache;



---------------------------------------------------------
--其它
---------------------------------------------------------
--查看事件等待
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 segment_name table_name , COUNT(*) extents
FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name
HAVING COUNT(*) = (SELECT MAX( COUNT(*)) FROM dba_segments GROUP BY segment_name);