Using Explain Plan

The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. A statement's execution plan is the sequence of operations Oracle performs to run the statement.

The row source tree is the core of the execution plan. It shows the following information:

• An ordering of the tables referenced by the statement
• An access method for each table mentioned in the statement
• A join method for tables affected by join operations in the statement
• Data operations like filter, sort, or aggregation

In addition to the row source tree, the plan table contains information about the following:

• Optimization, such as the cost and cardinality of each operation
• Partitioning, such as the set of accessed partitions
• Parallel execution, such as the distribution method of join inputs

Examining an explain plan lets you look for throw-away in cases such as the following:

• Full scans
• Unselective range scans
• Late predicate filters
• Wrong join order
• Late filter operations

The PLAN_TABLE is automatically created as a global temporary table to hold the output of an EXPLAIN PLAN statement for all users. PLAN_TABLE is the default sample output table into which the EXPLAIN PLAN statement inserts rows describing execution plans

Creating a PLAN_TABLE
CONNECT HR/your_password
@$ORACLE_HOME/rdbms/admin/utlxplan.sql

Table created.

Running EXPLAIN PLAN
To explain a SQL statement, use the EXPLAIN PLAN FOR clause immediately before the statement. For example:

EXPLAIN PLAN FOR
SELECT last_name FROM employees;

Displaying PLAN_TABLE Output

UTLXPLS.SQL
This script displays the plan table output for serial processing.
EXPLAIN PLAN Output

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 189 | 10 (10)|
| 1 | NESTED LOOPS | | 3 | 189 | 10 (10)|
| 2 | NESTED LOOPS | | 3 | 141 | 7 (15)|
|* 3 | TABLE ACCESS FULL | EMPLOYEES | 3 | 60 | 4 (25)|
| 4 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 513 | 2 (50)|
|* 5 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | |
| 6 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 27 | 432 | 2 (50)|
|* 7 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("E"."EMPLOYEE_ID"<103)
5 - access("E"."JOB_ID"="J"."JOB_ID")
7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")


UTLXPLP.SQL
This script displays the plan table output including parallel execution columns.

DBMS_XPLAN.DISPLAY procedure
This procedure accepts options for displaying the plan table output. You can specify:
A plan table name if you are using a table different than PLAN_TABLE
A statement Id if you have set a statement Id with the EXPLAIN PLAN
A format option that determines the level of detail: BASIC, SERIAL, and TYPICAL, ALL,

Some examples of the use of DBMS_XPLAN to display PLAN_TABLE output are:

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

SELECT PLAN_TABLE_OUTPUT
FROM TABLE(DBMS_XPLAN.DISPLAY('MY_PLAN_TABLE', 'st1','TYPICAL'));

sar/iostat/vmstat/netstat/top/sed/cron

Display the activity for the CPU (system activity report) -> sar
07:20:01 AM CPU %user %nice %system %iowait %idle
07:30:01 AM all 0.18 0.00 0.01 0.01 99.80
07:40:01 AM all 0.15 0.00 0.01 0.01 99.83
07:50:01 AM all 0.15 0.00 0.01 0.01 99.83
08:00:01 AM all 0.15 0.00 0.01 0.01 99.83
08:10:01 AM all 0.15 0.00 0.01 0.01 99.83
08:20:01 AM all 0.15 0.00 0.01 0.01 99.83
08:30:01 AM all 0.15 0.00 0.01 0.01 99.83
08:40:01 AM all 0.15 0.00 0.01 0.01 99.83
08:50:01 AM all 0.15 0.00 0.01 0.01 99.83
09:00:01 AM all 0.15 0.00 0.01 0.01 99.83
09:10:01 AM all 0.18 0.00 0.01 0.01 99.80
09:20:01 AM all 0.18 0.00 0.01 0.01 99.80
09:30:01 AM all 0.18 0.00 0.01 0.01 99.80
09:40:01 AM all 0.15 0.00 0.01 0.01 99.83
09:50:01 AM all 0.16 0.00 0.01 0.01 99.82
10:00:01 AM all 0.15 0.00 0.01 0.01 99.83
10:10:01 AM all 0.15 0.00 0.01 0.01 99.83
10:20:01 AM all 0.15 0.00 0.01 0.01 99.83
10:30:01 AM all 0.15 0.00 0.01 0.01 99.82
Average: all 0.17 0.00 0.01 0.02 99.81

vmstat --> memory usage
[oracle@X345 ~]$ vmstat
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
0 0 0 1737732 32576 212864 0 0 1 1 109 7 0 0 100 0

iostat --> input output disk usage
[oracle@X345 ~]$ iostat
Linux 2.6.9-67.ELsmp (X345.localdomain) 08/14/2009

avg-cpu: %user %nice %sys %iowait %idle
0.17 0.00 0.01 0.02 99.80

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 0.12 2.53 0.71 188760 53004
sda1 0.01 0.01 0.00 946 4
sda2 0.03 0.03 0.00 2431 16
sda3 0.01 0.03 0.00 2330 16
sda4 0.00 0.00 0.00 2 0
sda5 0.00 0.02 0.00 1682 16
sda6 0.15 2.41 0.71 180273 52952
sdb 0.28 3.58 4.63 267713 346320
sdb1 0.01 0.03 0.00 2432 16
sdb2 0.03 0.03 0.00 2431 16
sdb3 0.01 0.02 0.00 1368 16
sdb4 0.00 0.00 0.00 2 0
sdb5 0.65 3.48 4.63 260264 346272
dm-0 0.79 5.88 5.34 439098 399224
dm-1 0.00 0.00 0.00 360 0

netstat --> network statistics
[oracle@X345 ~]$ netstat
Active Internet connections (w/o servers)
Proto Recv-Q Send-Q Local Address Foreign Address State
tcp 0 132 X345.localdomain:ssh ::ffff:192.168.168.165:4700 ESTABLISHED


top -> cpu usage (glance in HP-UX, topas in IBM AIX)

PID USER PR NI %CPU TIME+ %MEM VIRT RES SHR S COMMAND
5863 root 15 0 1 6:48.32 0.4 19612 8760 2440 S X
8413 oracle 16 0 0 0:00.01 0.0 1956 1000 784 R top
1 root 16 0 0 0:00.65 0.0 1956 548 468 S init
2 root RT 0 0 0:00.01 0.0 0 0 0 S migration/0
3 root 34 19 0 0:00.00 0.0 0 0 0 S ksoftirqd/0
4 root RT 0 0 0:00.00 0.0 0 0 0 S migration/1
5 root 34 19 0 0:00.00 0.0 0 0 0 S ksoftirqd/1
6 root RT 0 0 0:00.01 0.0 0 0 0 S migration/2
7 root 34 19 0 0:00.00 0.0 0 0 0 S ksoftirqd/2
8 root RT 0 0 0:00.00 0.0 0 0 0 S migration/3
9 root 34 19 0 0:00.00 0.0 0 0 0 S ksoftirqd/3
10 root 5 -10 0 0:00.00 0.0 0 0 0 S events/0
11 root 5 -10 0 0:00.00 0.0 0 0 0 S events/1
12 root 5 -10 0 0:00.00 0.0 0 0 0 S events/2
13 root 5 -10 0 0:00.00 0.0 0 0 0 S events/3
14 root 7 -10 0 0:00.01 0.0 0 0 0 S khelper
15 root 15 -10 0 0:00.00 0.0 0 0 0 S kacpid


sed is stream editor.
Ex: sed s/day/night/ new
The substitute command changes all occurrences of the regular expression into a new value. A simple example is changing "day" in the "old" file to "night" in the "new" file.

sed 's///g' {g for global replacement}

Crontab
Cron table is used to schedule the jobs to run automatically at particular day and time. It consists of 5 fields.

* * * * * command or shell script to be executed
- - - - -
| | | | |
| | | | +----- day of week (0 - 6) (Sunday=0)
| | | +------- month (1 - 12)
| | +--------- day of month (1 - 31)
| +----------- hour (0 - 23)
+------------- min (0 - 59)

/usr/bin/crontab or /usr/sbin/crontab

crontab -e Edit your crontab file, or create one if it doesn't already exist.
crontab -l Display your crontab file.
crontab -r Remove your crontab file.
crontab -v Display the last time you edited your crontab file.

* * * * * => Execute every minute
0 * * * * => Execute every Hour
0 0 * * * => Execute every mid-night
0 0 0 * * => Execute every Month
0 0 0 0 * => Execute every Weekday

@reboot Run once, at startup. None
@yearly Run once a year 0 0 1 1 *
@annually (same as @yearly) 0 0 1 1 *
@monthly Run once a month 0 0 1 * *
@weekly Run once a week 0 0 * * 0
@daily Run once a day 0 0 * * *
@midnight (same as @daily) 0 0 * * *
@hourly Run once an hour 0 * * * *

Slash example: the following will run the script /home/user/test.pl every 5 minutes.

*/5 * * * * /home/user/test.pl

Run every 10 min and store the output in a log.
*/10 * * * * /bin/execute/this/script.sh 2>&1 >> /var/log/script_output.log

Ranges of numbers are allowed. Ranges are two numbers separated with a hyphen. The specified range is inclusive. For example, 8-11 for an "hours" entry specifies execution at hours 8, 9, 10 and 11.

Lists are allowed. A list is a set of numbers (or ranges) separated by commas. Examples: "1,2,5,9", "0-4,8-12".

Step values can be used in conjunction with ranges. Following a range with "/" specifies skips of the number's value through the range. For example, "0-23/2" can be used in the hours field to specify command execution every other hour (the alternative in the V7 standard is "0,2,4,6,8,10,12,14,16,18,20,22"). Steps are also permitted after an asterisk, so if you want to say "every two hours", just use "*/2".

Basic Unix Commands

list files and directories -> ls
long listing of files and directories -> ls -l
mark directories with a '/' -> ls -F
list hidden files -> ls -a
Number of files and directories -> ls | wc -l
Number of files excluding directories -> ls -l | grep ^[^d] | wc -l [OR] find ./ -type f | wc -l
make directory -> mkdir
change directory -> cd
change to parent directory -> cd ..
print working directory -> pwd
copy files -> cp
move files -> mv
remove a file -> rm filename
remove a directory -> rmdir
force to remove all files recursively from all sub-directories -> rm -rf *
display contents of file on console -> cat hello.txt
display less contents of file on console -> less hello.txt {hit space bar to see more text}
display top lines in a file -> head -10 hello.txt
display bottom lines in a file -> tail -10 hello.txt
search for a pattern in a file -> /searchword {hit n to display next}
search for specific words in a file -> grep science science.txt or grep 'vinay kumar' vinay.txt
redirect output to a file -> ls -l > out.txt
append output to a file -> ls -l >>out.txt
redirect input to a command -> sort <>out.txt
pipe the output of command 1 to input of command 2 -> command 1 | command 2
list users currently logged in -> who
concatenate two files into one -> cat file1 file2 > file 0
change permissions on a file -> chmod
give owner permission to a file -> chown
kill a process -> kill -9 pid
list current processes -> ps
list current active jobs -> jobs
suspend a background job -> stop
repeat last command -> r (in ksh shell)
to check your current quota -> quota -v
space left on file system -> df [-k] [-m] [-h]
space used on file system -> du [-k] [-m] [-h]
show history command list -> history
to increase size of history buffer -> set history = 100

create a tar file -> tar -cvf sample.tar /dev/oradata
extract a tar file -> tar -xvf sample.tar
list files in tar -> tar -tvf sample.tar
c Create
x Extract from the tape
t List files on the tape
v Verbose
f Next argument is the name of the archive
tar, compress and transfer -> tar -cvf - *.dbf | compress | ssh stederey "cd /u02/backup/TEST/ ;zcat | tar xvf -" &


gzip [ -acdfhlLnNrtvV19 ] [-S suffix] [ name . . . ]
Description
Compresses and expands data
Suffix .gz
Frequently Used Options
-c Write output on standard output
-d Uncompress
-h Help
-r Recursive
-v Verbose

gunzip [ -acfhlLnNrtvV ] [-S suffix] [ name ... ]
Description
Uncompresses files (Same as gzip –d )
Automatically detects input format
Frequently Used Options
-c Write output on standard output
-h Help
-r Recursive
-v Verbose

gzip myfile --> Compresses the file myfile, making it myfile.gz. Note. When doing this the original file will no longer exist on the drive.

gunzip -f myfile.gz --> Uncompress the file myfile.gz and if the uncompressed file(s) already exist force an overwrite. When doing this the file myfile.gz will no longer be on the drive.

zcat myfile --> Uncompress the file myfile.

unzip test.zip

compress
Compacts a file so that it is smaller. When compressing a file it will be replaced with a file with the extension .Z, while keeping all the same ownership modes.

Syntax

compress [-c] [-f] [-v] filenames

-c Write to the standard output; no files are changed and no .Z files are created. The behavior of zcat is identical to that of `uncompress -c'.

-f When compressing, force compression of file , even if it does not actually reduce the size of the file, or if the corresponding file .Z file already exists. If the -f option is not given, and the process is not running in the background, prompt to verify whether an existing file .Z file should be overwritten. When uncompressing, do not prompt for overwriting files. If the -f option is not given, and the process is not running in the background, prompt to verify whether an existing file should be overwritten. If the standard input is not a terminal and -f is not given, write a diagnostic message to standard error and exit with a status greater than 0.

-v Shows you how much the file shrank in size.

filenames The name of the file(s) that you wish to compress.

Ex:

compress -v test.exe - Would compress test.exe and rename that file to test.exe.Z.

uncompress
Uncompressed compressed files.

Syntax

uncompress [-c] [-f] [-v ] [file]

Ex:
uncompress sample.txt - would uncompress the file sample.txt.Z.

grep [-E| -F] [-c| -l| -q] [-insvx] pattern_list [file . . .]
Description
Searches the input files, selecting lines matching one or more patterns
Frequently Used Options
-i Case-insensitive search
-l Write file names only
-n Display line number

Examples
grep -i unix p1.c
grep -n UNIX *.c *.h
ps –ef | grep mary

wc [ -c|-m ] [ -lw ] [ file . . . ]
Description
Counts lines, words, and characters
Options
-c Count the number of bytes
-m Count the number of characters
-l Count the number of newline characters
-w Count the number of words
Examples
wc -l *.h *.c

move by word -> w
left -> h
down -> j
up -> k
right -> l
copy -> yy
paste -> p
delete -> x
find . -name 'oracle' -print
find the files with .ora extension in current directory and all sub directories -> find . -name *.ora
find within a specified directory -> find directoryPath -name *.ora -print
find . -name sam\*tet will search from the current directory down for sam*tet (that is, any filename that begins with sam and ends with tet)
find / -type f -mtime -7
will find any regular files (i.e., not directories or other special files) with the criteria "-type f", and only those modified seven or fewer days ago ("-mtime -7").
find / -mmin -10
to locate files modified less than 10 minutes ago
find . -mtime 0 # find files modified between now and 1 day ago
# (i.e., within the past 24 hours)
find . -mtime -1 # find files modified less than 1 day ago
# (i.e., within the past 24 hours, as before)
find . -mtime 1 # find files modified between 24 and 48 hours ago
find . -mtime +1 # find files modified more than 48 hours ago

find . -mmin +5 -mmin -10 # find files modified between
# 6 and 9 minutes ago

show user and its group -> id
which binary is used -> which sqlplus
go to previous directory path -> cd -

[oracle@vinay ~]$ set -o
allexport off
braceexpand on
emacs on
errexit off
errtrace off
functrace off
hashall on
histexpand on
history on
ignoreeof off
interactive-comments on
keyword off
monitor on
noclobber off


[oracle@vinay ~]$ nohup ls &
[1] 4628
[oracle@vinay ~]$ nohup: appending output to `nohup.out'
ls
afiedt.buf Desktop ed.hup nohup.out on.lst sqlnet.log
[1]+ Done nohup ls


$ jobs
[3] + Running first_one &
[2] - Stopped (SIGTSTP) second_one
[1] Stopped (SIGTTIN) third_one &

The reason for the plus and minus symbols on the jobs listing is that job numbers are reassigned when one
completes and another starts. In the previous example, if job number 2 finishes and you start another job, it is assignedjob number 2 and a plus sign because it is the most recent job.


[oracle@vinay ~]$ passwd
Changing password for user oracle.
Changing password for oracle
(current) UNIX password:

[oracle@vinay ~]$ id
uid=500(oracle) gid=500(oinstall) groups=500(oinstall),501(dba)

If command is successful, then exit status is 0 otherwise it would be not 0
[oracle@vinay /database]$ ls sysstat-5.0.5-19.el4.i386.rpm
sysstat-5.0.5-19.el4.i386.rpm
[oracle@vinay /database]$ echo $?
0
[oracle@vinay /database]$ ls sample.txt
ls: sample.txt: No such file or directory
[oracle@vinay /database]$ echo $?
1

Debugging the shell script
$ ksh -vx listener.sh LISTENER1

split
To split large files into smaller files in Unix, use the split command.
split [options] filename prefix

Replace filename with the name of the large file you wish to split. Replace prefix with the name you wish to give the small output files. You can exclude [options], or replace it with either of the following:
-l linenumber -b bytes

If you use the -l (a lowercase L) option, replace linenumber with the number of lines you'd like in each of the smaller files (the default is 1,000). If you use the -b option, replace bytes with the number of bytes you'd like in each of the smaller files.

split -l 500 myfile mytest

This will output six 500-line files: mytestaa, mytestab, mytestac, mytestad, mytestae, and mytestaf

test is a 160KB file: split -b 40k test mytest

This will output four 40KB files: mytestaa, mytestab, mytestac, and mytestad.

ls -lrt | grep "Dec" | grep 2008 | awk '{print $9}' --- this will list the log files created in the month of Dec, 2008. You can remove all of them at a time using:
rm `ls -lrt | grep "Dec" | grep 2008 | awk '{print $9}'`

Also, you can check which file is occupying more space using the below command:
du -sk /opt/oracle/*

Secure copy:
scp test.txt oracle@server1.com:/database/test
scp -p test.txt oracle@server1.com:/database/test -> Preserve modification times, access times, and modes from the original file.
scp -r testdir oracle@server1.com:/database/test -> recursively copy entire directory 'testdir'

Check memory
lsattr -El sys0 | grep realmem

fuser:
fuser is a UNIX command used to show which processes are using a specified file, file system, or socket
fuser

Disk space:
df -h | grep test | egrep 'uo|database'

Search for a word in file:
[oracle@vinay logs]$ grep -i parameter init10g.log
SQL> show parameter

To check the size of physical memory, execute:

grep MemTotal /proc/meminfo
MemTotal: 515700 kB

To check the size of swap space, execute:

grep SwapTotal /proc/meminfo
SwapTotal: 1004052 kB

AIX commands:

mpstat displays performance statistics for all logical processors in the system
ps -ef | grep TEST | wc -l will list sessions count that are using the instance TEST
k when used in escape mode will list backward command history
j when used in escape mode will list forward command history

Auditing Database Activity

Auditing involves monitoring and recording specific database activity. Oracle 10g supports four levels (statement auditing, privilege auditing, object auditing, fine-grained auditing) of auditing. Audit records can be stored in the database or in operating system files. You tell the database where to record audit trail records by setting initialization parameter audit_trail

audit_trail = {db | os | none | true | false | db_extended}

none or false will disable database auditing
os records all audit records to operating system audit trail. audit_file_dest specifies OS directory into which audit trail is written when audit_trail is set to 'os'
db or true records all audit records to database audit trail. All audit records are stored in SYS.AUD$ table
db_extended records all audit records to database audit trail. In addition, SQLBIND and SQLTEXT CLOB columns of SYS.AUD$ table

Managing Statement Auditing: Statement auditing involves monitoring and recording the execution of specific types of SQL statements.

Enabling Statement Auditing:
AUDIT table;
AUDIT table BY scott; [record audit entries for specific users]
AUDIT table BY SCOTT WHENEVER NOT SUCCESSFUL;

Identifying enabled Statement Auditing Options:
SELECT audit_option, failure, success, user_name
FROM dba_stmt_audit_opts;

Disabling Statement Auditing:
NOAUDIT session;
NOAUDIT table BY scott;

Managing Privilege Auditing: Privilege auditing involves monitoring and recording the execution of SQL statements that require a specific system privilege.

Enabling Privilege Auditing:
AUDIT create any table;
AUDIT create any table BY scott;
AUDIT delete any table BY scott BY ACCESS;

Identifying enabled Privilege Auditing Options:
SELECT privilege, user_name
FROM dba_priv_audit_opts
ORDER BY privilege, user_name;

Disabling Privilege Auditing:
NOAUDIT alter profile;
NOAUDIT delete any table BY scott;

Managing Object Auditing:
--audit SELECT statements on HR.EMPLOYEE_SALARY table
AUDIT select ON hr.employee_salary;

Identifying enabled Object Auditing Options:
SELECT owner, object_name, object_type
FROM dba_obj_audit_opts
WHERE owner = ‘SCOTT’
AND object_name = ‘EMPLOYEE_SALARY’;

Disabling Object Auditing:
NOAUDIT select ON hr.employee_salary WHENEVER NOT SUCCESSFUL;

Purging Audit Trail
Database audit records for statement, privilege, and object auditing are stored in the table SYS.AUD$. Depending on how extensive your auditing and retention policies are, you will need to periodically delete old audit records from this table. To purge audit records older than 90 days, execute the following as SYS user.

DELETE FROM sys.aud$ WHERE timestamp# < style="font-weight: bold; font-style: italic;">
Managing Fine Grained Auditing
FGA lets you monitor and record data access based on the content of the data. With FGA, you define an audit policy on a table and optionally a column. When the specified condition evaluates to TRUE, an audit record is created, and an optional event-handler program is called. You use PL/SQL package DBMS_FGA to configure and manage FGA.

Creating an FGA Policy
To create a new FGA policy, use packaged procedure DBMS_FGA.ADD_POLICY. This procedure has the following parameters:

* object_schema This is the owner of the object to be audited. The default is NULL, which tells database to use the current user
* object_name Name of the object to be monitored
* policy_name This is a unique name for the new policy.
* audit_condition This is a SQL expression that evaluates to a Boolean. When this condition evaluates to TRUE or NULL, an audit record can be created.
* handler_schema This is the owner of the event_handler procedure. Default is NULL which tells the database to use the current schema.
* handler_module This is the name of the event_handler procedure. If the event handler is a packaged procedure, the handler_module must reference both the package name and program using dot notation, like UT_MAIL.SEND_ATTACH_ROW
* enable Boolean that tells the database if this policy should be in effect. Default is TRUE
* statement_types This tells the database which types of statements to monitor. Valid values are a comma-delimited list of SELECT, INSERT, UPDATE, and DELETE. The default is SELECT
* audit_column_ops This parameter has only two values: DBMS_FGA.ALL_COLUMNS and DBMS_FGA.ANY_COLUMNS.

Ex: DBMS_FGA.ADD_POLICY(object_schema=>’HR’,
,object_name=>’EMPLOYEES’
,policy_name=>’COMPENSATION_AUD’
,audit_column=>’SALARY, COMMISSION_PCT’
,enable=>FALSE
,statement_types=>’SELECT’);

Enabling FGA Policy
Use the procedure DBMS_FGA.ENABLE_POLICY to enable an FGA policy. This procedure will not raise any exception if the procedure is already enabled. To enable the policy that is created in the above example

Ex: DBMS_FGA.ENABLE_POLICY(object_schema=>’HR’
,object_name=>’EMPLOYEES’
,policy_name=>’COMPENSATION_AUD’);

Disabling an FGA policy
DBMS_FGA.DISABLE_POLICY(object_schema=>’HR’
,object_name=>’EMPLOYEES’
,policy_name=>’COMPENSATION_AUD’);

Dropping an FGA policy
DBMS_FGA.DROP_POLICY(object_schema=>’HR’
,object_name=>’EMPLOYEES’
,policy_name=>’COMPENSATION_AUD’);

Identifying FGA Policies in the Database
Query the DBA_AUDIT_POLICIES data dictionary view to report on the FGA policies defined in your database.
Ex: SELECT policy_name, object_schema||’.’||object_name object_name, policy_column, enabled, audit_trail FROM dba_audit_policies;

Reporting on the FGA Audit Trail Entries
The DBA_FGA_AUDIT_TRAIL data dictionary view is used in reporting on the FGA audit entries that have been recorded in the database.

Ex: SELECT db_user, timestamp, userhost FROM dba_fga_audit_trail
WHERE policy_name=’COMPENSATION_AUD’

Dictionary Views
DBA_AUDIT_TRAIL displays all audit trail entries.

Related View
USER_AUDIT_TRAIL displays all audit trail entries related to the current user.

Using Regular Expressions in 10g

Oracle Database 10g introduces support for Regular Expressions, which is a method of describing both simple and complex patterns for searching and manipulating. You can use several predefined meta character symbols in the pattern matching.

Meta Characters:

* matches zero or more occurrences

+ matches one or more occurrence

? matches zero or one occurrence

. matches any character in the supported character set, except NULL

| Alternation operator for specifying alternative matches (a|b matches a or b)

^/$ Matches start of line/end of line

[] Bracket expression for a matching list matching any one of the expressions in the list

[...] match any character in the list

[^...] match any character not in the list

{m} matches exactly m occurrences

{m,} matches atleast m occurrences

{m,n} matches atleast m times but no more than n times

(...) Subexpression, treat expression as a unit

SQL> select * from emp where regexp_like(ename, '^A+');

EMPNO ENAME JOB MGR HIREDATE
---------- ---------- --------- ---------- -------------------------------
7499 ALLEN SALESMAN 7698 20-FEB-81 12.00.00.0000000 AM
7876 ADAMS CLERK 7788 23-MAY-87 12.00.00.0000000 AM



SQL> select * from emp where regexp_like(ename, 'A+');

EMPNO ENAME JOB MGR HIREDATE
---------- ---------- --------- ---------- ---------------------------------
7499 ALLEN SALESMAN 7698 20-FEB-81 12.00.00.0000000 AM
7521 WARD SALESMAN 7698 22-FEB-81 12.00.00.0000000 AM
7654 MARTIN SALESMAN 7698 28-SEP-81 12.00.00.0000000 AM
7698 BLAKE MANAGER 7839 01-MAY-81 12.00.00.0000000 AM
7782 CLARK MANAGER 7839 09-JUN-81 12.00.00.0000000 AM
7876 ADAMS CLERK 7788 23-MAY-87 12.00.00.0000000 AM
7900 JAMES CLERK 7698 03-DEC-81 12.00.00.0000000 AM


SQL> select * from emp where regexp_like(ename, 'AMS$');

EMPNO ENAME JOB MGR HIREDATE
---------- ---------- --------- ---------- ------------------------------
7876 ADAMS CLERK 7788 23-MAY-87 12.00.00.0000000 AM

Using ipcs to peek into kernel's storage mechanisms for IPC objects

The ipcs [-q] [-s] [-m] [-l] command gives you the kernel's storage mechanisms for IPC objects.
ipcs -q: Show only message queues
ipcs -s: Show only semaphores
ipcs -m: Show only shared memory
ipcs -l: Shows message queues, semaphores, and also shared memory

Commands for CPU/Memory/Process information on Linux/Unix servers

top displays all sorts of CPU/Memory/Process information
free -m will show you stats about RAM usage in MB
uptime will show you the load average for the past 1min, 5mins and 15mins

cat /proc/cpuinfo [OR] cat /proc/cpuinfo | more will give you general information about the CPU(s) and memory on server
vmstat will give you virtual memory statistics and vmstat n will give you a run down of memory use, disk operations and cpu usage every n seconds
i.e. vmstat 1 will get a run down of memory use, disk operations and cpu usage every 1 sec

Using WITH clause

WITH clause can be used only with the SELECT clause and can hold more than one query. The query name in the WITH clause is visible to other query blocks in the WITH clause as well as to the main query block.

Using WITH clause has following advantages:

• It enables users to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query.
• It can improve performance of a large query by storing the result of a query block having the WITH clause in the user's temporary tablespace

Example:
SQL> with emp_sal as(select sno, sum(salary) as tot_sal from sample group by sno),
avg_sal as (select sum(tot_sal)/count(*) as tot_avg from emp_sal)
select * from emp_sal where tot_sal > (select tot_avg from avg_sal)
order by sno;

SNO TOT_SAL
---------- ----------
1 23000
2 10000
4 10000

WHERE Vs HAVING clause in SQL statement

Listed below are some reasons on when to use WHERE or HAVING clause in SQL SELECT statement.

• WHERE clause is used to exclude rows before the grouping of data
• Aggregate functions cannot be used in the WHERE clause
• HAVING clause is used to exclude one or more aggregated results after grouping data
• HAVING clause conditions can have aggregate functions
• WHERE and HAVING clauses can be used together in a SQL statement

Setting up Cygwin X Server on client

Once you have installed Cygwin, you should be able to set up X Server for display purposes. Follow the steps below for setting it up.

1) Edit the batch file 'cygwin.bat' (Will be in the installed folder)

You will see the below line in it
bash --login -i

Add the following statement next to it (Note that the startxwin.sh should be there in C:\Cygwin\usr\X11R6\bin )

bash --login -i /usr/X11R6/bin/startxwin.sh

2) Once you are done editing, run the batch file as administrator:
You should be able to see the X windows popping up:

3) Once you see X window, issue xhost + command so that clients can access from any host. See the below image.

Issue xcalc or xclock command to test if you were able to see the GUI.


NOTE: If you get Reason: spawn:fork() failed. Then close the window, and run the batch file again. I often get this reason, and I simply run it twice or thrice to run successfully, doono why.

3) If you are using putty on the client, export DISPLAY environment variable in that session:
export DISPLAY = 192.168.2.1:0.0 (marked in red is IP address of client)

Problem: If you don't set up DISPLAY.
If you don't set up your DISPLAY variable, you will see the following error:
[oracle@vinay 10201]$ ./runInstaller
Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be redhat-3, SuSE-9, redhat-4, UnitedLinux-1.0, asianux-1 or asianux-2
Passed


All installer requirements met.

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2009-02-28_12-04-45AM. Please wait ...[oracle@vinay 10201]$ X connection to localhost:11.0 broken (explicit kill or server shutdown).

Solution: Set the DISPLAY variable
[oracle@vinay 10201]$ echo $DISPLAY
localhost:11.0
[oracle@vinay 10201]$ export DISPLAY=192.168.216.1:0.0
[oracle@vinay 10201]$ echo $DISPLAY
192.168.216.1:0.0
[oracle@vinay 10201]$ ls
doc install response runInstaller stage welcome.html
[oracle@vinay 10201]$ ./runInstaller
Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be redhat-3, SuSE-9, redhat-4, UnitedLinux-1.0, asianux-1 or asianux-2
Passed


All installer requirements met.

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2009-02-28_12-05-19AM. Please wait ...[oracle@vinay 10201]$

System Vs Object Privileges

Difference between system privilege and object privilege is that system privilege is the right to perform specific activities in a database whereas an object privilege is a right to perform activities on a specific object in the database.

Examples of System privileges: CREATE TABLE, CREATE INDEX, CREATE USER, CREATE VIEW...

Examples of Object privileges: SELECT, INSERT, UPDATE, DELETE, COMMENT...

All about using INDEXES

Indexes are used to improve the performance of data retrieval. They are automatically created when primary key or unique key is created. You can manually create it using CREATE INDEX statement and CREATE TABLE statement.

CREATE INDEX with CREATE TABLE
SQL> create table test
(tno number(3) primary key using index (create index tno_idx on test(tno)), tname varchar(20));

SQL> select index_name, index_type from user_indexes where table_name='TEST';

INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
TNO_IDX NORMAL

[OR]

SQL> create table test (tno number(3), tname varchar(20));

Table created.

SQL> create index tno_idx on test(tno);

Index created.

SQL> alter table test add primary key (tno)
using index tno_idx;

Table altered.

NOTE that index is automatically created when you specify a column as primary key.

SESSION_PRIVS Dictionary View

SESSION_PRIVS dictionary view contains current system privileges available in the user session.

SQL> select * from session_privs;

PRIVILEGE
---------------------------------------
ALTER SYSTEM
AUDIT SYSTEM
CREATE SESSION
ALTER SESSION
RESTRICTED SESSION
CREATE TABLESPACE
ALTER TABLESPACE
MANAGE TABLESPACE
DROP TABLESPACE
UNLIMITED TABLESPACE
CREATE USER

Displaying table metadata (data about data)

To find the names of the tables that you have created, use the view called cat or tab in the SELECT statement.

SQL> select * from cat;

TABLE_NAME TABLE_TYPE
------------------------------ -----------
SAMPLE TABLE
TEST TABLE
CHILD TABLE
SEQ_TEST SEQUENCE
SEQSAMPLE TABLE
BIN$U6KfB5ApT1O9xKAFd9PnsA==$0 TABLE
BIN$0Mf6cwpmTnOXVUmXDBLnPA==$0 TABLE
TESTA TABLE
BIN$9iac65ZYQvOMzxudpHYijw==$0 TABLE
MYTEST TABLE

21 rows selected.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
XYZ TABLE
TESTB TABLE
BIN$7E43RxQORwuc7tISq/SZVA==$0 TABLE
MYTAB TABLE
DIGITS TABLE
BIN$jwDk7ZM8TdO3TOBSmGrTeQ==$0 TABLE
MASTER TABLE

20 rows selected.



SQL> select interval '312' month,
interval '20-6' year to month,
interval '12:30:10.1234567' hour to second from dual;

INTERVAL'312'MONTH
-------------------------------------------------------------
INTERVAL'20-6'YEARTOMONTH
-------------------------------------------------------------
INTERVAL'12:30:10.1234567'HOURTOSECOND
-------------------------------------------------------------
+26-00
+20-06
+00 12:30:10.123457

Retrieve number of records in a table having column data as NULL

SQL statement that would retrieve the number of records having 'NAME' as NULL

Sample table that has NULL value for NAME column.
SQL> select * from mytab;

TNO NAME
---------- ---------------
1 vinay
2 suman
3 ravi
4 sai
5 sample
6
7

7 rows selected.

Incorrect Solutions:
SQL> select count(name) from mytab where name=NULL;

COUNT(NAME)
-----------
0

SQL> select count(name) from mytab where name IS NULL;

COUNT(NAME)
-----------
0

Correct Solution:
SQL> select count(nvl(name, 0)) from mytab where name is null;

COUNT(NVL(NAME,0))
------------------
2

Granting role,system, and object privileges

Role and system privileges can be granted together in a single GRANT statement.
Ex:
SQL> create role mgr;
SQL> grant mgr, create table to vinay;

System privileges and object privileges cannot be granted together in a single GRANT statement.
Ex:
SQL> create role mgr;
SQL> grant create table, select on scott.emp to mgr;

SQL> grant all on order, or_items to public;
Above statement raises error. Seperate GRANT statements are required for ORDER and OR_ITEMS tables.

Flashback Versions Query Walkthrough

SQL> create table digits (id number(2), description varchar(20));

Table created.

SQL> insert into digits values (1, 'ONE');

1 row created.

SQL> update digits set description = 'TWO' where id=1;

1 row updated.

SQL> insert into digits values (2, 'TWO');

1 row created.

SQL> commit;

Commit complete.

SQL> delete from digits;

2 rows deleted.

SQL> select description from digits versions between timestamp minvalue and maxvalue;

DESCRIPTION
--------------------
TWO
TWO

Using EXISTS and NOT EXISTS

EXISTS: Is a boolen values that ensures that the inner query does not continue when at least one match is found by the condition. Inner query does not specifically return a specific value, so a constant can be selected.

SQL> select empno, ename, mgr, deptno from emp e
where exists (select 'X' from emp where mgr=e.empno);

EMPNO ENAME MGR DEPTNO
---------- ---------- ---------- ----------
7566 JONES 7839 20
7698 BLAKE 7839 30
7782 CLARK 7839 10
7788 SCOTT 7566 20
7839 KING 10
7902 FORD 7566 20

6 rows selected.

SQL> select deptno, dname from dept d
where not exists (select 'X' from emp where deptno=d.deptno);

DEPTNO DNAME
---------- --------------
40 OPERATIONS

[OR]

NOT IN can be used as an alternative for NOT EXISTS operator.

SQL> select deptno, dname from dept d
where deptno not in (select deptno from emp);

DEPTNO DNAME
---------- --------------
40 OPERATIONS

DICTIONARY view

DICTIONARY is a view that contains the names of all the data dictionary views that the user can access.

SQL> select * from dictionary;

TABLE_NAME
------------------------------
COMMENTS
------------------------------------------------
DBA_ROLES
All Roles which exist in the database

DBA_PROFILES
Display all profiles and their limits

USER_RESOURCE_LIMITS
Display resource limit of the user

DELETE rows

To delete selected rows in a table:
SQL> delete testa where name1='test';

1 row deleted.

[OR]

SQL> delete from testa where name1='ravi';

1 row deleted.

To delete all the rows in the table:
SQL> delete master;

3 rows deleted.

SQL> rollback;

Rollback complete.

SQL> select * from master;

MNO MNAME
---------- ---------------
1 test1
2 test2
3 test3

[OR]

SQL> delete from master;

3 rows deleted.

BETWEEN...AND

SQL> select * from mytab where tno between 3 and 5;

TNO NAME TEST
---------- --------------- ----------
3 ravi 1
4 sai 1
5 sample 1


SQL> select ename,job from emp where empno not between 7000 and 7500;

ENAME JOB
---------- ---------
WARD SALESMAN
JONES MANAGER
MARTIN SALESMAN
BLAKE MANAGER
CLARK MANAGER

ALTER TABLE...[ADD/MODIFY/DROP/SET UNUSED]

ALTER TABLE statement can be used to add a new column, modify an existing column, define default value for new column, or drop a column.

ALTER TABLE table_name
ADD (column datatype [DEFAULT expr]
[, column datatype]...);

ALTER TABLE table_name
MODIFY (column datatype [DEFAULT expr]
[, column datatype]...);

ALTER TABLE table
DROP (column);
[OR]
ALTER TABLE table_name
DROP COLUMN column_name;

SQL> alter table sample
add (deptname varchar(20));

Table altered.

SQL> alter table sample
modify (deptname varchar(10));

Table altered.

SQL> alter table sample
drop (deptname);

Table altered.

OR

SQL> alter table master
drop column dept;

Table altered.

Using SET UNUSED option to mark one or more columns as unused.
ALTER TABLE
SET UNUSED();
OR
ALTER TABLE
SET UNUSED COLUMN ;

Using SET UNUSED option in ALTER TABLE Statement:

SQL> select * from sample;

SNO SNAME STEST
---------- -------------------- ----------
1 NJ test1
2 KS test2
3 WA test3
4 OH test4

SQL> alter table sample
set unused (stest);

Table altered.

SQL> select * from sample;

SNO SNAME
---------- ----------------
1 NJ
2 KS
3 WA
4 OH


To remove all the columns that are marked as unused.
ALTER TABLE
DROP UNUSED COLUMNS;

Adding Constraints:

ALTER TABLE
ADD [CONSTRAINT ]
type ();



To modify existing column and make it a primary key.
SQL> alter table test
modify tno primary key;

Table altered.

Modify existing column and make it as foreign key.
SQL> alter table child
add constraint mas_chi_fk
foreign key (cno)
references master(mno);

Table altered.

To delete child rows when a parent row is deleted, use ON DELETE CASCADE option:
SQL> alter table child
add constraint mas_chi_fk
foreign key (cno)
references master(mno) on delete cascade;

Deferring Constraints:
Constraints can be deferred on creation.
ALTER TABLE test
ADD CONSTRAINT test_tno_pk
PRIMARY KEY (tno)
DEFERRABLE INITIALLY DEFERRED;

Change a specific attribute:
SET CONSTRAINTS test_tno_pk IMMEDIATE;

Change all constraints for a session:
ALTER SESSION SET CONSTRAINTS= IMMEDIATE;

Drop Constraints:
SQL> alter table child
drop constraint mas_chi_fk;

To drop the primary key on a table and also drop the associated foreign key constraint:
SQL> alter table master
drop primary key cascade;

To disable and enable the constraints:
SQL> alter table child
disable constraint mas_chi_fk;

SQL> alter table child
enable constraint mas_chi_fk;

CASCASE CONSTRAINTS clause:
Is used along with DROP COLUMN clause that will drop all referential integrity constriants that refer to primary and unique keys defined on dropped columns.

ALTER TABLE test
DROP COLUMN tno CASCADE CONSTRAINTS;

ALTER TABLE test
DROP (tno_pk, eno_fk, tname) CASCADE CONSTRAINTS;

Alter table to add a column which cannot contain NULL

Walk-through example:
Alter table 'mytab' to add a column called 'test' which cannot contain NULL.

SQL> desc mytab
Name Null? Type
----------------------------------------- -------- --------------------------
TNO NUMBER(3)
NAME VARCHAR2(15)

SQL> alter table mytab add test number(8,2) not null;
alter table mytab add test number(8,2) not null
*
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column


SQL> alter table mytab add test number(8,2) default 0 not null;

Table altered.

SQL> desc mytab
Name Null? Type
----------------------------------------- -------- --------------------------
TNO NUMBER(3)
NAME VARCHAR2(15)
TEST NOT NULL NUMBER(8,2)

SQL> select * from mytab;

TNO NAME TEST
---------- --------------- ----------
9 0
2 suman 0
3 ravi 0
4 sai 0
5 sample 0
6 0
7 0

7 rows selected.

Query to retrieve Nth highest salary records

Get the records with Nth highest salary of employee:
SQL> select empno, ename, sal from emp
where sal = (select max(sal) from emp e1 where n<(select count(*) from emp e2 where e1.sal<=e2.sal));

Ex: To get the records that has 2nd highest salary in the emp table:

SQL> select empno, ename, sal from emp
where sal = (select max(sal) from emp e1 where 2<(select count(*) from emp e2 where e1.sal<=e2.sal));

EMPNO ENAME SAL
---------- ---------- ----------
7788 SCOTT 3000
7902 FORD 3000

Identifying and Releasing Locks on objects

First identify the object id that is holding the lock. Query dba_locks,v$lock, v$lock_object to get the SID and object_id

SQL> select * from dba_locks
SESSION_ID LOCK_TYPE MODE_HELD MODE_REQUESTED

LOCK_ID1
---------- -------------------------- ---------------------------------------- ---------------------
145 DML Row-X (SX) None

51966

SQL> select * from v$lock;


SQL> select sid, username from v$session;
SID USERNAME
------ ------------------
143
145 SCOTT
146 SCOTT


SQL> select session_id, process, locked_mode from v$locked_object where object_id=51966;

SESSION_ID PROCESS LOCKED_MODE
---------- ------------ -----------
145 1712:5368 3


SQL> select object_id from user_objects where object_name='SAMPLE';

OBJECT_ID
----------
51966
OR

SQL> select oracle_username os_user_name, locked_mode, object_name, object_type from v$locked_object a,dba_objects b where a.object_id = b.object_id;

OS_USER_NAME LOCKED_MODE OBJECT_NAME
------------------------------ ----------- ---------------------------------------------------------
SCOTT 3 SAMPLE

Also identify the serial# of the sid that is holding the lock.
SQL> select sid, serial#, username from v$session;

SID SERIAL# USERNAME
---------- ---------- ------------------------------
143 38
145 327 SCOTT
146 237 SCOTT
147 1886
154 1
158 4
160 1
161 1
162 1
163 1
164 1

SID SERIAL# USERNAME
---------- ---------- ------------------------------
165 1
166 1
167 1
168 1
169 1
170 1

17 rows selected.


Then, using the sid and serial#, you can kill the session to release the locks. Kill the session only when user is unavailable or in case of emergency. Or you can request the user to commit or rollback to release the locks.

SQL> alter system kill session '146, 237';

System altered.

Note that you cannot kill your own session.
SQL> alter system kill session '145, 327';
alter system kill session '145, 327'
*
ERROR at line 1:
ORA-00027: cannot kill current session

Using GROUPING function

GROUPING function can be used with either CUBE or ROLLUP operator, which helps us to understand how a summary value has been obtained. It uses single column as its argument. It returns 0 or 1 . 0 indicates that the expression has been used to calculate aggregate value and 1 indicates that the expression has bot been used to calculate aggregate value.

SQL> select sno, sname, sum(salary), grouping(sno) grp_sno, grouping(sname) grp_sname
from sample
where sno<6
group by cube(sno, sname);

SNO SNAME SUM(SALARY) GRP_SNO GRP_SNAME
---------- -------------------- ----------- ---------- ----------
52000 1 1
sai 2000 1 0
sai 8000 1 0
ravi 3000 1 0
test 1000 1 0
ravi 5000 1 0
suman 6000 1 0
vinay 20000 1 0
suman 4000 1 0
vinay 3000 1 0
1 23000 0 1

SNO SNAME SUM(SALARY) GRP_SNO GRP_SNAME
---------- -------------------- ----------- ---------- ----------
1 vinay 20000 0 0
1 vinay 3000 0 0
2 10000 0 1
2 suman 6000 0 0
2 suman 4000 0 0
3 8000 0 1
3 ravi 3000 0 0
3 ravi 5000 0 0
4 10000 0 1
4 sai 2000 0 0
4 sai 8000 0 0

SNO SNAME SUM(SALARY) GRP_SNO GRP_SNAME
---------- -------------------- ----------- ---------- ----------
5 1000 0 1
5 test 1000 0 0

24 rows selected.

ROLLUP and CUBE operators with GROUP BY clause

ROLLUP operator is used with GROUP BY clause and produces cumulative aggregates, such as subtotals.
Ex:
SQL> select * from sample order by sno;

SNO SNAME SALARY
---------- -------------------- ----------
1 vinay 3000
1 vinay 9000
1 vinay 3000
1 vinay 8000
2 suman 4000
2 suman 2000
2 suman 4000
3 ravi 3000
3 ravi 5000
4 sai 8000
4 sai 2000

SNO SNAME SALARY
---------- -------------------- ----------
5 test 1000
6 sdf 3223

13 rows selected.

SQL> select sno, sname, sum(salary) from sample
where sno<6 style="color: rgb(51, 102, 255);">rollup(sno, sname);

SNO SNAME SUM(SALARY)
---------- -------------------- -----------
1 vinay 20000
1 vinay 3000
1 23000
2 suman 6000
2 suman 4000
2 10000
3 ravi 3000
3 ravi 5000
3 8000
4 sai 2000
4 sai 8000

SNO SNAME SUM(SALARY)
---------- -------------------- -----------
4 10000
5 test 1000
5 1000
52000

15 rows selected.

In the above example, highlighted with
red: indicates a group totaled by both sno and sname
green: indicates a group totaled by sno
blue: indicates grand total

CUBE operator with GROUP BY clause is used to produce cross tabulation values. Can be applied to all aggregate functions (MIN, MAX, SUM, AVG and COUNT). CUBE produces subtotals for all possible combinations of groupings specified in the GROUP BY clause, and a grand total.

Ex:
SQL> select sno, sname, sum(salary) from sample
2 where sno<6 style="color: rgb(51, 51, 255); font-weight: bold;"> 52000
sai 2000
sai 8000
ravi 3000
test 1000
ravi 5000
suman 6000
vinay 20000
suman 4000
vinay 3000
1 23000

SNO SNAME SUM(SALARY)
---------- -------------------- -----------
1 vinay 20000
1 vinay 3000
2 10000
2 suman 6000
2 suman 4000
3 8000
3 ravi 3000
3 ravi 5000
4 10000
4 sai 2000
4 sai 8000

SNO SNAME SUM(SALARY)
---------- -------------------- -----------
5 1000
5 test 1000

In the above example, the highlighted color
blue: indicates grand total
green: indicates rows totaled by 'sname'
red: indicates rows totaled by 'sno' and 'sname'
purple: indicates rows totaled by only 'sno'

Multi-table INSERT

Types of Multi-table INSERT :
Unconditional INSERT ALL: Each insert into clause is executed
Conditional INSERT ALL: Each WHEN clause whose condition evaluates to TRUE is executed
Conditional INSERT FIRST: First WHEN clause that evalutes to TRUE is executed
Pivoting INSERT: Build a transformation such that each record from non-relational table is converted into multiple records for a relational table.

Unconditional INSERT ALL:
SQL> insert all
into testa values (sno, sname)
into testb values (sno, sname)
select sno, sname from sample where sno <=3;

6 rows created.

SQL> select * from testa;

NO1 NAME1
---------- ---------------
1 vinay
2 kumar
3 suman

SQL> select * from testb;

NO2 NAME2
---------- ----------
1 vinay
2 kumar
3 suman

Conditional INSERT ALL...
SQL> insert all
when sname='vinay' then into testa values (sno, sname)
when sname='kumar' then into testb values (sno, sname)
select sno, sname from sample where sno <=3;

2 rows created.

SQL> select * from testa;

NO1 NAME1
---------- ---------------
1 vinay

SQL> select * from testb;

NO2 NAME2
---------- ----------
2 kumar

Conditional INSERT FIRST:
SQL> insert first
when sname='ravi' then into testa values (sno, sname)
when sname='kumar' then into testb values (sno, sname)
select sno, sname from sample where sno <=3;

1 row created.

SQL> select * from testa;

no rows selected

SQL> select * from testb;

NO2 NAME2
---------- ----------
2 kumar

Using DEFAULT Values

SQL> create table mytest (tno number(3), tname varchar(15) default 'vinay');

Table created.

SQL> insert into mytest(tno) values (1);

1 row created.

SQL> select * from mytest;

TNO TNAME
---------- ---------------
1 vinay

Using Explicit default values:
SQL> insert into mytest values (2,default);

1 row created.

SQL> select * from mytest;

TNO TNAME
---------- ---------------
1 vinay
2 vinay

DML operations based on another table

Copy rows from another table
SQL> create table mytest (tno number(3), tname varchar(20), tloc varchar(10));

Table created.

SQL> insert into mytest
select * from test;

4 rows created.

Note that the 'test' table has the same number of columns as that of 'mytest'. Otherwise you should specify the column names. You should match the number of columns in the INSERT clause with that in the subquery.
SQL> insert into mytest (tno, tname)
select (no, name) from test;

Update two columns:
SQL> update mytest set tname='sample', tloc='CA' where tno=4;

1 row updated.

SQL> select * from mytest;

TNO TNAME TLOC
---------- -------------------- ----------
1 vinay NJ
2 suman NY
3 ravi KY
4 sample CA

SQL> update mytest set
tname=(select tname from mytest where tno=1),
tloc=(select tloc from mytest where tno=2)
where tno=4;

1 row updated.

SQL> select * from mytest;

TNO TNAME TLOC
---------- -------------------- ----------
1 vinay NJ
2 suman NY
3 ravi KY
4 vinay NY

Delete rows based on another table:
SQL> delete from mytest
where tname=(select name from test where tno=3);

1 row deleted.

Using MERGE statement

It performs an UPDATE if the row exists, and an INSERT if it is a new row. Especially useful in data warehousing applications.

SQL> select * from test;

TNO NAME LOC
---------- --------------- ----------
1 vinay NJ
2 suman NY
3 ravi KY
4 sai MO

SQL> select * from sample;

SNO SNAME SALARY
---------- -------------------- ----------
1 vinay 3000
2 kumar 4000
3 suman 5000
4 ravi 8000

SQL> merge into sample s
using test t
on (t.tno=s.sno)
when matched then
update set s.sname=t.name
when not matched then
insert values (t.tno, t.name, 3000)

4 rows merged.

SQL> select * from sample;

SNO SNAME SALARY
---------- -------------------- ----------
5 test 1000
1 vinay 3000
2 suman 4000
3 ravi 5000
4 sai 8000

SQL> select * from test;

TNO NAME LOC
---------- --------------- ----------
1 vinay NJ
2 suman NY
3 ravi KY
4 sai MO


NOTE that MERGE statement is not allowed on external tables. Below 'test' is an external table.

merge into test t
*
ERROR at line 1:
ORA-30657: operation not supported on external organized table

Get object_id of Database Object

To get the object id of an object you can query user_objects. Note that object_name is case-sensitive.

SQL> select object_id from user_objects where object_name='sample';

no rows selected

SQL> select object_id from user_objects where object_name='SAMPLE';

OBJECT_ID
----------
53673

[OR]

You can query dba_objects. Note that owner name is case-sensitive
SQL> select object_id, object_name from dba_objects where owner='SCOTT';

OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------
53930 MASTER
53931 MASTER_MNO_PK
53932 CHILD1
53933 BIN$HfQPV+0lQOWmj9HXRXQ01w==$0
53934 CHILD2
51250 PK_DEPT
51249 DEPT
51251 EMP
51252 PK_EMP
51253 BONUS
51254 SALGRADE

OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------
53673 SAMPLE
53674 BIN$aSuwOl8AQFCCphpKWoCRkg==$0
53675 BIN$Acsdzvs0RMewwv6ntnvFxQ==$0
54148 TEST

15 rows selected.

Using External Tables (Walk-through with an example)

External tables are read-only tables whose metadata is stored in the database and actual data is stored outside the database. You can query the data in external table using SQL, PL/SQL and Java. Since these tables are read-only no DML operations are possible and no indexes can be created on them .

Syntax:
CREATE TABLE table_name ( col_name datatype, … )
ORGANIZATION EXTERNAL
(TYPE access_driver_type
DEFAULT DIRECTORY dir_name
ACCESS PARAMETERS
(… ) )
LOCATION ('loc_specifier') )
REJECT LIMIT [0 | number | UNLIMITED];


access_driver_type can be either ORACLE_LOADER or ORACLE_DATAPUMP. The access driver ensures that the data from data source is processed so that it matches the definition of external table. ORACLE_LOADER is default access driver type and is used for reading of data from external files whose format can be interpreted by SQL*Loader utility. ORACLE_DATAPUMP is used to both import and export data using a platform-independent format.

ACCESS PARAMETERS: optional clause that enables you to assign values to the parameters of specific access driver for this external table

LOCATION: specify the external data source file

REJECT_LIMIT: how many conversion queries can occur during a query of external data befors an Oracle error is returned and the query is aborted. Default is 0.

Walk-through on using external tables:

1) Assume that you have a flat file (test.dat) that has records as shown below:

1, vinay, NJ
2, suman, NY
3, ravi, KY

I have this file located at 'D:\Oracle\OCP' on my system.

2) Create directory object that will point to the directory location where the flat file exists. Use directory names when referring to external data sources, instead of hard-coding the OS path name for greater file management flexibility.

SQL> create directory test_dir as 'D:\Oracle\OCP';

Directory created.

3) Now create the table using organization as external
SQL> create table test (tno number(3), name varchar(15), loc varchar(10))
organization external
(type oracle_loader
default directory test_dir
access parameters
(records delimited by newline
nobadfile
nologfile
fields terminated by ',')
location ('test.dat'))
parallel 2
reject limit 100;

Table created.

4) SQL> select * from test;

TNO NAME LOC
---------- --------------- ----------
1 vinay NJ
2 suman NY
3 ravi KY

5) If you try to perform DML operations on this table you will see the following error:
SQL> insert into test values (4,'sample', 'MO');
insert into test values (4,'sample', 'MO')
*
ERROR at line 1:
ORA-30657: operation not supported on external organized table

6) You can add or delete records from your flat file on disk. I have added the following line to 'test.dat'

4, sai, MO

7) SQL> select * from test;

TNO NAME LOC
---------- --------------- ----------
1 vinay NJ
2 suman NY
3 ravi KY
4 sai MO

8) Once your external table is created, it can be queried like a relational table

NOTE: You can use CREATE TABLE AS SELECT statement to unload data into regular table in the database from an external table.

Ex: In the following example 'test' is an external table.
SQL> create table mytab
as select tno, name from test;

Table created.

SQL> select * from mytab;

TNO NAME
---------- ---------------
1 vinay
2 suman
3 ravi
4 sai
5 sample

Querying V$DATABASE

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PRIMARY

SQL> select platform_id, platform_name from v$database;

PLATFORM_ID
-----------
PLATFORM_NAME
---------------------------------------------------------------------
7
Microsoft Windows IA (32-bit)


SQL> select current_scn from v$database;

CURRENT_SCN
-----------
901163

ORA-30040

Problem:
I have taken the undo tablespace offline through spfile. I shutdown the database and start the db next day from pfile

"IT GIVE ME THE SYSTEM ERROR, DISCONNECTION FORCED"

I tried to start database in nomount stage (it works)
alter database mount (it works)

But when I issue

alter database open OR simply type the "startup" command it gives the below error

Errors in file d:\sam\udump\sam_ora_3700.trc:
ORA-30040: Undo tablespace is offline

Thu Jan 22 00:00:10 2009
Error 30040 happened during db open, shutting down database
USER: terminating instance due to error 30040
Instance terminated by USER, pid = 3700
ORA-1092 signalled during: alter database open...

Solution:
Check UNDO_TABLESPACE (must be set to ur undo tablespace name) and UNDO_MANAGEMENT (set it to AUTO) parameters in PFILE or SPFILE

Using ALTER TABLE....SET UNUSED Statement

Use SET UNUSED option to mark one or more columns as unused.
ALTER TABLE table_name
SET UNUSED(column_name);

[OR]

ALTER TABLE table_name
SET UNUSED COLUMN column_name;

Ex: SQL> select * from sample;

SNO SNAME SALARY
---------- -------------------- ----------
1 vinay 3000
2 kumar 4000
3 suman 5000
4 ravi 8000

SQL> alter table sample set unused (salary);

Table altered.

SQL> select * from sample;

SNO SNAME
---------- --------------------
1 vinay
2 kumar
3 suman
4 ravi

Scenario: To undo the above operation follow the below steps:

SQL> select * from sample;

SNO SNAME SALARY
---------- -------------------- ----------
1 vinay 3000
2 kumar 4000
3 suman 5000
4 ravi 8000

Note the object_id from below query for 'sample' table:
SQL> select object_id, object_name from dba_objects where owner='SCOTT';

OBJECT_ID OBJECT_NAME
---------- ----------------------------------------------------------------
51250 PK_DEPT
51249 DEPT
51251 EMP
51252 PK_EMP
51253 BONUS
51254 SALGRADE
51729 TEST
51966 SAMPLE
51976 SYS_C005218

9 rows selected.

Login as sysdba
SQL> conn sys/manager as sysdba
Connected.

Another way to get the object_id for 'sample' table. Note that name is case-sensitive.
SQL> select obj# from obj$ where name='SAMPLE';

OBJ#
----------
51966

Note the values for COLS in tab$
SQL> select cols from tab$ where obj#=51966;

COLS
----------
3

Note the values for COL#, INTCOL#, PROPERTY, and NAME in col$
SQL> select col#, intcol#, property, name from col$ where obj#=51966;

COL# INTCOL# PROPERTY NAME
---------- ---------- ---------- ------------------------------
1 1 0 SNO
2 2 0 SNAME
3 3 0 SALARY

SQL> conn scott/tiger
Connected.

Use ALTER TABLE ... SET UNUSED to mark a column unused.
SQL> alter table sample set unused (salary);

Table altered.

Select from the table to confirm the column is unavailable. Now, you will not see the 'salary' column.
SQL> select * from sample;

SNO SNAME
---------- --------------------
1 vinay
2 kumar
3 suman
4 ravi

Now, update tab$ and col$ to before the ALTER conditions (see above for values), and commit.
SQL> conn sys/manager as sysdba
Connected.

SQL> update col$
set col#=3, property=0 where name='SYS_C00003_09012712:53:28$';

1 row updated.

SQL> select col#, intcol#, property, name from col$ where obj#=51966;

COL# INTCOL# PROPERTY NAME
---------- ---------- ---------- ------------------------------
1 1 0 SNO
2 2 0 SNAME
3 3 0 SYS_C00003_09012712:53:28$

SQL> update col$
set name='SALARY' where col#=3 and obj#=51966;

1 row updated.

SQL> select col#, intcol#, property, name from col$ where obj#=51966;

COL# INTCOL# PROPERTY NAME
---------- ---------- ---------- ------------------------------
1 1 0 SNO
2 2 0 SNAME
3 3 0 SALARY

SQL> update tab$ set cols=3 where obj#=51966;

1 row updated.

SQL> select cols from tab$ where obj#=51966;

COLS
----------
3

SQL> select cols from tab$ where obj#=51966;

COLS
----------
3

SQL> commit;

Commit complete.

Flush the shared pool and buffer cache.
SQL> alter system flush buffer_cache;

System altered.

SQL> alter system flush shared_pool;

System altered.

SQL> select * from sample;

SNO SNAME SALARY
---------- -------------------- ----------
1 vinay 3000
2 kumar 4000
3 suman 5000
4 ravi 8000