Oracle not releasing space after DROP TABLE

[Anomitro]

We have a few big tables in a schema. Now when we are dropping the tables the space acquired by the tables is not released by ORACLE. When we try to resize the datafile of that schema, it is not allowing us to do it and throwing an error.
But we need the space to be released and allocated to some other schema.
We are using ORACLE 9.2.0.5 on LINUX AS 2.1 on ITANIUM server.

Please give and workaround for this problem.

Thanks in advance,
Anomitro

[davey23uk]

have you tried coalescing the tablespace to try and get the space used own.

You dont mention if you are using LMT's or not

[akhadar]

Here is very article go through it

http://asktom.oracle.com/pls/ask/f?p...:153612348067,

[Anomitro]

Hello All,

I'm using LMT.

I've droppped a few large tables and released 3GB of space. This is seen in the TABLESPACE option of OEM, where it is saying 94% used. When I'm looking into datafiles its showing a lot of bytes free. Now when I try to resize the datafiles its throwing the error:

ORA-03297: file contains used data beyond requested RESIZE value

And I've gone thru ASKTOM's article and tried the the "maxshrink.sql" but it is also showing 'Poss. Savings = 0" for all the 4 datafiles of that tablespace.

I've tried ALTER TABLESPACE .. COALESCE , but the result is same.

I've time and space constraints so I cannot do an EXP & IMP to release space.

Please help me to solve the problem.

Thanks,
Ano

[marist89]

create a new tablespace
alter table xyz move new tablespace
alter index xyz rebulid new tablespace
drop old tablespace
rm datafiles

[Anomitro]

My tablespace almost 100GB and I cannot create another tablespace to move the tables due to space problem.

I've restarted the database but it didn't release the space.

Now I'm creating 1 small tablespace and moving 1 table to see whether it is releasing the space from the old tablespace.

ANY OTHER WORKAROUND TO RELEASE SPACE ????



Thanks,
Ano

[davey23uk]

there is free space, but not at the end of the datafile - you have to create another tablespace and move them

[julian]

Originally posted by Anomitro
Hello All,

I'm using LMT.

I've droppped a few large tables and released 3GB of space. This is seen in the TABLESPACE option of OEM, where it is saying 94% used. When I'm looking into datafiles its showing a lot of bytes free. Now when I try to resize the datafiles its throwing the error:

ORA-03297: file contains used data beyond requested RESIZE value

And I've gone thru ASKTOM's article and tried the the "maxshrink.sql" but it is also showing 'Poss. Savings = 0" for all the 4 datafiles of that tablespace.

I've tried ALTER TABLESPACE .. COALESCE , but the result is same.

I've time and space constraints so I cannot do an EXP & IMP to release space.

Please help me to solve the problem.

Thanks,
Ano

If you don't have the space, then you are stuck. Did someone ask you to do this online?

[abhaysk]

Try alter table tab_name move in the same TS (for all segments in that TS).. and then do shrink of datafiles..

Abhay.

[julian]

Originally posted by abhaysk
Try alter table tab_name move in the same TS (for all segments in that TS).. and then do shrink of datafiles..

Abhay.

Yeah, that might help. Remember to rebuild the unusable indexes (if any).