转载至http://www.cnblogs.com/jyzhao/p/3876634.html
实验环境:RHEL 6.4 + Oracle 11.2.0.3
实验:在线重定义 普通表 为 分区表,包括主键对应的索引都改造为分区索引.
1,构造普通表t_objects
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
conn test1/test1;
create
table
t_objects
as
select
*
from
dba_objects;
SQL>
select
count
(1)
from
t_objects;
COUNT
(1)
----------
468738
--t_objects建立主键和索引
alter
table
t_objects
add
constraint
pk_objects
primary
key
(created, object_id);
create
index
i_objects
on
t_objects(object_id, STATUS);
--表有主键,确认表可以重定义:
SQL>
EXEC
DBMS_REDEFINITION.CAN_REDEF_TABLE(
'test1'
,
't_objects'
);
PL/SQL
procedure
successfully completed.
--若表无主键 可以采用rowid重定义:
SQL>
EXEC
DBMS_REDEFINITION.CAN_REDEF_TABLE(
'test1'
,
't_objects'
,2);
PL/SQL
procedure
successfully completed.
|
2,创建重定义需要的临时表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
-- Create table
create
table
T_OBJECTS_TEMP
(
OWNER VARCHAR2(30),
OBJECT_NAME VARCHAR2(128),
SUBOBJECT_NAME VARCHAR2(30),
OBJECT_ID NUMBER
not
null
,
DATA_OBJECT_ID NUMBER,
OBJECT_TYPE VARCHAR2(19),
CREATED
DATE
not
null
,
LAST_DDL_TIME
DATE
,
TIMESTAMP
VARCHAR2(19),
STATUS VARCHAR2(7),
TEMPORARY
VARCHAR2(1),
GENERATED VARCHAR2(1),
SECONDARY VARCHAR2(1),
NAMESPACE NUMBER,
EDITION_NAME VARCHAR2(30)
)partition
by
range(created)(
partition P20130601
values
less than (TO_DATE(
' 2013-06-01 00:00:00'
,
'SYYYY-MM-DD HH24:MI:SS'
,
'NLS_CALENDAR=GREGORIAN'
))
tablespace DBS_D_GRNOPHQ,
partition P20140607
values
less than (TO_DATE(
' 2014-06-07 00:00:00'
,
'SYYYY-MM-DD HH24:MI:SS'
,
'NLS_CALENDAR=GREGORIAN'
))
tablespace DBS_D_GRNOPHQ,
partition P20140731
values
less than (TO_DATE(
' 2014-07-31 00:00:00'
,
'SYYYY-MM-DD HH24:MI:SS'
,
'NLS_CALENDAR=GREGORIAN'
))
tablespace DBS_D_GRNOPHQ
);
|
3,开始重定义
1
|
exec
DBMS_REDEFINITION.START_REDEF_TABLE(
'test1'
,
't_objects'
,
't_objects_temp'
);
|
注;若无主键不能这样重定义,需要指定以rowid重定义,示例如下:
1
2
3
|
SQL>
exec
DBMS_REDEFINITION.START_REDEF_TABLE(
'test1'
,
't_objects'
,
't_objects_temp'
,
null
,2);
PL/SQL
procedure
successfully completed.
|
4,开始拷贝表的属性(本次未做,因为这样转换的,索引不是分区索引)
1
2
3
4
5
6
7
8
9
10
11
12
|
DECLARE
error_count pls_integer := 0;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
uname =>
'test1'
,
orig_table =>
't_objects'
,
int_table =>
't_objects_temp'
,
ignore_errors =>
TRUE
,
num_errors => error_count);
DBMS_OUTPUT.PUT_LINE(
'errors := '
|| TO_CHAR(error_count));
END
;
/
|
--经实验,在开始重定义之后在临时表上创建local索引,重定义完成后,主键对应的索引也是分区索引;
1
2
|
alter
table
t_objects_temp
add
constraint
pk_objects_temp
primary
key
(created, object_id) using
index
local
;
create
index
i_objects_temp
on
t_objects_temp(object_id, STATUS)
local
;
|
5,同步数据
1
|
exec
DBMS_REDEFINITION.SYNC_INTERIM_TABLE (uname =>
'test1'
,orig_table =>
't_objects'
,int_table =>
't_objects_temp'
);
|
6,收集中间表的统计信息(选做)
1
|
EXEC
DBMS_STATS.gather_table_stats(
'test1'
,
't_objects_temp'
,
cascade
=>
TRUE
);
|
7,完成重定义
1
|
exec
DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname =>
'test1'
,orig_table =>
't_objects'
,int_table =>
't_objects_temp'
);
|
1
|
EXEC
DBMS_REDEFINITION.FINISH_REDEF_TABLE(
'TEST1'
,
'T_OBJECTS'
,
'T_OBJECTS_TEMP'
);
|
8,删除临时表
1
|
drop
table
t_objects_temp purge;
|
9,修改索引,约束名称和原表一致
1
2
3
|
alter
index
I_OBJECTS_TEMP rename
to
I_OBJECTS;
alter
index
PK_OBJECTS_TEMP rename
to
PK_OBJECTS;
alter
table
t_objects rename
constraint
pk_objects_temp
to
pk_objects;
|
10,ABORT_REDEF_TABLE使用
在FINISH_REDEF_TABLE之前,可以使用abort_redef_table停止重定义
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SQL>
select
*
from
cat;
TABLE_NAME TABLE_TYPE
------------------------------ -----------
MLOG$_T_OBJECTS
TABLE
T_OBJECTS
TABLE
T_OBJECTS_TEMP
TABLE
SQL>
exec
DBMS_REDEFINITION.ABORT_REDEF_TABLE (
'test1'
,
't_objects'
,
't_objects_temp'
);
PL/SQL
procedure
successfully completed.
SQL>
select
*
from
cat;
TABLE_NAME TABLE_TYPE
------------------------------ -----------
T_OBJECTS
TABLE
T_OBJECTS_TEMP
TABLE
|