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

最新推荐文章于 2024-09-05 10:00:22 发布

weixin_33950035

最新推荐文章于 2024-09-05 10:00:22 发布

阅读量90

点赞数

文章标签：数据库操作系统

原文链接：http://blog.51cto.com/dbpath/83603

版权

查看剩余表空间
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

查看回滚段是否回退结束
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#;

--查锁资源
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)',     /* L */
   3, 'Row-X (SX)',     /* R */
   4, 'Share',          /* S */
   5, 'S/Row-X (SSX)', /* C */
   6, 'Exclusive',      /* X */
   to_char(a.request)) mode_requested,
   to_char(a.id1) lock_id1, to_char(a.id2) lock_id2
from v$lock a
   where (id1,id2) in
     (select b.id1, b.id2 from v$lock b where b.id1=a.id1 and
     b.id2=a.id2 and b.request>0)

select b.username username, c.sid sid, c.owner object_owner,
   c.object object, b.lockwait, a.sql_text SQL
   from v$sqltext a, v$session b, v$access c
     where a.address=b.sql_address and
     a.hash_value=b.sql_hash_value and
     b.sid = c.sid and c.owner != 'SYS'

select substr(s.username,1,11) "USER", p.pid "PROCESS ID",
   s.sid "SESSION ID", s.serial#, osuser "OS USER", p.spid "PROC SPID",
   s.process "SESS SPID", s.lockwait "LOCK WAIT"
from v$process p, v$session s, v$access a
where a.sid=s.sid and
   p.addr=s.paddr and
   s.username != 'SYS'

SELECT /*+index(b SYS_C0011621) index(a IDX_UNIQUE_1)*/
NVL(SUM(A.PRE_CHARGE
),0),NVL(SUM(A.CHARGE),0),:b1,:b2,NVL(A.ACCT_ID,:b2 || '-999' ),
A.SERV_ID,B.ACCT_ITEM_ID FROM BILL_SERV_ACCT A,ACCT_SERV_ITEM_RELATION B WHERE A.SERV_ITEM_ID = B.SERV_ITEM_ID AND A.AREA_ID
= :b2 GROUP BY A.ACCT_ID,A.SERV_ID,B.ACCT_ITEM_ID

碎片检查
    select tablespace_name,sqrt(max(blocks)/sum(blocks))*
           (100/sqrt(sqrt(count(blocks)))) fsfi
    from dba_free_space
    group by tablespace_name order by 2;

fsfi值越小,碎片越大自由空间碎片索引

检查reverse_key index
select o.object_name
from dba_objects o
where wner='DB_ACCT'
AND O.OBJECT_ID IN
(SELECT I.OBJ# FROM SYS.IND$ I
WHERE BITAND(I.PROPERTY,4)=4)
查具体后台进程号
select spid from v$session a ,v$process b where a.PADDR=b.ADDR and sid=''

查看死锁表
SELECT SID, DECODE(BLOCK, 0, 'NO', 'YES' ) BLOCKER,
DECODE(REQUEST, 0, 'NO','YES' ) WAITER
FROM V$LOCK
WHERE REQUEST > 0 OR BLOCK > 0
ORDER BY block DESC;

查看缴费到帐
SELECT AREA_ID,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(count(1)) as cnt FROM ACCT_PAY_INTERFACE
WHERE PAY_DATE>=sysdate-1 and FLAG='0' group by AREA_Id

查看最消耗资源的sql
SELECT * FROM 　(SELECT PARSING_USER_ID　EXECUTIONS,SORTS,COMMAND_TYPE,
　　DISK_READS,　sql_text FROM v$sqlarea　ORDER BY disk_reads DESC ) WHERE ROWNUM<10

查看占用系统资源的进程号spid
SELECT a.username,a.machine,a.program,a.sid,a.serial#,a.status,c.piece,c.sql_text
　　FROM v$session a,v$process b,v$sqltext c　WHERE b.spid=''　AND b.addr=a.paddr　AND a.sql_address=c.address(+)
　　ORDER BY c.piece

查看占用系统io较大的session
SELECT se.sid,se.serial#,pr.SPID,se.username,se.status,se.terminal,se.program,
　se.MODULE,se.sql_address,st.event,st.p1text,si.physical_reads,si.block_changes
　FROM v$session se,　v$session_wait st,v$sess_io si,v$process pr WHERE st.sid=se.sid 　AND st.sid=si.sid
AND se.PADDR=pr.ADDR AND se.sid>6　AND st.wait_time=0 AND st.event NOT LIKE '%SQL%' ORDER BY physical_reads DESC
对检索出的结果的几点说明：

　　1、我是按每个正在等待的session已经发生的物理读排的序，因为它与实际的IO相关。
　　2、你可以看一下这些等待的进程都在忙什么，语句是否合理？
　　Select sql_address from v$session where sid=;
　　Select * from v$sqltext where address=;
　　执行以上两个语句便可以得到这个session的语句。
　　你也以用alter system kill session 'sid,serial#';把这个session杀掉。
　　3、应观注一下event这列，这是我们调优的关键一列，下面对常出现的event做以简要的说明：
a、buffer busy waits，free buffer waits这两个参数所标识是dbwr是否够用的问题，与IO很大相关的，当v$session_wait中的free buffer wait的条目很小或没有的时侯，说明你的系统的dbwr进程决对够用，不用调整；free buffer wait的条目很多，你的系统感觉起来一定很慢，这时说明你的dbwr已经不够用了，它产生的wio已经成为你的数据库性能的瓶颈，这时的解决办法如下：
　　
a.1增加写进程，同时要调整db_block_lru_latches参数
　　示例：修改或添加如下两个参数
　　db_writer_processes=4
　　db_block_lru_latches=8
　　a、2开异步IO，IBM这方面简单得多，hp则麻烦一些，可以与Hp工程师联系。
　　b、db file sequential read，指的是顺序读，即全表扫描，这也是我们应该尽量减少的部分，解决方法就是使用索引、sql调优，同时可以增大db_file_multiblock_read_count这个参数。
　　c、db file scattered read,这个参数指的是通过索引来读取，同样可以通过增加db_file_multiblock_read_count这个参数来提高性能。
　　d、latch free,与栓相关的了，需要专门调节。
　　e、其他参数可以不特别观注

外部联接"+"的用法
---- 外部联接"+"按其在"="的左边或右边分左联接和右联接.
若不带"+"运算符的表中的一个行不直接匹配于带"+"预算符的表中的任何行,
则前者的行与后者中的一个空行相匹配并被返回.若二者均不带’+’
则二者中无法匹配的均被返回.利用外部联接"+"
可以替代效率十分低下的 not in 运算,大大提高运行速度.例如,下面这条命令执行起来很慢
select a.empno from emp a where a.empno not in
(select empno from emp1 where job=’SALE’);
---- 倘若利用外部联接,改写命令如下:
select a.empno from emp a ,emp1 b
where a.empno=b.empno(+)
and b.empno is null
and b.job=’SALE’;
---- 可以发现,运行速度明显提高

如何更改UNDO tablespace
create undo tablespace undotbs2 datafile 'D:\oracle\product\10.2.0\oradata\qa\undotbs2.dbf' size 40M;
alter system set undo_tablespace=undotbs2 scope=both;
create pfile from spfile;
alter tablespace undotbs1 offline;
drop tablespace undotbs1 including contents;

----将表改成
ALTER TABLE t_monitor_real_minute NOLOGGING;

Oracle RAC的参数文件和单实例参数文件不同，所以修改参数文件时需要注意。

首先设置归档路径：

SQL> alter system set log_archive_dest='/opt/oracle/archive' scope=spfile sid='*';

System altered.
SQL> select sid,name,value from v$spparameter where name='log_archive_dest';

SID NAME VALUE
---------- -------------------- ------------------------------
* log_archive_dest /opt/oracle/archive
然后关闭两个实例，启动实例，更改数据库为归档模式：

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1258291200 bytes
Fixed Size                  1978336 bytes
Variable Size             352325664 bytes
Database Buffers          889192448 bytes
Redo Buffers               14794752 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /opt/oracle/archive
Oldest online log sequence     83
Next log sequence to archive   84
Current log sequence           84

接下来启动另外一个节点，完成归档模式的变更过程。

转载于:https://blog.51cto.com/dbpath/83603

weixin_33950035

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
本人关于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 su...
复制链接

扫一扫