Oracle | DBMS_IMMUTABLE

Oracle DBMS_IMMUTABLE_TABLE Version 23c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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

Subprograms

ADD_INTERVAL_PARTITIONING

DELETE_EXPIRED_ROWS

ADD_INTERVAL_PARTITIONING(new 23c)

Undocumented dbms_immutable_table.add_interval_partitioning( schema_name IN VARCHAR2, table_name IN VARCHAR2, interval_number IN NUMBER, interval_frequency IN VARCHAR2, first_high_timestamp IN TIMESTAMP); PRAGMA SUPPLEMENTAL_LOG_DATA(add_interval_partitioning, AUTO_WITH_COMMIT);

TBD

DELETE_EXPIRED_ROWS

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); PRAGMA SUPPLEMENTAL_LOG_DATA(delete_expired_rows, NONE);

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