分类目录归档：Oracle

undo损坏恢复–无事务

发表于 2011 年 6 月 10 日 由 惜分飞

startup nomount;
create pfile=’/tmp/pfile’ from spfile;

修改pfile
*.undo_management=’MANUAL’
*.undo_tablespace=’SYSTEM’
#*.undo_tablespace=’UNDOTBS02′
#*.undo_management=’AUTO’

startup pfile=’/tmp/pfile’ mount;
alter database datafile 2 offline drop;
alter database open;
drop tablespace undotbs1 including contents;
select segment_name,status from dba_rollback_segs;
drop rollback segment “_SYSSMUx”$;
drop tablespace undotbs1 including contents;

CREATE UNDO TABLESPACE UNDOTBS02
DATAFILE ‘/opt/oracle/oradata/xifenfei/UNDOTBS02.dbf’ SIZE 50M
shutdown immediate;

修改pfile
#*.undo_management=’MANUAL’
#*.undo_tablespace=’SYSTEM’
*.undo_tablespace=’UNDOTBS02′
*.undo_management=’AUTO’

startup pfile=’/tmp/pfile’;
create spfile from pfile=’/tmp/pfile’;

发表在 Oracle备份恢复 | 评论关闭

oracle分割函数

发表于 2011 年 6 月 2 日 由 惜分飞

1、创建varry

CREATE OR REPLACE TYPE Varchar2Varray IS VARRAY(100) of VARCHAR2(40);

2、创建分割函数

CREATE OR REPLACE FUNCTION f_strsplit (STRING VARCHAR2, substring VARCHAR2)
   RETURN varchar2varray
IS
   len       INTEGER        := LENGTH (substring);
   lastpos   INTEGER        := 1 - len;
   pos       INTEGER;
   num       INTEGER;
   i         INTEGER        := 1;
   ret       varchar2varray := varchar2varray (NULL);
   v_str     VARCHAR2 (20);
 /**自定义split函数,将指定的字符串按指定的标志符分割成字符数组*/
BEGIN
   LOOP
      pos := INSTR (STRING, substring, lastpos + len);

      IF pos > 0
      THEN                                                            --found
         num := pos - (lastpos + len);
      ELSE                                                         --not found
         num := LENGTH (STRING) + 1 - (lastpos + len);
      END IF;

      IF i > ret.LAST
      THEN
         ret.EXTEND;
      END IF;

      v_str := SUBSTR (STRING, lastpos + len, num);
      --DBMS_OUTPUT.put_line (v_str);

      ret(i) := v_str;

      EXIT WHEN pos = 0;
      lastpos := pos;
      i := i + 1;
   END LOOP;

   RETURN ret;
END;

3、调用

select * from tab_dailyreport_user where user_id in (
  SELECT * FROM 
( TABLE( select f_strsplit(user_invite,',')
                        from tab_dailyreport_user t
                       where t.user_id = 168148)));

说明：主要是改写开发提过来的一个sql语句的帮助

select * from tab_dailyreport_user where user_id in (
--'166445','164216','171128','184427','160113','160133','160134','160138','160144','160163','160587','166457','167914','168076','168192','168997','169842','169901','184407','184747','185089','185130','208134','208141','208143','208183','160142','166455','167838','168074','168194','168666','185088','185138','185145','208103','169844','171071','160107','166421','166874','168193','179871','179872','184096','184228','184232','184269','184774','184969','185111','207871','160167','161813','14880','134','164355','168149')
select substr(regexp_replace,3,length(regexp_replace)-4) from (
select regexp_replace((select user_invite
                        from tab_dailyreport_user t
                       where t.user_id = 168148),
                      '(,)',
                      chr(39)||','||chr(39)) regexp_replace
  from dual) )

括号里面的语句查询出来的结果是注释部分，直接使用注释的部分在in中可以查询，如果使用里面的语句不能查询，他们的需求是想把外面的user_id在里面的语句中的，可是oracle会把里面的in查询出来的结果作为一个整体，从而出现number类型不能和varchar类型匹配的提示，采用方法是分割in里面查询出来的语句，然后类型转换为table进行查询。

发表在 Oracle 开发 | 一条评论

该关注的sql语句

发表于 2011 年 5 月 29 日 由 惜分飞

一般来说，调优的第一手资料，很可能就是典型业务期的一个statspack报告，那么如何根据statspack报告来判断是哪些SQL消耗了最多的系统资源?哪些SQL是最需要调整的呢?这里给出了一个大致的优化思路。当然，思路是死的，人是活的，优化也需要随需应变。
　　一般来说，需要关注下面四种Top SQL
　　消耗最多CPU的(逻辑IO过多)
　　导致过多物理I/O的
　　执行次数较频繁的
　　执行时间较长的
　　我们知道，一个语句的响应时间有个很著名的公式：
　　响应时间=服务时间+等待时间
　　其中服务时间就是CPU为执行该语句花费的时间。
　　服务时间=分析时间+递归时间+执行时间
　　分析时间是CPU用于分析语句的时间，递归时间是CPU用于语句的递归SQL的时间，剩下的则就是CPU用于执行语句的真正时间了。
　　那么，上面的这些时间信息从哪里来的?Oracle提供的系统统计信息中就有部分的时间统计信息：
　　服务时间=CPU used by this session
　　分析时间=parse time cpu
　　递归时间=recursive cpu usage
　　那么，执行时间就可以根据上面三个统计信息计算得出：
　　执行时间=CPU used by this session – parse time cpu – recursive cpu usage
　　如果执行时间在整个响应时间中占较大的比例，那么下一步就是找出那些造成了最多逻辑IO的SQL语句，可以从statspack报告的SQL ordered by Gets部分找到。
　　如果分析时间在整个响应时间中占较大的比例，那么下一步就是查找哪些SQL分析过多，这在statspack报告中在SQL ordered by Parse Calls中列出。
　　如果等待时间在整个响应时间中占较大的比例，并且主要是块读取相关的等待时，下一步就是找出哪些SQL造成了过多的物理读，可以查看statspack报告中的SQL ordered by Reads部分。
　　那么，根据上面列出的一个简单的原则，我们需要关注三个关于CPU时间的统计信息: CPU used by this session, parse time cpu和recursive cpu usage，以及top5等待事件中和IO相关的等待时间。如果是其他的一些等待事件出现在Top5中，那么可能需要根据不同的等待事件来分析原因了。然后优先调优时间消耗最多的相关SQL。
　　除了上面的SQL ordered by Gets(逻辑IO最多)，SQL ordered by Parse Calls(软解析过多)，SQL ordered by Reads(物理IO过多)，statspack还按照其他的一些方式列出了Top SQL，这些Top SQL在某些情况下都是需要给予特别关注的。比如：
　　SQL ordered by Executions 执行次数超过100的
　　SQL ordered by Sharable Memory 占用library cache超过1M的
　　SQL ordered by Version Count 子cursor超过20的
　　如果没有statspack，那么根据v$sysstat/v$sesstat中的统计信息，结合v$sql/v$sqlarea，一样可以得到相关的SQL。
　　v$sql对于每一个子cursor都有一行统计记录，而v$sqlarea则对同一个父cursor只有一行统计记录，也就是v$sqlarea是对v$sql按照父cursor进行group by后的一个结果。这两个视图中都有诸如buffer_gets,parse_calls,disk_reads,,executions,sharable_mem等列，和上面提到的statspack中列出Top SQL的条件对应。

发表在 Oracle | 评论关闭