Re: cluster removal

On 31 Mar 2003 01:23:15 -0800, anaskhan_at_bns-solutions.com (Anas) wrote:

>Hello there
>
>In our database 10 tables use cluster storage.
>There are 3 clusters and all the tables contain lots of records.
>More than 20 foreign keys referencing tables in clusters.
>
>How can I move all tables from Clusters to nomal tablespace without
>affecting data loss or constraints.
>
>Our aim is to remove all the clusters from database.
>
>Will the export import help?
>
>Hope somebody have prevoius experience on this.
>
>Oracle version is 9.2.-.-
>
>
>
>Anas Khan .S

Hi Anas,

Seems to me there is no shortcut here. You must uncluster the individual tables residing in the cluster one by one in the old-fashioned way.

1. Store the definitions of indexes, constraints and grants on all the tables in the cluster in a safe and dry place. This can be done with exp/imp or other tools (e.g. OEM, TOAD), which can export object definitions.
2. Drop and recreate the table(s) in the cluster :

SQL> create table my_clustered_table_clone tablespace mytbs as (select * from my_clustered_table) ;

SQL>drop table my_clustered_table;

SQL>rename my_clustered_table_clone to my_clustered_table;

3) Regenerate indexes, constraints and grants on my_clustered_table;

• Kenneth Koenraadt