Oracle常用指令碼記錄,方便以後使用

Oracle常用指令碼記錄,方便以後使用:

lock:check lock info
col spid format a10
col inst_id format a7
col owner format a15
col object_name format a20
col machine format a30
col program format a20
select pr.spid,se.inst_id,se.sid,se.serial#,ob.owner,ob.object_name,se.osuser,se.machine,se.terminal,se.program
,gvl1.ctime,(select count(*)-1 from gv$lock  where inst_id=gvl2.inst_id and id1=gvl2.id1) as blocking_session,
trunc(gvl2.id1/power(2,16)) xidusn,bitand(gvl2.id1,to_number('ffff','xxxx')) xidslot,gvl2.id2 xidsqn
from gv$lock gvl1,gv$session se,dba_objects ob ,gv$process pr,
(select * from gv$lock where block=1) gvl2
where gvl1.inst_id=gvl2.inst_id and gvl1.sid=gvl2.sid and gvl1.type='TM'
and gvl1.inst_id=se.inst_id and gvl1.sid=se.sid and gvl1.id1=ob.object_id
and se.paddr=pr.addr;


actsess:check active session
select count(*) count from v$session where status='ACTIVE';


sp:check tablespace usage info
set pagesize 100
set timing on
select * from
(select x.tablespace_name,x.contents,x.status,x.extent_management,x.all_bytes/1024/1024 "ALL_SIZE(MB)",y.free_bytes/1024/1024 "FREE_SIZE(MB)",
(x.all_bytes-y.free_bytes)/1024/1024 "USED_SIZE(MB)",round((x.all_bytes-y.free_bytes)/x.all_bytes,4)*100 USAGE
from (select ts.tablespace_name,ts.contents,ts.status,ts.extent_management,sum(bytes) all_bytes from
dba_tablespaces ts, dba_data_files dbf
where ts.tablespace_name=dbf.tablespace_name group by ts.tablespace_name,ts.contents,ts.status,ts.extent_management) x,
(select tablespace_name,sum(bytes) free_bytes from dba_free_space fs group by tablespace_name) y
where x.tablespace_name=y.tablespace_name
union all
select h.tablespace_name,'TEMPORARY' contents,f.status,'LOCAL',sum(h.bytes_free+h.bytes_used)/1024/1024 "ALL_SIZE(MB)",sum((h.bytes_free+h.bytes_used-nvl(p.bytes_used,0)))/1024/1024 "FREE_SIZE(MB)",
sum(p.bytes_used)/1024/1024 "USED_SIZE(MB)",round(sum(p.bytes_used)/sum(h.bytes_free+h.bytes_used),4)*100 USAGE
from v$temp_space_header h,v$temp_extent_pool p,dba_temp_files f
where h.file_id=p.file_id(+)  and h.file_id=f.file_id
group by h.tablespace_name,f.status)
order by 8 desc;
select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') systime from dual;


yccs:select hide parameter info
set linesize 160
col ksppinm format a40
col ksppstvl format a30
col ksppdesc format a190
SELECT ksppinm, ksppstvl, ksppdesc
  FROM x$ksppi x, x$ksppcv y
 WHERE x.indx = y.indx
   AND ksppinm like '%&value%';


tsp:check temp tablespace usage
select y.name,y.bytes/1024/1024 total_mb,(x.total_blocks*y.block_size)/1024/1024 used_mb,(y.bytes-x.total_blocks*y.block_size)/1024/1024 free_mb,
round((x.total_blocks*y.block_size)/y.bytes,4)*100 pct
from v$sort_segment x,(
select b.name,sum(a.bytes) bytes,a.block_size from v$tempfile a,v$tablespace b
where a.ts#=b.ts# group by b.name,a.block_size) y
where x.tablespace_name=y.name;