首页 > Linux操作系统 > Linux操作系统 > 数据库解锁 ---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/,如需转载,请注明出处,否则将追究法律责任。