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>