This discussion is archived
9 Replies Latest reply: Jan 6, 2013 8:43 PM by sb92075 RSS

Move partition from one tablespace to other along with lob data.

Hokins Newbie
Currently Being Moderated
Hi,
I have a range partioned table with one lob column. Each partion is on a seperate tablespace except two partiones which are on same tablespace. Now I want to move a partition from one tablespace to another tablespace along with lob data. By using a simple alter table move partition will also move the lob data or there is some special procedure to adopt. Thanks..
  • 1. Re: Move partition from one tablespace to other along with lob data.
    Chanchal Wankhade Journeyer
    Currently Being Moderated
    Hi,

    to find out name of the partitions
    SELECT * FROM user_tab_partitions WHERE table_name = 'TABLE_NAME';
    To move the partition in other tablespace
    *alter table TABLE_NAME move partition PARTITION_NAME tablespace TABLESPACE_NAME;
    *
  • 2. Re: Move partition from one tablespace to other along with lob data.
    rp0428 Guru
    Currently Being Moderated
    >
    I have a range partioned table with one lob column. Each partion is on a seperate tablespace except two partiones which are on same tablespace. Now I want to move a partition from one tablespace to another tablespace along with lob data. By using a simple alter table move partition will also move the lob data or there is some special procedure to adopt. Thanks..
    >
    No - the default does NOT move the lob data.

    You need to explicitly specify the storage for the LOB data as part of the move.

    See 'Moving a Table to a New Segment or Tablespace' in the DBA Guide
    http://docs.oracle.com/cd/E14072_01/server.112/e10595/tables006.htm#i1106606
    >
    If the table includes LOB column(s), this statement can be used to move the table along with LOB data and LOB index segments (associated with this table) which the user explicitly specifies. If not specified, the default is to not move the LOB data and LOB index segments.
  • 3. Re: Move partition from one tablespace to other along with lob data.
    Hokins Newbie
    Currently Being Moderated
    Thanks for the reply but i don't want to move the complete table. i just want to move a partition to a new table space along with lob data. So please tel me the exact syntax.
  • 4. Re: Move partition from one tablespace to other along with lob data.
    rp0428 Guru
    Currently Being Moderated
    >
    Thanks for the reply but i don't want to move the complete table. i just want to move a partition to a new table space along with lob data. So please tel me the exact syntax.
    >
    READ the entire doc section. Don't just read the one excerpt I posted. The section applies to tables or partitions.
    >
    The ALTER TABLE...MOVE statement enables you to relocate data of a non-partitioned table or of a partition of a partitioned table into a new segment, and optionally into a different tablespace for which you have quota. This statement also lets you modify any of the storage attributes of the table or partition, including those which cannot be modified using ALTER TABLE.
    >
    It has example code. Why do you expect people to post 'exact syntax' when you don't bother to post the DDL you have?
  • 5. Re: Move partition from one tablespace to other along with lob data.
    Hokins Newbie
    Currently Being Moderated
    hi,
    Well to further explain. i have a table articles. its has columns id, insert_date , subject , author , article_file (BLOB). Table has range partitions (insert_date) yearly.

    PARTITION D_11 VALUES LESS THAN ( TIMESTAMP' 2011-12-31 00:00:00') TABLESPACE D_11,
    PARTITION D_12 VALUES LESS THAN ( TIMESTAMP' 2012-12-31 00:00:00') TABLESPACE D_12,
    PARTITION D_MAX VALUES LESS THAN ( MAXVALUE) TABLESPACE D_12.


    Now with the start of new year i was late to add the new partitions and values are going in D_MAX. So i want to move the D_MAX into D_13 along with BLOB. So now please tell me the exact syntax.
    Thanks...

    Edited by: Hokins on Jan 7, 2013 9:09 AM
  • 6. Re: Move partition from one tablespace to other along with lob data.
    rp0428 Guru
    Currently Being Moderated
    >
    Well to further explain. i have a table articles. its has columns id, insert_date , subject , author , article_file (BLOB). Table has range partitions (insert_date) yearly.

    PARTITION D_11 VALUES LESS THAN ( TIMESTAMP' 2011-12-31 00:00:00') TABLESPACE D_11,
    PARTITION D_12 VALUES LESS THAN ( TIMESTAMP' 2012-12-31 00:00:00') TABLESPACE D_12,
    PARTITION D_MAX VALUES LESS THAN ( MAXVALUE) TABLESPACE D_12.

    Now with the start of new year i was late to add the new partitions and values are going in D_MAX. So i want to move the D_MAX into D_13 along with BLOB. So now please tell me the exact syntax.
    >
    Then you need to do a partition SPLIT and split the max value partition into the new ones that you want to create. When you define the new partitions you can specify the storage.

    See this Oracle-base article for an example of SPLIT PARTITION.
    http://www.oracle-base.com/articles/misc/partitioning-an-existing-table-using-exchange-partition.php#split_partition

    That example splits a 2007 partition into a new 2005 partition and then again to create a new 2006 partition.
    Next, we split the single large partition into smaller partitions as required.
    
    ALTER TABLE big_table
      SPLIT PARTITION big_table_2007 AT (TO_DATE('31-DEC-2005 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
      INTO (PARTITION big_table_2005,
            PARTITION big_table_2007)
      UPDATE GLOBAL INDEXES;
    
    ALTER TABLE big_table
      SPLIT PARTITION big_table_2007 AT (TO_DATE('31-DEC-2006 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
      INTO (PARTITION big_table_2006,
            PARTITION big_table_2007)
      UPDATE GLOBAL INDEXES;
    
    EXEC DBMS_STATS.gather_table_stats(USER, 'BIG_TABLE', cascade => TRUE);
  • 7. Re: Move partition from one tablespace to other along with lob data.
    Hokins Newbie
    Currently Being Moderated
    Thanks for a fast reply. I know how to split a partition but my point of concern is to move the partition to a new tablespace. Like in the exmple u told how to move "big_table_2005" into a new tablespace along with lob data....
  • 8. Re: Move partition from one tablespace to other along with lob data.
    sb92075 Guru
    Currently Being Moderated
    Hokins wrote:
    Thanks for a fast reply. I know how to split a partition but my point of concern is to move the partition to a new tablespace. Like in the exmple u told how to move "big_table_2005" into a new tablespace along with lob data....
    DBMS_REDEFINITION
  • 9. Re: Move partition from one tablespace to other along with lob data.
    rp0428 Guru
    Currently Being Moderated
    >
    Thanks for a fast reply. I know how to split a partition but my point of concern is to move the partition to a new tablespace. Like in the exmple u told how to move "big_table_2005" into a new tablespace along with lob data....
    >
    If you are unwilling to read the doc link I gave you above that shows how to use ALTER to move the partition then I can't help you.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points