Reclaim tablespace size after a large table delete and the need for archiving

Below is an image of tablespace usage for HR tablespace from Oracle Enterprise Manager.

The image show the size of tablespace as 100 MB and the tablespace space used as 9.5%.

You can also get this tablespace usage from the below query.

select b.tablespace_name  "Tablespace Name" ,
       kbytes_alloc/1024  "Mbytes",
       (kbytes_alloc-nvl(kbytes_free,0))/1024  "Mbytes used",
       nvl(kbytes_free/1024,0) "Mbytes free",
       ((kbytes_alloc-nvl(kbytes_free,0))/
                          kbytes_alloc)*100 "pct_used"
from ( select sum(bytes)/1024 Kbytes_free,
        tablespace_name
       from  sys.dba_free_space
       group by tablespace_name ) a,
     ( select sum(bytes)/1024 Kbytes_alloc,
              tablespace_name
       from sys.dba_data_files
       group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name
and a.tablespace_name = 'HR'
order by a.tablespace_name;

Tablespace Name	Mbytes	Mbytes used	Mbytes free	pct_used
HR	96.6484375	9.1484375	87.5	9.46

We now insert about a million (10,00,000) records in the table and check the new tablespace size.

DECLARE
  v_employee_id   NUMBER := 206;
  v_commit_cnt  NUMBER := 0;
BEGIN
    FOR i IN 1 .. 1000000
    LOOP --{
      v_employee_id := v_employee_id + 1;
      v_commit_cnt := v_commit_cnt + 1;
      
      INSERT INTO BKP_EMPLOYEES
      (
        EMPLOYEE_ID,
        FIRST_NAME,
        LAST_NAME,
        EMAIL,
        PHONE_NUMBER,
        HIRE_DATE,
        JOB_ID,
        SALARY,
        COMMISSION_PCT,
        MANAGER_ID,
        DEPARTMENT_ID
      )
      SELECT
        v_employee_id,
        FIRST_NAME,
        LAST_NAME,
        EMAIL,
        PHONE_NUMBER,
        HIRE_DATE,
        JOB_ID,
        SALARY,
        COMMISSION_PCT,
        MANAGER_ID,
        DEPARTMENT_ID
        FROM EMPLOYEES
        WHERE EMPLOYEE_ID = 206;
        
        IF v_commit_cnt = 5000
        THEN
          COMMIT;
          v_commit_cnt := 0;
        END IF;  
    
    END LOOP ; --}

END;

Below is OEM diagrammatic representation of the increased size of HR tablespace.

Tablespace Name	Mbytes	Mbytes used	Mbytes free	pct_used
HR	96.6484375	91.1484375	5.5	94.31

The abive shows 94.3 percent of the tablespace size usage (91MB of 96MB used). Now issue the following delete command and view the tablespace size.

DELETE FROM BKP_EMPLOYEES;
1,000,000 rows deleted.
COMMIT;
SELECT COUNT(1) FROM BKP_EMPLOYEES;
COUNT(1)
----------
         0

If we now view the OEM tabelspace storage diagram or run the tablespace usage query, the output is still the same.

Tablespace Name	Mbytes	Mbytes used	Mbytes free	pct_used
HR	96.6484375	91.1484375	5.5	94.31

To reclaim the unused space we now run the following command. We have to enable row movement and shrink the table size with the below command.

ALTER TABLE BKP_EMPLOYEES ENABLE ROW MOVEMENT;
table BKP_EMPLOYEES altered.
ALTER TABLE BKP_EMPLOYEES SHRINK SPACE CASCADE;
table BKP_EMPLOYEES altered.

Now run the query to check the new tablespace usage and view the OEM tablepace storage.

Below is the tablespace size query output.

Tablespace Name	Mbytes	Mbytes used	Mbytes free	pct_used
HR	96.6484375	9.1484375	87.5	9.46

Thought for the day

With patience a ruler may be persuaded and a soft tongue can break bones.

Proverbs 25:15