OraFAQ Forum: Server Administration » Change the partition type online

Home » RDBMS Server » Server Administration » Change the partition type online (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Change the partition type online [message #337779]

Fri, 01 August 2008 03:40

mafc73
Messages: 38
Registered: November 2006

Member

Hi,

I have a partitioned table (RANGE-HASH) and now I want to change the partition type to RANGE-LIST. I have seen that I can convert a table non partitioned to partitioned with DBMS_REDEFINITION or EXCHANGE/SPLIT but can I change the partition type online?.

If the answer is not I can think of only create an empty table partitioned and INSERT /*+ APPEND */ INTO ... SELECT * ...

Thanks

Report message to a moderator

Re: Change the partition type online [message #337796 is a reply to message #337779]

Fri, 01 August 2008 04:12

Michel Cadot
Messages: 68666
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Yes, you can do it online using DBMS_REDEFINITION.

Regards
Michel

Report message to a moderator

Re: Change the partition type online [message #337863 is a reply to message #337779]

Fri, 01 August 2008 06:50

mafc73
Messages: 38
Registered: November 2006

Member

Hi Michel,

Do you have a link with some example?.

I have trying to do it following the example with a normal table. I have created the destination table with the new partition type and following all the steps but the result is the same partition type table. I don't know where I was wrong.

Thanks

[Updated on: Fri, 01 August 2008 07:01]

Report message to a moderator

Re: Change the partition type online [message #337865 is a reply to message #337863]

Fri, 01 August 2008 07:04

Michel Cadot
Messages: 68666
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I don't know waht you did but it seems impossible.
At the end all the data are in the new table you created so it can't be with another kind of partitioning than the one you created.

If you post (copy and paste) what you did maybe we can see what is wrong.

Regards
Michel

Report message to a moderator

Re: Change the partition type online [message #337869 is a reply to message #337779]

Fri, 01 August 2008 07:24

mafc73
Messages: 38
Registered: November 2006

Member

In this probe I want to convert a RANGE-LIST to RANGE.

Original table


CREATE TABLE IMPRESSION
(
  CMPGN_ID           NUMBER(7),
....
  RANGO_MES          VARCHAR2(19 BYTE)
)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
PARTITION BY RANGE (CMPGN_ID) 
SUBPARTITION BY LIST (RANGO_MES)
(  
  PARTITION P20703 VALUES LESS THAN (20704)
    NOLOGGING
    NOCOMPRESS
    TABLESPACE USERS
    PCTFREE    10
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                BUFFER_POOL      DEFAULT
               )
  ( SUBPARTITION "P20703-00001-00030" VALUES ('P20703-00001-00030')    TABLESPACE USERS,
    SUBPARTITION "P20703-06819-06848" VALUES ('P20703-06819-06848')    TABLESPACE DATOS,
    SUBPARTITION "P20703-06849-06879" VALUES ('P20703-06849-06879')    TABLESPACE DATOS,
    SUBPARTITION "P20703-06788-06818" VALUES ('P20703-06788-06818')    TABLESPACE USERS,
    SUBPARTITION "P20703-00031-00061" VALUES ('P20703-00031-00061')    TABLESPACE MIGRACION )
)
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

Destination table


CREATE TABLE IMPRESSION2
(
  CMPGN_ID           NUMBER(7),
...
  RANGO_MES          VARCHAR2(19 BYTE)
)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
PARTITION BY RANGE (CMPGN_ID) 
(  
  PARTITION P20703 VALUES LESS THAN (20704)
    NOLOGGING
    NOCOMPRESS
    TABLESPACE USERS
    PCTFREE    10
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                BUFFER_POOL      DEFAULT
               )
)
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

Process


EXEC DBMS_REDEFINITION.START_REDEF_TABLE(UNAME=>'PRUEBA', orig_table=>'IMPRESSION', int_table=>'IMPRESSION2');


EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE(UNAME=>'PRUEBA', orig_table=>'IMPRESSION', int_table=>'IMPRESSION2');


EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(UNAME=>'PRUEBA', orig_table=>'IMPRESSION', int_table=>'IMPRESSION2');

Result table


CREATE TABLE IMPRESSION2
(
  CMPGN_ID           NUMBER(7),
....
  RANGO_MES          VARCHAR2(19 BYTE)
)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
PARTITION BY RANGE (CMPGN_ID) 
SUBPARTITION BY LIST (RANGO_MES)
(  
  PARTITION P20703 VALUES LESS THAN (20704)
    NOLOGGING
    NOCOMPRESS
    TABLESPACE USERS
    PCTFREE    10
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                BUFFER_POOL      DEFAULT
               )
  ( SUBPARTITION "P20703-00001-00030" VALUES ('P20703-00001-00030')    TABLESPACE USERS,
    SUBPARTITION "P20703-06819-06848" VALUES ('P20703-06819-06848')    TABLESPACE DATOS,
    SUBPARTITION "P20703-06849-06879" VALUES ('P20703-06849-06879')    TABLESPACE DATOS,
    SUBPARTITION "P20703-06788-06818" VALUES ('P20703-06788-06818')    TABLESPACE USERS,
    SUBPARTITION "P20703-00031-00061" VALUES ('P20703-00031-00061')    TABLESPACE MIGRACION )
)
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

[Updated on: Fri, 01 August 2008 07:25]

Report message to a moderator

Re: Change the partition type online [message #337898 is a reply to message #337869]

Fri, 01 August 2008 08:41

Michel Cadot
Messages: 68666
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

First step should be a call to "dbms_redefinition.can_redef_table".
Then you have to copy and paste the execution of what you did.

Regards
Michel

Report message to a moderator

Re: Change the partition type online [message #338440 is a reply to message #337779]

Tue, 05 August 2008 02:10

mafc73
Messages: 38
Registered: November 2006

Member

Hi Michel,

I don't know what I did wrong in my first test but in the second it worked.

Thanks.

Report message to a moderator

Previous Topic:	Purge logs
Next Topic:	ORA-04034: unable to shrink pool to specified size

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jul 08 14:44:59 CDT 2024