1. move the subpartitions physically:
ALTER TABLE lkw.sales MOVE SUBPARTITION P_012013_WEEK1 TABLESPACE SALES_2013_DATA [ UPDATE INDEXES ];
2. alter the default attributes for the *partition* (as opposed to the subpartition which was physically moved in the previous step):
ALTER TABLE lkw.sales MODIFY DEFAULT ATTRIBUTES FOR PARTITION "P_012013" TABLESPACE SALES_2013_DATA;
3. Finally, alter the default attributes for the table:
ALTER TABLE lkw.sales MODIFY DEFAULT ATTRIBUTES TABLESPACE SALES_2013_DATA;
The default attributes for partitions and subpartitions can be found by querying the view DBA_PART_TABLES.
A script that will generate the DDL for you:
accept table_owner prompt 'Table owner: ' accept table_name prompt 'Table name: ' accept new_tabspc prompt 'New tablespace: ' accept old_tabspc prompt 'Old tablespace: ' set lines 300 set heading off set feedback off set verify off set echo off set pages 0 set trimspool on spool exec_movesubpart_&&table_owner..&&table_name..sql select 'alter session set nls_language=''american'';' from dual; select 'alter session force parallel ddl;' from dual; select 'set timing on' from dual; select 'set lines 200' from dual; select 'set trimspool on' from dual; select 'spool exec_move_subpart_&&table_owner..&&table_name..log' from dual; select 'exec DBMS_APPLICATION_INFO.SET_MODULE(module_name=>''Move'', action_name=>''MoveSubPart_&&table_name'');' from dual; select 'exec DBMS_APPLICATION_INFO.SET_CLIENT_INFO (client_info=>''MoveSubPart_&&table_name'');' from dual; select 'exec DBMS_SESSION.SET_IDENTIFIER (client_id=>''MoveSubPart_&&table_name''); ' from dual; SELECT 'alter table ' || ts.TABLE_OWNER || '.' || ts.TABLE_NAME || ' move subpartition ' || ts.subpartition_name || ' ONLINE TABLESPACE &&new_tabspc ROW STORE COMPRESS ADVANCED UPDATE GLOBAL INDEXES;' FROM dba_tab_subpartitions ts WHERE ts.table_name = '&&table_name' AND ts.tablespace_name = '&&old_tabspc' AND ts.table_owner = '&&table_owner' ORDER BY ts.subpartition_name DESC; select 'prompt alter default attributes for partitions:' from dual; SELECT 'alter table ' || ts.TABLE_OWNER || '.' || ts.TABLE_NAME || ' MODIFY DEFAULT ATTRIBUTES FOR PARTITION ' || ts.partition_name || ' TABLESPACE &&new_tabspc;' FROM dba_tab_subpartitions ts WHERE ts.table_name = '&&table_name' AND ts.tablespace_name = '&&old_tabspc' AND ts.table_owner = '&&table_owner' ORDER BY ts.subpartition_name DESC; select 'exit' from dual; exit
From 12.1 and onwards, this operation can be done online, meaning that it becomes a non-blocking DDL statement, allowing DML against the partition to continue without interuption. See this post for details.
No comments:
Post a Comment