使用在线重定义修改表的逻辑结构_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 1953 | 回复: 0   主题: 使用在线重定义修改表的逻辑结构        下一篇 
xingxing.qin
注册用户
等级:上尉
经验:537
发帖:28
精华:0
注册:1970-1-1
状态:离线
发送短消息息给xingxing.qin 加好友    发送短消息息给xingxing.qin 发消息
发表于: IP:您无权察看 2015-3-19 20:20:59 | [全部帖] [楼主帖] 楼主

在生产系统往往会因为一下原因去修改表的逻辑或者物理结构,比如

提升sql查询或者DML操作的性能;

适应应用程序变更;

存储调整。

ORACLE提供的在线重定义功能在基本不影响表的可用性的情况下可以对表的结构做出更改。

当对表做在线重定义的时候,查询和DML操作可以正常进行不受影响。根据表的大小、重定义表的复杂程度,需要做出结构更改的表只会在很短的时间窗口内被锁住,而且所有的操作对客户都是透明的。ORACLE提供的包DBMS_REDEFINITION即可做此操作。

在线重定义的主要功能:

Modify the storage parameters of a table or cluster
Move a table or cluster to a different tablespace in the same schema
Add, modify, or drop one or more columns in a table or cluster
Add or drop partitioning support (non-clustered tables only)
Change partition structure
Change physical properties of a single table partition, including moving it to a different tablespace in the same schema
Change physical properties of a materialized view log or an Oracle Streams Advanced Queueing queue table
Add support for parallel queries
Re-create a table or cluster to reduce fragmentation
Change the organization of a normal table (heap organized) to an index-organized table, or do the reverse.
Convert a relational table into a table with object columns, or do the reverse.
Convert an object table into a relational table or a table with object columns, or do the reverse.


下面构建实验环境。

生产一直在用的表是T04,新设计的中间表是T05,使用在线重定义将T04转换为T05的结构。

CREATE TABLE "SCOTT"."T04"
( "EMPNO" NUMBER(4,0) PRIMARY KEY,
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ;
insert into scott.t04 select * from emp;


重新设计的表T05 在T04的基础上多了两列,中间的TEST列,最后的BONUS列;

deptno部门号在原来部门的基础上加10,bonus初始值为0

使用下面的plsql不停模拟T04表的事务

declare
begin
for i in 1 .. 100000 loop
for j in 1 .. 3 loop
update scott.t04 set sal = sal + 1 where empno > 7800;
dbms_lock.sleep(1);
end loop;
commit;
end loop;
end;
/


在线重定义的具体实现:

1. Verify that the table is a candidate for online redefinition. In this case you specify

that the redefinition is to be done using primary keys or pseudo-primary keys.
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('hr','admin_emp',
DBMS_REDEFINITION.CONS_USE_PK);
END;
/
2. Create an interim table
CREATE TABLE "SCOTT"."T05"
( "EMPNO" NUMBER(4,0) PRIMARY KEY,
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"TEST" VARCHAR2(9) DEFAULT 'TEST COL',
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
"BONUS" NUMBER(7,2)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ;
3. Start the redefinition process.
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE('scott', 't04','t05',
'empno empno, ename ename, job job, mgr mgr, hiredate hiredate , sal sal ,comm comm ,deptno+10 deptno, 0.6 bonus',
dbms_redefinition.cons_use_pk);
END;
/


开始重定义不会影响生成表中的事务,它会去请求一张表锁。因为上面的plsql一直持有行锁,所以必须暂时停掉plsql,使开始重定义进程

可以获得表锁,否则该进程会挂起,一直请求表锁。

然后再继续执行plsql

4. Copy dependent objects. (Automatically create any triggers, indexes, grants, and

constraints on scott.t04)
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('scott', 't04','t05',
DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;
Note that the ignore_errors argument is set to TRUE for this call. The reason is
that the interim table was created with a primary key constraint, and when
COPY_TABLE_DEPENDENTS attempts to copy the primary key constraint and
index from the original table, errors occurs. You can ignore these errors, but you
must run the query shown in the next step to see if there are other errors.
5. Query the DBA_REDEFINITION_ERRORS view to check for errors.
SQL> select object_name, base_table_name, ddl_txt from
DBA_REDEFINITION_ERRORS;
OBJECT_NAME       BASE_TABLE_NAME        DDL_TXT
-------------------------------------------------------------------------------------------------------------------
SYS_C005161       T04                    CREATE UNIQUE INDEX "SCOTT"."TMP$$_SYS_C0051610" ON "SCOTT"."T05" ("EMPNO")
P
SYS_C005161       T04                    ALTER TABLE "SCOTT"."T05" ADD CONSTRAINT "TMP$$_SYS_C0051610" PRIMARY KEY ("EMPN
6. Optionally, synchronize the interim table t05
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('scott', 't04', 't05');
END;
/
7. Complete the redefinition.
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('scott', 't04', 't05');
END;
/


这个过程如果生产表中一直有会话持有锁,就会造成重定义进程挂起,等待事件为 wait for table lcok 

那我们再次中断一下plsql的执行

使该进程获得表锁

Note: The best approach is to define the interim table with a primary
key constraint, use REGISTER_DEPENDENT_OBJECT to register the
primary key constraint and index, and then copy the remaining
dependent objects with COPY_TABLE_DEPENDENTS. This approach
avoids errors and ensures that the redefined table always has a
primary key and that the dependent object names do not change.
The table scott.t04 is locked in the exclusive mode only for a small
window toward the end of this step. After this call the table scott.t04 is
redefined such that it has all the attributes of the scott.t05 table.


然后再次查询一下表T04,已经变了

select * from t04
SQL> select * from scott.t04;
EMPNO ENAME      JOB          TEST MGR HIREDATE            SAL COMM       DEPTNO      BONUS
---------- ---------- --------- --------- ---------- --------- ---------- ---------- ---------- ----------
7369 SMITH      CLERK        TEST COL 7902 17-DEC-80      800            30        .6
7499 ALLEN      SALESMAN
TEST COL 7698 20-FEB-81     1600  300       40        .6
7521 WARD       SALESMAN     TEST COL 7698 22-FEB-81     1250  500       40        .6
7566 JONES      MANAGER      TEST COL 7839 02-APR-81     2975            30        .6
7654 MARTIN     SALESMAN     TEST COL 7698 28-SEP-81     1250  1400      40        .6
7698 BLAKE      MANAGER      TEST COL 7839 01-MAY-81     2850            40        .6
7782 CLARK      MANAGER      TEST COL 7839 09-JUN-81     2450            20        .6
7788 SCOTT      ANALYST      TEST COL 7566 19-APR-87     3000            30        .6
7839 KING       PRESIDENT    TEST COL      17-NOV-81     7088            20        .6
7844 TURNER     SALESMAN     TEST COL 7698 08-SEP-81     3588   0        40        .6
7876 ADAMS      CLERK        TEST COL 7788 23-MAY-87     3188            30        .6
7900 JAMES      CLERK        TEST COL 7698 03-DEC-81     3038            40        .6
7902 FORD       ANALYST      TEST COL 7566 03-DEC-81     5088            30        .6
7934 MILLER     CLERK        TEST COL 7782 23-JAN-82     3388            20        .6


查看一下T05,发现他就是原来的T04

SQL> select * from scott.t05;
EMPNO ENAME      JOB            MGR  HIREDATE    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH      CLERK         7902  17-DEC-80    800              20
7499 ALLEN      SALESMAN      7698  20-FEB-81   1600        300   30
7521 WARD       SALESMAN      7698  22-FEB-81   1250        500   30
7566 JONES      MANAGER       7839  02-APR-81   2975              20
7654 MARTIN     SALESMAN      7698  28-SEP-81   1250       1400   30
7698 BLAKE      MANAGER       7839  01-MAY-81   2850              30
7782 CLARK      MANAGER       7839  09-JUN-81   2450              10
7788 SCOTT      ANALYST       7566  19-APR-87   3000              20
7839 KING       PRESIDENT           17-NOV-81   7088              10
7844 TURNER     SALESMAN      7698  08-SEP-81   3588          0   30
7876 ADAMS      CLERK         7788  23-MAY-87   3188              20
7900 JAMES      CLERK         7698  03-DEC-81   3038              30
7902 FORD       ANALYST       7566  03-DEC-81   5088              20
7934 MILLER     CLERK         7782  23-JAN-82   3388              10
8. Drop the interim table
drop table t05 ;




赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论