Frequently faced Database Issues

DeAllocating Unused Space in the database:

Create tables to track/store the statistics of Tables being used for Defragmentation.
1. TABDEFRAG_TRACE and
2. TSMVMNT_LOG

Below statements are used to deallocate the unused space:

alter table <TABLE_NAME> deallocate unused;
alter table <TABLE_NAME> enable row movement;
alter table <TABLE_NAME> shrink space compact;
alter table <TABLE_NAME> shrink space;
alter table <TABLE_NAME> disable row movement;

Complete Generic Local Procedure to deallocate the Unused space/ Defragment :

Below procedure would be useful:


CREATE TABLE TABDEFRAG_TRACE ( TABNAME VARCHAR2(1000),LOG_DATE DATE DEFAULT SYSDATE) ;

CREATE TABLE TSMVMNT_LOG (TABNAME VARCHAR2(1000),TEXT VARCHAR2(4000),LOG_DATE DATE DEFAULT SYSDATE);

DECLARE
    ERRORMSG VARCHAR2(4000);
BEGIN
    FOR i IN (Select TABLE_NAME FROM USER_TABLES) LOOP
        BEGIN
           
              execute immediate 'alter table  '||i.TABLE_NAME||'  deallocate unused';
              execute immediate 'alter table  '||i.TABLE_NAME||'  enable row movement';
              execute immediate 'alter table  '||i.TABLE_NAME||'  shrink space compact';
              execute immediate 'alter table  '||i.TABLE_NAME||'  shrink space';
              execute immediate 'alter table  '||i.TABLE_NAME||'  disable row movement';
            INSERT INTO TABDEFRAG_TRACE(TABNAME) VALUES(i.TABLE_NAME);
                COMMIT;
        EXCEPTION
            WHEN OTHERS THEN
                ERRORMSG := SUBSTR(SQLERRM,1,4000);
                INSERT INTO TSMVMNT_LOG(TABNAME,text) VALUES(i.TABLE_NAME,ERRORMSG);
                COMMIT;
                 ERRORMSG := '';
         END;
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
                    ERRORMSG := SUBSTR(SQLERRM,1,4000);
                INSERT INTO TSMVMNT_LOG(TABNAME,text) VALUES('OutSide Loop',ERRORMSG);
                COMMIT;
                ERRORMSG := '';
END;
/