resize tablespace

[jm]

does any one can tell me:

How to reduce the data file size of a tablespace.

thanks

[gandolf989]

ALTER DATABASE TEMPFILE
'E:\ORACLE\ORADATA\BSG1\TEMPORARY_DATA.ORA'
RESIZE 40M;

You may want to do the following

ALTER TABLESPACE TABLESPACENAME DELETE UNUSED;

ALTER TABLESPACE TABLESPACENAME COALESCE;

[gandolf989]

Actually it should be
ALTER DATABASE
DATAFILE 'D:\ORADATA\BSG1\USER1.DBF' RESIZE 40M

I use locally managed tablespaces.

[jm]

Hi, gandolf989,

please verify it for me:

1. ALTER TABLESPACE users DELETE UNUSED;

2. ALTER TABLESPACE users COALESCE;

3. ALTER DATABASE DATAFILE 'D:\ORADATA\BSG1\USER1.DBF' RESIZE 10M;

is it the correct sequence?

thanks

[pando]

Originally posted by jm


1. ALTER TABLESPACE users DELETE UNUSED;



wow is that a new command :D

[julian]

Originally posted by gandolf989
ALTER TABLESPACE TABLESPACENAME DELETE UNUSED;


And I thought I knew all TABLESPACE related commands in Oracle :-)

[jm]

Hi,

so what's the correct command and step?

all I know is use

ALTER DATABASE DATAFILE 'filename' RESIZE 10M;

in fact, my true question is:

if I have a tablespace was 5Mb initally, later someone create some "hugh" tables to make the tablespace growing
to 500Mb, then those tables being dropped. I assume those space will be free.

can I use resize to set back to original size?

[pipo]

you can reduce the size of a datafile, providing there is NO data in the space you're removing (in the last 495 Mb in your example).
what you should do is :

drop table huge1;
drop table huge2;
...

once it's ok :

alter tablespace [tbs] coalesce;

and then :

alter database datafile 'your datafile' resize 5M;

if this command fails, then you certainly have data in the bad area, you should look for what it is by looking in dba_extents and dba_data_files ...

[jm]

thanks, it is loud, clear and works.