Now that you have got your audit table somewhere a little more sensible (i.e. not in the SYSTEM tablespace), there’s probably a policy about how many audit records should be kept.
Thoughtfully, the DBMS_AUDIT_MGMT package provides some of what you need to keep the audit records in check. However, a little more thought by Oracle would have helped. Lets see what I mean.
First we need to initialise for audit control. You can check (more...)
One of the oldest problems with the Auditing capabilities within Oracle is that the SYS.AUD$ table resides in the SYSTEM tablespace. Unless you are rigorous in ensuring that your audit records are routinely pruned to keep the table manageable, it can single-handedly make the SYSTEM tablespace enormous.
Historically, we used to move the table and its associated objects to a new tablespace ourselves. In Oracle 7 is was a drop and re-create. Later we performed (more...)
These are a bit of a pain as their location isn’t intuitive. So to remind me where they are:
Login to the server(s) as the grid owner and check the scan listener status. This will show you the location of the listener log. cd to just below the diag directory and you’re off!:
server-name:/u01/grid>ps -ef | grep SCAN
grid 8542 8282 0 10:20 pts/0 00:00:00 grep SCAN
grid 9349 1 0 Mar07 ? 00:07:33 (more...)
When you end up spending a far great a percentage of your day than seems sensible killing off Java connections that Developers have carelessly left lying around, locking objects all over the place, you need a solution to get them to go away. The solution is to let them do it themselves!
I’m not advocating granting ALTER SYSTEM to Developers! That way madness lies, or certainly some unintended consequences. I’m all for Dev’s having a lot (more...)
When giving a presentation last year about how much a DBA should do to get to the bottom of a problem; in a discussion between Martin Widlake, myself, and the audience we amusingly concluded that we probably shouldn’t be doing things quite right first time.
What do I mean by this? Well, we should be doing things right-enough. We don’t want anything to fail. We want the project you are working on to succeed. BUT, (more...)
When installing Oracle Grid Infrastructure 11.2 (and all other releases), you need to make sure that you have all of the server setting correct and to standard before you do the install. One that bit me recently was the timezone setting. The Red Hat 6.4 server(s) in question has the correct file in /etc/localtime (copied from /usr/share/zoneinfo/whatever). If I type in date, I get the reply in the correct timezone (more...)
So, you’re creating (or rebuilding) an index ONLINE on a busy system. Your session dies, or it becomes necessary to kill the command, you may find that Oracle does not (always manage to) automatically clean up after itself.
CREATE INDEX my_ind ON my_table (mycol ASC)
LOCAL LOGGING COMPRESS 1 ONLINE;
ORA-01013: user requested cancel of current operation
select * from user_indexes where index_name = 'my_ind';
OMG! WTF! TLA’s! The index (more...)
Here’s a good tip In SQL*Plus, that I have learned only recently; You’re in the middle of typing in some SQL or a PL/SQL Anonymous block, and can’t remember the column name, or other table information? At the start of the next line type “#desc <table_name>” and the table is (more...)
In Oracle 11G, Oracle introduced SQL Plan Management (SPM). It is excellent (I love it to bits). It allows you to create Baselines against SQL which lock-down the SQL execution plan. No more plan flips. More consistency. Perfect.
Whenever some Baselined SQL is ran, Oracle still parses it and compares (more...)