/////////////查询所有DDL操作造成的表锁和行锁////////////////
col TERMINAL for a15
col OSUSER for a15
col MACHINE for a25
col SCHEMANAME for a15
col USERNAME for a15
SELECT s.sid,
s.serial#,
s.username,
s.schemaname,
s.osuser,
s.process,
s.machine,
s.terminal,
s.logon_time,
l.type
FROM v$session s, v$lock l
WHERE s.sid = l.sid
AND s.username IS NOT NULL
ORDER BY sid;
///////////////查看被锁的表//////////////////////////////////
select b.owner,
b.object_name,
a.session_id,
a.locked_mode
from v$locked_object a,dba_objects b
where b.object_id = a.object_id;
////////////////////查看锁表的用户和进程SID////////////////
select b.username,
b.sid,
b.serial#,
logon_time
from v$locked_object a,v$session b
where a.session_id = b.sid
order by b.logon_time;
//////////////////解锁//////////////////////////////////////////
alter system kill session 'SID,serial#';
///////////用户发起的活跃session////////////////////////////
select SID,
SQL_ID,
LAST_CALL_ET,
STATUS,
event,
SECONDS_IN_WAIT,
state,
BLOCKING_SESSION
from v$session
where sid = '130';
////////////////////////看系统spid////////////////////////////
set linesize 200
set pagesize 1000
col event for a35
col cd_ for 999
col state for a10
select a.inst_id,a.sid,a.sql_id,a.sql_child_number cd_,
a.last_call_et,a.state,a.status,a.event,b.spid,a.blocking_session
from gv$session a,gv$process b
where a.paddr=b.addr
and sid=130
order by a.inst_id,a.last_call_et;
//////////////////查询卡慢///////////////////////////////////
set linesize 200
set pagesize 1000
col event for a35
col cd_ for 999
col state for a10
col SQL_ID for a15
select inst_id,
sid,
sql_id,
sql_child_number cd_,
last_call_et,
state,
status,
event,
blocking_session
from gv$session
where status = 'ACTIVE'
and type = 'USER'
order by inst_id, last_call_et;
///////////////////////查sql最后执行时间/////////////////////////
SELECT SQL_TEXT,
LAST_ACTIVE_TIME,
SQL_FULLTEXT
FROM v$sql where sql_id='xxxxxxxxx'
ORDER BY LAST_ACTIVE_TIME DESC;
/////////////////////查看表空间使用率///////////////////////////
set linesize 200
set pages 100
col TNAME for a50
select a.tablespace_name TNAME,TOTAL/1024/1024 "TOTAL(MB)",FREE/1024/1024 "FREE(MB)",MAXBYTES/1024/1024 "MAXBYTE(MB)",round((total-free)/total*100,0) "PERCENT(%)"
from (select tablespace_name,sum(bytes) free
from dba_free_space
group by tablespace_name) a,
(select tablespace_name,sum(bytes) total
from dba_data_files
group by tablespace_name) b,
(select tablespace_name,sum(maxbytes) maxbytes
from dba_data_files
group by tablespace_name) c
where a.tablespace_name = b.tablespace_name
and a.tablespace_name = c.tablespace_name
/
//////////////////////////查看自动扩展信息//////////////////////
select tablespace_name,AUTOEXTENSIBLE,INCREMENT_BY from dba_data_files group by tablespace_name,INCREMENT_BY,AUTOEXTENSIBLE;
///////////////////////////查看数据文件位置/////////////////////
select TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 m,MAXBYTES/1024/1024 m from dba_data_files;
/////////////////////Oracle RAC使用ASM磁盘组////////////////
su - grid
asmcmd
lsdg //查看ASM剩余空间
//////////////////////扩展表空间///////////////////////////////////
alter tablespace TEXT add datafile '+DATA' size 1G AUTOEXTEND ON NEXT 128M; //扩展表空间
