DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: move partition from one tablespace to another

  1. #1
    Join Date
    Jan 2003
    Posts
    141

    move partition from one tablespace to another

    Hi
    Can you easily move partitions from one tablespace to another tablespace? If so, is this instantaneous upon issueing the command? And what is the SQL to do this?


    Cheers!

  2. #2
    Join Date
    May 2001
    Posts
    736
    Alter table xxx move partition partition_1 tablespace your_tablespace;

  3. #3
    Join Date
    Jan 2003
    Posts
    141
    Hi !
    Thanks! for the command.

    Create table script:
    --------------------
    CREATE TABLE sample_regional_sales
    (deptno number, item_no varchar2(20),
    txn_date date, txn_amount number, state varchar2(2))
    PARTITION BY RANGE (txn_date)
    SUBPARTITION BY LIST (state)
    (PARTITION q1_1999 VALUES LESS THAN (TO_DATE('1-APR-1999','DD-MON-YYYY'))
    TABLESPACE TBS_CDB
    (SUBPARTITION q1_1999_northwest VALUES ('OR', 'WA'),
    SUBPARTITION q1_1999_southwest VALUES ('AZ', 'UT', 'NM'),
    SUBPARTITION q1_1999_northeast VALUES ('NY', 'VM', 'NJ'),
    SUBPARTITION q1_1999_southeast VALUES ('FL', 'GA'),
    SUBPARTITION q1_others VALUES (DEFAULT) TABLESPACE TS_HIST
    ),
    PARTITION q2_1999 VALUES LESS THAN ( TO_DATE('1-JUL-1999','DD-MON-YYYY'))
    TABLESPACE TS_MISRPT
    (SUBPARTITION q2_1999_northwest VALUES ('OR', 'WA'),
    SUBPARTITION q2_1999_southwest VALUES ('AZ', 'UT', 'NM'),
    SUBPARTITION q2_1999_northeast VALUES ('NY', 'VM', 'NJ'),
    SUBPARTITION q2_1999_southeast VALUES ('FL', 'GA'),
    SUBPARTITION q2_1999_northcentral VALUES ('SD', 'WI'),
    SUBPARTITION q2_1999_southcentral VALUES ('OK', 'TX')
    ),
    PARTITION q3_1999 VALUES LESS THAN (TO_DATE('1-OCT-1999','DD-MON-YYYY'))
    TABLESPACE TS_HIST
    (SUBPARTITION q3_1999_northwest VALUES ('OR', 'WA'),
    SUBPARTITION q3_1999_southwest VALUES ('AZ', 'UT', 'NM'),
    SUBPARTITION q3_others VALUES (DEFAULT) TABLESPACE TS_HIST
    ),
    PARTITION q4_1999 VALUES LESS THAN ( TO_DATE('1-JAN-2000','DD-MON-YYYY'))
    TABLESPACE TS_MISRPT
    );
    *************************************************************
    Insert Script:
    ------------
    INSERT INTO SAMPLE_REGIONAL_SALES (DEPTNO,ITEM_NO,TXN_DATE,TXN_AMOUNT,STATE ) VALUES (10,'RD','01-OCT-1999',1000,'WA');
    INSERT INTO SAMPLE_REGIONAL_SALES (DEPTNO,ITEM_NO,TXN_DATE,TXN_AMOUNT,STATE ) VALUES (10,'RD','01-OCT-1999',1000,'AZ');
    INSERT INTO SAMPLE_REGIONAL_SALES (DEPTNO,ITEM_NO,TXN_DATE,TXN_AMOUNT,STATE ) VALUES (10,'RD','01-OCT-1999',1000,'OR');
    INSERT INTO SAMPLE_REGIONAL_SALES (DEPTNO,ITEM_NO,TXN_DATE,TXN_AMOUNT,STATE ) VALUES (10,'RD','01-OCT-1999',1000,'NM');
    INSERT INTO SAMPLE_REGIONAL_SALES (DEPTNO,ITEM_NO,TXN_DATE,TXN_AMOUNT,STATE ) VALUES (10,'RD','01-OCT-1999',1000,'TX');
    INSERT INTO SAMPLE_REGIONAL_SALES (DEPTNO,ITEM_NO,TXN_DATE,TXN_AMOUNT,STATE ) VALUES (10,'RD','01-OCT-1999',1000,'NY');
    INSERT INTO SAMPLE_REGIONAL_SALES (DEPTNO,ITEM_NO,TXN_DATE,TXN_AMOUNT,STATE ) VALUES (10,'RD','01-JUL-1999',1000,'WA');
    INSERT INTO SAMPLE_REGIONAL_SALES (DEPTNO,ITEM_NO,TXN_DATE,TXN_AMOUNT,STATE ) VALUES (10,'RD','01-JUL-1999',1000,'AZ');
    INSERT INTO SAMPLE_REGIONAL_SALES (DEPTNO,ITEM_NO,TXN_DATE,TXN_AMOUNT,STATE ) VALUES (10,'RD','01-JUL-1999',1000,'OR');
    INSERT INTO SAMPLE_REGIONAL_SALES (DEPTNO,ITEM_NO,TXN_DATE,TXN_AMOUNT,STATE ) VALUES (10,'RD','01-JUL-1999',1000,'NM');
    INSERT INTO SAMPLE_REGIONAL_SALES (DEPTNO,ITEM_NO,TXN_DATE,TXN_AMOUNT,STATE ) VALUES (10,'RD','01-JUL-1999',1000,'TX');
    INSERT INTO SAMPLE_REGIONAL_SALES (DEPTNO,ITEM_NO,TXN_DATE,TXN_AMOUNT,STATE ) VALUES (10,'RD','01-JUL-1999',1000,'NY');
    ************************************************************
    After inserting data into the table:
    -------------------------------------
    SELECT TABLESPACE_NAME,PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='SAMPLE_REGIONAL
    _SALES';

    TABLESPACE_NAME PARTITION_NAME
    ------------------------------ ------------------------------
    TBS_CDB Q1_1999
    TS_MISRPT Q2_1999
    TS_HIST Q3_1999
    TS_MISRPT Q4_1999
    *************************************************************
    Got the following ERROR:
    -----------------------
    SQL> Alter table sample_regional_sales move partition q2_1999 tablespace TS_CDB;
    Alter table sample_regional_sales move partition q2_1999 tablespace TS_CDB
    *
    ERROR at line 1:
    ORA-14257: cannot move partition other than a Range or Hash partition

    My table partition is based on range and list.

    How to go about?

    Cheers!

  4. #4
    Join Date
    May 2001
    Posts
    736
    Alter table sample_regional_sales move subpartition q2_1999 tablespace TS_CDB;

  5. #5
    Join Date
    Jan 2003
    Posts
    141
    Hi! Thanks a lot...

    The script is working but with a slight change!

    Alter table sample_regional_sales move subpartition q2_1999_northwest tablespace Tbs_cdb;

    Many Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width