-3

Deal All,

I created a table with partitions and inserted data in tables. I mention tablespace for partitions correctly But i forget to mention tablespace for table. Now i tried to move tablespace for table by using "Alter Table INC_MDK Move Tablespace KOP_PES_D" command. But it shown oracle error 

'ORA-14511: cannot perform operation on a partitioned object';

Please revert me if you need any other information for analysis.

My Table creation script 

CREATE TABLE INC_MDK
  (
    INC_MDK_USER_IPN VARCHAR2(7 CHAR),
    INC_COD_AKK      VARCHAR2(6 CHAR) NOT NULL,
    INC_MDK_DATE     DATE ,
    CONSTRAINT INC_C02 UNIQUE (INC_MDK_USER_IPN) USING INDEX TABLESPACE KOP_PES_I
  )
  partition BY range
  (
    INC_MDK_DATE
  )
  (
    partition INC_AVAN VALUES less than ( TO_DATE ('01-09-2000', 'DD-MM-YYYY') ) TABLESPACE KOP_PES_D,
    partition INC_0009 VALUES less than ( TO_DATE ('01-10-2000', 'DD-MM-YYYY') ) TABLESPACE KOP_PES_D,
   partition INC_APRS values less than (MAXVALUE)
   tablespace SOP_PES_D
 enable row movement ;
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
user3797516
  • 1
  • 1
  • 1

1 Answers1

1

A partitioned table doesn't have a tablespace. Each partition is a separate segment. Each partition will be in a tablespace. But since the data is stored in one of the partition's segments, there is no need for the table to have a segment so no need for the table's segment to be assigned to a tablespace.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • Thanks Justin Cave for your helpful information. Here is My Client Expecting to Move tablespace :( . – user3797516 Mar 13 '15 at 06:58
  • @user3797516 - As I said, you can't. Well, I suppose you could drop the table, recreate it as a non-partitioned table, and create the table segment in whatever tablespace you desire. But that doesn't seem like a useful solution. – Justin Cave Mar 13 '15 at 07:02
  • @user3797516 - you need to discuss this with your client. The tablespace defines where the data is stored. For partitioned tables that definition applies to the partitions not the table. Because the table in this situation is just a label to identify a collection of partitions. So, what is the real problem your client wants you to solve? – APC Mar 13 '15 at 07:24
  • Thanks Justin Cave and APC for your spending valuable time for me. First i will explain above details to my client. if not i will go for Justin Cave suggestion. – user3797516 Mar 13 '15 at 10:34