Recreate the TOOLS Tablespace

[Scott Buckingham]

We have Oracle 8.1.7 on an HP-9000, 11.00 box. Our TOOLS tablespace is over 1 GB in size, but only 13 MB is ever used. I would like to resize it to a much smaller space but the 'ALTER DATBASE DATAFILE' command will not do it since there appears to be data beyond what I want to shrink it to. Therefore, I would like to drop and recreate this tablespace. What should I worry about when doing this? Is it just a matter of:
- exporting the data;
- dropping the tablespace;
- recreating it with a smaller size;
- importing the data back in.
Can I do this when the database is on-line? This is a test instance, but I don't want to knock anyone off if I don't have to.

[Jean-Luc Oudart]

What is the tools tablespace is used for ?
(Perfstat ? other ...) any Application using it ?

Regards,
Jean-Luc

[Jean-Luc Oudart]

Actually you can use the ALTER TABLE MOVE / ALTER INDEX REBUILD to move the tables to a different tablespace.
I suppose if you want to Keep TOOLS as the tablespace name you will have to run the operation twice !

Check Metalink Note:147356.1

Regards,
Jean-Luc

[Scott Buckingham]

Actually, that is a good question, what IS the TOOLS tablespace for? I only have one user accessing it, but it is the SYSTEM user. That is why I'm questioning whether or not it can be deleted and recreated while on-line. I really don't want to move anything, just rebuild it.

[twang]

The easiest way to do this is to decrease size of a datafile:
ALTER DATABASE vis DATAFILE '/oracledata/tool.dbf' RESIZE 900M;

Or you may use 'ALTER TABLE MOVE' to move the tables on TOOLS to another tempory-create tablespace, then shrink it to the proper size,
finally, you can user 'ALTER TABLE MOVE' to move these tables back on TOOLS

[Jean-Luc Oudart]

Scott,

With the exp/imp solution you must be sure that no update is processed during the tablesapce rebuild.
The table move / index rebuild advantage is that the work can be done online.
And as I say if you want to keep same name you move the tables twice
TOOLS -> TMPTOOLS
TMPTOOLS -> TOOLS (resized)

Regards,
Jean-Luc

[Brian Crabtree]

Scott,

You do not technically need to create a new tablespace. If you issue the rebuild statement without specifying a tablespace, it should take extents off of the front of the file, and let you resize the datafile down.

I would recommend finding the object that is causing the problem by checking the dba_extents view.

Thanks,

Brian

[Yogeeraj_1 · Accepted solution]

hi,

Your Datafile (a collection of extents) is in a similar state:

0mb<-------------------------------------------------------->1024mb
OOOOxxxxxxxxxxOxxxxxxxxxxxxOOOOOOxxxxxxxxxxOOxxxxxxxxxxxxxxxxxxO

(O = allocated extent, x = free extent)

The last X out by 1024mb is preventing you from shrinking this file.

If you can determine what object is out there you can perhaps "move or rebuild" that object only -- or perhaps just drop it and recreate it later.

To see what object is hanging out out there, you can run the following query on sqlplus:

column tablespace_name format a20
column "Name" format a45
break on file_id skip 1
ttitle &1
select file_id, block_id, blocks,
owner||'.'||segment_name "Name"
from sys.dba_extents
where tablespace_name = upper('&1')
UNION
select file_id, block_id, blocks,
'Free'
from sys.dba_free_space
where tablespace_name = upper('&1')
order by 1,2,3
/

and pass it in the name of the tablespace. Look for the large block_id at the end of the query and it'll show you the object you are looking for.

best regards
Yogeeraj

[Eric Antunes]

You can also do it this way (ensure no one is using the database before doing this):

ALTER TABLESPACE "TOOLS" OFFLINE NORMAL;
ALTER TABLESPACE "TOOLS" ONLINE;
ALTER TABLESPACE "TOOLS" COALESCE;
ALTER DATABASE DATAFILE '/disc.../.../tools01.dbf' RESIZE 512M;

[Scott Buckingham]

Thanks everyone! I was able to accomplish what I was after. In a nutshell, I used the Enterpise Manager's Reorg Wizard to shuffle things around and I was then able to resize the datafile. Your input helped me realize what I needed to do and when!

Points all around! Thanks again!

[Eric Antunes]

I think you should coalesce TEMP (Temporary) and RBS (Rollback Segments) periodically because those are normally the most fragmented segments.

Antunes