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...)
Currently drinking a bottle of this
I didn’t actually notice the name until I opened it tonight.
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...)
A minor change came in with 184.108.40.206 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...)
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...)
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
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)
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...)
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...)
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;
select file_name from dba_data_files;
EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK(ts_list=&amp;amp;amp;amp;amp;gt;'EXAMPLE', incl_constraints =&amp;amp;amp;amp;amp;gt;TRUE);
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 &amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;quot;SYS&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;quot;.&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;quot;SYS_EXPORT_TRANSPORTABLE_01&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;quot;: /******** AS SYSDBA
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK