table partition

[sonaliak]

I want to partition a table.. not on a PK column but say on a category > 2000..

What is the syntax for doing that ?

If I want to delete all the records greater than 2000 category ( truncate that partition), can some one help me with that syntax ?

Can I take out that partition after doing this ?

thanks

[tamilselvan]

create table test_pa (
id number primary key,
category number)
partition by range (category)
(partition p1 values less than (1000),
partition p2 values less than (2000),
partition p3 values less than (MAXVALUE));

Now you can truncate partition p3 alone.

[sonaliak]

Do I have to recreate that table( as it is already there with million records) with partition and then drop the original one and then do the truncate on the partition and then again recreate the table without partition as I do not intend to keep the table with partition ?

thanks
Sonali

[sonaliak]

Refreshing this thread if some one can help please ?

How do I partition existing table ?

After I am done with my work, I would like to go back to original table and do not want to keep partitions, how do I do that ?

Thanks

[tamilselvan]

I have not partitioned existing table. I do not know whether it can be done on existing table.

Other option is:
Create a temp table with partitions and then insert rows from the existing table. Once you are done with partitioned table, drop the temporary table.

[marist89]

You can't partition an unpartitioned table.

1. create a partitioned table with one partition (maxvalue)
2. exchange the table into the partitioned table ( http://technet.oracle.com/docs/produ...rtiti.htm#8503 )
3. SPLIT the partitions the way you want them laid out
4. TRUNCATE your partition
5. MERGE partitions back to one partition
6. EXCHANGE out to your base table.

It seems like a lot of work just to get rid of a range of values. Personally, I would just delete with a periodic commit.

[sonaliak]

thanks a lot for all your help, this has really helped me.

Do I have to have additional considerations while doing export import of the full database ?

Also, if I partition on say p1 as category > 2000 in category table..
and I want to index that.
How do I create an index, will it be for all records (p1 + p2) or just the p2 partition ?

thanks
Sonali