Oracle | DBMS_IMMUTABLE

Oracle DBMS_IMMUTABLE_TABLE Version 21c

General Information

Library Note

Morgan's Library Page Header

Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.

Purpose Allows deletion of expired rows in an immutable table

AUTHID CURRENT_USER

Dependencies

DBMS_ASSERT

DBMS_BLOCKCHAIN_TABLE_LIB

DBMS_UTILITY

Documented Yes

Exceptions

Error Code	Reason
ORA-05771	<table_name> is not an immutable table

First Available Yes: Packages and Types Reference

Pragma PRAGMA SUPPLEMENTAL_LOG_DATA(default, UNSUPPORTED);

Security Model Owned by SYS with EXECUTE granted to PUBLIC

Source {ORACLE_HOME}/rdbms/admin/dbmsbctab.sql

DELETE_EXPIRED_ROWS (new 21.3c)

Undocumented dbms_immutable_table.delete_expired_rows( schema_name IN VARCHAR2, table_name IN VARCHAR2, before_timestamp IN TIMESTAMP WITH TIME ZONE DEFAULT NULL, number_of_rows_deleted OUT NUMBER);

DECLARE

       delRows INTEGER;

      BEGIN

        dbms_immutable_table.delete_expired_rows('C##UWCLASS', 'AUDIT_LOG', SYSDATE-90, delRows);

        dbms_output.put_line(TO_CHAR(delRows));

      END;

      /

      42