Dropping all tables of a given MySQL-Database is easy. We fetch all tablenames from the information_schema, concatenate them to a a series of “DROP TABLE”-Commands and pipe them to mysql for execution:

Developer reports problems with a Tomcat-Server. Something with established connection-pool-sessions which suddenly cease to work, but apparently have not disconnected. Blaming the database as the cause for that mess.

So, I checked the DBA_AUDIT_TRAIL for that servers connections. There were tons of normal “LOGON”- / “LOGOFF”-actions recorded, but also occasionally a bunch of “LOGOFF BY CLEANUP”-actions. “CLEANUP” to me smelled like PMON cleaned up some abandoned connections whose client-sides died away. To verify my assumption I checked the Oracle support docs at MOS (Doc ID 274697.1), which states:

(...)
"LOGOFF BY CLEANUP" Action Name in SYS.AUD$ Table
-------------------------------------------------
This audit entry is written by PMON when it clears a dead process/session once
the DBA enabled auditing on :
CREATE SESSION or
CONNECT or
SESSION
When a server process dies abruptly, it would not have got the opportunity to
put the audit record.
The PMON finds this dead process and starts cleaning up this dead process.
At this time, PMON writes this audit record indicating the process termination
was abnormal.
The known situations when we have a dead process is when the server process is
either killed at the OS level or within the database using the ALTER SYSTEM KILL
SESSION command.
When the server process cannot contact the client, the server process exits
gracefully.
This does not constitute a LOGOFF BY CLEANUP record, but a LOGOFF record.
(...)

This seemed to prove me wrong, as this note says that the problem lies on the DB-server-side. So I started off examining my otherwise smooth running DB-Server for anomalies, being the root of that evil. But in vain. “LOGOFF BY CLEANUP”-actions still happened leaving me without a clue.

Remembering my first assumption I set up a test-case for to check what action would be really recorded in the audit-trail when I simply kill my connected sqlplus-process at the client-side. According to the note above, this should end up in a “gracefully exited server process” with a plain “LOGOFF” record. But guess what…

After some testing I found the above MOS-Note to be wrong and misleading. According to my tests, the opposite of the note’s assertion is true.

As I presumed, the “LOGOFF BY CLEANUP” is recorded, when the client-side vanishes without a clean disconnect. It’s the client-process that “dies abruptly” – not the server’s one.
Killing the session via “ALTER SYSTEM KILL SESSION” or killing the server-process at OS-Level leaves no trace in the audit-log – contradicting the note above.

But if we try to adapt this for to get the DDL of a scheduler-job, we’ll get stuck:

select dbms_metadata.get_ddl('JOB','JOB01') from dual;
ORA-31604: invalid NAME parameter "NAME" for object type JOB in function SET_FILTER
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 116
ORA-06512: at "SYS.DBMS_METADATA_INT", line 4517
ORA-06512: at "SYS.DBMS_METADATA_INT", line 8252
ORA-06512: at "SYS.DBMS_METADATA", line 2752
ORA-06512: at "SYS.DBMS_METADATA", line 2639
ORA-06512: at "SYS.DBMS_METADATA", line 4220
ORA-06512: at line 1
31604. 00000 - "invalid %s parameter \"%s\" for object type %s in function %s"
*Cause: The specified parameter value is not valid for this object type.
*Action: Correct the parameter and try the call again.

The same for scheduler-programs or -schedules:

select dbms_metadata.get_ddl('SCHEDULE','EVERY_MONDAY_800PM') from dual;
ORA-31600: invalid input value SCHEDULE for parameter OBJECT_TYPE in function GET_DDL
ORA-06512: at "SYS.DBMS_METADATA", line 2576
ORA-06512: at "SYS.DBMS_METADATA", line 2627
ORA-06512: at "SYS.DBMS_METADATA", line 4220
ORA-06512: at line 1
31600. 00000 - "invalid input value %s for parameter %s in function %s"
*Cause: A NULL or invalid value was supplied for the parameter.
*Action: Correct the input value and try the call again.

The trick is to use “PROCOBJ” (stands for ‘procedural object’) as object-type for all scheduler-objects:

As per MOS-Notes this is “because by using PROCOBJ the package [DBMS_METADATA] is using similar functionality as which datapump export would use and as known SYS objects are marked as non-exportable” (Doc ID 567504.1).
This note also states, that the lack of exporting Scheduler-DDL by using “JOB” as object-type is filed as a bug at Oracle. So there is hope, that one day we will have a more intuitive way for extracting Scheduler-DDL.

The Oracle-Scheduler is a nice alternative to cronjobs or alike – and it is not as complicated as it may seem at a first glance.

Well, with the package DBMS_SCHEDULER it is possible to define very granular scheduler-objects. There are “programs” that hold the definition for to be executed things, or “schedules” that hold the definition for certain schedule-types. And all of these can be combined to “jobs” consisting of “programs” and “schedules”. And even job-chains are possible, that respond to certain events and start the next job-chain-job dependent on an event.

But if we just want a certain procedure to be executed at a regular intervall it is quite easy. We just need the DBMS_SCHEDULER.CREATE_JOB:

To see what jobs are created, we use the views USER_SCHEDULER_JOBS or DBA_SCHEDULER_JOBS. There we can see what jobs are enabled, what they shall do or for what time the next run is scheduled.

Information about run jobs can be obtained from the views USER_SCHEDULER_JOB_LOG and USER_SCHEDULER_JOB_RUN_DETAILS.

The details of logging and the retention-time of the log-data are controlled by job_classes. Per default our job is set to the “DEFAULT_JOB_CLASS”. Information about all job_classes can be found in DBA_SCHEDULER_JOB_CLASSES.

Per default an oracle-maintenance-job is run every nigth at 3 a.m. which purges obsolete log-data according to the JOB_CLASS-Definition. Notice that there are pre-defined job-classes with a log_history of 1 million days; so eventually a regular (manual) check and purge with DBMS_SCHEDULER.PURGE_LOG is needed.

The named table SYS.AUD$ is the target for all auditing-data. But actually I didn’t intentionally enabled any auditing.

It appeared that since Oracle Database 11g the Database Configuration Assistent (dbca) and Database Upgrade Assistent (dbua) automatically sets the parameter audit_trail=’DB’ to enable a base-auditing by default which goes to the system-tablespace. In the dbca’s UI is no single hint of that fact since 11g R2. And now it bit me.

This setting exists in all of my dbca-databases. But until now I was neither aware of it nor was there ever a problem. So how come the 2,5 GB system-tablespace filled up until the brim within 12 month on a system with moderate traffic?!
Examining the auditdata in dba_audit_trail showed up permanent LOGONs/LOGOFFs with connection-durations of just some 30 seconds until a few (less than five) minutes.

The reason seems to be a “misconfigured” connection-pool on the application-server that disconnects to soon instead of holding the sessions for 30 minutes or so to reuse them. And so I ended up with more than 10-million lines of audit-data with the size of more than 2GB and a 100% used system-tablespace.

For a quick fix I simply truncated the table SYS.AUD$.

truncate table AUD$;

On next occasion I maybe stop the auditing – or set up a maintenance-job for the AUD$.

I tended to not use the DBMS_AUDIT_MGMT for cleaning up the audit-trail. As my auditing is for information-purposes only and not for compliance, I do not archive the log-data. Using DBMS_AUDIT_MGMT requires to set an archiving-date prior to delete the records from SYS.AUD$. This would impose unnecessary load on my database, as it would mean updating all audit-records with the archiving-date and then deleting all that records – each time generating redo-information. Also I wanted a rolling window of 180 days of audit-trail in my database. So the easiest way was to simply set up a scheduler-job:

During ex-import-operations it is often the case, that related synonyms in other schemas become invalid, because of the temporary absence of the underlying objects.

Unlike other objects like procedures or functions invalid synonyms are not automatically “recompiled” on next use. So we have to recreate them one by one. To ease this task and recreate all of them in a batch we can use this piece of code, which dynamically recreates all invalid synonyms in the database: