Purge_stats – a worked example of improving performance and reducing storage

I have written a number of blog entries around managing the SYSAUX tablespace and more specifically the stats tables that are in there.




This is another one around the same theme. What I offer in this one is some new scripts to identify what is working and what is not, along with a worked example with real numbers and timings.

Firstly let’s define the problem. This (more...)

Manage a PSU into a CDB having several PDBS

I thought it would be a good idea to show how to apply PSU into the CDB and PDB that came with 12c. I start off with a quick reminder about how 11g worked and then move into examples of 12c

11G reminder

get the latest version of opatch

check for conflicts

opatch prereq  CheckConflictAgainstOHWithDetail -ph ./

start the downtime

Stop all the databases in the home (one node at a atime for RAC)

apply (more...)

Identifying database link usage

As part of ongoing security reviews I wanted to determine if all database links on production systems were in use. That is not very easy to do and this article is a listing of some of the options I have considered to get that information and how it is now possible from 11GR2 onwards.

The first option was to look and see if auditing can be used. The manual states “You can audit statements that (more...)

Using SYSBACKUP in 12c with a media manager layer

I think most large sites who have multiple support teams are aware of how the phrase “Segregation of Duties” is impacting the DBA world. The basic principle, that one user should not be able to, for instance, add a user, grant it privileges, let the user run scripts and then drop the user and remove all log files is a sound one and cannot be argued with.

With the release of 12c Oracle e added (more...)



We have a number of updates to partitioned tables that are run from within pl/sql blocks which have either an execute immediate ‘alter session enable parallel dml’ or execute immediate ‘alter session force parallel dml’ in the same pl/sql block. It appears that the alter session is not having any effect as we are ending up with non-parallel plans. When the same queries are run outside pl/sql either in sqlplus or sqldeveloper sessions the (more...)

Migrating tablespaces across endian platforms

T 4 HP-UX IA (64-bit)   Big 13 Linux x86 64-bit   Little[/code]


Set the tablespace EXAMPLE read only


 alter tablespace example read only;

Tablespace altered.

select file_name from dba_data_files;



PL/SQL procedure successfully completed.

SELECT * FROM transport_set_violations;

no rows selected

Export the data using the keywords transport_tablespaces


expdp directory=data_pump_dir transport_tablespaces=example dumpfile=hpux.dmp logfile=hpux.log Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": /******** AS SYSDBA

Processing  (more...)

Creating standby database inc DG Broker and 12c changes

I thought I would refresh my knowledge of creating a standby database and at the same time include some DataGuard Broker configuration which also throws in some changes that came along with 12c


Database Name QUICKIE host server 1 ASM disk

Database Name STAN host server 2 ASM disk

Create a standby database STAN using ACTIVE DUPLICATE from the source database QUICKIE


(ADDRESS = (PROTOCOL = TCP)(HOST = server1)(PORT  (more...)

Startup PDB databases automatically when a container is started – good idea?

I posed a note on the Oracle-L Mailing list around pluggable database and why they were not opened automatically by default when the container database was opened. The post is below

I am trying to get my head around the thing about how pluggable databases react after the container database is restarted.

Pre it was necessary to put a startup trigger in to run a ‘alter pluggable database all open;’ command (more...)

Issues around recreating a standby database in 12c

When you create a database in 12C it now creates a resource in HAS/CRS , which isn’t a problem

However, when you come to recreate a standby database, probably because it has got such a big lag that it is quicker to recreate than recover the log files, then you will see the following error message :-


RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command  (more...)

Large audit trail table causes high db activity – especially when using OEM

On various databases, apparently unrelated we have noticed high activity that seems to be associated with the query below. The quieter the database the more the query stands out.


SELECT TO_CHAR(current_timestamp AT TIME ZONE 'GMT', 'YYYY-MM-DD HH24:MI:SS TZD') AS curr_timestamp, COUNT(username) AS failed_count, TO_CHAR(MIN(timestamp), 'yyyy-mm-dd hh24:mi:ss') AS first_occur_time, TO_CHAR(MAX(timestamp), 'yyyy-mm-dd hh24:mi:ss') AS last_occur_time
FROM sys.dba_audit_session
WHERE returncode != 0 AND timestamp >= current_timestamp - TO_DSINTERVAL('0 0:30:00')


The detail of the audit table is that it contains 73M records (more...)