--查询数据库表空间
SELECT /*+parallel (f,4)*/ UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB/1024 " 表空间大小 (g) ",
(D.TOT_GROOTTE_MB - F.TOTAL_BYTES)/1024 " 已使用空间 (g) ",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100 , 2 ), '990.99' ) " 使用比 ",
F.TOTAL_BYTES " 空闲空间 (M) ",
F.MAX_BYTES " 最大块 (M) "
FROM
(SELECT TABLESPACE_NAME,
ROUND(SUM (BYTES)/( 1024 * 1024 ), 2 ) TOTAL_BYTES,
ROUND(MAX (BYTES)/( 1024 * 1024 ), 2 ) MAX_BYTES
FROM SYS .DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME, ROUND(SUM (DD.BYTES)/( 1024 * 1024 ), 2 ) TOT_GROOTTE_MB
FROM SYS .DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
--and F.TABLESPACE_NAME='TEMP'--'UNDOTBS1'
--and (F.TABLESPACE_NAME like 'DATA_SUM_SJZ%' or F.TABLESPACE_NAME like 'DATA_IODATA_SJZ_%')
ORDER BY 4 DESC ;
-- 查询锁表及解锁
select ' alter system kill session '''|| t2.sid||','||t2.serial#||''' ;' kq,
t2.username as oracle用户名 ,
t2.sid 进程号, t2.serial# 序列号,
t3.object_name 表名,
t2.OSUSER 操作系统用户名,t2.MACHINE 机器名,t2.PROGRAM 操作工具,t2.LOGON_TIME 登陆时间 ,t2.COMMAND,
t2.LOCKWAIT, /*表示当前这张表是否正在等待其他用户解锁这张表*/
t2.SADDR,t2.PADDR,t2.TADDR,t2.SQL_ADDRESS,
t1.LOCKED_MODE 锁表模式
from v$locked_object t1, v$session t2, dba_objects t3
where t1.session_id = t2.sid
and t1.object_id = t3.object_id
order by t2.logon_time;
alter system kill session '7897,23' ;
-- 单表查询大小
SELECT SUM(MB) FROM (
select OWNER,
t.segment_name,
t.segment_type,
sum(t.bytes / 1024 / 1024) MB
from dba_segments t
where t.owner= '用户'
and t.segment_type IN ( 'TABLE','INDEX')
and (t.segment_name in upper( '表名')
or t.segment_name in (select index_name from all_ind_columns where index_owner in('用户') and table_name in upper('表名') ))
group by OWNER, t.segment_name, t.segment_type
order by MB desc ) T;
-- 查询登录的节点
select * from v$instance;
-- 被锁项目
select * from v$locked_object;
-- ddl锁查询
select * from dba_ddl_locks where owner='用户'
-- 会话查询;
select * from v$session where username='用户'
-- 查询被锁会话进程
select sid,serial#,s.sql_id,sql_text,program
from v$session s,v$sql l where sid in( 754) and s.sql_id=l.sql_id
select * from v$access where owner='用户' and OBJECT='目标';
-- 集群
select * from gv$access where owner like '用户%' and OBJECT='目标';
select distinct A.owner, ' alter system kill session '''||s.sid||','||s.serial#||',@'||S.INST_ID||''' ;' k
,s.sid,s.serial#,s.sql_id,sql_text,program -- ,S.*
from gv$session s,gv$sql l
, gv$access A
where S.sid =A.SID
and s.sql_id=l.sql_id
AND L.PARSING_SCHEMA_NAME=S.USERNAME
AND S.USERNAME=A.OWNER
AND A.owner =upper( '用户')
alter system kill session '759,16957,@2' ;
SELECT * FROM gv$session s;
SELECT * FROM gv$sql l
SELECT * FROM gv$access A
v$locked_object 视图中记录了所有session中的所有被锁定的对象信息。
v$session 视图记录了所有session的相关信息。
dba_objects 为oracle用户对象及系统对象的集合,通过关联这张表能够获取被锁定对象的详细信息。
v$locked_object中的LOCKED_MODE字段表示锁的模式,oracle中锁的模式有如下几种:
0:none
1:null 空
2:Row-S 行共享(RS):共享表锁,sub share
3:Row-X 行独占(RX):用于行的修改,sub exclusive
4:Share 共享锁(S):阻止其他DML操作,share
5:S/Row-X 共享行独占(SRX):阻止其他事务操作,share/sub exclusive
6:exclusive 独占(X):独立访问使用,exclusive
数字越大锁级别越高, 影响的操作越多