JAVA J2EE 4 U

SET SERVEROUTPUT ON

SET LINESIZE 200

SET PAGESIZE 1000

DECLARE

    v_tablespace    VARCHAR2(30) := 'USERS'; -- << CHANGE YOUR TABLESPACE NAME HERE

    v_block_size    NUMBER;

    v_file_id       NUMBER;

    v_file_name     VARCHAR2(512);

    v_current_bytes NUMBER;

    v_hwm_blocks    NUMBER;

    v_hwm_bytes     NUMBER;

    v_new_size_bytes NUMBER;

    v_autoextensible VARCHAR2(3);

    v_min_resize_bytes NUMBER;

    v_buffer_mb     NUMBER := 10; -- Safety margin in MB

BEGIN

    -- Get block size of the tablespace

    SELECT block_size INTO v_block_size

    FROM dba_tablespaces

    WHERE tablespace_name = UPPER(v_tablespace);

    -- Loop through datafiles of the tablespace

    FOR r IN (

        SELECT file_id, file_name, bytes, autoextensible

        FROM dba_data_files

        WHERE tablespace_name = UPPER(v_tablespace)

    ) LOOP

        v_file_id := r.file_id;

        v_file_name := r.file_name;

        v_current_bytes := r.bytes;

        v_autoextensible := r.autoextensible;

        -- Get highest block used in this file

        SELECT NVL(MAX(block_id + blocks), 0)

        INTO v_hwm_blocks

        FROM dba_extents

        WHERE file_id = v_file_id;

        v_hwm_bytes := v_hwm_blocks * v_block_size;

        v_min_resize_bytes := v_hwm_bytes + (v_buffer_mb * 1024 * 1024);

        -- Only suggest resize if current size is more than the HWM + buffer

        IF v_current_bytes > v_min_resize_bytes THEN

            v_new_size_bytes := FLOOR(v_min_resize_bytes / (1024*1024)) * 1024*1024; -- aligned to MB

            DBMS_OUTPUT.PUT_LINE(

                'ALTER DATABASE DATAFILE ''' || v_file_name || ''' RESIZE ' || ROUND(v_new_size_bytes / (1024*1024)) || 'M;'

            );

        ELSE

            DBMS_OUTPUT.PUT_LINE('-- No reclaimable space in file: ' || v_file_name);

        END IF;

    END LOOP;

END;

/