Month: October 2024
-
SYSAUX Tablespace Growing Rapidly due to SCHEDULER$_JOB_OUTPUT Table and ORA-01031 Error in Automatic segment advisor task
The SYSAUX tablespace unexpectedly grew, primarily due to the SYS.SCHEDULER$_JOB_OUTPUT table, which consumed 14 GB. The V$SYSAUX_OCCUPANTS view was utilized to identify space usage issues, revealing excessive data generated from the DBMS_OUTPUT.PUT_LINE command in a loop. After performing purge and truncate operations, log records generated with DBMS_OUTPUT.PUT_LINE ceased; however, a log record related to the Automatic Segment Advisor task showed that insufficient privileges (ORA-01031) occurred daily. Granting explicit CREATE TABLE permission resolved the issue, ensuring the proper functionality of the Automatic Segment Advisor.
-
A 3D data with a 2D SRID may result with ORA-13029 #JoelKallmanDay
This post discusses the importance of correctly storing 3D geometry with an appropriate 3D Spatial Reference Identifiers (SRIDs) following Oracle’s upgrade to version 19c. It details the ORA-13029 error encountered when a 3D data stored with a 2D SRID and provides solutions, including applying patch 31022826 and setting event 54713. Alternatively, it suggests updating metadata with an equivalent 3D SRID and recreating the spatial index as another solution.
-
How to send mail from PostgreSQL database
This content discusses methods for sending emails directly from a PostgreSQL database. Three approaches are highlighted: using PostgreSQL extensions like pgMail or pgSmtp, creating custom stored procedures with untrusted languages, and employing the LISTEN/NOTIFY mechanism to communicate with external services. The LISTEN/NOTIFY method is implemented in detailed steps: configuring Postfix for email delivery, creating a PostgreSQL table for email records, setting up a trigger, writing a Python listener, creating a systemd service, and managing logs. This solution provides a reliable method for managing emails directly from the database.
Osman’s DBlog 