Operating System - HP-UX
1748066 Members
5348 Online
108758 Solutions
New Discussion юеВ

Recreate the TOOLS Tablespace

 
SOLVED
Go to solution
Scott Buckingham
Regular Advisor

Recreate the TOOLS Tablespace

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.
Long time dabbler, first time Admin / DBA
10 REPLIES 10
Jean-Luc Oudart
Honored Contributor

Re: Recreate the TOOLS Tablespace

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

Regards,
Jean-Luc
fiat lux
Jean-Luc Oudart
Honored Contributor

Re: Recreate the TOOLS Tablespace

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
fiat lux
Scott Buckingham
Regular Advisor

Re: Recreate the TOOLS Tablespace

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.
Long time dabbler, first time Admin / DBA
twang
Honored Contributor

Re: Recreate the TOOLS Tablespace

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
Honored Contributor

Re: Recreate the TOOLS Tablespace

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
fiat lux
Brian Crabtree
Honored Contributor

Re: Recreate the TOOLS Tablespace

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
Honored Contributor
Solution

Re: Recreate the TOOLS Tablespace

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Eric Antunes
Honored Contributor

Re: Recreate the TOOLS Tablespace

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;
Each and every day is a good day to learn.
Scott Buckingham
Regular Advisor

Re: Recreate the TOOLS Tablespace

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!
Long time dabbler, first time Admin / DBA