Table size doen not reduce [message #291931] |
Mon, 07 January 2008 04:54 |
ora_2007
Messages: 430 Registered: July 2007 Location: Mumbai
|
Senior Member |
|
|
Hi Expets,
I am deleting records in the TEST table because of space problem.
Please find tablespace details.
TABLESPACE_NAME Alloc MB Free MB Used MB % Used
-------------------------------------------------------------
TEST_TBS 32968 3808 29160 88.4494055
Among the 142335929 records from TEST table i deleted 124000 records.
And when i seen the tablespace size is not reduced means the free space remains same.
As far as my knowledge is concerned the tablespace size must be reduced.
Please tell me why space is not reduced or any solution so that i can look into it.
Thanks in advance.
|
|
|
|
Re: Table size doen not reduce [message #291965 is a reply to message #291940] |
Mon, 07 January 2008 05:42 |
ora_2007
Messages: 430 Registered: July 2007 Location: Mumbai
|
Senior Member |
|
|
Thanks Frank.
Does ALTER TABLE .. DEALLOCATE UNUSED; command locks the table?
Because i am working in 24*7 production environment.
The table on which i will work is the important table.
If it locks the table then please tell me how much time it will take?
Thanks in advance.
|
|
|
|
|
|
|
Re: Table size does not reduce [message #291984 is a reply to message #291982] |
Mon, 07 January 2008 06:15 |
Frank Naude
Messages: 4581 Registered: April 1998
|
Senior Member |
|
|
That's what I thought. Both ALTER TABLE .. DEALLOCATE UNUSED; and ALTER TABLE .. MOVE ONLINE; should work.
DEALLOCATE UNUSED would be the fastest - it will only release unused blocks above the high level mark.
MOVE ONLINE will take longer, but will release ALL unused blocks in the table.
|
|
|
|
|
Re: Table size doen not reduce [message #291996 is a reply to message #291931] |
Mon, 07 January 2008 06:54 |
mkbhati
Messages: 93 Registered: February 2007 Location: Mumbai
|
Member |
|
|
You have following five options available to meet your desired goal. Use which suits you best. Every option have some advantage/disadvantage.
Option-1
ALTER TABLE employees DEALLOCATE UNUSED;
Use the deallocate unused to explicitly deallocate unused space at the end of the table and make the space available for other segments in the tablespace. This command frees all unused space for reuse in table where the high water mark is above MINEXTENTS Only
Option-2
ALTER TABLE employees shrink space;
Use this command to manally shrink space in a table. This command is valid only for segments in tablespaces with automatic segment management. By default, Oracle Database compacts the segment,
adjusts the high water mark, and releases the recuperated space immediately.Remember that Compacting the segment requires row movement. Therefore, you must enable row movement for the object you want to shrink before using shrink on a object. Also please disable any rowid-based triggers before using Shrink.
Option-3
ALTER TABLE employees shrink space compact;
If you use COMPACT, then Oracle Database only defragments the
segment space and compacts the table rows for subsequent release. The database does not readjust the high water mark and does not release the space immediately. You must issue another ALTER TABLE ... SHRINK SPACE command later to complete the operation. This clause is useful if you want to accomplish the shrink operation in two shorter steps rather than one longer step which usually takes time depending upon pre delete table size.
Option - 4
ALTER TABLE employees shrink space compact cascade;
If you use CASCADE then Oracle Database performs the same operations on all dependent objects of table, including secondary indexes on index-organized tables.
Option - 5
Recreate the table with different name by using create table as select ..... when table is created check constraints, dependencies etc if satisfied than put database in suspension for few moments rename old table to something & rename newly created to old. You will be lucky if users have not locked any rows. Preferably plan a maintenance window to do this if you can afford it.
Also please note that delete do not reclaim free space from deleted rows. Oracle keep this space for future insertions because oracle assumes that there will be (some day) some inserting to this table.
Also one thing bogs me down that in this era of cheap disk storage why you want to reclaim space from deleted rows when you will always have possibility or probability of inserting or modifying rows in future.
Hope this will clear what you are looking for.
Regards
mkbhati
[Updated on: Mon, 07 January 2008 06:55] Report message to a moderator
|
|
|
|
|
|
Re: Table size doen not reduce [message #292050 is a reply to message #292040] |
Mon, 07 January 2008 10:28 |
|
Michel Cadot
Messages: 68718 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
It is mostly (a copy and paste of) parts of the ALTER TABLE documentation.
For instance, for option 2, you have at http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_3001.htm#i2192484
Quote: | shrink_clause
The shrink clause lets you manually shrink space in a table, index-organized table or its overflow segment, index, partition, subpartition, LOB segment, materialized view, or materialized view log. This clause is valid only for segments in tablespaces with automatic segment management. By default, Oracle Database compacts the segment, adjusts the high water mark, and releases the recuperated space immediately.
Compacting the segment requires row movement. Therefore, you must enable row movement for the object you want to shrink before specifying this clause. Further, if your application has any rowid-based triggers, you should disable them before issuing this clause.
|
So if you want to have all the options go to ALTER TABLE
Regards
Michel
|
|
|
Re: Table size doen not reduce [message #292185 is a reply to message #292050] |
Tue, 08 January 2008 03:04 |
ora_2007
Messages: 430 Registered: July 2007 Location: Mumbai
|
Senior Member |
|
|
I will be using one of the option to free the space.
ALTER TABLE employees DEALLOCATE UNUSED;/ OR
ALTER TABLE employees shrink space;/ OR
ALTER TABLE employees shrink space compact;/ OR
ALTER TABLE employees shrink space compact cascade;
Still i have a question will table locks while doing this activity?
I am asking this because I am working in 24*7 environment and table contains 142335929 records, so i think that this activity will take more time to complete.
And locking of the table is not affordable.
Thanks in advacne.
|
|
|
|
Re: Table size doen not reduce [message #292935 is a reply to message #292187] |
Thu, 10 January 2008 03:44 |
ora_2007
Messages: 430 Registered: July 2007 Location: Mumbai
|
Senior Member |
|
|
After reading documentataion i implemented the things which are required.
1. Disabled row level trigers.
2.
SQL> ALTER TABLE TEST ENABLE ROW MOVEMENT;
Table altered.
Elapsed: 00:00:00.09
SQL> ALTER TABLE TEST SHRINK SPACE COMPACT;
ALTER TABLE GLOBASE.LEAD SHRINK SPACE COMPACT
*
ERROR at line 1:
ORA-10631: SHRINK clause should not be specified for this object
Elapsed: 00:00:00.03
3. Then i checkecd for the error then i find that there should not be any function based index on the table.
On the TEST table there is function based index.
One thing i can do is that i can drop the index.
Then again execute the SHRINK SPACE COMPACT command.
And again i can create an index.
Is it a right solution what i am thinking?
If yes then i can not drop index on the production.
What can i do in this situation?
Please tell me other way to do it?
Thanks in advance.
[Updated on: Thu, 10 January 2008 04:33] Report message to a moderator
|
|
|
Re: Table size doen not reduce [message #293851 is a reply to message #291931] |
Tue, 15 January 2008 04:59 |
ora_2007
Messages: 430 Registered: July 2007 Location: Mumbai
|
Senior Member |
|
|
One thing i want to share is that when i performed:
ALTER TABLE TEST ENABLE ROW MOVEMENT;
That time all the dependent objects gets invalidated.
i.e.procedure,function,package.
So i recompiled those objects.
Thanks.
|
|
|
|
|
Re: Table size doen not reduce [message #293861 is a reply to message #293855] |
Tue, 15 January 2008 05:57 |
ora_2007
Messages: 430 Registered: July 2007 Location: Mumbai
|
Senior Member |
|
|
Thanks Michel,
I am using oracle 10.2.0.1.0 version.
The thing is i need to validate those objects depend on the TEST table.
Michel, do you think any other wrong thing will happen on the table/database if i will execute alter table TEST enable row movement.
Thanks in advance.
|
|
|
Re: Table size doen not reduce [message #293862 is a reply to message #293861] |
Tue, 15 January 2008 06:06 |
|
Michel Cadot
Messages: 68718 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Invalidation of procedure... is not really important as they will be automatically recompile at next access. It just takes a little more time to execute.
The only bad point is if you use PL/SQL package variables or Java procedure as this also invalidates the environment context and can lead to application problem.
So do this only during maintenance time.
Regards
Michel
[Updated on: Tue, 15 January 2008 06:06] Report message to a moderator
|
|
|
|
|
|