a High water mark question

[hany]

hi all,
i need to know the use of high water mark, i know the concept behind it, but why does oracle need to keep the high water mark after deleting rows?, it has a bad effect in database performance, right? (in case of a full table scan), i also know that we can deallocate the unused space using "Alter table .. deallocate unused", but does deallocating unused space has a bad impact?
i need to know that because i'm thinking of dallocating all the unused space before performing backup, so that minimizing as much as i can the size of the backup files.

i also need to know if there is other statment that can deallocate the unused space of the whole tablespace, instead of deallocating a table by table.

Thanks in advance.

[anandkl]

If you perform delete operation,you will not be removing the records permanently from the database as they will be reference of it the index,and also since undo info would be generated,and once u rollback oracle needs to knwo till what point the records have to be place and into which all blocks.and since HWM gives us the highest no. of the block holding the record,its required.
You can also resize at the tablespace level using 'resize' option of the alter database datafile 'file name' resize ;
If you data exists behind the resize values oracle will generate an error.

regards
anandkl

[Sureshy]

why would you want to deallocate unused for a tablespace (i.e every table) ?

When your database is operational again, most tables would have run out of space and would each need a new extent, causing contention.

Why not consider another strategy for reducing datafile size, like compress.

[hany]

Originally posted by Sureshy
why would you want to deallocate unused for a tablespace (i.e every table) ?

well, when i need to make a full database backup,i'll be trying as much as i can to minimize the size of the generated backup sets, so i was thinking to deallocate the unused space in my database (i.e tablespaces) before performing backup.
now i know that server session skips the datafile blocks that have never been used, but what about the free blocks (blocks that are not currently used) under the high water mark?
why would i need them? why would i need the high water mark?

Originally posted by Sureshy

When your database is operational again, most tables would have run out of space and would each need a new extent, causing contention.

what do you mean by when my database is operational again? what would stop my database from being operational?

Originally posted by Sureshy

Why not consider another strategy for reducing datafile size, like compress.

well as i mentioned, i'm trying to minimize the size of the files to be backed up, so if you think compression is a good method, would you please give me some links to know more about it?

Thanks in advance.

[hany]

well let me rephrase the questions.

1- is it a good idea to deallocate the unused space before making a full backup using Rman?

2- if the answer is yes, is there a way to deallocate a whole tablespace by one statment, instead of deallocating a table by table?

3- if the answer is no, and i'd better go for compression, could anyone please provide me with a document about compression?

Thanks in advance.

[hany]

anyone has an answer?

Thanks.