Category: Oracle
-
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.
-
Oracle ACFS File System: Inode Limitations Explained
This content discusses an issue with running out of inodes on a directory in the ACFS (Oracle Automatic Storage Management Cluster File System) file system after a large table export. Generally, even with sufficient disk space, a high number of small files can exhaust available inodes, prompting alerts. In this case, we have seen that ACFS doesn’t have a pre-allocated inode table; rather, it estimates inodes based on available space, leading to confusion during inode checks.
-
Troubleshooting gc cr/current block lost events and Improving Oracle RAC Network Performance
The Enterprise Manager Cloud Control’s Overview of Incidents and Problems section identified ‘Metrics Global Cache Blocks Lost at XXX’ for a relatively new database, suggesting a potential network issue, likely related to the private network interfaces. Troubleshooting was guided by My Oracle Support documents, which outline common causes and initial diagnostic steps. Further actions included making adjustments across nearly all OSI Model Layers; such as enabling jumbo frames, tuning NIC buffers, adjusting IP fragmentation parameters, and optimizing socket buffers as well as refining the database layer. These measures collectively reduced ‘gc block lost’ events to nearly zero.
-
Oracle Enq: TX – Row Lock Contention: Find Blocking SQL
When encountering the Enq: TX – row lock contention wait event, it’s crucial to identify the root cause before taking action. This contention arises when multiple sessions attempt to modify the same row in a table simultaneously. To resolve it, database administrators should pinpoint the blocking session and sql statement. A detailed analysis of specific SQL statements can provide valuable insights for the development team, facilitating faster resolution.
-
v$sql INVALIDATION column and Prevent SQL Cursor Invalidation
This content highlights the impact of SQL cursor invalidation on system performance and suggests solutions to reduce the number of hard parses. It emphasizes the importance of avoiding hard parses in OLTP systems and discusses reasons for SQL cursor invalidation, such as schema changes, statistics updates and parameter changes. The implementation of atomic refresh for materialized views is presented as a solution to reduce SQL cursor invalidations leading to improved performance in OLTP systems.
