Friday 27 July 2012

Stopping Auditing of Objects

Assume you have set-up auditing of tables in the following 3 schemas:
FINANCE1, DATAUSER2 and REPORTUSER9
The purpose of the auditing is to check whether any of the schemas' tables is no longer being used i.e. not queried or changed in any way.

Any unused tables in these schemas will be dropped as part of housekeeping.

You want to stop AUD$ becoming too large, and so want to stop auditing of a table soon after any rows for it are written to AUD$.

You have set-up auditing of the 3 schemas' table by executing the SQL generated by the following dynamic SQL:
select distinct 'AUDIT SELECT, INSERT, UPDATE, DELETE ON "'||owner||'"."'||table_name||'" BY SESSION;'
from dba_tables
where owner in ('FINANCE1','DATAUSER2','REPORTUSER9');
 
If the following dynamic SQL is executed it will generate a NOAUDIT SQL statement for each table in the 3 schemas that is being audited, and which has has at least 1 row in AUD$:
SELECT  DISTINCT 'NOAUDIT INSERT, UPDATE, DELETE, SELECT ON '||OBJ$CREATOR||'.'||OBJ$NAME||';'
FROM SYSTEM.AUD$
WHERE TRUNC(NTIMESTAMP#) = TRUNC(SYSDATE)
AND OBJ$CREATOR||'.'||OBJ$NAME IN (SELECT OWNER||'.'||OBJECT_NAME FROM DBA_OBJ_AUDIT_OPTS WHERE OWNER IN ('FINANCE1','DATAUSER2','REPORTUSER9') AND OBJECT_TYPE='TABLE');


Note:
If your
AUD$ is large, then you may want to index AUD$

Thursday 26 July 2012

What Is Being Audited?

If you want to know which objects are currently being audited, then run this: 
SELECT * FROM DBA_OBJ_AUDIT_OPTS ORDER BY OWNER,OBJECT_NAME;

Wednesday 25 July 2012

INS-32025

The problem:
You can't install the 11gR2 Client on Windows, and are seeing an error like this:

Preparing to launch Oracle Universal Installer from C:\DOCUME~1\orauser\LOCALS~1\Temp\1\OraInstall2012-07-24_12-47-55PM.
Please wait ... [FATAL] [INS-32025] The chosen installation conflicts with software already installed in the given Oracle home.
CAUSE: The chosen installation conflicted with software already installed in the given Oracle home.
ACTION: Install into a different Oracle home.

Please press Enter to exit...

Basically, the installer thinks there's already Oracle software installed in the Oracle Home.
Assuming that you used - correctly - the deinstall tool, then be aware that the tool does not do a 100% job. It can fail to remove previous 11g entries in the inventory.xml file, located in (in my case) c:\Program Files\Oracle\Inventory\ContentsXML

So, manually remove the line pertaining to the Oracle Home that no longer exists, having backed-up the file first, just in case.

Monday 23 July 2012

Indexes and the Recyclebin

I found out recently that if a table is dropped (without PURGE) and the table has indexes, then, if the table is "undropped", using:

FLASHBACK TABLE my_table TO BEFORE DROP;

the table's indexes are also restored but with their recyclebin names. You can rename such indexes later to their original names.

This is still the behaviour in 11.2.0.3.

So, keep a record of index names before performing any table drops, if you want to avoid problems finding out their original names.

Friday 20 July 2012

Indexing AUD$

AUD$ can become very large. It depends, of course, on how you set-up auditing in your database.

It can be a bit of a pain querying a monolithic AUD$, especially as it has no index.

So, what to do? Well, the only real option is to create an index on AUD$.

Caveat: Oracle does not support additional indexes on AUD$ (see MOS Note ID 1329731.1), but it's not a show-stopper.

A good approach to creating an index on AUD$ is to use a Function-Based Index (FBI), on the trunc of NTIMESTAMP#. This means each index leaf points to just a day's worth of AUD$ rows.

The SQL:
CREATE INDEX AUD_NTIMESTAMP
ON AUD$ (TRUNC(NTIMESTAMP#))
ONLINE
TABLESPACE MY_NEW_TABLESPACE;

To use the new index:

Example 1:
SELECT *
FROM SYSTEM.AUD$
WHERE TRUNC(NTIMESTAMP#) = TRUNC(SYSDATE);

Example 2:
SELECT *
FROM SYSTEM.AUD$
WHERE TRUNC(NTIMESTAMP#) =
TO_DATE('01-JUL-2011 00:00:00','DD-MON-YYYY HH24:MI:SS');

I found that the FBI index was about 4% of the size of AUD$.

Tuesday 10 July 2012

Query Results in Comma-Separated String

Another hidden gem.

Ever wanted to get the results of a query in a single string, with values separated by commas? e.g. you want all tables for a user to be exported using Data Pump.

Here's an easy way to get that list of tables in Data Pump-friendly format:

select wm_concat('SCOTT.'||table_name)
from dba_tables 
where owner = 'SCOTT';

Results:
SCOTT.TABLE_01,SCOTT.TABLE_02,SCOTT.TABLE_03, ...

wm_concat is an Oracle-supplied function and may change at some point in the future, etc., etc.