select a.tablespace_name,free/total*100 pct_free,free/1024/1024 "free(M)" from
(select sum(bytes) free ,tablespace_name from dba_free_space group by tablespace_name) a,
(select sum(bytes) total ,tablespace_name from dba_data_files group by tablespace_name) b
where a.tablespace_name=b.tablespace_name
order by pct_free;
DROP TABLESPACE temp1 INCLUDING CONTENTS AND DATAFILES;
日常维护工作中,时常会碰到数据出错的情况.
一般有:锁表,空间不够,表无法扩展,数据库被某个写的很烂的sql占用很大的资源等情况.
一下是一些经常要用的sql脚本.希望对大家有帮助.
(不过这个可不是我整理出来的)
---增加临时表空间大小
alter temporary tablespace temp add tempfile '/opt/oracle/oradata/ora9/temp10.dbf' size 1000M;
--查看表的字录条数
select 'select count(1) from '||tname||';' from tab where tname not like '%BIN%'
--回滚段监视
select n.usn 回滚段标识,
n.NAME 回滚段名称,
s.Username 用户名,
s.sid 会话ID,
rs.EXTENTS 回滚段扩展次数,
rs.wraps,
rs.rssize/1024/1024 "使用空间(MBytes)",
rs.status 回滚段状态
from v$rollname n, v$rollstat rs, v$session s, v$transaction t
where t.addr = s.taddr(+)
and rs.usn(+) = n.usn
and t.xidusn(+) = n.usn
/*and rs.status = 'ONLINE'*/
order by rs.rssize
--回滚段块事务查询
select s.sid,s.serial#,t.start_time,t.xidusn,s.username
from v$session s,v$transaction t,v$rollstat r
where s.saddr=t.ses_addr
and t.xidusn=r.usn
and ((r.curext=t.start_uext-1) or
((r.curext=r.extents-1) and t.start_uext=0));
--锁监视
SELECT b.os_user_name 操作系统用户,
b.oracle_username ORACLE用户,
b.session_id 会话ID,
b.process 进程号,
a.object_name 对象名,
a.subobject_name 子对象名,
d.machine 客户端机器,
d.lockwait 锁等待,
d.status 会话状态,
d.schemaname 数据库对象名称,
d.terminal 终端名,
d.program 终端程序名,
d.logon_time 登陆时间
FROM dba_objects a,v$locked_object b,v$session d
--,v$lock c
WHERE a.object_id=b.object_id
AND b.session_id=d.sid
select a.username, a.sid, a.serial#, b.id1
from v$session a, v$lock b
where a.lockwait = b.kaddr
select a.username, a.sid, a.serial#, b.id1
from v$session a, v$lock b
where b.id1 in
(select distinct e.id1
from v$session d, v$lock e
where d.lockwait = e.kaddr)
and a.sid = b.sid
and b.request = 0