Re: Calculating new tablespace sizes

Hi Andy,

Assuming there are multiple schema owners in your database, and you want to separate tables and indexes (which can only be strongly recommended), here's what you need
select segment_type, sum(bytes)/1024 K
from dba_segments
where owner = <your owner>
and segment_type in ('TABLE','INDEX')
group by segment_type;
Please allow for room (at least the tablespace header block) in your space calculations.
I advise moving your rollback segments to a separate tablespace and your temporary tablespace being a separate tablespace. Then you will need to make a full database export. You should drop those users and precreate them before you import the tables again. Please make sure the default tablespace of the user is the new tablespace and the user doesn't have unlimited tablespace privilege but quota unlimited on < the new tablespace > only. During import tables will automatically end up in the new tablespace. You probably should import with indexes=N, generate a file with create indextstatements from the import (by running an import with indexfile=<arbritary filename>, this will write all create index statements to a file), edit the file accordingly, and run the script you just created. In theory this should be all (and did it that way myself several times)

Good luck,

Sybrand Bakker, Oracle DBA

Andy Hardy wrote:

> Hi,
>
> We have a database with a single large tablespace.
>
> We'd like to rebuild the database, but use more tablespaces for simpler
> admin.
>
> Is there and easy way of calculating tablespace requirements based on
> existing table definitions and sizes?
>
> Andy
Received on Sat Sep 12 1998 - 03:15:54 CDT