How To Change the Partition Column Of A Partitioned Table Using DBMS_Redefinition [ID 846405.1]
|
|
|
Modified22-OCT-2009TypeHOWTOStatusPUBLISHED
|
|
In this Document
Goal
Solution
References
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.4 to 11.2.0.2.0
Information in this document applies to any platform.
Goal
The purpose of this document is to provide step by step instructions on how to convert the partitioned column of an existing partitioned table to have a completely different partitioned column using dbms_redefinition package.
Solution
1) Create partitioned table (This is the table that needs to be redefined) with the name SALES. We plan to repartition this table to use the MONTH_NO as the partitioning column.
Note: The table already has a PK on this defined in the Create statement.
CREATE TABLE sales (
acct_no NUMBER(5),
acct_name CHAR(30),
amount_of_sale NUMBER(6),
week_no INTEGER,
month_no integer,
sale_details VARCHAR2(1000),
PRIMARY KEY (acct_no, acct_name, week_no))
PARTITION BY LIST (week_no) (
PARTITION part1234 VALUES (1, 2, 3, 4) tablespace users,
PARTITION part5678 VALUES (5, 6, 7, 8) tablespace users,
PARTITION partdefault VALUES (DEFAULT) tablespace users);
insert into sales values (1,'acc 1',1,52,12,'sales details 1');
insert into sales values (2,'acc 2',2,51,12,'sales details 2');
insert into sales values (3,'acc 3',3,50,12,'sales details 3');
insert into sales values (4,'acc 4',4,1,1,'sales details 4');
insert into sales values (5,'acc 5',5,2,1,'sales details 5');
commit;
2) Gather statistics on the table:
EXEC DBMS_STATS.gather_table_stats('RK_MVIEW', 'SALES', cascade => TRUE);
3) Create a Partitioned Interim Table:
CREATETABLEinterim_sales(
acct_noNUMBER(5),
acct_nameCHAR(30),
amount_of_saleNUMBER(6),
week_noINTEGER,
month_nointeger,
sale_detailsVARCHAR2(1000),
PRIMARYKEY(acct_no,acct_name,month_no))
PARTITIONBYLIST(month_no)(
PARTITIONint_part1VALUES(1)tablespaceusers,
PARTITIONint_part12VALUES(12)tablespaceusers,
PARTITIONint_partdefaultVALUES(DEFAULT)tablespaceusers);
4) Start the Redefinition Process:
a) Check the redefinition is possible using the following command:
EXEC Dbms_Redefinition.can_redef_table('RK_MVIEW', 'SALES');
b)If no errors are reported, start the redefintion using the following command:
BEGIN
DBMS_REDEFINITION.start_redef_table(
uname=>'RK_MVIEW',
orig_table=>'SALES',
int_table=>'INTERIM_SALES');
END;
/
c) Optionally synchronize new table with interim name before index creation:
BEGIN
dbms_redefinition.sync_interim_table(
uname=>'RK_MVIEW',
orig_table=>'SALES',
int_table=>'INTERIM_SALES');
END;
/
d) Gather statistics on the new table:
EXECDBMS_STATS.gather_table_stats('RK_MVIEW','INTERIM_SALES',cascade=>TRUE);
e) Complete the Redefintion Process:
BEGIN
dbms_redefinition.finish_redef_table(
uname=>'RK_MVIEW',
orig_table=>'SALES',
int_table=>'INTERIM_SALES');
END;
/
At this point the interim table has become the "real" table and their names have been switched in the name dictionary.
f) Remove original table which now has the name of the interim table:
DROP TABLE INTERIM_SALES;
g) Check whether partitioning is successful or not:
SELECT partition_name
FROM user_tab_partitions
WHERE table_name = 'SALES';
PARTITION_NAME
------------------
INT_PART1
INT_PART12
INT_PARTDEFAULT
------------------------------------------------------------------------------
分享到:
相关推荐
A20_sys_partition.fex分区表说明v1.3_20140612
makeinfo is usually part of the texinfo package in your distribution make: *** [dependencies] 错误 1 make:离开目录“/home/wwt/linux_r16/lichee/buildroot” ERROR: build buildroot Failed rootroot@cm-...
用于Ultra96-V2的引导加载程序(U-Boot、FSBL、PMUFW、ATF)_Tcl_下载.zip
where a.table_owner = b.table_owner and a.table_name = b.table_name and a.partition_position = b.part_posi) loop v_part_name := v_minp_rec.minpart; -----------------------------------------------...
8.5 The Partition Problem . . . . . . . . . . . . . . . . . . . . . . . . 294 8.6 Parsing Context-Free Grammars . . . . . . . . . . . . . . . . . . 298 8.7 Limitations of Dynamic Programming: TSP . . ...
A40-T3的sys_partition.fex分区表说明
网上现在很难找到这个资源,现在共享给大家!
See the benchmark page to get an idea of the possible data rates.IDeletes <file>.syncEnsures all buffered data is written to the card.touch <file>Creates <file>.write <file> &...
Loading indicator is now displayed when previewing a large script in the formatting options window Improvements to CTE formatting options OPTION clause is now placed on a new line (UserVoice and forum...
basketball.root_partition.cmp.cdb
makeinfo is usually part of the texinfo package in your distribution make: *** [dependencies] 错误 1 make:离开目录“/home/wwt/linux_r16/lichee/buildroot” ERROR: build buildroot Failed rootroot@cm-...
专门介绍sql server 2008分区表和索引策略。
0.7 How to read this specification............................................................................................................................ xvi 1 Scope ................................
./octopus_perf.mk:PRODUCT_MODEL := UltraOcta A83 perf 将octopus_perf.mk另存为:octopus_cb5801.mk,直接用cb5801搜索替换perf即可。 ./configs/media_profiles.xml: not perform any checks at all. 不用修改...
a64_android/lichee/brandy/u-boot-2014.07/arch/arm/cpu/armv7/sun50iw1p1/nand/libnand.o /home/wwt/a64/bpi_a64_android/lichee/brandy/u-boot-2014.07/sunxi_spl/sbrom/flash/libflash.o /home/wwt/a64/bpi_a64_...
针对ZedBoard REV_C开发板配套的HDMI显示Linaro Ubuntu系统界面需要的BOOT.BIN、devicetree.dtb、logo.bin、zImage文件,测试LED、OLED的可执行文件。
./octopus_perf.mk:PRODUCT_MODEL := UltraOcta A83 perf 将octopus_perf.mk另存为:octopus_cb5801.mk,直接用cb5801搜索替换perf即可。 ./configs/media_profiles.xml: not perform any checks at all. 不用修改...
Apache Kafka是开源的分布式流处理平台,也是高吞吐量的分布式跨平台订阅消息系统,主要包含Broker服务器、Topic消息类别、Partition物理分区、Producer生产者、Consumer消费者、Consumer Group消费组部分。
MiniTool Partition Wizard官方版是一个完整的高级Windows分区管理工具,可帮助您在Windows分区上执行许多驱动器管理功能,如复制,合并,扩展,擦除,删除,格式化或切割。终极软件为您提供完全控制,并为您的硬盘...
code for implementation of matlab hoefdding phi squere code to partition of row and column vectors using phi