OraFAQ Forum: Server Administration » Free space after a delete

Home » RDBMS Server » Server Administration » Free space after a delete

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

Free space after a delete [message #54549]

Thu, 21 November 2002 03:58

Rick
Messages: 49
Registered: March 2000

Member

After a successfull export from a db, I've deleted the exported rows.
I should know the amount of space that got free after this operation.
Does the column bytes in the 'dba_free_space' show the
amount of space available for new insert or for update too?
How many blocks are below the PCTUSED value?

Thanks in advance,
Rick

Report message to a moderator

Re: Free space after a delete [message #54551 is a reply to message #54549]

Thu, 21 November 2002 06:21

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

Senior Member
Account Moderator

deleting a table data wont reset the HIGHWATER MARK or release the used space.

u need to truncate the table.

Report message to a moderator

Re: Free space after a delete [message #54560 is a reply to message #54549]

Thu, 21 November 2002 12:05

Trifon Anguelov
Messages: 514
Registered: June 2002

Senior Member

To find out how much space was de-allocated, run This Script before and after such operations. It will show you the byte size for user's objects before and after the DML. But you have to use :

TRUNCATE TABLE table DROP STORAGE;

for your tables to release the allocated space (DROP STORAGE is the default, so you can even omit it).

To find out the available space in each tablespace, run This script and look under DB SIZING section.

Hope that helps,

clio_usa
OCP - DBA

Visit our Web site

The latest Oracle DBA forums

Report message to a moderator

Re: Free space after a delete [message #54575 is a reply to message #54551]

Fri, 22 November 2002 02:01

Rick
Messages: 49
Registered: March 2000

Member

Thanks Mahesh and Trifon,
for your help.
The problem is that my export is partial, I only export 1/5 of the rows of two table so I can't run a TRUNCATE command. Maybe I should export and import table data to release the space.
What can you suggest me?

Thanks again
Rick

Report message to a moderator

Re: Free space after a delete [message #54580 is a reply to message #54551]

Fri, 22 November 2002 07:23

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

Senior Member
Account Moderator

YES.
That will also reset the highwater mark.
make sure u use COMPRESS =y

Report message to a moderator

What about 'ALTER TABLE DEALLOCATE UNUSED'? [message #54584 is a reply to message #54551]

Fri, 22 November 2002 09:36

Rick
Messages: 49
Registered: March 2000

Member

Thanks again Mahesh. I was taking a look at the documentation and I found the command:
ALTER TABLE
DEALLOCATE UNUSED
Do you think it can work for my purposes?

Bye

Rick

Report message to a moderator

Re: What about 'ALTER TABLE DEALLOCATE UNUSED'? [message #54585 is a reply to message #54584]

Fri, 22 November 2002 09:41

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

Senior Member
Account Moderator

to some extent....YES.
But using

[i]Alter tablespace deallocate unused[/i]

will only free the space that is above the highwater mark.

Report message to a moderator

Previous Topic:	root.sh - whats the overall purpose of it?
Next Topic:	Free Datadictionary tools

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Sep 19 20:03:48 CDT 2024