Skip to Main Content
  • Questions
  • Is it possible to add a Partion on existing table

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, shan.

Asked: July 14, 2002 - 11:29 pm UTC

Last updated: January 30, 2009 - 3:03 pm UTC

Version: 9.0.1

Viewed 10K+ times! This question is

You Asked

Hi Tom,
I have a table called SO, It is not partioned, I need to partion based on the column trans_date. If trans_date's month Jan means partition m1, feb means m2 .. upto 12 partition. I don't want to recreate the table. How can I alter the table with the above 12 partion. I triled by using the following syntax, ALTER TABLE pt ADD PARTITION p3 VALUES LESS THAN (somevalue); But I got ORA-14501: object is not partitioned Error. Thanks in advance.

With best regards
shan




and Tom said...


Well, one way or another, you'll have to recreate the table -- there is quite simply NO WAY AROUND that. Think about it -- you have an existing table full of data. Rows with jan are next to feb are next to mar. The entire table needs to be rewritten.

You can use online redefinition to accomplish this if you like (or just

a) export the table
b) create a new empty table that has the partition definition on it
c) import the table with IGNORE=Y


Here is an online redef example.

=================================================================


Lets build a table we'll want to redefine.


ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> create table t NOLOGGING
2 as
3 select * from all_objects;

Table created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> set echo off
Enter to continue

ops$tkyte@ORA920.US.ORACLE.COM> alter table t add constraint t_pk primary key(object_id);

Table altered.

ops$tkyte@ORA920.US.ORACLE.COM> exec dbms_redefinition.can_redef_table( user, 'T' );

PL/SQL procedure successfully completed.

=================================================================

Here is the new table we want to reorg into , we are dropping some
NOT NULL constraints, creating a derived column flags from other columns
and getting rid of some columns -- renaming others (and partitioning it)


ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> create table t_interim
2 (
3 object_id number primary key,
4 object_type varchar2(18),
5 owner varchar2(30),
6 oname varchar2(30),
7 created date,
8 last_ddl_time date,
9 status varchar2(7),
10 flags varchar2(5)
11 )
12 partition by hash(object_id) partitions 8
13 /

Table created.

ops$tkyte@ORA920.US.ORACLE.COM>

=================================================================
This block of code starts moving the data over for us. changes
to the base table are being recorded off to the side while this happens..


ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> declare
2 l_colmap varchar(512);
3 begin
4 l_colmap :=
5 'object_id,
6 object_type,
7 owner,
8 object_name oname,
9 created,
10 last_ddl_time,
11 status,
12 temporary || ''/'' || generated || ''/'' || secondary flags ';
13
14 dbms_redefinition.start_redef_table
15 ( uname => user,
16 orig_table => 'T',
17 int_table => 'T_INTERIM',
18 col_mapping => l_colmap );
19 end;
20 /

PL/SQL procedure successfully completed.

=================================================================
here you would
- index t_interim
- grant on it
- add constraints
- add triggers
- add EVERYTHING you need to make t_interim look just like T,
behave just like T
=================================================================


Now we'll sync them -- to speed up the "finish". The finish will
be a non-online operation -- so we want it to be fast


ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> begin
2 dbms_redefinition.sync_interim_table( user, 'T', 'T_INTERIM' );
3 end;
4 /

PL/SQL procedure successfully completed.

=================================================================


Tables are synced, we are ready to rollover


ops$tkyte@ORA920.US.ORACLE.COM> begin
2 dbms_redefinition.finish_redef_table( user, 'T', 'T_INTERIM' );
3 end;
4 /

PL/SQL procedure successfully completed.

ops$tkyte@ORA920.US.ORACLE.COM> set echo off
=================================================================


This finished the process, this is the ONLY time the table
is in fact locked. We can verify the tables are swapped now

Enter to continue

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> desc t
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
OBJECT_ID NOT NULL NUMBER
OBJECT_TYPE VARCHAR2(18)
OWNER VARCHAR2(30)
ONAME VARCHAR2(30)
CREATED DATE
LAST_DDL_TIME DATE
STATUS VARCHAR2(7)
FLAGS VARCHAR2(5)

ops$tkyte@ORA920.US.ORACLE.COM> desc t_interim
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(18)
CREATED NOT NULL DATE
LAST_DDL_TIME NOT NULL DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> set echo off

Table dropped.


Table dropped.

ops$tkyte@ORA920.US.ORACLE.COM> quit

Rating

  (36 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Partion on existing table

shan, July 18, 2002 - 5:54 am UTC

Thanks Mr.Tom. The information is very useful to me.
Another clarification, I have table SO this is my master
table. Then I have following transaction table. SOI(Sales_order_items), SOID(Sales_order_item_details), SOP(Sales_order_payments). If I do the partition for master table, will it take care of all transaction tables records ? That is, Is any way of Group Partition based on master deatil relation ship. Basically I need to do common logical partition for all the above four tables based master detail relation ship.



Tom Kyte
July 18, 2002 - 8:38 am UTC

You would partition each table independently of every other table. Partitioning is at the segment level only -- no cross segment. A partitioned parent table will not affect the child tables physical characteristics at all.

Partion on existing table

shan, July 18, 2002 - 9:01 am UTC

Thank your very much for your information. I have a table called customer.
It contains a column id it has the value like following
ID
-------------------
S0142877B
S0113547C
S2200626F
S1465471B
S2665992B
S1174972D
S2500848B
S7838243A
The last character has some uniqeness. I want to createi partition based on the last character.
RangeA means last charter A,
RangeB means last charter B,
RangeC means last charter C,
RangeD means last charter D,
RangeE means last charter E,
RangeO means last character may be other value. Can you pls give me some solutions.


Tom Kyte
July 18, 2002 - 9:24 am UTC

You'll need to create another column that is this character and partition based on that.

Partion on existing table

shan, July 18, 2002 - 9:39 am UTC

Thanks Mr.Tom. The table have no data now. I can drop and re-create a table with additional column. Then how to do the Partition, can you give me a example ?

Tom Kyte
July 18, 2002 - 10:36 am UTC

Sorry, for that (something so basic) I'll just refer you to the documentation. You should learn about the feature before even attempting to use it.

Partitioning Example

Jan van Mourik, July 18, 2002 - 10:27 am UTC

Table partition

Neena, September 18, 2002 - 10:58 pm UTC

Very good.
I am having a partitioned table which is partitioned on date. I had defined a partition named cust_aug for which values is less than '20020831'. I want to redefine the value as less than '20020901', the partition is having data in it. how can i redefine the value for a partition.
Alter table modify partition command is not working

Regards,

neena

Tom Kyte
September 19, 2002 - 7:38 am UTC

Need more info. is there a partition "above" this one already -- you would merge them. if not, you could add one and merge them.

Will the foreign key relationships of the redefined table be maintained?

A reader, December 06, 2002 - 4:32 pm UTC

Hi Tom,

A quick question. Say table t in your example had two child tables, tc1 and tc2,
where t.object_id is the primary key and tc1.object_id and tc2.object_id are
foreign keys. Also, say we create the t_interim table with object_id as the
primary_key. Then after partition via dbms_redefinition, will the fk
relationship between t, tc1 and t, tc2 be maintained? (I can understand that if
I want to partition tc1 and tc2, I would have to do that separately).

Thanks so much, Tom.

Tom Kyte
December 07, 2002 - 9:33 am UTC

You'll want to read:

http://docs.oracle.com/docs/cd/B10501_01/server.920/a96521/tables.htm#12458

which is "redefining tables online" for all of the details but basically it works like this.  You create the fkeys to the interim table from the child tables in a disabled mode.  when we finish the redef, we disable the old fkeys and enable the news (without having to validate).  consider:

ops$tkyte@ORA920.US.ORACLE.COM> create table t NOLOGGING
  2  as
  3  select * from all_objects;

Table created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> alter table t add constraint t_pk primary key(object_id);

Table altered.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> create table t1( x references t );

Table created.

ops$tkyte@ORA920.US.ORACLE.COM> create table t2( y references t );

Table created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> insert into t1 select object_id from t where rownum <= 100;

100 rows created.

ops$tkyte@ORA920.US.ORACLE.COM> insert into t2 select object_id from t where rownum <= 100;

100 rows created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> create table t_interim
  2  (
  3    object_id        number primary key,
  4    object_type      varchar2(18),
  5    owner         varchar2(30),
  6    oname         varchar2(30),
  7    created       date,
  8    last_ddl_time date,
  9    status        varchar2(7),
 10    flags         varchar2(5)
 11  )
 12  partition by hash(object_id) partitions 8
 13  /

Table created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> declare
  2          l_colmap varchar(512);
  3  begin
  4          l_colmap :=
  5               'object_id,
  6                    object_type,
  7                    owner,
  8                    object_name oname,
  9                    created,
 10                    last_ddl_time,
 11                    status,
 12            temporary || ''/'' || generated || ''/'' || secondary flags ';
 13
 14          dbms_redefinition.start_redef_table
 15          ( uname => user,
 16            orig_table => 'T',
 17            int_table => 'T_INTERIM',
 18            col_mapping => l_colmap );
 19  end;
 20  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> alter table t1 add constraint t1_new_fk foreign key(x) references t_interim <b>disable;</b>

Table altered.

ops$tkyte@ORA920.US.ORACLE.COM> alter table t2 add constraint t2_new_fk foreign key(y) references t_interim <b>disable;</b>

Table altered.

ops$tkyte@ORA920.US.ORACLE.COM> select constraint_name, status from user_constraints where constraint_type = 'R';

CONSTRAINT_NAME                STATUS
------------------------------ --------
SYS_C004733                    ENABLED     <b><<<== original constraint</b>
T1_NEW_FK                      DISABLED
SYS_C004734                    ENABLED
T2_NEW_FK                      DISABLED

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> begin
  2     dbms_redefinition.sync_interim_table( user, 'T', 'T_INTERIM' );
  3  end;
  4  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA920.US.ORACLE.COM> begin
  2     dbms_redefinition.finish_redef_table( user, 'T', 'T_INTERIM' );
  3  end;
  4  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> select constraint_name, status from user_constraints where constraint_type = 'R';

CONSTRAINT_NAME                STATUS
------------------------------ --------
SYS_C004733                    DISABLED       <b><<< flip flopped the status</b>
T1_NEW_FK                      ENABLED
SYS_C004734                    DISABLED
T2_NEW_FK                      ENABLED

ops$tkyte@ORA920.US.ORACLE.COM> drop table t_interim cascade constraints;

Table dropped.

ops$tkyte@ORA920.US.ORACLE.COM> select constraint_name, status from user_constraints where constraint_type = 'R';

CONSTRAINT_NAME                STATUS
------------------------------ --------
T1_NEW_FK                      ENABLED
T2_NEW_FK                      ENABLED

ops$tkyte@ORA920.US.ORACLE.COM> drop table t;
drop table t
           *
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

<b>and that just shows that T is now the parent table</b>
 

Wow, what a thorough reply! What about partitioning the indexes?

A reader, December 09, 2002 - 3:28 pm UTC

Tom, thanks so much for a such a thorough reply on the constraints. I have one more question on indexes before I partition an existing table. Here is some background:

I have a non-partitioned table t with 3 million rows as follows:

create table t (
pid number(10),
attrA varchar2(30),
attrB varchar2(30),
attrC varchar2(30)
constraint t_pk primary key (pid)
);

This table also has function based indexes as follows:
create index t_idx_a on t (upper(attrA));
create index t_idx_b on t (upper(attrB));


I would like to partition this table via dbms_redefinition, using pid as the partition key. pid is the foreign key in a lot of child tables and hence is used a lot in joins. There are few other important queries which make use of the t_idx_a and t_idx_b indexes.

All these indexes are stored in a separate tablespace from data.

My questions are

1) if pid is a sequence-generated number, bearing no significance to our application as such, what kind of partition would be better - range or hash?

2) what kind of partition, if any, do I need to create on the 3 indexes mentioned above? Could you kindly provide some guidance? Your help will be greatly appreciated, as always.

Thanks,
Raj


Tom Kyte
December 09, 2002 - 3:53 pm UTC

1) sounds like HASH if you do equi-comparisions (equals). A hash does not allow for range scans with partition elimination (eg: where pid between X and Y would have to look in all partitions -- where pid = X looks in one)....


2) you might not need ANY, again, it depends on how you use them -- how big they are. 3 million rows isn't really that "large" these days.

If you query "select * from t where upper(attrA) = 'VALUE'" -- you won't want that index to be locally partitioned (we would have to do 3 index range scans now instead of just one). You would want a single global index .

If you query "select * from t where pid = X and upper(attrA) = 'VALUE'" you can locally partition.


What I do is envision how the data would be laid out PHYSICALLY. Then, run some sample queries through your head and ask yourself "can we elimination partitions down to 1 or will we end up hitting them all".... That'll help you answer "whats best"

Thanks for the previous reply. Hash vs. Range for equi-comparisons

A reader, December 10, 2002 - 9:58 am UTC

Tom, your recommendation of hash in 1)is so helpful. Yes, equi-comparisons such as 'where pid = 123' is exactly the kind of queries we do with pid. I am just trying to understand why you recommended hash over range..wouldn't range also eliminate partitions in equi-comparisons?

Thanks again, Tom.

Tom Kyte
December 10, 2002 - 12:14 pm UTC

but you have nothing by which to "range" on -- that is why.

tell me -- how would you propose to use RANGE on a sequence meaningfully in this case?

I was thinking of pre-creating a few partitions..

A reader, December 11, 2002 - 3:43 pm UTC

Tom,
Forgive my ignorance but I was thinking of pre-creating the a few partitons.. This is not as meaningful as Hash but is it sensible at all to do this?
Thanks.

create table t (
pid number (10)
...
...
partition by range (pid) (
partition part1 values less than (1000001) tablespace users,
partition part2 values less than (2000001) tablespace users,
partition part3 values less than (3000001) tablespace users,
partition part4 values less than (4000001) tablespace users,
partition part5 values less than (5000001) tablespace users,
partition part6 values less than (maxvalue) tablespace users
)
;

Tom Kyte
December 11, 2002 - 10:29 pm UTC

No, all of the data would be in part1 pretty much. And then part2. And then part3 (after a really long time). What would that do for you?

what is your goal, what are you trying to achieve via partitioning.
List that out on paper.

then ask yourself -- will range or hash help me get there.

Add range partition - 9iR2 compression feature

Raj, February 05, 2003 - 6:39 pm UTC

Hi Tom,

I did use hash partition based on the sequence-generated primary key. See my DDL below. My questions are follows:

Now,

1) Per this discussion on your site
"Table Compression -- in regards to Jan/Feb 2003 article"
I would like to take advantage of this compression feature so that all records with ins_date > 6 months old would be compressed but still available in this table rather than be moved to a 'historical' db. How do I add ins_date based range partition on top of the existing hash partition?

2)The only index I have partitoned is the primary key. All other indexes are not-partitioned. Do you recommend any other indexes to be created local nonprefixed? Here are the different where clauses that are used against this table.

where pid=123
where devpart.pid = some_other_table.pid
where upper(serial_num) = 'ABC123'
where upper(pin) = 'ABC123'
where c_user_id = 123
where m_user_id = 234

=======================

create sequence seq_devpart;

CREATE TABLE DEVPART (
PID NUMBER(10) NOT NULL,
SERIAL_NUM VARCHAR2(50),
PIN VARCHAR2(8),
MON CHAR(1),
LEVEL NUMBER(10),
INS_DATE DATE NOT NULL,
MOD_DATE DATE,
C_USER_ID NUMBER(10) NOT NULL,
M_USER_ID NUMBER(10),
CONSTRAINT PK_DEVPART
PRIMARY KEY ( PID )
USING INDEX local
(partition pk_devpart_idx1 tablespace bigindx,
partition pk_devpart_idx2 tablespace bigindx,
partition pk_devpart_idx3 tablespace bigindx,
partition pk_devpart_idx4 tablespace bigindx,
partition pk_devpart_idx5 tablespace bigindx,
partition pk_devpart_idx6 tablespace bigindx,
partition pk_devpart_idx7 tablespace bigindx,
partition pk_devpart_idx8 tablespace bigindx)
partition pk_devpart_idx7 tablespace bigindx,
partition pk_devpart_idx8 tablespace bigindx)
STORAGE ( PCTINCREASE 0 )
)
partition by hash(pid)
(
partition devpart1 tablespace bigusers,
partition devpart2 tablespace bigusers,
partition devpart3 tablespace bigusers,
partition devpart4 tablespace bigusers,
partition devpart5 tablespace bigusers,
partition devpart6 tablespace bigusers,
partition devpart7 tablespace bigusers,
partition devpart8 tablespace bigusers
)
PCTFREE 10 PCTUSED 40
INITRANS 1 MAXTRANS 255
STORAGE ( PCTINCREASE 0)
;


create unique index un_devpart_1 on devpart (
decode(serial_num,null,null,serial_num)
)
PCTFREE 10
TABLESPACE bigindx
STORAGE(PCTINCREASE 0
) ;

create unique index un_devpart_2 on devpart ( decode(pin,null,null,pin) )
PCTFREE 10
TABLESPACE bigindx
STORAGE(PCTINCREASE 0
) ;

create index devpart_upper_pin on devpart (UPPER(pin))
PCTFREE 10
TABLESPACE indx
STORAGE(PCTINCREASE 0
) ;

create index devpart_upper_serial on devpart (UPPER(serial_num))
PCTFREE 10
TABLESPACE indx
PCTFREE 10
TABLESPACE indx
STORAGE(PCTINCREASE 0
) ;
CREATE INDEX DUP_DEVPART_2 ON DEVPART(M_USER_ID)
PCTFREE 10
TABLESPACE bigindx
STORAGE(PCTINCREASE 0
) ;

CREATE INDEX DUP_DEVPART_3 ON DEVPART(C_USER_ID)
PCTFREE 10
TABLESPACE bigindx
STORAGE(PCTINCREASE 0
) ;


Tom Kyte
February 06, 2003 - 7:31 am UTC

1) by creating a composite partitioned table -- partition by range subpartition by hash...

BUT -- and this is a big BUT -- that could be deadly if your queries do not include this date field in their predicate as well -- or you change you indexing structure. If your queries are all by this primary key -- all of a sudden you might well find you are doing N index range scans instead of just one as partition elimination may not be able to kick in.

Analyze how you query the data before doing something physical to the underlying organization of it.


2) Only you can make that judgment call. Understand what partitioning would do to the index, imagine what the query would have to do in order to find the data -- would index partition elimination still work?

Benchmark it -- test it, set up the empty tables and use dbms_stats to set table stats and see what the query plans are (looking at the pstart/pstop's to make sure what you want to be happening is)

easier said than done..??

anonymoys, March 18, 2003 - 11:40 am UTC

Tom,

You said:
============================================================
here you would
- index t_interim
- grant on it
- add constraints
- add triggers
- add EVERYTHING you need to make t_interim look just like T,
behave just like T
=================================================================

this seems to have some big consequences when your table has tons of triggers, synonyms, contraints, and everything has be redefined with new names.
correct?

if I had a synonym on table t, named t_syn, do I now have to create a new synonym named t_syn_new? If so, this would than interfere with all the applications that use the synonyms, or anything that referenced the name of something that was previously defined with a different name.

Does the redef wipe out everything that was associated with t before?
If not, couldn't my synonyms, triggers, grants, etc.. be left in place?

I thought this might just be a temporary name that would be matched back up with the original name based on definition, but from your index example with the enabling/disabling of the indexes, it showed they are in fact renamed.

Am I missing something Tom?
Thanks in advance.

Tom Kyte
March 18, 2003 - 12:52 pm UTC

you can rename triggers contraints everything...


the synonym is unchanged.


the redef doesn't wipe out ANYTHING -- you do. The redef just copies data and lets you change names in the data dictionary. You do the rest. You end up with TWO objects at the end.

You cannot just have the triggers/et al get transferred - the redef could

o invalidate them (did some transformation)
o change them
o render them obsolete
o have been done in order to change them






decode(pin, null, null, pin) ????

Logan Palanisamy, March 18, 2003 - 2:06 pm UTC

Tom,

What does the decode in the statement below (posted on Feb 05 on this thread) achieve?.

create unique index un_devpart_2 on devpart (decode(pin,null,null,pin) )
PCTFREE 10
TABLESPACE bigindx
STORAGE(PCTINCREASE 0);

I think the decode doesn't server any purpose. What is your opinion?

Thanks as always

Tom Kyte
March 18, 2003 - 4:46 pm UTC

well, in that particular case it is sort of like indexing to_char(pin) since the decode is a defacto conversion from number to char in this case...

but yes, I don't see its rational purpose in life.

Getting error ORA-06512: at "SYS.DBMS_REDEFINITION", line 8

Sikandar Hayat, April 26, 2003 - 1:08 pm UTC

Hi TOM I was trying to practice your example and can_redef_table has given no error then why i am getting this error.

SCOTT > select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
PL/SQL Release 9.0.1.1.1 - Production
CORE 9.0.1.1.1 Production
TNS for 32-bit Windows: Version 9.0.1.1.0 - Production
NLSRTL Version 9.0.1.1.1 - Production

SCOTT > drop table t;

Table dropped.

SCOTT > create table t nologging as select * from all_objects;

Table created.

SCOTT > set echo off
SCOTT > alter table t add constraint t_pk primary key(object_id);

Table altered.



SCOTT > alter table t add constraint t_pk primary key(object_id);

SCOTT > exec dbms_redefinition.can_redef_table(user,'T');

PL/SQL procedure successfully completed.

SCOTT > create table t_interim(
2 object_id number primary key,
3 object_type varchar2(18),
4 owner varchar2(30),
5 oname varchar2(30),
6 created date,
7 last_ddl_time date,
8 status varchar2(7),
9 flags varchar2(5)) partition by hash(object_id) partition 8/
10 /
Table created.

SCOTT > declare
2 l_colmap varchar(512);
3 begin
4 l_colmap :=
5 'object_id,
6 object_type,
7 owner,
8 object_name oname,
9 created,
10 last_ddl_time,
11 status,
12 temporary || ''/'' || generated || ''/'' || secondary flags ';
13 dbms_redefinition.start_redef_table
14 ( uname => user,
15 orig_table => 'T',
16 int_table => 'T_INTERIM',
17 col_mapping => l_colmap );
18* end;
19 /
declare
*
ERROR at line 1:
ORA-12091: cannot online redefine table "SCOTT"."T" with materialized views
ORA-06512: at "SYS.DBMS_REDEFINITION", line 8
ORA-06512: at "SYS.DBMS_REDEFINITION", line 138
ORA-06512: at line 13




Tom Kyte
April 26, 2003 - 1:44 pm UTC

12091, 00000, "cannot online redefine table \"%s\".\"%s\" with materialized views"
// *Cause: An attempt was made to online redefine a table that had
// materialized views defined on it or had a materialized view log
// defined on it or is a master.
// *Action: Drop all materialized views and materialized view logs before
// attempting to online redefine the table.


that is not a cut and paste -- something else is happening here. query your data dictionary and see if there isn't a MV on T already.

You are right

Sikandar Hayat, April 27, 2003 - 6:22 am UTC

This time no error. In the last copy, paste there were some errors due to typeing mistakes in the procedures which I did not paste here but there was no creation last time.
Anyway thanks for your nice response. Next time I will try atleast 3 times and then will contact you.

SCOTT > drop table t;

Table dropped.

SCOTT > create table t nologging
2 as
3 select * from all_objects;

Table created.

SCOTT > set echo off
SCOTT > alter table t add constraint t_pk primary key(object_id);

Table altered.

SCOTT > exec dbms_redefinition.can_redef_table(user,'T');

PL/SQL procedure successfully completed.

SCOTT > create table t_interim
2 (
3 object_id number primary key,
4 object_type varchar2(18),
5 owner varchar2(30),
6 oname varchar2(30),
7 created date,
8 last_ddl_time date,
9 status varchar2(7),
10 flags varchar2(5)
11 )
12 partition by hash(object_id) partitions 8
13 /
create table t_interim
*
ERROR at line 1:
ORA-00955: name is already used by an existing object


SCOTT > drop table t_interim;

Table dropped.

SCOTT > ed
Wrote file afiedt.buf

1 create table t_interim
2 (
3 object_id number primary key,
4 object_type varchar2(18),
5 owner varchar2(30),
6 oname varchar2(30),
7 created date,
8 last_ddl_time date,
9 status varchar2(7),
10 flags varchar2(5)
11 )
12* partition by hash(object_id) partitions 8
SCOTT > /

Table created.

SCOTT > declare
2 l_colmap varchar(512);
3 begin
4 l_colmap :=
5 'object_id,
6 object_type,
7 owner,
8 object_name oname,
9 created,
10 last_ddl_time,
11 status,
12 temporary || ''/'' || generated || ''/'' || secondary flags';
13
14 dbms_redefinition.start_redef_table
15 (uname => user,
16 orig_table => 'T',
17 int_table => 'T_INTERIM',
18 col_mapping => l_colmap);
19
20 end;
21 /

PL/SQL procedure successfully completed.
****************************************************
From another session to check the synch effect.

SCOTT > update t set owner = 'HR2' where owner = 'HR';

34 rows updated.

SCOTT > commit;
*****************************************************
SCOTT > begin
2 dbms_redefinition.sync_interim_table(user,'T','T_INTERIM');
3 end;
4 /

PL/SQL procedure successfully completed.

SCOTT > begin
2 dbms_redefinition.finish_redef_table(user,'T','T_INTERIM');
3 end;
4 /

PL/SQL procedure successfully completed.

SCOTT > set echo off
SCOTT > desc t
Name
--------------------------------------------------------------------------------------------------
OBJECT_ID
OBJECT_TYPE
OWNER
ONAME
CREATED
LAST_DDL_TIME
STATUS
FLAGS

SCOTT > select distinct owner from t;

OWNER
------------------------------
AURORA$JIS$UTILITY$
CGLBYTE
CTXSYS
HR2
MDSYS
OE
OLAPDBA
OLAPSYS
ORDPLUGINS
ORDSYS
OSE$HTTP$ADMIN
OUTLN
PM
PUBLIC
QS
QS_ADM
QS_CBADM
QS_CS
QS_ES
QS_OS
QS_WS
RMAN
SCOTT
SH
SYS
SYSTEM
WKSYS

27 rows selected.

SCOTT >

--------------------------------
After rollover we have to recompile the procedures as well?

Tom Kyte
April 27, 2003 - 8:32 am UTC

they will recompile themselves.

Error on Redefinition

Elizabeth, February 15, 2005 - 9:56 am UTC

Tom,
I'm still trying to work through all your examples of Online Redefinition, but I can't even get to first base. I keep getting the error:

Error on line 0
begin
exec dbms_redefinition.start_redef_table('CEASAPP','EMP','INT_EMP');
end;

ORA-00439: feature not enabled: Online Redefinition

I've googled around looking for what I'm missing and I'm at a loss.
Thanks for the help.


Tom Kyte
February 15, 2005 - 3:35 pm UTC

are you perhaps in standard edition?

David Aldridge, February 15, 2005 - 3:57 pm UTC

Back to the original partition topic, sort-of ... it occured to me a while ago that a functionality that is logically missing from Oracle is the ability to add a new partition to a table where the partition is already populated. Kind-of like a blend between a partition exchange and a partition add.

Sort of like ...

Alter table my_table
Add partition Y2005_M02 values ('2005 02')
From table my_table_2005_02
including indexes with validation;

... instead of creating a partition then exchanging with a table.

So anyhoo what would be the procedure for submitting feature requests to Oracle Corp, Tom?

Tom Kyte
February 16, 2005 - 7:32 am UTC

metalink.oracle.com, support -- they do that.

Why I still got the full-table scan?

ChunMei, April 29, 2005 - 4:34 am UTC

Hi Tom,
Why did I still get the full-table scan when I use the partition key column in WHERE clause on my partition table?
I execute two sqls for the result.
sql1: select * from sales partition(p1) where to_char(sales_date,'yyyy-mm-dd')='2005-05-25';
sql2: select * from sales where to_char(sales_date,'yyyy-mm-dd')='2005-05-25';
To my disappointment, sql2 is full-table scan.

Another example:
I create another Range-Partion table,a non-date type column as the partition key, then I execute sql as follow:
select * from sales where sales_id=1; (sale_id is the partition key)
This time, table scan occurred in partition #1.

I can not understand this difference.
How can I get right result on Range-Partition table when the date type column is the partition key? I am expecting your answer, thanks.

Best Regards!


Tom Kyte
April 29, 2005 - 8:25 am UTC

if you are looking at autotrace explain, it doesn't show partition information.  consider:

ops$tkyte@ORA9IR2> CREATE TABLE t
  2  (
  3    dt  date,
  4    x   int,
  5    y   varchar2(25)
  6  )
  7  PARTITION BY RANGE (dt)
  8  (
  9    PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) tablespace users, 10    PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) tablespace tools, 11    PARTITION junk VALUES LESS THAN (MAXVALUE)
 12  )
 13  /

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select * from t where dt = to_date('14-mar-2003');
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=36)
   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1 Bytes=36)
 
 
 
ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2> @plan "select * from t where dt = to_date('14-mar-2003')"
ops$tkyte@ORA9IR2> delete from plan_table;
 
2 rows deleted.
 
ops$tkyte@ORA9IR2> explain plan for &1;
old   1: explain plan for &1
new   1: explain plan for select * from t where dt = to_date('14-mar-2003')
 
Explained.
 
ops$tkyte@ORA9IR2> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
-----------------------------------
 
------------------------------------------------------------------------------
| Id  | Operation            |  Name  | Rows  | Bytes | Cost  | Pstart| Pstop |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        |     1 |    36 |     2 |       |       |
|*  1 |  TABLE ACCESS FULL   | T      |     1 |    36 |     2 |     3 |     3 |
------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("T"."DT"=TO_DATE('2003-03-14 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
 
Note: cpu costing is off
 
14 rows selected.

<b>see the pstart/pstop -- it full scanned A partition</b> 

global vs local indexes

A reader, May 02, 2005 - 9:29 pm UTC

A few posts back, someone was asking whether to use global or local indexes on columns that were not part of the partition key. You said:
"If you query "select * from t where upper(attrA) = 'VALUE'" -- you won't want that index to be locally partitioned (we would have to do 3 index range scans now instead of just one). You would want a single global index"

Wouldn't 3 simultaenous in parallel index range scans on one third of the data each be faster than one big one?

Tom Kyte
May 03, 2005 - 8:44 am UTC

no

a "big" index is going to be height of 3 or 4 max. 3/4 IO's to get to the key.

a partitioned version of that MIGHT get luck and become 2/3 - 2*3 = 6, 3*3 = 9 so 6 or 9 IO's

And the fact that the time to set up and coordinate the three PQ processes probably takes 10 times longer than the query itself (PQ is good for things that run minutes - not sub-second)...

PQ would be dog slow and slam the machine sideways (think about it -- every time you run a query you would have 4 processes, not 1) resource wise.

Removing the Partitions

Arvind, May 12, 2005 - 11:43 pm UTC

Dear Tom,
Hello,

Tom, I have a schema where I have used paritiond tables as given below
CREATE TABLE MR_MASTER (
PART_KEY NUMBER(2) NOT NULL,
DEPT_CODE NUMBER(2),
SECTION NUMBER(2),
JOBNO VARCHAR2(4),
MR_SRNO NUMBER(4),
CONSTRAINT genmr_master PRIMARY KEY (DEPT_CODE,SECTION,JOBNO,MR_SRNO)
)
PARTITION BY RANGE(PART_KEY)
(PARTITION p1644 VALUES LESS THAN (2) TABLESPACE genmr_1644,
PARTITION p1645 VALUES LESS THAN (3) TABLESPACE genmr_1645,
PARTITION p1646 VALUES LESS THAN (4) TABLESPACE genmr_1646,
PARTITION p1647 VALUES LESS THAN (5) TABLESPACE genmr_1647,
PARTITION p1648 VALUES LESS THAN (6) TABLESPACE genmr_1648,
PARTITION p1650 VALUES LESS THAN (7) TABLESPACE genmr_1650,
PARTITION p1651 VALUES LESS THAN (8) TABLESPACE genmr_1651,
PARTITION p1671 VALUES LESS THAN (9) TABLESPACE genmr_1671)
/

Now due to some unavoidable reasons I want to remove Paritions and want to convert it to normal table. Is it possible, if yes then How?

Tom Kyte
May 13, 2005 - 9:14 am UTC

create table temp as select * from mr_master;
- index it
- grant it
- constraint it
- add triggers
- whatever you need to make temp as good as mr_master

drop table mr_master
rename temp to mr_master



or search this site for dbms_redefinition to see how to accomplish this online.


You could get this to be a partitioned table in a SINGLE partition by merging the partitions, but you will still have a partitioned table, just one with a single partition.

Partition by RANGE and then subpartition by LIST

atul, July 25, 2005 - 1:24 am UTC

Hi,

We are on 9.2.0.6.
1)I want to create a table partition by range with create_date key and then subpartition by list with other key.

Could you let me know if following syntax is Ok.


+++++++++++++++++
CREATE TABLE table_name
...
)
partition by range (key1)
subpartition by list (key2)
(
partition fc_12dy_20020531 values less than (20020601)
(
subpartition fc_1_12dy_20020531 values (1) tablespace data_fc_1_12dy_20020531,
subpartition fc_2_12dy_20020531 values (2) tablespace data_fc_2_12dy_20020531,
subpartition fc_3_12dy_20020531 values (3) tablespace data_fc_3_12dy_20020531,
subpartition fc_4_12dy_20020531 values (4) tablespace data_fc_4_12dy_20020531,
subpartition fc_5_12dy_20020531 values (5) tablespace data_fc_5_12dy_20020531,
subpartition fc_6_12dy_20020531 values (6) tablespace data_fc_6_12dy_20020531,
....
subpartition fc_148_12dy_20020531 values (148) tablespace data_fc_148_12dy_20020531,
subpartition fc_149_12dy_20020531 values (149) tablespace data_fc_149_12dy_20020531,
subpartition fc_150_12dy_20020531 values (150) tablespace data_fc_150_12dy_20020531
),
partition fc_12dy_20020628 values less than (20020629)
(
subpartition fc_1_12dy_20020628 values (1) tablespace data_fc_1_12dy_20020628,
....

+++++++++++++


2)table which i am going to partition contains CLOB column,which is in there own tablespace .
So while partitioning will any extra care i need to take?

Thanks,
Atul


Tom Kyte
July 25, 2005 - 7:36 am UTC

1) did it execute? (believe it or not, I'm not a sql compiler :)


I can say it appears you might be thinking about using a number to store a DATE -- that would be a really bad idea. We have a DATE datatype just for that sort of data. You can do lots more with a date in a DATE than you can with a date in a number and the optimizer will be happier too.

2) clobs are always equipartitioned with the base table naturally, other than putting things into the tablespace of your choice (you put things in tablespaces to make your life easier and better, you choose a scheme that makes you happy), not really.


I'm not sure I'd use what appears to be thousand(s) of tablespaces for a single object like that.

Partitions

atul, July 26, 2005 - 10:53 am UTC

Hi,

If i have a table with clob column.
I have moved clob to its own tablespace names clob_moved.
and then if i decide to partition table on timestamp column
by tablespace part1_tbl.

if table has been created in its default tabledspace tab.
so after this will i get any benifit of moving clob's in its own diff tablespace.


Thanks,
Atul

Tom Kyte
July 26, 2005 - 11:00 am UTC

what benefit are you trying to achieve? eg: what is your goal with separate tablespaces.

CLOB

atul, July 26, 2005 - 2:58 pm UTC

Hi,

To explain further,

If i have a table
test with columns (timestamp date,servicein clob,serviceout clob)

If i made 5 partitions on timestamp each in diff tablespaces,what will happen to CLOB columns?
In which tablespace they will go

Thanks,
Atul

Tom Kyte
July 26, 2005 - 3:28 pm UTC

whichever tablespace you tell them to, by default, the lob index/lobs partitions will be in the same tablespace as the table partition.

Impact of partition on existing queries

A reader, August 03, 2005 - 6:35 pm UTC

Tom,
Will existing queries on a non-partioned table change once the table is partioned? We are conisdering partioning a table having 10 + million records.

Thank you


Tom Kyte
August 03, 2005 - 8:24 pm UTC

you would hope they would change, wouldn't you?

Yes, they will definitely change.

dead link above

Alberto Dell'Era, October 08, 2005 - 5:12 am UTC

on this review:
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:4636779130376#6783811821076 <code>
I would be cool to have a "permalink" feature for the online Oracle docs ...

Tom Kyte
October 08, 2005 - 8:56 am UTC

Yeah, tell ME about it - i thought i got them all, missed that one. They moved the docs..

asktom v3.0 will store urls very differently - extracted out of the text so I can easily "fix" them.

fixed it.

Can we add SubPartition to the table?

A reader, May 23, 2006 - 6:41 am UTC

Can we add SubPartition to the table?

Tom Kyte
May 23, 2006 - 7:36 am UTC

to what table.

What about Partition to Non-Partition table

Dav, November 16, 2006 - 10:46 am UTC

We partitioned a oracle application table and now we want to revert back. Lot of packages and functions depend on that table and we don't want to invalidate them.

Is there anyway to move the partition table to non-partition table without affecting it's dependecies.

Appreciate your input.

Tom Kyte
November 16, 2006 - 3:25 pm UTC

"sorry", but you will.


Great package

Eric Peterson, May 23, 2007 - 3:42 pm UTC

I've been trying to get this package to work. I wrote one test case and was successful. But on my second test I keep getting ORA-12093 error. The two tables structure are identical except one is partitioned and one is not. I can't seem to find much information on what triggers this error.


Thanks for any insight you can provide.
Tom Kyte
May 26, 2007 - 9:38 am UTC

hows about a TEST CASE

make it small
make it simple
make it work for anyone (eg: NO tablespace references for example)

get it down to the smallest example possible to demonstrate your issue and the mistake being made will likely become obvious - if not, post it and we'll take a look at it.

how to create partition

Surbhi, September 25, 2007 - 4:51 am UTC

Hi Tom,

I have table without any keys. Table has 593134 rows. I want to create a partition in this table. Without creating new table.

becuase i want to increase performance of a database.

thanks

Tom Kyte
September 26, 2007 - 9:16 pm UTC

hahahahahaha

ouch, it always hurts to fall out of my chair like that....


a) you cannot get there from here, eg: you MUST create a partitioned table to partition data (think about it please, the data MUST MOVE)

b) it is highly unlikely you will achieve any improved performance, in fact, I would rather guess that since you are stabbing in the dark, you will negatively impact performance by doing stuff like this, not knowing what you are doing.

suggestion: don't do that, get mentor, learn how to find out what is causing any perceived slowness....

DBMS_REDEFINITION & Compress

MN, March 25, 2008 - 9:42 pm UTC

In regards to the first solution at the top:
----------
a) export the table
b) create a new empty table that has the partition definition on it
c) import the table with IGNORE=Y
----------
Will compression functionality work with this approach?
---------
Moreover MetaLink Doc ID: 177407.1
says: For using DBMS_REDEFINITION 
Must have a primary key (restriction lifted in 9.2.0.5)
but:
------
SQL> /

COMP_NAME VERSION STATUS
------------------------------ ------------------------------ -----------
Oracle9i Catalog Views 9.2.0.8.0 VALID
Oracle9i Packages and Types 9.2.0.8.0 VALID

Elapsed: 00:00:00.00
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for 32-bit Windows: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
Elapsed: 00:00:00.01
SQL> show user
USER is "ARCHIVE"
SQL> execute DBMS_REDEFINITION.CAN_REDEF_TABLE('ARCHIVE','AMMT_POL_COMM_DETAILC');
BEGIN DBMS_REDEFINITION.CAN_REDEF_TABLE('ARCHIVE','AMMT_POL_COMM_DETAILC'); 
END;
*
ERROR at line 1:
ORA-12089: cannot online redefine table "ARCHIVE"."AMMT_POL_COMM_DETAILC" with
no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 8
ORA-06512: at "SYS.DBMS_REDEFINITION", line 247
ORA-06512: at line 1
Elapsed: 00:00:00.01
-----------------

Regards,


Tom Kyte
March 26, 2008 - 8:54 am UTC

import uses a conventional path insert - not direct path.

so in 10g and before, import cannot load compressed data.

using insert /*+ APPEND */ into the new table would.


http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_redef3.htm#1002748

you send an option into start redef to use rowids.

caveat about dbms_redefinition

Janis Vanags, March 27, 2008 - 10:12 am UTC

My problem was - table is partitioned by one column and it turns out it actually needs partioning by another column. So I come here and find this nifty way to do it without much downtime. I did not think much about the fact that all my indexes were local...

After 2 hours of work (had to wait for grant on DBMS_REDEFINITION) the "ORA-12089: cannot online redefine table with no primary key" almost made me cry. I really think it should be pointed out somewhere in the beginning, because the primary key in the first example looks rather non-mandatory (should be working by rowid).

To anyone who wants to try this -

MAKE SURE YOUR TABLE HAS A PRIMARY KEY BEFORE ATTEMPTING THIS!!!
Tom Kyte
March 27, 2008 - 10:58 am UTC

did you try it with the rowid option????? it can use rowids....


(and a table without a primary key - how sad, how did you get into such a state.... especially if you are going to the trouble of partitioning it, it must contain something useful....)

Compressd + Partition

MN, March 27, 2008 - 9:20 pm UTC

See Metalink Note 210407.1 for using rowid,

Tom,
I created three test to check performance testing on compression+Partition.
Result for uncompressed-Unpartition table with compressed-unpartition table(compressed indexex) is ok(Quick), but when I partition a table and compress old partition leaving one latest partition uncompressed, Explain plan changes and take a lot more time.
-----Query used--- changing table name
SELECT SUM(NVL(N_FIRST_YR_PREM,0) * NVL(10,0)/100) FYP,
SUM(NVL(N_SINGLE_PREM,0) * NVL(12,0)/100) SP,
SUM(DECODE(NVL(N_SINGLE_PREM,0),0,N_POLICIES_SOLD,0)) "POL"
FROM AMDT_AGENT_PRODUCTION_HISTORY
WHERE N_AGENT_NO = 4806
AND N_PREM_YEAR = 1
AND V_PROD_LEVEL = 'ATM'
AND (TRUNC(D_TRANSACTION_DATE)
BETWEEN TRUNC(to_date('01/01/2006','DD/MM/YYYY'))
AND TRUNC(to_date('31/12/2006','DD/MM/YYYY')))
AND V_PLAN_CODE IN ('WISDOM1') ;
-----------------
Result for Uncompressed-unpartition table
-----
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=1 Bytes=28)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'AMDT_AGENT_PRODUCTION_
HISTORY' (Cost=14 Card=1 Bytes=28)

3 2 INDEX (RANGE SCAN) OF 'TUNE_PROMOTION' (NON-UNIQUE) (Cost=6 Card=376)
-----------------
Cmpressed+partioned result.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=88)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'AMDT_AGENT_PROD
_HISTORYCP' (Cost=2 Card=1 Bytes=88)

3 2 INDEX (RANGE SCAN) OF 'IDX_AGENT_PROD_HIST_AG' (NON-UN
IQUE) (Cost=1 Card=145026)
----------------------------------
Both table have same amount of data and same index created, no change made to index creation( Local, Global etc)
Regards,








Tom Kyte
March 31, 2008 - 7:06 am UTC

look at the card= values. why is the second one so high (145,026) - something else is different here, you don't give us much useful information - like how you partitioned, what indexes are there, what the table looks like.

Why would you do this:

AND (TRUNC(D_TRANSACTION_DATE)
BETWEEN TRUNC(to_date('01/01/2006','DD/MM/YYYY'))
AND TRUNC(to_date('31/12/2006','DD/MM/YYYY')))


why would you apply the trunc function to d_transaction_date?

Add More info on above issue

MN, March 27, 2008 - 11:13 pm UTC

one more info for your review:
-----
CREATE INDEX EAGLEISF.TUNE_PROMOTIONCP ON EAGLEISF.AMDT_AGENT_PROD_HISTORYCP
(N_AGENT_NO, V_PROD_LEVEL, TRUNC("D_TRANSACTION_DATE"), V_PLAN_CODE)
NOLOGGING
TABLESPACE EGLPRDMAIN
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1659048K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 50
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;
---------------
Tom Kyte
March 31, 2008 - 7:09 am UTC

(this addition is not very useful, we need everthing, and now I see why you do that trunc, ugh....)


...

What privileges are required to use the dbms_redefinition package?

A reader, September 10, 2008 - 2:57 pm UTC

I tired to run through your example of redefining a table above. I got the following exception when I tried to start redefine the table. Other than the execute privilege on the package, do I need any other privileges? Both t and t_interim tables are owned by the user that executed the script.


SQL> DECLARE
  2     l_columnMapping VARCHAR2(512);
  3  
  4  BEGIN
  5     l_columnMapping := 
  6        'object_id,
  7         object_type,
  8         owner,
  9         object_name oname,
 10         created,
 11         last_ddl_time,
 12         status,
 13         temporary || ''/'' || generated || ''/'' || secondary flags';
 14  
 15     dbms_redefinition.start_redef_table (
 16        uname        => user,
 17        orig_table   => 'T',
 18        int_table    => 'T_INTERIM',
 19        col_mapping  => l_columnMapping
 20     );
 21  END;
 22  /
DECLARE
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_REDEFINITION", line 50
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1343
ORA-06512: at line 15

Adding multiple partitions

sl, January 28, 2009 - 2:23 am UTC

Hi Tom,

Could you please suggest me whether the two below given cases make a difference (in terms of execution time)?

1. Adding multiple partitions to a table one at a time
2. Adding multiple partitions to a table through a single ALTER TABLE..... option.

I need an understanding of this in the context of creating partitions for a huge number of huge tables for every new entity, say func_area. Creation of partitions for these tables is taking 1 hr for each func_area. Hence 4 func_areas means 4 hrs. This is a very time-consuming process. Hence to minimize the time-frame, would the second option above help?

Thanks in Advance
Srilakshmi P.

Tom Kyte
January 28, 2009 - 3:24 pm UTC

give me more context here, I don't know why it would take 4 hours, it should be "less than a second"

unless you are moving a ton of data, so - give the full set of information...

Adding multiple partitions

sl, January 30, 2009 - 6:42 am UTC

Thanks for the prompt reply, Tom.

I have a procedure that adds partitions to a huge set of tables. This happens each time a new func_area gets added to the application. My query is

1. Can we add multiple partitions to a table at a time through the ALTER TABLE...ADD PARTITION statement? Please explain with example.

2. If so, will it make any difference in the execution time for the below given 2 cases?


case 1 : Adding multiple partitions to a table one at a time using ALTER TABLE..ADD PARTITION statement.

case 2 : Adding multiple partitions to a table through a single ALTER TABLE.....ADD PARTITION statement

Thanks in advance.
Tom Kyte
January 30, 2009 - 3:03 pm UTC

ops$tkyte%ORA10GR2> !oerr ora 14043
14043, 00000, "only one partition may be added"
// *Cause:  ALTER TABLE ADD PARTITION contained descriptions of more than one
//          partition to be added
// *Action: Ensure that the statement contains exactly one partition
//          definition and that it does not contain any commas


Helena Marková, October 08, 2009 - 1:22 am UTC


More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.