Re: Re-assigning Tablespaces

From: <rspeaker_at_my-dejanews.com>
Date: Wed, 17 Feb 1999 18:01:25 GMT
Message-ID: <7af05f$ck0$1@nnrp1.dejanews.com>

We have been doing a lot of this lately, to consolidate many small tables into fewer larger tablespaces. What we do is this:

export the "real" table

create a "temp" table in a "temp" tablespace, with field defs matching the table desired to be moved

insert into the "temp" table a select * from "real" table

alter "real" table drop all constraints alter "real" table drop primary key
drop indexes from "real" table
drop real table

recreate "real" table in the new desired tablespace insert into "real" table a select * from "temp" table

recreate primary key on "real" table
recreate remaining constraints on "real" table recreate indexes on "real" table

drop "temp" table.

You may be able to cut down on some time by using the create table .... as select * from ...., but we have been reorganizing columns for better performance, so we haven't done it that way.

It has worked well for us, but we learned 2 lessons the hard way. First, always always always do the initial table export. Second, if you script the process, do it in chunks. There's nothing worse when using a script than having your create temp table or insert into temp table fail due to a typo, but your drop "real" table statements work beautifully, especially when you don't export that table first.

HTH,
Roy

In article <36CAA128.CAE2F778_at_email.mot.com>, Keith Jamieson <pdkj02_at_email.mot.com> wrote:
> <HTML>
> One of my Databases has several tablespaces assigned but all tables and
> indices are using the same tablespace. I have imported data from two 
> Database Tables into the new database. The import was successful.
>
> When I delete from a particular table the data gets copied to an "archive
> table". Thus the amount of data in the DB is fairly consistent. However,
> the delete does not recover the tablespace. So, although the Data in the
> Database is fairly constant, I am running out of tablespace.
>
> Is it possible to change the default tablespace for a table without
> dropping the table.
> If this is not possible, what is the best strategy for getting out
> of this mess? I need to retain the Database Schema, Triggers, Constraints
> and Data as is, but I could allow the database to go offline for a couple
> of hours
>  
>  
>
> I</HTML>
>
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Feb 17 1999 - 12:01:25 CST