当前位置:Gxlcms > mysql > 关于Oracle一些常用脚本的汇总

关于Oracle一些常用脚本的汇总

时间:2021-07-01 10:21:17 帮助过:51人阅读

查看Oracle剩余表空间 select a.tablespace_name,free/total*100 pct_free,free/1024/1024 quot;free(M)quot; from (sel

查看Oracle剩余表空间

  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

linux

人气教程排行