Restrictions on the append hint

I wanted to add an append hint to some code but realised that there are known restrictions when either triggers or referential integrity are involved.

That does make sense as potentially rows would be inserted that either did not fire a trigger or broke RI constraints.

I decided to produce a test case to prove that the restrictions did work and were still in place.

Test append with trigger created

set lines 240
set pages  (more...)

A blog related beer that I am currently enjoying

Currently drinking a bottle of this


I didn’t actually notice the name until I opened it tonight.

Why do you need to resetlogs after a cold backup restore

I posted a routine on how to take a cold backup locally to disk and then restore it back in 2010. Last week I was asked in a comment ‘why did you have to open the database using resetlogs?’  A very good question I thought so I proceeded to backup and recover just as the blog showed and I now know why.

Because Oracle will not let you do otherwise

Let me run through (more...) enhancement – large trace file produced when cursor becomes obsolete

A minor change came in with which is causing large trace files to be produced under certain conditions.

The traces are produced as a result of an enhancement introduced in an unpublished bug.

The aim of the bug is to improve cursor sharing diagnostics by dumping information about an obsolete parent cursor and it’s child cursors after the parent cursor has been obsoleted N times.
A parent cursor will be marked (more...)

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...)