Re: cluster removal

Hi Kenneth,
Thnaks for your suggestion.

I have to write patch for updating client's db. Export/import is still in my mind
But to automate the procedure, i think, some other logic is better than export/import
(sometimes, the export import need some user intervention)

Anas

Kenneth Koenraadt wrote in message news:<3e881cdf.12409273_at_news.inet.tele.dk>...
> 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
Received on Mon Mar 31 2003 - 22:04:57 CST