Help - Search - Members - Calendar
Full Version: space usage oracle
Oracle DBA Forums > Oracle > Oracle Forum
crazyblood
If there is no space for new inserts in the database, and after that if I delete some rows, can I insert new rows immediately? or do I have to do shrink or use alter table move clause to reuse deleted blocks.
Oracle 10g
dave
the rows you deleted free enough space then yes
crazyblood
QUOTE (crazyblood @ Feb 5 2008, 07:54 PM) *
If there is no space for new inserts in the database, and after that if I delete some rows, can I insert new rows immediately? or do I have to do shrink or use alter table move clause to reuse deleted blocks.
Oracle 10g


so deleted blocks can be used for new inserts even if they are below high water mark
Am i right?
Asad
QUOTE (crazyblood @ Feb 5 2008, 08:26 PM) *
so deleted blocks can be used for new inserts even if they are below high water mark
Am i right?


you need to give /*+append */ hint to put data over hwm.
crazyblood
QUOTE (Asad @ Feb 5 2008, 08:30 PM) *
you need to give /*+append */ hint to put data over hwm.


so after deletion the deleted blocks below the hwm can be used for new inserts,I dont need to use alter table move or shrink clause right?
dave
course not
crazyblood
shrink reset empty blocks whereas alter table move doesnt.Is it true?
What is the diffrence between shrink and alter table move
crazyblood
QUOTE (crazyblood @ Feb 6 2008, 11:46 AM) *
shrink reset empty blocks whereas alter table move doesnt.Is it true?
What is the diffrence between shrink and alter table move.
burleson
>> If there is no space for new inserts in the database, and after that if I delete some rows, can I insert new rows immediately?

It depends on your PCTUSED setting. Ifthe deletes moved the block onto the freelist, then "yes":

http://www.dba-oracle.com/oracle_tips_PCTFREE_PCTUSED.htm

>> What is the diffrence between shrink and alter table move

Good question!

http://www.dba-oracle.com/t_alter_table_mo...hrink_space.htm

Alter table move - The alter table xxx move command moves rows down into un-used space and adjusts the HWM but does not adjust the segments extents, and the table size remains the same. The alter table move syntax also preserves the index and constraint definitions.

Alter table shrink space - Using the "alter table xxx shrink space compact" command will re-pack the rows, move down the HWM, and releases unused extents.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.