分类目录归档：Oracle

oracle修改表增加列删除列修改列

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

1.增加列

ALTER TABLE table_name ADD( column datatype [DEFAULT EXPR][,column datatype...]);

例如:

SQL>ALTER TABLE emp01 ADD eno NUMBER(4);

2.修改列定义

例如:

SQL>ALTER TABLE emp01 MODIFY job VARCHAR2(15)

2 DEFAULT ‘CLERK’

3.删除列

例如:

SQL> ALTER TABLE emp01 DROP COLUMN dno;

4.修改列名

例如:

SQL>ALTER TABLE emp01 RENAME COLUMN eno TO empno;

5.修改表名

例如:

SQL>RENAME emp01 TO employee;

6.增加注释

例如:

SQL>COMMENT ON TABLE employee IS ‘存放雇员信息’;

发表在 Oracle | 评论关闭

goldengate 异常处理

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

异常处理一（异常表通用型）
新建异常处理表

create table ogg.exception_log
    ( replicat_name varchar2(10),
      table_name varchar2(100),
      errno number,
      dberrmsg varchar2(4000),
      optype varchar2(20),
      errtype varchar2(20),
      logrba number,
      logposition number,
     committimestamp timestamp,
     primary key(logrba,logposition,committimestamp)
);

REPLICAT添加异常处理

REPERROR (DEFAULT, EXCEPTION)
REPERROR (DEFAULT2,discard)---abend根据需求
map chf.a_t_1, target chf.a_t_1;
map chf.a_t_1, target ogg.exception_log,
EXCEPTIONSONLY,
INSERTALLRECORDS,
COLMAP (   replicat_name = "repl"
, table_name = @GETENV ("GGHEADER", "TABLENAME")
, errno = @GETENV ("LASTERR", "DBERRNUM")
, dberrmsg = @GETENV ("LASTERR", "DBERRMSG")
, optype = @GETENV ("LASTERR", "OPTYPE")
, errtype = @GETENV ("LASTERR", "ERRTYPE")
, logrba = @GETENV ("GGHEADER", "LOGRBA")
, logposition = @GETENV ("GGHEADER", "LOGPOSITION")
, committimestamp = @GETENV ("GGHEADER", "COMMITTIMESTAMP"));
--实例中只处理chf.a_t_1表

异常处理二(异常表需要定制)
新建表(正常表和异常表)

--正常表
create table fei_1_1(id number , name varchar2(1000));
--异常表
create table ogg.exception_fei_1
    ( id number,
      name varchar2(1000),
      table_name varchar2(100),
      errno number,
      dberrmsg varchar2(4000),
      optype varchar2(20),
      errtype varchar2(20),
      logrba number,
      logposition number,
     committimestamp timestamp,
     primary key(logrba,logposition,committimestamp)
);

异常处理程序

map chf.fei_1, target chf.fei_1_1;
map chf.fei_1, target ogg.exception_fei_1,
EXCEPTIONSONLY,
INSERTALLRECORDS,
COLMAP ( USEDEFAULTS
, table_name = @GETENV ("GGHEADER", "TABLENAME")
, errno = @GETENV ("LASTERR", "DBERRNUM")
, dberrmsg = @GETENV ("LASTERR", "DBERRMSG")
, optype = @GETENV ("LASTERR", "OPTYPE")
, errtype = @GETENV ("LASTERR", "ERRTYPE")
, logrba = @GETENV ("GGHEADER", "LOGRBA")
, logposition = @GETENV ("GGHEADER", "LOGPOSITION")
, committimestamp = @GETENV ("GGHEADER", "COMMITTIMESTAMP"));

异常处理三（通配符MAPEXCEPTION）
新建异常表

create table ogg.exception_fei_all
    ( replicat_name varchar2(10),
      table_name varchar2(100),
      errno number,
      dberrmsg varchar2(4000),
      optype varchar2(20),
      errtype varchar2(20),
      logrba number,
      logposition number,
     committimestamp timestamp,
     primary key(logrba,logposition,committimestamp)
);

异常处理程序

MAP chf.fei_*, TARGET chf.*,
MAPEXCEPTION (TARGET ogg.exception_fei_all,
COLMAP (   replicat_name = "repl"
, table_name = @GETENV ("GGHEADER", "TABLENAME")
, errno = @GETENV ("LASTERR", "DBERRNUM")
, dberrmsg = @GETENV ("LASTERR", "DBERRMSG")
, optype = @GETENV ("LASTERR", "OPTYPE")
, errtype = @GETENV ("LASTERR", "ERRTYPE")
, logrba = @GETENV ("GGHEADER", "LOGRBA")
, logposition = @GETENV ("GGHEADER", "LOGPOSITION")
, committimestamp = @GETENV ("GGHEADER", "COMMITTIMESTAMP")));

处理说明：
REPERROR参数用以控制Replicat进程如何响应映射过程中发生的错误
DEFAULT参数代表一种全局错误类型，即除去所有已明确指定的错误外的一切错误
DEFAULT2参数代表当DEFAULT错误以Exception方式响应时，所有MAP映射中未定义Exception部分出现的所有错误
EXCEPTIONSONLY只能用于确定表的异常处理
MAPEXCEPTION可以用于通配符的表异常处理

发表在 GoldenGate | 2 条评论

oracle 中如何定位重要(消耗资源多)的SQL

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

1、查看值得怀疑的SQL

select substr(to_char(s.pct,'99.00'),2)||'%'load,
       s.executions executes,
       p.sql_text
from(select address,
               disk_reads,
               executions,
               pct,
               rank()over(order by disk_reads desc) ranking
         from(select address,
                       disk_reads,
                       executions,
                      100*ratio_to_report(disk_reads)over() pct
                 from sys.v_$sql
                where command_type!=47)
        where disk_reads>50*executions) s,
       sys.v_$sqltext p
where s.ranking<=5
  and p.address=s.address
order by 1, s.address, p.piece;

2、查看消耗内存多的sql

select b.username,
       a. buffer_gets,
       a.executions,
       a.disk_reads / decode(a.executions, 0, 1, a.executions),
       a.sql_text SQL
  from v$sqlarea a, dba_users b
 where a.parsing_user_id = b.user_id
   and a.disk_reads > 10000
 order by disk_reads desc;

3、查看逻辑读多的SQL

select*
from(select buffer_gets, sql_text
         from v$sqlarea
        where buffer_gets>500000
        order by buffer_gets desc)
where rownum<=30;

4、查看执行次数多的SQL

select sql_text, executions
  from (select sql_text, executions from v$sqlarea order by executions desc)
 where rownum < 81;

5、查看读硬盘多的SQL

select sql_text, disk_reads
from(select sql_text, disk_reads from v$sqlarea order by disk_reads desc)
where rownum<21;

6、查看排序多的SQL

select sql_text, sorts
from(select sql_text, sorts from v$sqlarea order by sorts desc)
where rownum<21;

7、分析的次数太多，执行的次数太少，要用绑变量的方法来写sql

select substr(sql_text, 1, 80) "sql", count(*), sum(executions) "totexecs"
  from v$sqlarea
 where executions < 5
 group by substr(sql_text, 1, 80)
having count(*) > 30
 order by 2;
发表在 Oracle性能优化 | 评论关闭