datapump filter data

To filter out certain data from exported tables you can use the QUERY parameter of datapump.

Which is basically a WHERE clause of your query. So you can filter out certain rows by using table columns in your filter. But if you need to use a JOIN and a SUBQUERY in your WHERE clause, then it won't work. Because datapump is setting an alias for your main table, so you cannot reference it in your WHERE clause by it's name. In 10.2.0.3 the alias for the table is KU$. So you need to use that when referencing your original table and then it will work just fine.

eg parameters might look like this:

include=TABLE:"LIKE 'table1'"
query="WHERE exists (select * from table2 where KU$.fk=table2.pk and table2.foo='xyz')"

fix backspace in corn shell

export TERM=vt220
stty erase ^?

How to cleanup the FGA log table?

SQL> truncate table fga_log$;

SQL> delete from fga_log$ where timestamp# < sysdate-14;

How to Determine The Lock Type and Mode from an Enqueue Wait

Doc ID: Note:413934.1

WAIT #1: nam='enqueue' ela= 3007866 p1=1414332419 p2=30152 p3=0

The P1 value is 1414332419 which in hex is 544D0003, the first four digits are: 54 and 4D.
54 hex = "T" in ASCII
4D hex = "M" in ASCII
So, this is a "TM" lock type.

The last digit is the lock mode that is being requested. The modes are:

• 0 - none
• 1 - null (NULL)
• 2 - row-S (SS)
• 3 - row-X (SX)
• 4 - share (S)
• 5 - S/Row-X (SSX)
• 6 - exclusive (X)

library cache pins/locks

Note:169139.1 - on investigating library cache pin/locks situations

Oracle Clusterware installation fails on Linux

when running root.sh, the following error is returned:

Failed to upgrade Oracle Cluster Registry configuration

Solution:

Note:465001.1
If using any form of multipathing (EMC PowerPath, MPIO, device-mapper-multipath, etc.) to access Clusterware devices, be sure to obtain and apply Oracle 10.2.0 CRS patch for Bug 4679769 when installing the Oracle Clusterware (Step 5).


After applying the patch, installation was successful.

configure ssh for rac installation

1. create the keys

/usr/bin/ssh-keygen -t dsa
/usr/bin/ssh-keygen -t rsa

2. copy the public keys into authorized_keys

3. run
exec /usr/bin/ssh-agent $SHELL
/usr/bin/ssh-add

CLUVFY

to run the prerequisites check for oracle clusterware, download the latest cluvfy.

1. unzip into /home/oracle/cvu
2. setup the environment

export CV_HOME=/home/oracle/cvu
export CV_JDKHOME=/home/oracle/cvu/jdk/jre

3. run

./cluvfy stage -pre crsinst -n hostname -orainv dba -r 10gR2

(don't forget to specify the -r version)

TAR + gzip + remove files

tar --remove-files -czf datafiles.tar.gz *

LIST BACKUP

to list backups taken at a certain point in time: