Monday, March 19, 2012

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>

No comments: