select ff.s tablespace_name,
ff.b total,
(ff.b - fr.b) usage,
fr.b free,
round((ff.b - fr.b) / ff.b * 100) || '%' usagep
from (select tablespace_name s, sum(bytes) / 1024 / 1024 b
from dba_data_files
group by tablespace_name) ff,
(select tablespace_name s, sum(bytes) / 1024 / 1024 b
from dba_free_space
group by tablespace_name) fr
where ff.s = fr.s
--求表空间 空间大小 使用多少空间 剩余多少空间
select username 用户,
default_tablespace 缺省表空间,
temporary_tablespace 缺省临时表空间
from dba_users
where username = '&username';
select owner 用户, sum(bytes) / 1024 / 1024 用户对象所占空间mb
from dba_extents
where owner = '&username'
group by owner;
1.找出谁锁定了什么
Select v2.username,v2.sid,v2.serial#,v2.logon_time From v$locked_object v1,v$session v2 Where v1.session_id=v2.sid order by v2.logon_time
2.结束掉这个session
alter system kill session 'sid,serial#';
ORA-00054:资源正忙,要求指定NOWAIT
找出占用资源的会话,并删除
1:找出所有被锁的对象,定位出哪个回话占用
select l.session_id,o.owner,o.object_name
from v$locked_object l,dba_objects o
where l.object_id=o.object_id
2:找出所有照成锁的会话
select t2.username,t2.sid,t2.serial#,t2.logon_time
from v$locked_object t1,v$session t2
where t1.session_id=t2.sid order by t2.logon_time;
等待其他会话释放资源
在创建语句中添加online,会话释放资源之后,该语句会自动执行。
create index sa.idx_test_1_id on sa.test_1 (id) online;
这种方式是采用阻塞方式,不报错
3:kill 所有占用资源的会话
命令形式:alter system kill session 'sid,serial#';
占用test_1的资源的会话:
alter system kill session '158,15184';
alter system kill session '146 ,8229';