Home » RDBMS Server » Server Administration » Table size doen not reduce
Table size doen not reduce [message #291931] Mon, 07 January 2008 04:54 Go to next message
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 #291940 is a reply to message #291931] Mon, 07 January 2008 05:16 Go to previous messageGo to next message
Frank Naude
Messages: 4580
Registered: April 1998
Senior Member
Try using the ALTER TABLE .. DEALLOCATE UNUSED; command.
Re: Table size doen not reduce [message #291965 is a reply to message #291940] Mon, 07 January 2008 05:42 Go to previous messageGo to next message
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 doen not reduce [message #291966 is a reply to message #291931] Mon, 07 January 2008 05:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68666
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

As far as my knowledge is concerned the tablespace size must be reduced.

The tablespace NEVER reduce unless you shrink the data files or drop a file if you are in 10g and up (and if this is possible, in both cases).

Regards
Michel
Re: Table size doen not reduce [message #291967 is a reply to message #291965] Mon, 07 January 2008 05:48 Go to previous messageGo to next message
Frank Naude
Messages: 4580
Registered: April 1998
Senior Member
I don't think it will. However, rather test it yourself on a dev system before trying it on production.

PS: Another option might be: ALTER TABLE ... MOVE ONLINE;
Re: Table size does not reduce [message #291968 is a reply to message #291967] Mon, 07 January 2008 05:49 Go to previous messageGo to next message
Frank Naude
Messages: 4580
Registered: April 1998
Senior Member
Oops, I've missed that. Do you want to shrink the tablespace or free-up more space within the tablespace?
Re: Table size does not reduce [message #291982 is a reply to message #291968] Mon, 07 January 2008 06:11 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
My concern is that when i will delete data from the TEST table then i should get the more free space in the tablespace.

I dont want to shrink the tablespace.

Re: Table size does not reduce [message #291984 is a reply to message #291982] Mon, 07 January 2008 06:15 Go to previous messageGo to next message
Frank Naude
Messages: 4580
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 #291985 is a reply to message #291931] Mon, 07 January 2008 06:18 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Oracle did not give authority to "delete" statement to free up space.
do create table as select * from table and rename back the table.
Re: Table size does not reduce [message #291993 is a reply to message #291984] Mon, 07 January 2008 06:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68666
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

DEALLOCATE UNUSED would be the fastest - it will only release unused blocks above the high level mark.

So it will not deallocate more than it could do it BEFORE deletes.

The different way to compact the table are:
- export/drop/import
- alter table move
- alter table shrink space
- alter table shrink space compact + alter table shrink space

alter table shrink is available only if you use ASSM.

Regards
Michel

Re: Table size doen not reduce [message #291996 is a reply to message #291931] Mon, 07 January 2008 06:54 Go to previous messageGo to next message
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 #292000 is a reply to message #291996] Mon, 07 January 2008 06:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68666
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nice wrap-up.
You didn't mention:
- export/truncate/import
- alter table move [online]
or even dbms_redefinition.

Regards
Michel
Re: Table size doen not reduce [message #292021 is a reply to message #292000] Mon, 07 January 2008 08:19 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
I really appreciate your answers and thank you very much for your perfect replies.
It helps me a lot.

Thanks again.
Re: Table size doen not reduce [message #292040 is a reply to message #292021] Mon, 07 January 2008 09:43 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member
Very nice explaination for most of the options..

Thanks mkbhati
Re: Table size doen not reduce [message #292050 is a reply to message #292040] Mon, 07 January 2008 10:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68666
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 #292187 is a reply to message #292185] Tue, 08 January 2008 03:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68666
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you read the documentation?

Regards
Michel
Re: Table size doen not reduce [message #292935 is a reply to message #292187] Thu, 10 January 2008 03:44 Go to previous messageGo to next message
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. Sad

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 Go to previous messageGo to next message
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 #293853 is a reply to message #293851] Tue, 15 January 2008 05:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68666
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback.

Regards
Michel
Re: Table size doen not reduce [message #293855 is a reply to message #293851] Tue, 15 January 2008 05:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68666
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I checked, it is no more the case in 11g:
SQL> create table t (col integer);

Table created.

SQL> create or replace procedure p (p integer)
  2  is
  3    val integer;
  4  begin
  5    select col into val from t where col=p;
  6  end;
  7  /

Procedure created.

SQL> select status from user_objects where object_name='P';
STATUS
-------
VALID

1 row selected.

SQL> alter table t enable row movement;

Table altered.

SQL> select status from user_objects where object_name='P';
STATUS
-------
INVALID

1 row selected.

SQL> @v

Version Oracle : 10.2.0.3.0

SQL> create table t (col integer);

Table created.

SQL> create or replace procedure p (p integer)
  2  is
  3    val integer;
  4  begin
  5    select col into val from t where col=p;
  6  end;
  7  /

Procedure created.

SQL> select status from user_objects where object_name='P';
STATUS
-------
VALID

1 row selected.

SQL> alter table t enable row movement;

Table altered.

SQL> select status from user_objects where object_name='P';
STATUS
-------
VALID

1 row selected.

SQL> @v

Version Oracle : 11.1.0.6.0

Regards
Michel
Re: Table size doen not reduce [message #293861 is a reply to message #293855] Tue, 15 January 2008 05:57 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68666
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: Table size doen not reduce [message #293865 is a reply to message #293862] Tue, 15 January 2008 06:18 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Thanks for perfect reply, Michel.

Re: Table size doen not reduce [message #340127 is a reply to message #293865] Mon, 11 August 2008 09:44 Go to previous messageGo to next message
ankush_chawla
Messages: 136
Registered: November 2006
Senior Member
does the below command has any side effect
Alter tablespace <tablespace> coalesce
Re: Table size doen not reduce [message #340132 is a reply to message #340127] Mon, 11 August 2008 10:11 Go to previous message
Michel Cadot
Messages: 68666
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not for reducong the size, and no one on LMT.

Regards
Michel
Previous Topic: 10g upgrade questions
Next Topic: Password File (oracle)
Goto Forum:
  


Current Time: Mon Jul 08 15:31:06 CDT 2024