How to Partition an Existing Table

This example was tested on Oracle 9. First create an ordinary table with some data in and count the number of rows it contains:

SQL> create table andrew
  2  as select * from dba_tables
  3  /

Table created.

SQL> select count(*) from andrew
  2  /

  COUNT(*)
----------
       831

SQL>

Next create an empty partitioned version of the table and show that it is empty:

SQL> create table andrew_partitioned
  2  partition by range(owner)
  3  (partition part01
  4  values less than (maxvalue))
  5  as select * from andrew
  6  where 1=2
  7  /

Table created.

SQL> select count(*) from andrew_partitioned
  2  /

  COUNT(*)
----------
         0

SQL>

Now move the data from the ordinary table to the partitioned table:

SQL> alter table andrew_partitioned
  2  exchange partition part01
  3  with table andrew
  4  /

Table altered.

SQL>

Show that the ordinary table is empty and that the partitioned table now has all the data:

SQL> select count(*) from andrew
  2  /

  COUNT(*)
----------
         0

SQL> select count(*) from andrew_partitioned
  2  /

  COUNT(*)
----------
       831

SQL>

Give the partitioned table the same name as the original non-partitioned one:

SQL> drop table andrew
  2  /

Table dropped.

SQL> rename andrew_partitioned to andrew
  2  /

Table renamed.

SQL> 

Finally, you can split up the new partitioned table as you wish:

SQL> alter table andrew
  2  split partition part01 at ('O')
  3  into (partition part01, partition part02)
  4  /

Table altered.

SQL>

How to List Chained Rows in a Partioned Table

I will be looking at partititioning and chaining elsewhere but this simple example shows how you can look for chained rows in a partitioned table. First create a partitioned table:

SQL> create table andrew

  2  partition by range (owner)

  3  (partition p1 values less than ('M'),

  4   partition p2 values less than (maxvalue))

  5  as select * from dba_tables

  6  /

 

Table created.

 

SQL> 

Then look for any chained rows in it. If a table is partitioned , you have 2 options. You can analyze the whole table or you can analzye individual partitions:

SQL> analyze table andrew list chained rows

  2  /

 

Table analyzed.

 

SQL> analyze table andrew partition(p1)

  2  list chained rows

  3  /

 

Table analyzed.

 

SQL> 

As you might expect, if you specify a partition which does not exist, you get an appropriate error message:

SQL> analyze table andrew partition(does_not_exist)

  2  list chained rows

  3  /

analyze table andrew partition(does_not_exist)

                               *

ERROR at line 1:

ORA-02149: Specified partition does not exist

 

SQL>

You also get an error if you try to use this syntax on a non-partitioned table:

SQL> create table fred

  2  as select * from dba_objects

  3  /

 

Table created.

 

SQL> analyze table fred

  2  partition(in_non_partitioned_table)

  3  list chained rows

  4  /

analyze table fred

              *

ERROR at line 1:

ORA-14501: object is not partitioned

 

SQL>