Home » RDBMS Server » Server Administration » Change the partition type online (Oracle 10g)
Change the partition type online [message #337779] Fri, 01 August 2008 03:40 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
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 Go to previous message
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:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Nov 29 19:55:18 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.