SQL - Joins

Equijoins or Inner Join

SQL >SELECT Table_A.letter, Table_B.letter
2    FROM Table_A, Table_B
3   WHERE Table_A.letter = Table_B.letter;

LETTER     LETTER
---------- ----------
A          A

SQL >SELECT Table_A.letter, Table_B.letter
2    FROM Table_A INNER JOIN Table_B
3      ON Table_A.letter = Table_B.letter;

LETTER     LETTER
---------- ----------
A          A

Self Joins

SQL >SELECT A1.letter, A2.letter
2    FROM Table_A A1, Table_A A2
3   WHERE A1.letter = A2.letter;

LETTER     LETTER
---------- ----------
A          A
B          B

SQL >SELECT A1.letter, A2.letter
2    FROM Table_A A1 INNER JOIN Table_A A2
3      ON A1.letter = A2.letter;

LETTER     LETTER
---------- ----------
A          A
B          B

Left Outer Joins

SQL >SELECT Table_A.letter, Table_B.letter
2    FROM Table_A, Table_B
3   WHERE Table_A.letter = Table_B.letter(+);

LETTER     LETTER
---------- ----------
A          A
B

SQL >SELECT Table_A.letter, Table_B.letter
2    FROM Table_A LEFT OUTER JOIN Table_B
3      ON Table_A.letter = Table_B.letter;

LETTER     LETTER
---------- ----------
A          A
B

Right Outer Joins

SQL >SELECT Table_A.letter, Table_B.letter
2    FROM Table_A, Table_B
3   WHERE Table_A.letter(+) = Table_B.letter;

LETTER     LETTER
---------- ----------
A          A
C

SQL >SELECT Table_A.letter, Table_B.letter

  2  FROM Table_A RIGHT OUTER JOIN Table_B
3  ON Table_A.letter = Table_B.letter;

LETTER     LETTER
---------- ----------
A          A
C

Full Outer Joins

SQL >SELECT Table_A.letter, Table_B.letter
2    FROM Table_A, Table_B
3   WHERE Table_A.letter = Table_B.letter(+)
4   UNION
5  SELECT Table_A.letter, Table_B.letter
6    FROM Table_A, Table_B
7   WHERE Table_A.letter(+) = Table_B.letter;

LETTER     LETTER
---------- ----------
A          A
B
C

SQL >SELECT Table_A.letter, Table_B.letter
2    FROM Table_A FULL OUTER JOIN Table_B
3      ON Table_A.letter = Table_B.letter;

LETTER     LETTER
---------- ----------
A          A
B
C

Cartesian Products

SQL >SELECT Table_A.letter, Table_B.letter
2    FROM Table_A, Table_B;

LETTER     LETTER
---------- ----------
A          A
A          C
B          A
B          C

SQL >SELECT Table_A.letter, Table_B.letter
2    FROM Table_A CROSS JOIN Table_B;

LETTER     LETTER
---------- ----------
A          A
A          C
B          A
B          C


Reference Document dbasupport.com 

Application Patch Info

APPLSYS
ad_bugs
ad_applied_patches

APPS
synonyms

SQL

  select * from dba_objects where object_name = 'AD_BUGS';

Voting Disk cheat Sheet

Voting disk

dd if=voting_disk_name of=backup_file_name
dd if=backup_file_name of=voting_disk_name


crsctl query css votedisk - lists the voting disks used by CSS
crsctl add css votedisk - adds a new voting disk
crsctl delete css votedisk - removes a voting disk

OCR Cheatsheet

Restore OCR from Backup

Connect as root and from the OCR backup directory choose the last backup before the problem started

[root@vmractest1]# cd /vmasmtest/BACKUP/OCR/
[root@vmractest1]# ls -ltr
total 458328
-rw-r--r-- 1 oracle dba 156279808 Nov 21 16:48 OCR_21_nov_06_backup1
-rw-r--r-- 1 oracle dba 156279808 May 3 16:39 OCR_03_may_07
-rw-r--r-- 1 oracle dba 156279808 May 16 16:06 OCR_16_may_07_small
Restore the last backup using the dd command
[root@vmractest1]# dd if=/vmasmtest/BACKUP/OCR/OCR_03_may_07 of=/dev/raw/ocr.dbf
305234+0 records in
305234+0 records out

OCR.LOC
used by ASM to start CSS daemon

/var/opt/oracle on Sun Solaris
/etc/oracle on RHEL

$ cat ocr.loc
ocrconfig_loc=/dev/asmdisk/ocr1 <-specifies the location of the Oracle Cluster Registry (OCR) used by the CSS daemon
ocrmirrorconfig_loc=/dev/asmdisk/ocr2
local_only=FALSE

OCR

$ ocrconfig –option
$ ocrcheck
$ ocrconfig -showbackup
$ ocrconfig –export myfile
$ ocrdump -backupfile my-file
$ ocrconfig –restore my_file
$ cluvfy comp ocr –n all -verbose #check OCR integrity
$ ocrconfig -replace ocr destination_file or disk
$ ocrconfig -replace ocrmirror destination_file or disk
# ../bin/crs stop

ASM Cheatsheet

# Start/Stop ASM

srvctl stop asm -n linux1
srvctl start asm -n linux1

# Status of an ASM instance

srvctl status asm -n linux1
ASM instance +ASM1 is running on node linux1.

# Display the configuration for the ASM instance(s)

srvctl config asm -n linux1
+ASM1 /u01/app/oracle/product/10.2.0/db_1

# asmcmd commands

asmcmd -p
asmcmd lsdg
asmcmd ls ORADATA001

# Check asm status

crsstat |grep asm

# ASM SQL's
prompt asm diskgroups
prompt
SELECT group_number, name, total_mb,state, type FROM V$asm_diskgroup
/

prompt All ASM disk that belong to the 'ORCL_DATA1' disk group
prompt
SELECT path FROM v$asm_disk
WHERE group_number IN (select group_number from v$asm_diskgroup
where name = upper('&diskgroup');

# Create on ASM the root directory of the database

$ asmcmd
ASMCMD> cd datadg
ASMCMD> mkdir racdbtst
ASMCMD> ls
racdbtst/

# Directories to be created for Duplicating Database

oracle@lnx521> . oraenv
ORACLE_SID = [xxxx] ? +ASM1
oracle@lnx521> asmcmd
ASMCMD> cd oradata001
ASMCMD> mkdir gwyd
ASMCMD> cd gwyd
ASMCMD> mkdir CONTROLFILE DATAFILE ONLINELOG PARAMETERFILE TEMPFILE
ASMCMD>

# Create pfile from spfile

create pfile='/vmasmtest/BACKUP/ASM-SPFile' from spfile;