ORA-01735: Invalid ALTER TABLE option when deallocated unused space on a segment with dynamic SQL

Question

I have developed a simple packaged procedure that allows free space not used by a segment (above the HWM). It receives as parameters the schema name, the name of the table and the value for the KEEP clause.

When I run the DDL statement using the EXECUTE IMMEDIATE statement it tells me that the ALTER option is invalid. If I run the same statement from a normal terminal this works correctly.

BLOQUES ASIGNADOS -> 8
BLOQUES OCUPADOS -> 5
TAMAÑO INICIAL -> 9
LIBERANDO ESPACIO ALTER TABLE SERGIO11.EMPLOYEES DEALLOCATE UNUSED;
ERROR : -1735MENSAJE: ORA-01735: opción ALTER TABLE no válida

Here is the definition of the procedure:

PROCEDURE RELEASE_UNUSED_SPACE(
            p_owner IN DBA_TABLES.OWNER%TYPE,
            p_table IN DBA_TABLES.TABLE_NAME%TYPE,
            p_keep IN NUMBER DEFAULT NULL,
            p_unit IN VARCHAR2 DEFAULT 'M'
        ) AS
            UNAVAILABLE_UNIT EXCEPTION;
            TYPE R_SEGMENT_BLOCKS_INFO IS RECORD (
                occupied_blocks   DBA_SEGMENTS.BLOCKS%TYPE,
                assigned_blocks   DBA_SEGMENTS.BLOCKS%TYPE,
                initial_extent    DBA_SEGMENTS.INITIAL_EXTENT%TYPE
            );

            v_segmentBlocksInfo R_SEGMENT_BLOCKS_INFO;
            v_deallocate_sql VARCHAR2(100);

        BEGIN

            IF(p_owner IS NULL OR p_table IS NULL) THEN
                RAISE INVALID_ARGUMENTS;
            END IF;

            IF (p_keep IS NOT NULL AND UPPER(p_unit) NOT IN ('M', 'K')) THEN
                RAISE UNAVAILABLE_UNIT;
            END IF;

            SELECT T.BLOCKS, S.BLOCKS, CEIL(S.INITIAL_EXTENT / 8000)
            INTO v_segmentBlocksInfo
            FROM DBA_TABLES T, DBA_SEGMENTS S
            WHERE S.SEGMENT_NAME = T.TABLE_NAME AND S.OWNER = T.OWNER
                AND S.OWNER = T.OWNER
                AND T.TABLE_NAME = UPPER(p_table) AND T.OWNER = UPPER(p_owner);

            PRINT_LINE('BLOQUES ASIGNADOS -> ' || v_segmentBlocksInfo.assigned_blocks);
            PRINT_LINE('BLOQUES OCUPADOS -> ' || v_segmentBlocksInfo.occupied_blocks);
            PRINT_LINE('TAMAÑO INICIAL -> ' || v_segmentBlocksInfo.initial_extent);

            IF v_segmentBlocksInfo.occupied_blocks < v_segmentBlocksInfo.assigned_blocks THEN
                v_deallocate_sql := 'ALTER TABLE ' || p_owner || '.' || p_table || ' DEALLOCATE UNUSED';
                IF p_keep IS NOT NULL THEN
                    v_deallocate_sql := v_deallocate_sql || ' KEEP ' || p_keep || 'M'; 
                END IF;
                v_deallocate_sql := v_deallocate_sql || ';';
                PRINT_LINE('LIBERANDO ESPACIO ' || v_deallocate_sql);
                EXECUTE IMMEDIATE v_deallocate_sql;
            END IF;

        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                PRINT_LINE('TABLE ' || p_table || ' ON SCHEMA ' || p_owner || ' NOT EXISTS');
            WHEN UNAVAILABLE_UNIT THEN
                PRINT_LINE('UNIT OF MEASURE NOT VALID. Only M or K is allowed');
            WHEN OTHERS THEN
                PRINT_LINE('ERROR : ' || SQLCODE || 'MENSAJE: ' || SQLERRM);
        END RELEASE_UNUSED_SPACE;

For example when executing the procedure with this parameters the resulting sentence would be this:

EXEC MANAGEMENT_OF_TABLES_AND_INDEX.RELEASE_UNUSED_SPACE('SERGIO11', 'EMPLOYEES');

ALTER TABLE SERGIO11.EMPLOYEES DEALLOCATE UNUSED;

I have explicitly granted the ALTER ANY TABLE permission to the owner user who is also the same one that executes it.

Does anyone know what the problem is. Thanks in advance!!!.

Answer 1

Try remove this statement

v_deallocate_sql := v_deallocate_sql || ';';

So your sentence should be :

ALTER TABLE SERGIO11.EMPLOYEES DEALLOCATE UNUSED