How To Partition Existing Table Using DBMS_Redefinition [ID 472449.1]
来源:互联网 发布:淘宝里的表情序号 编辑:程序博客网 时间:2024/06/05 15:39
How To Partition Existing Table Using DBMS_Redefinition [ID 472449.1]
Modified 28-SEP-2010 Type HOWTO Status MODERATED
In this Document
Goal
Solution
Platforms: 1-914CU;
This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
Applies to:
Oracle Server - Standard Edition - Version: 9.2.0.4 and later [Release: 9.2 and later ]
Information in this document applies to any platform.
"Checked for relevance on 29-Sep-2010"
Goal
The purpose of this document is to provide step by step instructions on how to convert unpartitioned table to partitioned one using dbms_redefinition package.
Solution
1) Create unpartitioned table with the name unpar_table
SQL> CREATE TABLE unpar_table (
id NUMBER(10),
create_date DATE,
name VARCHAR2(100)
);
2) Apply some constraints to the table:
SQL> ALTER TABLE unpar_table ADD (
CONSTRAINT unpar_table_pk PRIMARY KEY (id)
);
SQL> CREATE INDEX create_date_ind ON unpar_table(create_date);
3) Gather statistics on the table:
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'unpar_table', cascade => TRUE);
4) Create a Partitioned Interim Table:
SQL> CREATE TABLE par_table (
id NUMBER(10),
create_date DATE,
name VARCHAR2(100)
)
PARTITION BY RANGE (create_date)
(PARTITION unpar_table_2005 VALUES LESS THAN (TO_DATE('01/01/2005', 'DD/MM/YYYY')),
PARTITION unpar_table_2006 VALUES LESS THAN (TO_DATE('01/01/2006', 'DD/MM/YYYY')),
PARTITION unpar_table_2007 VALUES LESS THAN (MAXVALUE));
5) Start the Redefinition Process:
a) Check the redefinition is possible using the following command:
SQL> EXEC Dbms_Redefinition.can_redef_table(USER, 'unpar_table');
b)If no errors are reported, start the redefintion using the following command:
SQL> BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => USER,
orig_table => 'unpar_table',
int_table => 'par_table');
END;
/
Note: This operation can take quite some time to complete.
c) Optionally synchronize new table with interim name before index creation:
SQL> BEGIN
dbms_redefinition.sync_interim_table(
uname => USER,
orig_table => 'unpar_table',
int_table => 'par_table');
END;
/
d) Create Constraints and Indexes:
SQL> ALTER TABLE par_table ADD (
CONSTRAINT unpar_table_pk2 PRIMARY KEY (id)
);
SQL> CREATE INDEX create_date_ind2 ON par_table(create_date);
e) Gather statistics on the new table:
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'par_table', cascade => TRUE);
f) Complete the Redefintion Process:
SQL> BEGIN
dbms_redefinition.finish_redef_table(
uname => USER,
orig_table => 'unpar_table',
int_table => 'par_table');
END;
/
At this point the interim table has become the "real" table and their names have been switched in the name dictionary.
g) Remove original table which now has the name of the interim table:
SQL> DROP TABLE par_table;
h)Rename all the constraints and indexes to match the original names.
ALTER TABLE unpar_table RENAME CONSTRAINT unpar_table_pk2 TO unpar_table_pk;
ALTER INDEX create_date_ind2 RENAME TO create_date_ind;
i) Check whether partitioning is successful or not:
SQL> SELECT partitioned
FROM user_tables
WHERE table_name = 'unpar_table';
PAR
---
YES
1 row selected.
SQL> SELECT partition_name
FROM user_tab_partitions
WHERE table_name = 'unpar_table';
PARTITION_NAME
------------------------------
unpar_table_2005
unpar_table_2006
unpar_table_2007
3 rows selected.
Please not that the 9i redefinition procedures has some restrictions:
* There must be enough space to hold two copies of the table.
* Primary key columns cannot be modified.
* Tables must have primary keys.
* Redefinition must be done within the same schema.
* New columns added cannot be made NOT NULL until after the redefinition operation.
* Tables cannot contain LONGs, BFILEs or User Defined Types.
* Clustered tables cannot be redefined.
* Tables in the SYS or SYSTEM schema cannot be redefined.
* Tables with materialized view logs or materialized views defined on them cannot be redefined.
* Horizontal sub setting of data cannot be performed during the redefinition.
------------------------------------------------------------------------------
Blog: http://blog.csdn.net/tianlesoftware
网上资源: http://tianlesoftware.download.csdn.net
相关视频:http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
DBA1 群:62697716(满); DBA2 群:62697977(满)
DBA3 群:62697850 DBA 超级群:63306533;
聊天 群:40132017
--加群需要在备注说明Oracle表空间和数据文件的关系,否则拒绝申请
- How To Partition Existing Table Using DBMS_Redefinition [ID 472449.1]
- How To Partition Existing Table Using DBMS_Redefinition [ID 472449.1]
- How To Partition Existing Table Using DBMS_Redefinition [ID 472449.1]
- How To Partition Existing Table Using DBMS_Redefinition
- How To Change the Partition Column Of A Partitioned Table Using DBMS_Redefinition [ID 846405.1]
- How To Change the Partition Column Of A Partitioned Table Using DBMS_Redefinition [ID 846405.1]
- How to Partition a Non-partitioned Table [ID 1070693.6]
- How to Partition a Non-partitioned Table [ID 1070693.6]
- How to Partition a Non-partitioned Table [ID 1070693.6]
- How to use partition table
- MySQL: How to add column to existing table
- How to partition a non-partitioned table
- How to Split Partition of Heavily Used Table
- How to Add the JVM Component to an Existing Oracle Database (Doc ID 1461562.1)
- SQL SERVER – 2005 – Database Table Partitioning Tutorial – How to Horizontal Partition Database Table
- How to Backup Partition of Range Partitioned Table with Local Indexes
- How to Using exploits.
- How to using TOR
- hashCode与equals Set 集合排序
- C#中HashTable的用法
- C#文件读写、操作与监控
- PowerManager File path
- ASP.NET为textbox 添加 失去焦点事件 和回车失去焦点
- How To Partition Existing Table Using DBMS_Redefinition [ID 472449.1]
- Windows Mobile中如何使PicturesBox控件上的Label控件背景透明
- CString 转化为 const char*的方法(经验证) “0x77d287f1”指令引用的“0x00000000”内存。该内存不能为“written”。
- Oracle常用函数总结
- 关于按回车键提交的问题
- CPU 卡中的双向认证的实现
- 解决session 关闭问题 WARN - unclosed connection, forgot to call close() on your session?
- 第一节课第二周
- window下apache配置多个站点