查看剩余表空间
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.osuser 操作系统用户,
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
#p#副标题#e#
查看回滚段是否回退结束
select used_ublk,used_urec from v$transaction a,v$session b where a.ses_addr=b.saddr and b.sid=442;
select a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk from v$session a, v$transaction b where a.saddr=b.ses_addr and a.sid=''
select distinct /*+ index_ffs(c,pk_auto) parallel_index_
(automobile, pk_auto) color, count(*)
from
automobiles
group by color;
锁等待
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request;
SELECT /*+index(b,IDX_YHDA_NEW_YHBH)*/ (强制索引)
a.yhbh,v_rent.product_id,b.dhhm,NVL(ktrq,TO_DATE('19000101','YYYYMMDD')),TO_DATE('20500101','YYYYMMDD'),b.xq
FROM mcm_tyt_yhtf_cur a,mcm_tyt_yhda_new b
WHERE TO_CHAR(a.yhbh)=(b.yhbh)
AND b.jz=v_rent.jz
AND a.tf=v_rent.tf
AND a.xq=v_rent.xq
AND b.xq=v_rent.xq
AND b.tch LIKE'TRYT%'
AND a.ktrq<cur_end_date;
select * from v$sqlarea a,v$session b where a.address=b.sql_address AND a.sql_text like
表空间不能扩展的表
select tablespace_name tablespace,
table_name table_name,
next_extent next
from dba_tables outer
where not exists (select 'x'
from sys.dba_free_space inner
where outer.tablespace_name = inner.tablespace_name
and bytes>=next_extent)
select 'alter system kill session '||''''||sid||','||serial#||''''||';' from v$session a,v$sqlarea b where a.sql_address=b.address and b.sql_text like '%REAL_FEE%' AND A.USERNAME in ('ZJLT','SZMCM')
--AND A.STATUS='INACTIVE'
select 'ALTER SYSTEM KILL SESSION'||''''||SID||','||SERIAL#||''''||';' from v$session A where status='INACTIVE' AND A.OSUSER='Administrator'
select * from v$process d,v$session e where d.addr=e.paddr and sid
in
(select sid from
v$session a,v$sqlarea b where a.sql_address=b.address and b.sql_text like
'%REAL_FEE%' AND A.USERNAME in ('ZJLT','SZMCM')
AND A.STATUS='INACTIVE')
--latch
select
c.name,a.addr,a.gets,a.misses,a.sleeps, a.immediate_gets,a.immediate_misses,b.pid
from v$latch a, v$latchholder b, v$latchname c
where
a.addr = b.laddr(+) and a.latch# = c.latch#
order by a.latch#;
select
name
from
v$latchname a, v$latch b
where
b.addr = '&addr' and b.latch#=a.latch#;
select
c.name,a.addr,a.gets,a.misses,a.sleeps,a.immediate_gets,
a.immediate_misses,b.pid
from
v$latch a, v$latchholder b, v$latchname c
where
a.addr = b.laddr(+) and a.latch# = c.latch# and c.name like '&latch_name%'
order by a.latch#;
#p#副标题#e#
--查锁资源
select a.sid,
decode(a.type,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
a.type) lock_type,
decode(a.lmode,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
to_char(a.lmode)) mode_held,
decode(a.request,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', &
关键词标签：Oracle常用脚本
相关阅读
热门文章
Oracle中使用alter table来增加,删除,修改列
oracle中使用SQL语句修改字段类型-oracle修
使用低权限Oracle数据库账户得到管理员权限
Oracle对user的访问控制
人气排行 ORACLE SQL 判断字符串是否为数字的语句Oracle中使用alter table来增加,删除,修改列的语法ORACLE和SQL语法区别归纳(1)oracle grant 授权语句如何加速Oracle大批量数据处理Oracle删除表的几种方法ORACLE修改IP地址后如何能够使用Oracle 10g创建表空间和用户并指定权限
查看所有0条评论>>