Hi

Using Oracle 9i.

I have some logical volumes that are nearing capacity. Before adding more space, I can see that if some house-keeping is performed on the db, I can gain a few GB of free space.
Part of this housekeeping is some tablespace mgmt. I have a couple of tablespaces which have 10 datafiles each, each 2GB in size but only 12% of that 2GB is used when I look at the tablespace in 'Toad'. Even when running a query.

I also ran a script to find out if I could resize the datafiles but there is no free space at the end of the file.

The next best option I'm thinking of is to Export the user, drop the existing one, re-create it and Import the user back. (Ideally I'd like to create another user first, import the data into this user and then drop the exported user if all is fine but for this I'll have to create the new user with a different name. Are there any downsides to this?

Also, am I correct in saying I'll have to take the tablespace offline?

Is there a better, simpler, more risk-free method for this?

Regards
Sheraz

You could re-org the tables in that tablespace by "moving" them:

1) Create new tablespace.
2) Move the tables to new tablespace.
3) Drop old tablespace.


-- Or --

Try "moving" the tables within same tablespace.

Oracle will still use the unused space of the datafiles. Is there a problem with that?

>Is there a better, simpler, more risk-free method for this?
Yes, do nothing & allow Oracle to manage tablespace internals.

Yes, Oracle will still use the space but it's only like 12% in each of the 10 datafiles in 2 years so have been advised to use the free space in these datafiles for other data.

For reorg, I understand there needs to be enough free space on the disk (logical vol) in my case. But there's not. LVOL is 97% full.

I have enough free space on another lvol of abot the same size which is only 50% full.

If I create a new tablespace, how will I move the tables? (I take it moving datafiles is different?)

And I take it after I move the tables, I won't have to update anything else as all links to those tables will automatically be updated?

If Locally Managed Tablespace, then Oracle handles all these details.

To move tables:

ALTER TABLE "SCOTT"."EMP" MOVE TABLESPACE NEW_TS_DAT1;

To move indexes:

ALTER INDEX "SCOTT"."EMP_IX0" REBUILD TABLESPACE NEW_TS_IDX1;

To shrink:

ALTER TABLE "SCOTT"."EMP" ENABLE ROW MOVEMENT;
ALTER TABLE "SCOTT"."EMP" SHRINK SPACE;

PS: If you omit the TABLESPACE clause it will reorg in the same tablespace.

Thanks for the syntax.

Will

ALTER TABLE "SCOTT"."EMP" MOVE TABLESPACE NEW_TS_DAT1;

work even if the new tablespace is on a different logical volume?

Also is the whole procedure to move tables first and then rebuild indexes with

ALTER INDEX "SCOTT"."EMP_IX0" REBUILD TABLESPACE NEW_TS_IDX1;

The tablespaces are locally managed here.

Can I shrink tables in 9i?

Yes to all...


PS: Ooops, not sure the "shrink" syntax will work in 9i.