OraFAQ Forum: Server Administration » Unable to release space from table

Home » RDBMS Server » Server Administration » Unable to release space from table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Unable to release space from table [message #234328]

Mon, 30 April 2007 21:08

nneog
Messages: 6
Registered: April 2007
Location: Duliajan

Junior Member

Hi all,

We are unable to release space from a table called TST03 even after deletion of records.

Followings informations are for you.
Database : 9.2
Table Name : TST03
Tablespace : LOCALY MANAGED.
Previously there were lots of rows.
Now only 9 No of Rows are there.
Space allocated : 41 GB
PCT_INCREASE : Null
One of the column is of LONG RAW type.
In fact this is SAP Table.

Since the table is in LMTS, we were expecting that the space allocated will be released automaticaly after deletion of records.

Now, what are the option left with us to release 41GB of space ?
a. Is there any effect of "Drop storage" option of 'Truncate table' command ?
b. If yes , can i copy all the 9 rows to a new table, then use "Truncate table TST03 drop storage", check if space is released and then copy back all the 9 rows to this table.
c. Do you have any other easy solution apart from export/import ?

Hope I could give you clear picture.

Thanks .
Naba J Neog

Report message to a moderator

Re: Unable to release space from table [message #234329 is a reply to message #234328]

Mon, 30 April 2007 21:26

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Search the forum for HWM.
Delete will not release space.
Truncate will.
do an

alter table mytable move tablespace same_tablespace_as_it_was_before;

Rebuild your indexes and dependents.

Report message to a moderator

Re: Unable to release space from table [message #234330 is a reply to message #234328]

Mon, 30 April 2007 21:27

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Meanwhile, I re-read your question.
>>Now, what are the option left with us to release 41GB of space ?
You mean shrinking the datafile/tablespace?

Report message to a moderator

Re: Unable to release space from table [message #234331 is a reply to message #234330]

Mon, 30 April 2007 21:30

nneog
Messages: 6
Registered: April 2007
Location: Duliajan

Junior Member

Yes .
What are the option left to get back 41GB space.

Thanks.
Naba

Report message to a moderator

Re: Unable to release space from table [message #234332 is a reply to message #234331]

Mon, 30 April 2007 22:11

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

10g might have some interesting options for you.
In 9i, YOu need to resize the datafile.
http://orafaq.com/faqdba.htm#FILERESIZE

Report message to a moderator

Re: Unable to release space from table [message #234337 is a reply to message #234332]

Mon, 30 April 2007 23:39

nneog
Messages: 6
Registered: April 2007
Location: Duliajan

Junior Member

Hi Mr. Mahesh Rajendran,

I am sorry I could not explain my problem properly.

I would like to reclaim space of this table(below HWM) to the tablespace.

Will Resizing of datafile help ?

Thanks & Regards.

Naba J Neog.

Report message to a moderator

Re: Unable to release space from table [message #234347 is a reply to message #234337]

Tue, 01 May 2007 00:44

nneog
Messages: 6
Registered: April 2007
Location: Duliajan

Junior Member

Hi Mr. Mahesh Rajendran,

I am sorry I could not explain my problem properly.

My query was
1. How can I reclaim space of this table(below HWM) to the tablespace, so that other growing table can make use of it.

2. Will the command :-
alter table move same_tablespace_as_it_was_before
help reclaim space. Also please note that this table has LONG RAW coloumn.

Thanks & Regards.

Naba J Neog.

Report message to a moderator

Re: Unable to release space from table [message #234351 is a reply to message #234347]

Tue, 01 May 2007 00:58

Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

If you use ASSM: alter table shrink space
If you use MSSM: alter table move

Regards
Michel

Report message to a moderator

Re: Unable to release space from table [message #234358 is a reply to message #234351]

Tue, 01 May 2007 02:36

nneog
Messages: 6
Registered: April 2007
Location: Duliajan

Junior Member

Hi Michel Cadot,

Thanks for the reply.

Command "Alter table TST03 move" is not possible for this table because it contains a LONG RAW column.

Would please specify exactly how you wanted to do.

Naba J Neog.

Report message to a moderator

Re: Unable to release space from table [message #234360 is a reply to message #234358]

Tue, 01 May 2007 02:43

Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You choose to keep LONG RAW.
You're stick with old possible actions.
You can't do anything but export/truncate/import or ctas/truncate/insert back.

Regards
Michel

Report message to a moderator

Re: Unable to release space from table [message #234361 is a reply to message #234360]

Tue, 01 May 2007 03:09

Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India

Senior Member

hi,
try this..

Suppose your table name in ori_table.
1) Create table test_table as select * from ori_table;

2) Drop that original table ori_table

3) Rename test_table to ori_table using alter table command..

Regards...

Dipali

Report message to a moderator