CLEAN UP DATABASE FROM THE SESSHIS USER AND HIS OBJECTS
NEEDS How can I remove all objects that were created in the ALTERNATIVE TO V$ACTIVE_SESSION_HISTORY VIEW IN STANDARD EDITION and/or the DUMPING SQL EXECUTION HISTORY INFORMATION SNAPSHOTS IN STANDARD EDITION RELATED ALTERNATIVE TO V$ACTIVE_SESSION_HISTORY VIEW IN STANDARD EDITION DUMPING SQL EXECUTION HISTORY INFORMATION SNAPSHOTS IN…
DUMPING SQL EXECUTION HISTORY INFORMATION SNAPSHOTS IN STANDARD EDITION
NEEDS How can I get historical SQL execution information in a Standard Edition database? GOAL The goal of this note is to invent a method for storing and getting the historical information about the specific SQL ever executed in past in an Oracle Database. As…
ALTERNATIVE TO V$ACTIVE_SESSION_HISTORY VIEW IN STANDARD EDITION
NEEDS How can I get historical session information in a Standard Edition database? GOAL The goal of this note is to invent a method for storing and getting the historical information about the sessions ever connected in past in an Oracle Database. As a database…
ENTERPRISE MANAGER QUERIES ARE IN TOP AS ELAPSED TIME QUERIES CAUSE OF AUDIT RECORDS
SYMPTOMS The Enterprise Manager Queries are the first CPU/TIME consuming queries in the order by Elapsed time section of a statspack report. For instance ————————————————————- ^LSQL ordered by Elapsed time for DB: MERIDB2 Instance: meridb2 Snaps: 27081 -27 -> Total DB Time (s): 6,849 ->…
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 executing the PSU patch post-step script on one of the test databases, 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…
HOW TO AUTO-PURGE STATSPACK SNAPSHOTS
GOAL This note shows the way to purge automatically and regularly STATSPACK data that are older than a specified number of days. SOLUTION To purge unnecessary STATSPACK data use the STATSPACK.PURGE procedure. One of the procedure parameters accepts the number of days and removes all…
Categories
- Linux (8)
- Iptables (2)
- Memory Usage (1)
- Postfix (1)
- Yum (4)
- Networker (2)
- Oracle (69)
- Account (2)
- AWR (4)
- Database Errors (7)
- Database Performance (10)
- Datapump (4)
- Enterprise Manager (25)
- Enterprise Manager Metrics (4)
- Environments (4)
- High CPU Usage (3)
- High IO Load (4)
- Historical Session Information (3)
- 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)