OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » Partition

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

Partition [message #299918]

Wed, 13 February 2008 12:29

caprikar
Messages: 226
Registered: March 2007

Senior Member

Hi I have the following table which is not partitioned and I want to partition it, here is the method am using to partition that table, can anyone tell me is it the right way to do or is there anything else i need to look at before i partition?

Current status:

CREATE TABLE CHANGE_REC
(
CHANGE_REC_ID NUMBER(15) NOT NULL,
CHANGE_LOG_ID NUMBER(15),
TAB_NAME VARCHAR2(30 BYTE),
COL_NAME VARCHAR2(30 BYTE),
OLD_VALUE VARCHAR2(4000 BYTE),
NEW_VALUE VARCHAR2(4000 BYTE),
CHNGE_TIME TIMESTAMP(6)
)
TABLESPACE DATA
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

CREATE UNIQUE INDEX PK_CHANGE_REC ON CHANGE_REC
(CHANGE_REC_ID)
LOGGING
TABLESPACE INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;

ALTER TABLE CHANGE_REC ADD (
CONSTRAINT PK_CHANGE_REC
PRIMARY KEY
(CHANGE_REC_ID)
USING INDEX
TABLESPACE INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
);

---------------------------------------------------------

Want to partition that table using..

CREATE TABLE CHANGE_REC
(
CHANGE_REC_ID NUMBER(15) NOT NULL,
CHANGE_LOG_ID NUMBER(15),
TABLE_NAME VARCHAR2(30 BYTE),
COL_NAME VARCHAR2(30 BYTE),
OLD_VALUE VARCHAR2(4000 BYTE),
NEW_VALUE VARCHAR2(4000 BYTE),
CHNGE_TIME TIMESTAMP(6)
)
TABLESPACE DATA
LOGGING
PARTITION BY RANGE (CHNGE_TIME)
(
PARTITION Y08M01 VALUES LESS THAN (TIMESTAMP'2008-02-01 00:00:00')
LOGGING
NOCOMPRESS
TABLESPACE DATA,

PARTITION Y08M02 VALUES LESS THAN (TIMESTAMP'2008-03-01 00:00:00')
LOGGING
NOCOMPRESS
TABLESPACE DATA,

PARTITION Y08M03 VALUES LESS THAN (TIMESTAMP'2008-04-01 00:00:00')
LOGGING
NOCOMPRESS
TABLESPACE DATA
)
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

CREATE UNIQUE INDEX PK_CHANGE_REC ON CHANGE_REC
(CHANGE_LOG_ID, CHNGE_TIME)
TABLESPACE INDEX
INITRANS 2
MAXTRANS 255
LOGGING
LOCAL (
PARTITION Y08M01
LOGGING
NOCOMPRESS
TABLESPACE INDEX,

PARTITION Y08M02
LOGGING
NOCOMPRESS
TABLESPACE INDEX,

PARTITION Y08M03
LOGGING
NOCOMPRESS
TABLESPACE INDEX
)
NOPARALLEL;

ALTER TABLE CHANGE_REC ADD (
CONSTRAINT PK_CHANGE_REC
PRIMARY KEY
(CHANGE_REC_ID, CHNGE_TIME)
USING INDEX
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
);

Report message to a moderator

Re: Partition [message #299920 is a reply to message #299918]

Wed, 13 February 2008 12:38

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

Senior Member
Account Moderator

Quote:

is there anything else i need to look

Maybe OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.
Also post your Oracle version (4 decimals).

Regards
Michel

Report message to a moderator

Re: Partition [message #299928 is a reply to message #299920]

Wed, 13 February 2008 13:15

caprikar
Messages: 226
Registered: March 2007

Senior Member

We are using Oracle 10.2.0.2 and following are the statements again:

CREATE TABLE CHANGE_REC
(
  CHANGE_REC_ID  NUMBER(15)                  NOT NULL,
  CHANGE_LOG_ID   NUMBER(15),
  TAB_NAME        VARCHAR2(30 BYTE),
  COL_NAME          VARCHAR2(30 BYTE),
  OLD_VALUE         VARCHAR2(4000 BYTE),
  NEW_VALUE         VARCHAR2(4000 BYTE),
  CHNGE_TIME       TIMESTAMP(6)
)
TABLESPACE DATA
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

CREATE UNIQUE INDEX PK_CHANGE_REC ON CHANGE_REC
(CHANGE_REC_ID)
LOGGING
TABLESPACE INDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

ALTER TABLE CHANGE_REC ADD (
  CONSTRAINT PK_CHANGE_REC
 PRIMARY KEY
 (CHANGE_REC_ID)
    USING INDEX 
    TABLESPACE INDEX
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
 );

I will use the following to partition:

CREATE TABLE CHANGE_REC
(
  CHANGE_REC_ID  NUMBER(15)                  NOT NULL,
  CHANGE_LOG_ID   NUMBER(15),
  TABLE_NAME        VARCHAR2(30 BYTE),
  COL_NAME          VARCHAR2(30 BYTE),
  OLD_VALUE         VARCHAR2(4000 BYTE),
  NEW_VALUE         VARCHAR2(4000 BYTE),
  CHNGE_TIME       TIMESTAMP(6)
)
TABLESPACE DATA
LOGGING
PARTITION BY RANGE (CHNGE_TIME) 
(  
  PARTITION Y08M01 VALUES LESS THAN (TIMESTAMP'2008-02-01 00:00:00')
    LOGGING
    NOCOMPRESS
    TABLESPACE DATA,
  
  PARTITION Y08M02 VALUES LESS THAN (TIMESTAMP'2008-03-01 00:00:00')
    LOGGING
    NOCOMPRESS
    TABLESPACE DATA,
  
  PARTITION Y08M03 VALUES LESS THAN (TIMESTAMP'2008-04-01 00:00:00')
    LOGGING
    NOCOMPRESS
    TABLESPACE DATA
)
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX PK_CHANGE_REC ON CHANGE_REC
(CHANGE_LOG_ID, CHNGE_TIME)
  TABLESPACE INDEX
  INITRANS   2
  MAXTRANS   255
LOGGING
LOCAL (  
  PARTITION Y08M01
    LOGGING
    NOCOMPRESS
    TABLESPACE INDEX,  

  PARTITION Y08M02
    LOGGING
    NOCOMPRESS
    TABLESPACE INDEX,
  
  PARTITION Y08M03
    LOGGING
    NOCOMPRESS
    TABLESPACE INDEX
)
NOPARALLEL;


ALTER TABLE CHANGE_REC ADD (
  CONSTRAINT PK_CHANGE_REC
 PRIMARY KEY
 (CHANGE_REC_ID, CHNGE_TIME)
    USING INDEX 
    TABLESPACE USERS
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
 );

Report message to a moderator

Re: Partition [message #299932 is a reply to message #299928]

Wed, 13 February 2008 13:26

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

Senior Member
Account Moderator

Quote:

is there anything else i need to look at before i partition?

You have to clearly know why you have to (not want) partition.

Regards
Michel

Report message to a moderator

Re: Partition [message #299935 is a reply to message #299932]

Wed, 13 February 2008 14:24

caprikar
Messages: 226
Registered: March 2007

Senior Member

I know why I wnat to partition, but my question is, is my approach is right or not?

Report message to a moderator

Re: Partition [message #299937 is a reply to message #299935]

Wed, 13 February 2008 14:59

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

Senior Member
Account Moderator

Right regarding what?
Is the syntax valid? Yes, so it is alright. No, it is not.

Regards
Michel

Report message to a moderator

Previous Topic:	changing host name problem
Next Topic:	Low open file descriptor

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Sep 16 05:48:32 CDT 2024