Reclaim space of deleted lob datatype

-
When we simply delete  some rows from a table having multiple lob datatype columns  the no space released from the data file.
To reclaim space of delete lob datatype columns we need to use shrink  as follows-


select 'alter table TABLE_NAME modify lob ('||column_name||') (SHRINK SPACE);' from user_tab_columns
where table_name='TABLE_NAME' and data_type='BLOB';

Put your table name on above query and it will generate a script to shrink space occupied by lob segment.



Comments

Post a Comment

Popular posts from this blog

Overcome from large trace file issue

-
When you are facing large size trace file generation in oracle database then further space lack possible to occur. To limit size of trace file generation you need to modify this oracle parameter - Set max_dump_file_size parameter to a lower value as - SQL> show parameter dump_file NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ max_dump_file_size string unlimited SQL> alter system set max_dump_file_size='500M' scope=both sid='*'; System altered. SQL> show parameter dump_file NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ max_dump_file_size string 500M
Read more

Start Enterprise Manager After Hostname Change

-
Some time we need to change hostname or ip address  of machine , the result is no listner and unable to start Enterprise Manager. To resolve this issue  thre are some simple steps we need to follow- 1. first change your host name in listner.ora file 2. execute following command to reconfigure enterprise manager-     emca -deconfig dbcontrol db emca -config dbcontrol db -repos recreate   or   emca -deconfig dbcontrol db -repos drop emca -config dbcontrol db -repos create  
Read more