SYSAUX TABLESPACE IS TOO LARGE BECAUSE OF OPTIMIZER STATISTICS HISTORY
SYMPTOMS The SYSAUX tablespace uses too much space on the disk. — SYSAUX occupation by segment types SELECT SEGMENT_TYPE, ROUND(SUM(BYTES)/1024/1024) MB FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = ‘SYSAUX’ GROUP BY SEGMENT_TYPE ORDER BY 2; SEGMENT_TYPE MB ——————– ———- TABLE SUBPARTITION 2 NESTED TABLE 2 LOB PARTITION…
ERRORS ORA-39150 AND ORA-08186 DURING DATAPUMP IMPORT WHEN USING FLASHBACK_TIME OPTION
SYMPTOMS When attempting to perform a datapump import with the FLASHBACK_TIME option, the following errors occur ORA-39001: invalid argument value ORA-39150: bad flashback time ORA-08186: invalid timestamp specified For instance, the datapump command that was issued oracle@s001dev ~: impdp system/pwd NETWORK_LINK=MERIDB2_TOMSKPROD FLASHBACK_TIME=\”TO_TIMESTAMP\(TO_CHAR\(SYSDATE,\’YYYY-MM-DD HH24:MI:SS\’\),\’YYYY-MM-DD HH24:MI:SS\’\)\” schemas=TOMSKPROD…
ENTERPRISE MANAGER AGENT IS IN THE “COLLECTIONS_HALTED” STATUS
PROBLEM The emctl status agent command output shows that the agent is Running and Ready but its collection status is the [COLLECTIONS_HALTED(UPLOAD SYSTEM Threshold – unable to purge files in upload system)] SYMPTOMS The agent status shows oracle@dbpilot|09:23|~/scripts: emctl status agent Oracle Enterprise Manager Cloud…
HOW TO GRANT THE SELECT PRIVILEGE ON A VIEW TO THIRD PARTY USERS
SYMPTOMS When the owner of a view granting the SELECT privilege on its view to another user the following error is encountered ORA-01720: grant option does not exist for ‘schema_name.table_name’ CAUSE When a view is based on the objects of other schemas it is not…
ORA-29516: AURORA ASSERTION FAILURE: ASSERTION FAILURE AT OEX.C:359
SYMPTOMS During a PSU patch’s post steps, I had an unusual error that was intermittent. Sometimes the post step script completed successfully without any errors and sometimes it failed. I tried many workarounds but none of them helped me out. For instance, when I…
HOW TO AUTO-PURGE STATSPACK SNAPSHOTS
GOAL This note shows a way to purge statspack data automatically that is older than a specified number of days SOLUTION To purge unnecessary statspack data use the STATSPACK.PURGE procedure. The procedure has the parameter that accepts the number of days…
ORA-02049: TIMEOUT: DISTRIBUTED TRANSACTION WAITING FOR LOCK
SYMPTOMS An application reports about multiple errors ORA-02049: timeout: distributed transaction waiting for lock in the application’s log file. RELATED A BLOCKING SESSION ORA-00060: DEADLOCK DETECTED WHILE WAITING FOR RESOURCE DIAGNOSE The error occurs when a session is trying to…
THE FAILED LOGINS AND THE FAILED LOGINS (HISTORICAL) METRICS ARE EXTREMELY SLOW RESULTING IN HIGH IO AND CPU CONSUMPTION
SYMPTOMS The Oracle Enterprise Manager Cloud Control 13c Release 3 and The Oracle Management Agent 13c release 3 are used to monitor the Oracle database 12.1.0.1.0. Both The Failed Logins and Failed Logins (Historical) Metric are enabled and execute on a regular base. The…
HOW TO TURN OFF ORACLE 12C UNIFIED AUDITING AND CLEAN UP ALL UNIFIED AUDITING RECORDS
12c AUDITING Since the Oracle Database 12c Release 12.1.0.1.0 the Oracle invented a new approach of auditing – The Unified Auditing The Unified Auditing can operate in one of 2 available auditing modes – the Mixed Mode Auditing and the Pure Unified…
HOW TO FIND A QUERY THAT IS EXECUTED BY A DATABASE METRIC IN A TARGET DATABASE
PURPOSE This testcase is made up of number of steps helping out to find out the exact query that is executed by a database metric. The example is based on the Failed Logins (Historical) metric that is regulary executed by the Oracle Enterprise Manager…
Categories
- Linux (8)
- Iptables (2)
- Memory Usage (1)
- Postfix (1)
- Yum (4)
- Networker (2)
- Oracle (65)
- Account (2)
- AWR (2)
- Database Errors (6)
- Database Performance (9)
- Datapump (4)
- Enterprise Manager (24)
- Enterprise Manager Metrics (4)
- Environments (4)
- High CPU Usage (2)
- High IO Load (4)
- Initialization Parameters (5)
- job_queue_processes (1)
- Processes (3)
- NLS (1)
- Objects (1)
- Objects DDL (2)
- OJVM/PSU/DRU Patches (1)
- PL/SQL (5)
- UTL_FILE (4)
- Prerequisites (1)
- Privileges (1)
- Profile (1)
- Queries (2)
- Redologs (1)
- Resource Limits (1)
- Segment Shrink (1)
- Session (6)
- Spfile (3)
- SQL PLUS (1)
- Spooling (1)
- Standard Queries (7)
- Statistics (2)
- Tablespaces (1)
- Temporary Tablespace (2)