資料庫解鎖 ---011

SELECT * FROM v$session WHERE username IS NOT NULL AND lockwait IS NOT NULL

SELECT * FROM dba_ddl_locks WHERE mode_held='Share'

SELECT * FROM v$locked_object

SELECT * FROM v$session WHERE sid IN('12')

SELECT * FROM v$process WHERE addr IN(
SELECT paddr FROM v$session WHERE sid IN('12'))

select s.sid,s.value/100/60
from v$sesstat s,v$statname n
where s.statistic#=n.statistic# and n.name='CPU used by this session'
and s.value>0
order by 2 desc;

SELECT sql_text FROM v$sqltext_with_newlines WHERE (hash_value,address)
     IN (SELECT sql_hash_value,sql_address FROM v$session WHERE sid IN(SELECT ses.sid FROM v$session ses,v$process pro WHERE pro.spid='20066' AND ses.paddr=pro.addr)
  ) ORDER BY address,piece
 
SELECT sql_text FROM v$sqltext_with_newlines WHERE (hash_value,address) IN
(SELECT sql_hash_value,sql_address FROM v$session WHERE username='SFIS1')
 ORDER BY address,piece; 
 
 SELECT * FROM v$sqltext_with_newlines  where hash_value='0'  and address='00'
 
 select sql_hash_value,sql_address from gv$session  where sid='838'
 
 SELECT sid,serial#,terminal,program,sql_text FROM v$sqltext_with_newlines a,v$session b
 WHERE a.hash_value=b.sql_hash_value AND a.address=b.sql_address AND b.username='SFIS1' AND TERMINAL='FOXCONN-JACKEN'
  ORDER BY address,piece;
 
 sid in(SELECT SESSION_ID FROM v$locked_object)--b.username='SFIS1' --AND TERMINAL='FOXCONN-JACKEN'
 ORDER BY address,piece;

ALTER SYSTEM KILL SESSION '271,1616';

SELECT 'ALTER SYSTEM KILL SESSION'||''''||sid||','||SERIAL#||''''||';'  FROM gv$session --WHERE username IS NOT NULL AND lockwait IS NOT NULL
where INST_ID=2
and PROGRAM='CtoAppSfc.exe'

SELECT * FROM V$LOCKED_OBJECT A,ALL_OBJECTS B   WHERE A.OBJECT_ID=B.OBJECT_ID

select  osuser, username, sql_text from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;

SELECT * FROM v$process WHERE addr IN(
SELECT addr FROM v$process MINUS SELECT paddr FROM v$session)

KILL -9 spid

SELECT * FROM dba_objects WHERE status='INVALID'

SELECT 'alter '||DECODE(object_type,'PACKAGE BODY','PACKAGE',object_type)||' '||owner||'."'||object_name||'" compile'||DECODE(object_type,'PACKAGE BODY',' BODY','PACKAGE',' PACKAGE','')||';'  dd FROM dba_objects WHERE object_type<>'SYNONYM' and status='INVALID'

ALTER PACKAGE SYS.DBMS_AQADM_SYS COMPILE BODY;
ALTER PACKAGE SYS.DBMSOBJG COMPILE PACKAGE;
ALTER PROCEDURE SFIS2.Nb_Tag_Update COMPILE;
ALTER FUNCTION SFIS1.GET_CHECKCODE COMPILE;
gerry.sjt f3408988

select tablespace_name, count(*) chunks , max(bytes/1024/1024) max_chunk  from dba_free_space
group by tablespace_name;

select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name having count(tablespace_name)>100

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);

alter tablespace I_SFISM4_DATA_01 coalesce;

alter table   deallocate unused;

SELECT inst_id , instance_number inst_no, instance_name inst_name, parallel, status, database_status db_status, active_state state, host_name host
FROM gv$instance
ORDER BY inst_id;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25850100/viewspace-703054/，如需轉載，請註明出處，否則將追究法律責任。