RE: Moving tables into new tablespace

In article <8jcb9g$q803_at_cesio.mundo-r.com>,   "Juan miradna" <jmirandavigo_at_hotmail.com> wrote:
>
> Oracle doc´s says how you can move tables from one tablespace to
 another.
> Look at the export -import help.
> You must export tables, delete it, asign quota cero on the onder
 tablespaces
> and then change the default tablespace
> of the user you will import tables....
>

Presuming, of course, that the user account importing the tables has not been granted RESOURCE privilege. Should this be the case it is likely that the tables/indexes could end up in the SYSTEM tablespace since the tablespace specified by the export file does not exist.

> Syltrem <syltrem_at_videotron.ca> escribió en el mensaje de noticias
> dw865.6056$LX4.120655_at_weber.videotron.net...
> > Hi!
> >
> > I want to move some tables from one tablespace to another and I
 don't know
> > what's the best way to do it.
> >
> > I want to make sure my indexes and other constraints will follow.
> >
> > Also, is there a way to know how much % of the space used by an
 index is
> > actually being used (equivalent to tables's dba_tables.empty_blocks)
> >
> > Can someone help?
> >
> > Thanx
> >
> >
>
>

Export the schema as the first step..Using imp create an index file:

imp user/password file=<dumpfile> full=y indexfile=<schema>.sql

This will place all 'CREATE TABLE' and 'CREATE INDEX' statements into the <schema>.sql file; the 'CREATE TABLE' statements will be remarked out with 'REM '; Edit the file to remove the 'REM ' statements and then modify the tablespace locations for the tables and the indexes. Unfortunately this will not include the constraints, so they will probably end up in the default tablespace for the given user.

Drop the tables you wish to relocate, then run the modified <schema>.sql script to create these tables in the new tablespace (don't worry if you don't drop all of the tables listed in <schema>.sql; they won't be duplicated but you will receive and error that the object already exists). Import the tables using the ignore=y parameter (this will instruct imp to ignore the existence of the object and to proceed to import the data and constraints). This should re-locate the tables and indexes into the new tablespaces, while the constraints (primary keys, foreign keys) that do not depend on existing indexes will be created in the user's default tablespace.

To answer your second question I believe your best bet is the following:

select owner||'.'||index_name name,
distinct_keys*avg_data_blocks_per_key est_block_usage from dba_indexes;

This should provide a rough idea of the actual size of the index. I say rough due to the "average data blocks per key" value in the calculation.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.