Thursday, December 30, 2010

How to Kill Oracle Session

how to Kill Oracle Session
=============================

sometime one oracle session can make you tension.
for this you have to kill that session immediately.
but how ?
what is best way to kill a session ?
when which way you choose ?


First way :-
=============


via SQL* PLUS utility:

In order to use SQL*PLUS Approach, first we need to find out the SID, SERIAL# of the Session
which we want to kill. The following command to find the session and kill the session .

SQL> select username,sid,serial#,terminal from v$session;


SQL> alter system kill session ‘SID, SERIAL#’;


[note :
When we issue the above statement, It terminates a session, rolls back ongoing transactions,
releases all session locks, frees all session resources.
If the session is performing some activity that must be completed
(e.g. waiting for a reply from a remote database or rolling back a transaction),
Oracle waits for this activity to complete, kills the session then returns control.
If the wait lasts for 60 seconds then Oracle marks the session to be killed,
and returns control with a message that the session is marked to be killed.
It then gets killed when the activity is complete.
]

This is the best way. and Oracle recommended way.


Second way :-
=============



via KILL Command (for Unix/Linux)

To kill the sessions using KILL Command, we need to find out the SPID ( Server Process ID)
of the Oracle Session.

To find out a spid of a specific session

SQL> SELECT s.sid,p.spid, s.osuser, s.programFROM
v$process p, v$session s
WHERE p.addr = s.paddr
and s.sid=<:your_given_sid>;


Then issue the KILL Command


$ kill -9 SPID




[Note: Do not kill the sessions at the OS level (as per as possible).
This is not Oracle Recommendation way ]


Third way :-
=============


via ORAKILL Command (in Windows command prompt)

To kill the sessions using ORAKILL Command ( Windows), we need to find out the SPID of Session
and ORACLE_SID of your Oracle Database. Then issue ORAKILL Command

To find out a spid of a specific session


SQL> SELECT s.sid,p.spid, s.osuser, s.programFROM
v$process p, v$session s
WHERE p.addr = s.paddr
and s.sid=<:your_given_sid>;


Then issue the orakill Command



C:\> orakill ORACLE_SID SPID




[Note: Do not kill the sessions at the OS level (as per as possible).
This is not Oracle Recommendation way ]

Wednesday, December 29, 2010

Export backup script of oracle database

===================================================
Export backup script of oracle database
===================================================


You can let Export prompt you for parameters by entering the EXP
command followed by your username/password:

Example: EXP SCOTT/TIGER

Or, you can control how Export runs by entering the EXP command followed
by various arguments. To specify parameters, you use keywords:

Format: EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

Keyword Description (Default)
======= =====================
USERID- username/password

BUFFER- size of data buffer

FILE - output files (EXPDAT.DMP)

COMPRESS- import into one extent (Y)

GRANTS - export grants (Y)

INDEXES - export indexes (Y)

DIRECT - direct path (N)

LOG - log file of screen output

ROWS - export data rows (Y)

CONSISTENT - cross-table consistency(N)

FULL - export entire file (N)

OWNER - list of owner usernames

TABLES - list of table names

RECORDLENGTH - length of IO record

INCTYPE - incremental export type

RECORD- track incr. export (Y)

TRIGGERS- export triggers (Y)

STATISTICS- analyze objects (ESTIMATE)

PARFILE - parameter filename

CONSTRAINTS - export constraints (Y)

OBJECT_CONSISTENT- transaction set to read only during object export (N)

FEEDBACK - display progress every x rows (0)

FILESIZE - maximum size of each dump file

FLASHBACK_SCN - SCN used to set session snapshot back to

FLASHBACK_TIME - time used to get the SCN closest to the specified time

QUERY - select clause used to export a subset of a table

RESUMABLE - suspend when a space related error is encountered(N)

RESUMABLE_NAME - text string used to identify resumable statement

RESUMABLE_TIMEOUT - wait time for RESUMABLE

TTS_FULL_CHECK -perform full or partial dependency check for TTS

TABLESPACES - list of tablespaces to export

TRANSPORT_TABLESPACE- export transportable tablespace metadata (N)

TEMPLATE -template name which invokes iAS mode export




With Example
=================
In CMD command line window.

Export help
exp -help
exp -help
--------------------------------
Export user
exp userid=
exp halim/halim
--------------------------------
Export File Name
exp userid= FILE=
exp halim/halim file=c:\emp\uw_test.dmp
--------------------------------
Log File Name
exp userid= LOG=
exp halim/halim log=c:\emp.log
------------------------------------------------
Buffer size -- O/S dependent and can usually be ignored
exp userid= BUFFER=
-- rows_in_array * maximum_row_size
exp halim/halim buffer=64000
--------------------------------------------
Compress (default is Y) -- The default is Y and it is best to override it.
exp userid= COMPRESS=
exp halim/halim compress=N
--------------------------------------------
Consistent (default is N) -- Implements SET TRANSACTION READ ONLY
exp userid= CONSISTENT=
exp halim/halim file=c:\emp\cnsstnt.dmp consistent=Y
--------------------------------------------------------
Constraints (default is Y)
exp userid= CONSTRAINTS=
exp halim/halim file=c:\emp\cnstrnt.dmp constraints=N
--------------------------------------------------------------
Direct Path (default is N)
exp userid= DIRECT=
exp halim/halim file=c:\emp\dirpath.dmp direct=Y
-----------------------------------------------
Feedback (default is 0)
exp userid= FEEDBACK=
exp halim/halim file=c:\emp\back.dmp feedback=100
---------------------------------------------------
File Size (default unlimited)
exp userid= FILESIZE= [KB|MB]
exp halim/halim file=c:\emp\size.dmp filesize 100MB
---------------------------------------------------------------
Flashback By SCN
exp userid= FLASHBACK_SCN=
exp halim/halim file=c:\emp\bscn.dmp flashback_scn=4567892
------------------------------------------------------------
Flashback By Timestamp
exp userid= FLASHBACK_TIME=
exp halim/halim FLASHBACK_TIME="TIMESTAMP '2002-05-01 11:00:00'"
or
exp halim/halim flashback_time="TO_TIMESTAMP('12-02-2001 14:35:00','DD-MM-YYYY HH24:MI:SS')"
-----------------------------------------------------------
Full -- Yes requires the user to have the EXP_FULL_DATABASE role
exp userid= FULL=
exp halim/halim full=Y
----------------------------------------------------------------
Grants
exp userid= GRANTS=
exp halim/halim grants=N
--------------------------------------------
Indexes
exp userid= INDEXES=
exp halim/halim indexes=N
----------------------------------------------
Object Consistent
exp userid= OBJECT_CONSISTENT=
exp halim/halim object_consistent=Y
---------------------------------------------
Owner
exp userid= OWNER=(o1, o2, ... o#)
exp halim/halim owner=(halim, ids, webapps)
-----------------------------------------
Parameter File
exp userid= PARFILE=
exp halim/halim parfile=c: emp\uwparfile.ctl
--------------------------------------------------
Query
exp userid= QUERY=
exp halim/halim owner=SCOTT tables=emp
query=\"WHERE job=\'MANAGER\' AND sal \>50000\"
exp scott/tiger@orcl file=C:/emp_depno.dmp tables=emp query=\"where deptno=10\"
--------------------------------------------------------
Record Length
exp userid= RECORDLENGTH=
exp halim/halim recordlength=32000
---------------------------------------------------------
Resumable
exp userid= RESUMABLE=
exp halim/halim resumable=Y
------------------------------------------------------------
Resumable Name
exp userid= RESUMABLE_NAME = 'User USERNAME (USERID), Session SESSIONID, Instance INSTANCEID'
exp halim/halim resumable_name 'halim'
-----------------------------------------------------------------
Resumable Time Out
exp userid= RESUMABLE_TIMEOUT= DEFAULT 7200 (2 hours)
exp halim/halim resumable_timeout=18000
-------------------------------------------------------------
Rows
exp userid= ROWS=
exp halim/halim rows=N
-------------------------------------------------------------
Statistics
exp userid=
STATISTICS=
exp halim/halim statistics=COMPUTE
-------------------------------------------------------------
Tables
exp userid= TABLES=(t1, t2, ... t#)
exp halim/halim tables=(emp, dept, bonus)
-------------------------------------------------------------
Tablespaces
exp userid=
TABLESPACES=(tbsp1, tbsp2, ... tbsp#)
exp halim/halim tablespaces=(uwdata, user_data)
-------------------------------------------------------------
Transportable Tablespaces
exp userid= TRANSPORT_TABLESPACE
exp halim/halim transport_tablespace=Y
-------------------------------------------------------------
Triggers
exp userid= TRRIGGERS
exp halim/halim triggers=N
-------------------------------------------------------------
TTS Full Check
exp userid= TTS_FULL_CHECK
exp halim/halim tts_full_check=Y
-------------------------------------------------------------
Volume Size
exp userid= VOLSIZE [KB|MB|GB]
exp halim/halim volsize=10GB

export a table data with where clause condition in oracle

exp scott/tiger@orcl file=C:/emp_depno.dmp tables=emp query=\"where deptno=10\"

Monday, December 27, 2010

Oracle Enterprise manager not start after changing listener name or port.

Oracle Enterprise manager not start after changing listener name or port.
------------------------------------------------------------------------

Solution:-

you can find in following file listener & port entries,
change it according to your new listener configurations.


G:\oracle\product\10.2.0\db_1\localhost_orcl\sysman\emd\targets.xml

Sunday, December 26, 2010

what type of database google use?

Dears, have you any curious about "what type of database google use?" .

Google use primarily "A Distributed Storage System for Structured Data" Such as

Bigtable :- is a distributed storage system for managing
structured data that is designed to scale to a very large
size. . . for more download the document from here.

"http://labs.google.com/papers/bigtable.html"


Google also use Oracle and Mysql database for their some applications.

any more information from you is highly appreciated .

Thursday, December 23, 2010

What is Database Scale-Out ?

What is Database Scale-Out? (My SQL Database One of them)
=========================================================

Scale-Out is a modern computing architecture that enables organizations to improve application performance and scalability on an incremental, as-needed basis by adding multiple replicated database servers on low-cost commodity hardware. This is in contrast to a Scale-Up approach, which requires organizations to make a large up-front investment in more expensive and complex server hardware and database licenses in order to add capacity.

In the online world, many of the largest and fastest-growing companies use MySQL to cost-effectively Scale-Out their successful businesses, saving millions of dollars over high-cost proprietary technology — including Google, Yahoo, craigslist, Ticketmaster, Wikipedia, YouTube, and Evite/Citysearch.

Tuesday, December 7, 2010

kkjcre1p: unable to spawn jobq slave process

why in alert log file showing this message

kkjcre1p: unable to spawn jobq slave process

Process J000 died, kkjcre1p: unable to spawn jobq slave process
================================================================

PROCESS J000 And M000 Die,

this might be related to a lack of OS-resources.

Using OSWatcher is a good tool to get more evidence of this.
or
use top/vmstat/prstat to check it.

Normally OSWatcher is taking snapshots every 30 seconds and in the above output it shows that several snapshots are missing. OSWatcher is using OS-commands like 'top' and 'vmstat'. So if they are having problems, than there is a serious problem on OS-level.

Another indication of the OS problems are the very high number of processes in the CPU Run and Blocked Q.

Alert log shows like belows-
------------------------------------------------
Process PN82 died
Tue Feb 22 16:42:29 2011
Process J000 died
Tue Feb 22 16:42:29 2011
kkjcre1p: unable to spawn jobq slave process
Tue Feb 22 16:42:29 2011
Errors in file /d04/admin/stlbas/bdump/stlbas_cjq0_1880.trc:

Tue Feb 22 16:42:30 2011
Process PN83 died
Tue Feb 22 16:42:32 2011
Process PN84 died
Tue Feb 22 16:42:33 2011
Process PN85 died
Tue Feb 22 16:42:34 2011
Process PN86 died
Tue Feb 22 16:42:35 2011
Process PN87 died
Tue Feb 22 16:42:36 2011
Process J000 died
Tue Feb 22 16:42:36 2011
kkjcre1p: unable to spawn jobq slave process
Tue Feb 22 16:42:36 2011
Errors in file /d04/admin/stlbas/bdump/stlbas_cjq0_1880.trc:

Tue Feb 22 16:42:37 2011
Process PN88 died
Tue Feb 22 16:42:40 2011

-----------------------------------------------


=======================
or
===================

"unable to spawn jobq slave process" this message can be show for
not correct setting of JOB_QUEUE_PROCESSES .




How to determine the correct setting for JOB_QUEUE_PROCESSES
============================================================



Solution
==============


The ideal setting for JOB_QUEUE_PROCESSES should be set to the maximum number of jobs that would ever be run concurrently on a system PLUS a few more.

1) Initially set JOB_QUEUE_PROCESSES higher than the most number of jobs that could ever run concurrently

Be careful not to set this too high as it can effect database performance or even exhaust OS resources

A fair estimate can be done by looking at DBA_JOBS ... NEXT_DATE and NEXT_SEC columns

The reason that this is a fair estimate is the JOB SCHEDULER submits one time execute jobs to the queue
for execution when it chooses to run a job ... so it is possible that the above estimate would be a good bit
off ... Example ... Running or UTLRP.sql ... on a system with multiple CPU's ... will cause he SCHEDULER
to submit multiple jobs ... at the same time ... to the job queue

NOTE: Add one more to your estimate for the job that will be created below

2) Use the job queue to monitor itself

a) Login as SYS

b) Create the table to do the monitoring

CREATE TABLE MAX_CONCURRENT_JOBS (
SAMPLE_DATE DATE,
JOBS_RUNNING NUMBER);

c) Create the job to do the monitoring

CREATE OR REPLACE PROCEDURE MAX_CONCURRENT_JOBS_MONITOR IS
BEGIN
INSERT INTO MAX_CONCURRENT_JOBS
SELECT SYSDATE, COUNT(*) FROM DBA_JOBS_RUNNING;
COMMIT;
END;
/

d) Create the job queue job to do the monitoring

DECLARE
v_jobno number;
BEGIN
DBMS_JOB.SUBMIT(
JOB => v_jobno,
WHAT => 'MAX_CONCURRENT_JOBS_MONITOR;',
NEXT_DATE => SYSDATE,
INTERVAL => 'SYSDATE + (5/1440)');
COMMIT;
END;
/

NOTE : The example above uses a 5 minute interval (5/1440) ... this can be adjusted as needed

e) Monitor MAX_CONCURRENT_JOBS

SELECT MAX(JOBS_RUNNING) FROM MAX_CONCURRENT_JOBS;

The length of time for monitoring depends on the INTERVAL in the job queue for ALL jobs ... It is recommended that at least two (2) executions of every job in the job queue be monitored with this process

3) Set JOB_QUEUE_PROCESSES to the appropriate setting

ALTER SYSTEM SET JOB_QUEUE_PROCESSES = SCOPE = ...

4) Remove the monitoring system

SELECT JOB FROM DBA_JOBS WHERE WHAT='MAX_CONCURRENT_JOBS_MONITOR;';

EXEC DBMS_JOB.REMOVE();

DROP PROCEDURE MAX_CONCURRENT_JOBS_MONITOR;

DROP TABLE MAX_CONCURRENT_JOBS;




NOTE : this process needs to be done periodically ... especially if new jobs are being added to the job queue


and also see here:
http://halimdba.blogspot.com/2010/12/solaris-error-32-broken-pipe-or-linux.html


Solaris Error: 32: Broken pipe or Linux Error: 32: broken pipe

Yesterday I found in alert log file bellows warning.
and In that time some clients informed us they are not able to log in
to the application and after some times they able to log in to
database automatically. so we tried to find out the cause and solution. see below...



It is a dedicated environment .

In parameter file
----------------------
processes = 4000



===========================================
Content of alert log file.
===========================================


Mon Dec 6 16:03:00 2010
Thread 1 advanced to log sequence 67690 (LGWR switch)
Current log# 3 seq# 67690 mem# 0: /d01/oracle/oradata/stlbas/redo03.log
Mon Dec 6 16:20:39 2010
Process J000 died, see its trace file
Mon Dec 6 16:20:39 2010
kkjcre1p: unable to spawn jobq slave process
Mon Dec 6 16:20:39 2010
Errors in file /d04/admin/stlbas/bdump/stlbas_cjq0_1885.trc:

Process J000 died, see its trace file
Mon Dec 6 16:20:45 2010
kkjcre1p: unable to spawn jobq slave process
Mon Dec 6 16:20:45 2010
Errors in file /d04/admin/stlbas/bdump/stlbas_cjq0_1885.trc:

Mon Dec 6 16:21:01 2010
Process J000 died, see its trace file
Mon Dec 6 16:21:01 2010
kkjcre1p: unable to spawn jobq slave process
Mon Dec 6 16:21:01 2010
Errors in file /d04/admin/stlbas/bdump/stlbas_cjq0_1885.trc:

Mon Dec 6 16:21:17 2010
Process J000 died, see its trace file
Mon Dec 6 16:21:17 2010
kkjcre1p: unable to spawn jobq slave process
Mon Dec 6 16:21:17 2010
Errors in file /d04/admin/stlbas/bdump/stlbas_cjq0_1885.trc:

Mon Dec 6 16:36:24 2010
Process J000 died, see its trace file
Mon Dec 6 16:36:24 2010
kkjcre1p: unable to spawn jobq slave process
Mon Dec 6 16:36:24 2010
Errors in file /d04/admin/stlbas/bdump/stlbas_cjq0_1885.trc:

Process J000 died, see its trace file
Mon Dec 6 16:36:30 2010
kkjcre1p: unable to spawn jobq slave process
Mon Dec 6 16:36:30 2010
Errors in file /d04/admin/stlbas/bdump/stlbas_cjq0_1885.trc:

Mon Dec 6 16:36:41 2010
Process J000 died, see its trace file
Mon Dec 6 16:36:41 2010
kkjcre1p: unable to spawn jobq slave process
Mon Dec 6 16:36:41 2010
Errors in file /d04/admin/stlbas/bdump/stlbas_cjq0_1885.trc:

Mon Dec 6 16:38:18 2010
Thread 1 advanced to log sequence 67691 (LGWR switch)
Current log# 4 seq# 67691 mem# 0: /d01/oracle/oradata/stlbas/redo04.log



====================================
contents in the trace file are -
====================================


/d04/admin/stlbas/bdump/XXXX_cjq0_1885.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
ORACLE_HOME = /d04/oracle/ora102
System name: SunOS
Node name:XXXX
Release: 5.10
Version: Generic_142900-07
Machine: sun4v
Instance name: XXXX
Redo thread mounted by this instance: 1
Oracle process number: 26
Unix process pid: 1885, image: oracle@XXXX (CJQ0)

*** SERVICE NAME:(SYS$BACKGROUND) 2010-12-01 13:24:30.248
*** SESSION ID:(2191.1) 2010-12-01 13:24:30.248
*** 2010-12-01 13:24:30.248
Process J000 is dead (pid=25006, state=3):
*** 2010-12-01 13:24:37.277
Process J000 is dead (pid=25012, state=3):
*** 2010-12-01 13:59:56.397
Process J000 is dead (pid=1185, state=3):
*** 2010-12-01 15:55:42.277
Process J000 is dead (pid=15686, state=3):
*** 2010-12-06 16:20:39.526
Process J000 is dead (pid=9458, state=3):
*** 2010-12-06 16:20:45.565
Process J000 is dead (pid=9480, state=3):
*** 2010-12-06 16:21:01.641
Process J000 is dead (pid=9518, state=3):
*** 2010-12-06 16:21:17.712
Process J000 is dead (pid=9550, state=3):
*** 2010-12-06 16:36:24.213
Process J000 is dead (pid=11942, state=3):
*** 2010-12-06 16:36:30.238
Process J000 is dead (pid=11970, state=3):
*** 2010-12-06 16:36:41.289
Process J000 is dead (pid=11998, state=3):



===============================================================
content of listener log file like bellows (in that time only)
================================================================

06-DEC-2010 16:16:21 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=STLBAS)(CID=(PROGRAM=D:\OraNT\BIN\ifrun60.EXE)(HOST=APPLICATION-07)(USER=001zohur))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.11.1.36)(PORT=1771)) * establish * STLBAS * 0
06-DEC-2010 16:16:21 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=STLBAS)(CID=(PROGRAM=D:\OraNT\BIN\RWRBE60.exe)(HOST=APPLICATION-02)(USER=154shahadat))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.11.1.32)(PORT=1528)) * establish * STLBAS * 0
06-DEC-2010 16:16:21 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=STLBAS)(CID=(PROGRAM=D:\OraNT\BIN\RWRBE60.exe)(HOST=APPLICATION-07)(USER=043sathekur))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.11.1.36)(PORT=1758)) * establish * STLBAS * 12518
TNS-12518: TNS:listener could not hand off client connection
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact
Solaris Error: 32: Broken pipe
06-DEC-2010 16:16:21 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=STLBAS)(CID=(PROGRAM=D:\OraNT\BIN\ifrun60.EXE)(HOST=APPLICATION-07)(USER=038alfee))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.11.1.36)(PORT=1772)) * establish * STLBAS * 12518
TNS-12518: TNS:listener could not hand off client connection
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact
Solaris Error: 32: Broken pipe
06-DEC-2010 16:16:22 * service_update * stlbas * 0
06-DEC-2010 16:16:24 * service_update * stlbas * 0
06-DEC-2010 16:16:25 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=STLBAS)(CID=(PROGRAM=D:\OraNT\BIN\ifrun60.EXE)(HOST=APPLICATION-07)(USER=003aftab))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.11.1.36)(PORT=1794)) * establish * STLBAS * 12518
TNS-12518: TNS:listener could not hand off client connection
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact
Solaris Error: 32: Broken pipe
06-DEC-2010 16:16:25 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=STLBAS)(CID=(PROGRAM=D:\OraNT\BIN\RWRBE60.exe)(HOST=NEW-SUN-APP)(USER=023sohel))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.11.1.37)(PORT=4291)) * establish * STLBAS * 12518
TNS-12518: TNS:listener could not hand off client connection
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact
Solaris Error: 32: Broken pipe
06-DEC-2010 16:16:27 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=STLBAS)(CID=(PROGRAM=D:\OraNT\BIN\ifrun60.EXE)(HOST=APPLICATION-07)(USER=038alfee))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.11.1.36)(PORT=1796)) * establish * STLBAS * 0
06-DEC-2010 16:16:27 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=STLBAS)(CID=(PROGRAM=D:\OraNT\BIN\ifrun60.EXE)(HOST=APPLICATION-07)(USER=038alfee))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.11.1.36)(PORT=1797)) * establish * STLBAS * 0
06-DEC-2010 16:16:28 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=STLBAS)(CID=(PROGRAM=D:\OraNT\BIN\RWRBE60.exe)(HOST=APPLICATION-11)(USER=030salah))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.11.1.33)(PORT=3982)) * establish * STLBAS * 0
06-DEC-2010 16:16:29 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=stlbas)(CID=(PROGRAM=D:\OraNT\BIN\ifrun60.EXE)(HOST=REPORTS_APP)(USER=504refayet))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.11.1.233)(PORT=4995)) * establish * stlbas * 0
06-DEC-2010 16:16:29 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=STLBAS)(CID=(PROGRAM=D:\OraNT\BIN\ifrun60.EXE)(HOST=APPLICATION-07)(USER=019aporna))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.11.1.36)(PORT=1800)) * establish * STLBAS * 0
06-DEC-2010 16:16:29 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=stlbas)(CID=(PROGRAM=D:\OraNT\BIN\ifrun60.EXE)(HOST=REPORTS_APP)(USER=504refayet))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.11.1.233)(PORT=4996)) * establish * stlbas * 0
06-DEC-2010 16:16:30 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=STLBAS)(CID=(PROGRAM=D:\OraNT\BIN\ifrun60.EXE)(HOST=APPLICATION-07)(USER=003aftab))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.11.1.36)(PORT=1801)) * establish * STLBAS * 12518
TNS-12518: TNS:listener could not hand off client connection
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact
Solaris Error: 32: Broken pipe
06-DEC-2010 16:16:30 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=STLBAS)(CID=(PROGRAM=D:\OraNT\BIN\ifrun60.EXE)(HOST=APPLICATION-07)(USER=019aporna))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.11.1.36)(PORT=1802)) * establish * STLBAS * 12518
TNS-12518: TNS:listener could not hand off client connection
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact
Solaris Error: 32: Broken pipe
06-DEC-2010 16:16:30 * service_update * stlbas * 0
06-DEC-2010 16:16:30 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=stlbas)(CID=(PROGRAM=D:\OraNT\BIN\ifrun60.EXE)(HOST=REPORTS_APP)(USER=501azad))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.11.1.233)(PORT=4997)) * establish * stlbas * 0
06-DEC-2010 16:16:31 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=stlbas)(CID=(PROGRAM=D:\OraNT\BIN\ifrun60.EXE)(HOST=REPORTS_APP)(USER=501azad))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.11.1.233)(PORT=4999)) * establish * stlbas * 0
06-DEC-2010 16:16:32 * service_update * stlbas * 0









What is the cause ?
and
why it shown "Solaris Error: 32: Broken pipe" ?




===================================================
===================================================
solutions:
===================================================
===================================================



1. listener log file is so big (greate than 2GB in linux)

2.

You have reached your max utilization of process parameter.

So increase processes and sessions parameter.


[ Each dedicated session creates process onto the server, consequently, you may overload your server itself. These parameters should not be set too large. you can think about shared server

Check CPU/Memory utilization of server before increasing the values .

Also you might have to increase other parameter also like SGA and PGA accordingly. So check their utilization also.

You will have to analyze database load during peak time and also find out the which processes are creating so many sessions. ]


SQL> select * from v$resource_limit ;

RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
------------------------------ ------------------- --------------- ---------- ----------
processes 1607 4000 4000 4000
sessions 1596 4005 4405 4405
enqueue_locks 1380 6280 57390 57390
enqueue_resources 563 2901 19600 UNLIMITED
ges_procs 0 0 0 0
ges_ress 0 0 0 UNLIMITED
ges_locks 0 0 0 UNLIMITED
ges_cache_ress 0 0 0 UNLIMITED
ges_reg_msgs 0 0 0 UNLIMITED
ges_big_msgs 0 0 0 UNLIMITED
ges_rsv_msgs 0 0 0 0
gcs_resources 0 0 0 0
gcs_shadows 0 0 0 0
dml_locks 43 718 19380 UNLIMITED
temporary_table_locks 0 3 UNLIMITED UNLIMITED
transactions 306 722 4845 UNLIMITED
branches 1 13 4845 UNLIMITED
cmtcallbk 2 4 4845 UNLIMITED
sort_segment_locks 1594 4451 UNLIMITED UNLIMITED
max_rollback_segments 136 361 4845 65535
max_shared_servers 0 0 UNLIMITED UNLIMITED
parallel_max_servers 1324 3600 2560 3600

22 rows selected.


or

SQL> select * FROM v$license;


========================
caution:
===========================



PROCESSES
=============================================
Property Description
Parameter type Integer
Default value 40 to operating system-dependent
Modifiable No
Range of values 6 to operating system dependent
Basic Yes

PROCESSES specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. Its value should allow for all background processes such as locks, job queue processes, and parallel execution processes.

The default values of the SESSIONS and TRANSACTIONS parameters are derived from this parameter. Therefore, if you change the value of PROCESSES, you should evaluate whether to adjust the values of those derived parameters.



SESSIONS
================================================
Property Description
Parameter type Integer
Default value Derived: (1.1 * PROCESSES) + 5
Modifiable No
Range of values 1 to 231
Basic Yes
Real Application Clusters Multiple instances can have different values.



SESSIONS specifies the maximum number of sessions that can be created in the system. Because every login requires a session, this parameter effectively determines the maximum number of concurrent users in the system. You should always set this parameter explicitly to a value equivalent to your estimate of the maximum number of concurrent users, plus the number of background processes, plus approximately 10% for recursive sessions.

Oracle uses the default value of this parameter as its minimum. Values between 1 and the default do not trigger errors, but Oracle ignores them and uses the default instead.

The default values of the ENQUEUE_RESOURCES and TRANSACTIONS parameters are derived from SESSIONS. Therefore, if you increase the value of SESSIONS, you should consider whether to adjust the values of ENQUEUE_RESOURCES and TRANSACTIONS as well. (Note that ENQUEUE_RESOURCES is obsolete as of Oracle Database 10g release 2 (10.2).)

In a shared server environment, the value of PROCESSES can be quite small. Therefore, Oracle recommends that you adjust the value of SESSIONS to approximately 1.1 * total number of connections.

Sunday, December 5, 2010

viewing concurrent connections in oracle Database

viewing concurrent connections in oracle Database
------------------------------------------------

(Viewing Licensing Limits and Current Values)


select * FROM v$license;


SQL> desc v$license;
Name Null? Type
----------------------------------------- -------- ----------------
SESSIONS_MAX NUMBER
SESSIONS_WARNING NUMBER
SESSIONS_CURRENT NUMBER
SESSIONS_HIGHWATER NUMBER
USERS_MAX NUMBER
CPU_COUNT_CURRENT NUMBER
CPU_CORE_COUNT_CURRENT NUMBER
CPU_SOCKET_COUNT_CURRENT NUMBER
CPU_COUNT_HIGHWATER NUMBER
CPU_CORE_COUNT_HIGHWATER NUMBER
CPU_SOCKET_COUNT_HIGHWATER NUMBER

SQL>


SQL> SELECT RPAD (c.NAME || ':', 11)
|| RPAD (' current logons=' || (TO_NUMBER (b.sessions_current) - 1),
20)
|| 'cumulative logons='
|| RPAD (SUBSTR (a.VALUE, 1, 10), 10)
|| 'highwater mark='
|| b.sessions_highwater information
FROM v$sysstat a, v$license b, v$database c
WHERE a.NAME = 'logons cumulative' ;




---about number of users

SELECT COUNT(*) FROM dba_users;

---also session can see from here

select count(sid) from v$session

Tuesday, August 24, 2010

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Solution:
================
Your problem is solved after bellows two step


1. Regestration you Listener from Dynamically to static like below.

Make a listerner.ora for you database with your DB Information.


SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = H:\oracle\product\10.2.0\db_1)
(SID_NAME = orcl)
)
)

LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hp-bb88068f9f5d)(PORT = 1521))
)


then stop and start the listener.

The data within the SID_DESC section is written about instance is referred to as 'static information' for that instance.
Note : [all static information in the listener.ora is overwritten when the instance is dynamically registered with the listener.]




2. increase PROCESSES parameter in the initSID.ora file or in spfile like

see your alert_log file first.

SQL> alter system set processes = 400 scope=spfile;

reboot the database.

Monday, August 16, 2010

What is SQL injection ? How is Oracle application subject to SQL injection attacks ?

What is SQL injection ? How is Oracle application subject to SQL injection attacks ?
==============================================================================

[This is just for creating awareness about SQL Injection attacks]

SQL injection is a basic attack used to either gain unauthorized access to a database or to retrieve
information directly from the database.SQL injection attacks are simple in nature – an attacker passes
string input to an application in hopes manipulating the SQL statement to his or her advantage.

Oracle may provide stronger and more inherent protections against SQL injection attacks than other
database, however applications without proper defenses against these types of attacks can be vulnerable.
Despite these advantages many web applications are vulnerable to SQL injection attacks.

Oracle has generally faired well against SQL injection attacks as there is no multiple SQL statement
support (SQL Server and PostgreSQL), no EXECUTE statement (SQL Server), and no INTO OUTFILE
function (MySQL).
Also, use of bind variables in Oracle environments for performance reasons provides
strong protection against SQL injection attacks.

Generally Four Types of SQL Injection Attack. these are :-

1. SQL Manipulation
2. Code Injection
3. Function Call Injection
4. Buffer Overflows

All of these types of SQL injection are valid for databases including SQL Server, DB2, MySQL, PostgreSQL and Oracle.

Among these SQL Manipulation is more important. and vulnerable. i just want to describe it Here.

1. SQL Manipulation:
======================
SQL Injection attack is SQL manipulation. The attacker attempts to modify the existing SQL statement
by adding elements to the WHERE clause or extending the SQL statement with set operators
like UNION, INTERSECT, or MINUS.

In this example in first query I add in where clause just "OR 'A'='A' " then what happens.

In second query i add union clause just
" UNION
SELECT USERNAME FROM USER_MAS
WHERE USERNAME LIKE '%' "


Example:-

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 16 15:09:49 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn halim/halim@test107
Connected.
SQL>
SQL>
SQL> create table user_mas (username varchar2(20),password varchar2(20));

Table created.

SQL> insert into user_mas values ('HALIM','TEST');

1 row created.

SQL> insert into user_mas values ('SCOTT','TEST1');

1 row created.

SQL> insert into user_mas values ('ADMIN','TEST2');

1 row created.

SQL> COMMIT;

Commit complete.

SQL>
SQL> SELECT * FROM USER_MAS;

USERNAME PASSWORD
-------------------- --------------------
HALIM TEST
SCOTT TEST1
ADMIN TEST2

SQL>
SQL>
SQL> SELECT USERNAME FROM USER_MAS
2 WHERE USERNAME='HALIM' AND PASSWORD='TEST';

USERNAME
--------------------
HALIM

SQL>
SQL> ED
Wrote file afiedt.buf

1 SELECT USERNAME FROM USER_MAS
2* WHERE USERNAME='HALIM' AND PASSWORD='TEST' OR 'A'='A'
SQL> /

USERNAME
--------------------
HALIM
SCOTT
ADMIN

SQL>
SQL>
SQL> ED
Wrote file afiedt.buf

1 SELECT USERNAME FROM USER_MAS
2* WHERE USERNAME='HALIM' AND PASSWORD='TEST'
SQL> /

USERNAME
--------------------
HALIM

SQL>
SQL> ED
Wrote file afiedt.buf

1 SELECT USERNAME FROM USER_MAS
2 WHERE USERNAME='HALIM' AND PASSWORD='TEST'
3 UNION
4 SELECT USERNAME FROM USER_MAS
5* WHERE USERNAME LIKE '%'
6 /

USERNAME
--------------------
ADMIN
HALIM
SCOTT

SQL>
SQL>
SQL>
SQL>



PLSQL Codes subject to SQL injection attacks
===================================

SQL statements can be executed four different ways in PL/SQL –
a) embedded SQL,
b) cursors,
c) execute immediate statements,
d) the DBMS_SQL package.

Embedded SQL statements and static cursors only allow bind variables, this is not subject to SQL injection attacks.
But following are subject to SQL injection attacks.

B) dynamic cursors.(ref cursor) [To prevent SQL INJECTION attack bind variables should always be used]

CREATE OR REPLACE PROCEDURE pro_sample(P_empno IN VARCHAR2)
AS
sql_s VARCHAR2;
BEGIN
sql_s := 'SELECT * FROM emp WHERE empno = ''' || p_empno || '''';
OPEN cursor_states FOR sql_s;
LOOP FETCH cursor_states
INTO rec_state
EXIT WHEN cursor_states%NOTFOUND;
END LOOP;
CLOSE cursor_status;
END;

This can subject to attack by Sql injection

C) EXECUTE IMMEDIATE [To prevent SQL INJECTION attack bind variables should always be used]

D) DBMS_SQL package (for dynamic SQL statements)
[To prevent SQL INJECTION attack bind variables should always be used]



To prevent SQL injection and to improve application performance, bind variables should always be used.

Using LogMiner to Analyze Redo Log Files or archivelog files in oracle 10g

Using LogMiner to Analyze Redo Log Files or archivelog files in oracle 10g
=====================================================
=====================================================


Enables you to query online redolog files and archived log files through a SQL interface.
Redo log and archived log files contain information about the history of activity on a database.
such as

1. you can find what errors made at the application level. These might include
errors such as those where the wrong rows were deleted because of incorrect
values in a WHERE clause, rows were updated with incorrect values, the wrong
index was dropped, and so forth.

2. Using LogMiner to Track Changes Made by a Specific User.

3. you can use it as a powerful audit tool.

4. You can determine which tables get the most updates and inserts.
5. Performance tuning and capacity planning through trend analysis.
6. Determining what actions you would have to take to perform fine-grained recovery at the transaction level
7. LogMiner can be used to track any data manipulation language (DML)
and data definition language (DDL) statements executed on the Database



Extract a LogMiner Dictionary

To use LogMiner, you must supply it with a dictionary by doing one of the following:

1. Specify use of the online catalog by using the DICT_FROM_ONLINE_CATALOG option when you start Log Miner.
(This is very convenient. i use it in the following )

2. Extract database dictionary information to the redo log files.
(When table structure change)

3. Extract database dictionary information to a flat file.



Steps are :=



1. Enable SUPPLEMENTAL Log to Database.
=======================================

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;


2. Install logminer package (if not installed by default installed) from following path
============================================
ORACLE_HOME/rdbms/admin/dbmslm.sql



3. Determine which redo log file was most recently archived.
===============================================

SQL> SELECT NAME FROM V$ARCHIVED_LOG
WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);

------H:\FLASH_BACK\STLBAS\ARCHIVELOG\2010_08_16\O1_MF_1_2556_66KP9J6T_.ARC

4. Specify the list of redo log files to be analyzed.
=========================================

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
LOGFILENAME => 'H:\FLASH_BACK\STLBAS\ARCHIVELOG\2010_08_16\O1_MF_1_2556_66KP9J6T_.ARC', -
OPTIONS => DBMS_LOGMNR.NEW);

5. Start LogMiner.
========================

Start LogMiner and specify the dictionary to use.

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( -
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

OR
Start LogMiner by specifying the dictionary to use and the COMMITTED_DATA_ONLY option.

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( -
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
DBMS_LOGMNR.COMMITTED_DATA_ONLY);

OR

Start LogMiner by specifying the dictionary to use and the COMMITTED_DATA_ONLY and PRINT_PRETTY_SQL options.

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(-
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
DBMS_LOGMNR.COMMITTED_DATA_ONLY + -
DBMS_LOGMNR.PRINT_PRETTY_SQL);


6. Query the V$LOGMNR_CONTENTS view.
==================================


SQL> SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID,
SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE username IN ('HALIM');

7. End the LogMiner session.
==============================

SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();






Example:=

The easiest way to examine the modification history of a database is to mine at the source database and use the
online catalog to translate the redo log files. This example shows how to do the simplest analysis using LogMiner.

This example finds all modifications that are contained in the last archived redo log generated by the database
(assuming that the database is not an Oracle Real Application Clusters database)

=====================================
======================================


SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 16 12:11:54 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn sys@test107 as sysdba
Enter password:
Connected.
SQL>
SQL>
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

SQL>
SQL>
SQL>
SQL>
SQL> grant dba to halim identified by halim ;

Grant succeeded.

SQL>
SQL> conn halim/halim@test107
Connected.
SQL>
SQL>
SQL> create table halim_table (id number,name varchar2(20));

Table created.

SQL>
SQL> insert into halim_table select rownum,rownum||'halim' from all_objects where rownum<=10;

10 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> delete from halim_table;

10 rows deleted.

SQL> rollback;

Rollback complete.

SQL> update halim_table
2 set name='change_halim' ;

10 rows updated.

SQL> commit;

Commit complete.

SQL>
SQL>
SQL> alter system switch logfile;

System altered.

SQL>
SQL>
SQL>
SQL> SELECT NAME FROM V$ARCHIVED_LOG
2 WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);

NAME
--------------------------------------------------------------------------------
H:\FLASH_BACK\STLBAS\ARCHIVELOG\2010_08_16\O1_MF_1_2556_66KP9J6T_.ARC

SQL>
SQL>
SQL>
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
> LOGFILENAME => 'H:\FLASH_BACK\STLBAS\ARCHIVELOG\2010_08_16\O1_MF_1_2556_66KP9J6T_.ARC', -
> OPTIONS => DBMS_LOGMNR.NEW);

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( -
> OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>
SQL> set linesize 1000
SQL> set pagesize 1000
SQL>
SQL>
SQL>
SQL>
===================================================================
===================================================================
Now you find all activity of above.
You can find here Original sql as well as Undo sql, you can undo the change by undo sql
===================================================================
===================================================================

SQL>
SQL> SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID,
2 SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE username IN ('HALIM');

USR XID
------------------------------ --------------------------------------------------------------------------------------------------------------------------
SQL_REDO
-----------------------------------------------------------------------
SQL_UNDO
-----------------------------------------------------------------------------------------------------------


HALIM 4.26.6403
create table halim_table (id number,name varchar2(20));


HALIM 7.21.8139
set transaction read write;


HALIM 7.21.8139
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('1','1halim');
delete from "HALIM"."HALIM_TABLE" where "ID" = '1' and "NAME" = '1halim' and ROWID = 'AAATjnAAEAACJW0AAA';

HALIM 7.21.8139
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('2','2halim');
delete from "HALIM"."HALIM_TABLE" where "ID" = '2' and "NAME" = '2halim' and ROWID = 'AAATjnAAEAACJW0AAB';

HALIM 7.21.8139
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('3','3halim');
delete from "HALIM"."HALIM_TABLE" where "ID" = '3' and "NAME" = '3halim' and ROWID = 'AAATjnAAEAACJW0AAC';

HALIM 7.21.8139
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('4','4halim');
delete from "HALIM"."HALIM_TABLE" where "ID" = '4' and "NAME" = '4halim' and ROWID = 'AAATjnAAEAACJW0AAD';

HALIM 7.21.8139
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('5','5halim');
delete from "HALIM"."HALIM_TABLE" where "ID" = '5' and "NAME" = '5halim' and ROWID = 'AAATjnAAEAACJW0AAE';

HALIM 7.21.8139
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('6','6halim');
delete from "HALIM"."HALIM_TABLE" where "ID" = '6' and "NAME" = '6halim' and ROWID = 'AAATjnAAEAACJW0AAF';

HALIM 7.21.8139
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('7','7halim');
delete from "HALIM"."HALIM_TABLE" where "ID" = '7' and "NAME" = '7halim' and ROWID = 'AAATjnAAEAACJW0AAG';

HALIM 7.21.8139
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('8','8halim');
delete from "HALIM"."HALIM_TABLE" where "ID" = '8' and "NAME" = '8halim' and ROWID = 'AAATjnAAEAACJW0AAH';

HALIM 7.21.8139
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('9','9halim');
delete from "HALIM"."HALIM_TABLE" where "ID" = '9' and "NAME" = '9halim' and ROWID = 'AAATjnAAEAACJW0AAI';

HALIM 7.21.8139
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('10','10halim');
delete from "HALIM"."HALIM_TABLE" where "ID" = '10' and "NAME" = '10halim' and ROWID = 'AAATjnAAEAACJW0AAJ';

HALIM 7.21.8139
commit;


HALIM 2.34.7758
set transaction read write;


HALIM 2.34.7758
delete from "HALIM"."HALIM_TABLE" where "ID" = '1' and "NAME" = '1halim' and ROWID = 'AAATjnAAEAACJW0AAA';
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('1','1halim');

HALIM 2.34.7758
delete from "HALIM"."HALIM_TABLE" where "ID" = '2' and "NAME" = '2halim' and ROWID = 'AAATjnAAEAACJW0AAB';
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('2','2halim');

HALIM 2.34.7758
delete from "HALIM"."HALIM_TABLE" where "ID" = '3' and "NAME" = '3halim' and ROWID = 'AAATjnAAEAACJW0AAC';
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('3','3halim');

HALIM 2.34.7758
delete from "HALIM"."HALIM_TABLE" where "ID" = '4' and "NAME" = '4halim' and ROWID = 'AAATjnAAEAACJW0AAD';
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('4','4halim');

HALIM 2.34.7758
delete from "HALIM"."HALIM_TABLE" where "ID" = '5' and "NAME" = '5halim' and ROWID = 'AAATjnAAEAACJW0AAE';
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('5','5halim');

HALIM 2.34.7758
delete from "HALIM"."HALIM_TABLE" where "ID" = '6' and "NAME" = '6halim' and ROWID = 'AAATjnAAEAACJW0AAF';
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('6','6halim');

HALIM 2.34.7758
delete from "HALIM"."HALIM_TABLE" where "ID" = '7' and "NAME" = '7halim' and ROWID = 'AAATjnAAEAACJW0AAG';
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('7','7halim');

HALIM 2.34.7758
delete from "HALIM"."HALIM_TABLE" where "ID" = '8' and "NAME" = '8halim' and ROWID = 'AAATjnAAEAACJW0AAH';
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('8','8halim');

HALIM 2.34.7758
delete from "HALIM"."HALIM_TABLE" where "ID" = '9' and "NAME" = '9halim' and ROWID = 'AAATjnAAEAACJW0AAI';
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('9','9halim');

HALIM 2.34.7758
delete from "HALIM"."HALIM_TABLE" where "ID" = '10' and "NAME" = '10halim' and ROWID = 'AAATjnAAEAACJW0AAJ';
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('10','10halim');

HALIM 2.34.7758
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('10','10halim');


HALIM 2.34.7758
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('9','9halim');


HALIM 2.34.7758
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('8','8halim');


HALIM 2.34.7758
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('7','7halim');


HALIM 2.34.7758
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('6','6halim');


HALIM 2.34.7758
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('5','5halim');


HALIM 2.34.7758
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('4','4halim');


HALIM 2.34.7758
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('3','3halim');


HALIM 2.34.7758
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('2','2halim');


HALIM 2.34.7758
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('1','1halim');


HALIM 2.34.7758
rollback;


HALIM 10.20.11872
set transaction read write;


HALIM 10.20.11872
update "HALIM"."HALIM_TABLE" set "NAME" = 'change_halim' where "NAME" = '1halim' and ROWID = 'AAATjnAAEAACJW0AAA';
update "HALIM"."HALIM_TABLE" set "NAME" = '1halim' where "NAME" = 'change_halim' and ROWID = 'AAATjnAAEAACJW0AAA';

HALIM 10.20.11872
update "HALIM"."HALIM_TABLE" set "NAME" = 'change_halim' where "NAME" = '2halim' and ROWID = 'AAATjnAAEAACJW0AAB';
update "HALIM"."HALIM_TABLE" set "NAME" = '2halim' where "NAME" = 'change_halim' and ROWID = 'AAATjnAAEAACJW0AAB';

HALIM 10.20.11872
update "HALIM"."HALIM_TABLE" set "NAME" = 'change_halim' where "NAME" = '3halim' and ROWID = 'AAATjnAAEAACJW0AAC';
update "HALIM"."HALIM_TABLE" set "NAME" = '3halim' where "NAME" = 'change_halim' and ROWID = 'AAATjnAAEAACJW0AAC';

HALIM 10.20.11872
update "HALIM"."HALIM_TABLE" set "NAME" = 'change_halim' where "NAME" = '4halim' and ROWID = 'AAATjnAAEAACJW0AAD';
update "HALIM"."HALIM_TABLE" set "NAME" = '4halim' where "NAME" = 'change_halim' and ROWID = 'AAATjnAAEAACJW0AAD';

HALIM 10.20.11872
update "HALIM"."HALIM_TABLE" set "NAME" = 'change_halim' where "NAME" = '5halim' and ROWID = 'AAATjnAAEAACJW0AAE';
update "HALIM"."HALIM_TABLE" set "NAME" = '5halim' where "NAME" = 'change_halim' and ROWID = 'AAATjnAAEAACJW0AAE';

HALIM 10.20.11872
update "HALIM"."HALIM_TABLE" set "NAME" = 'change_halim' where "NAME" = '6halim' and ROWID = 'AAATjnAAEAACJW0AAF';
update "HALIM"."HALIM_TABLE" set "NAME" = '6halim' where "NAME" = 'change_halim' and ROWID = 'AAATjnAAEAACJW0AAF';

HALIM 10.20.11872
update "HALIM"."HALIM_TABLE" set "NAME" = 'change_halim' where "NAME" = '7halim' and ROWID = 'AAATjnAAEAACJW0AAG';
update "HALIM"."HALIM_TABLE" set "NAME" = '7halim' where "NAME" = 'change_halim' and ROWID = 'AAATjnAAEAACJW0AAG';

HALIM 10.20.11872
update "HALIM"."HALIM_TABLE" set "NAME" = 'change_halim' where "NAME" = '8halim' and ROWID = 'AAATjnAAEAACJW0AAH';
update "HALIM"."HALIM_TABLE" set "NAME" = '8halim' where "NAME" = 'change_halim' and ROWID = 'AAATjnAAEAACJW0AAH';

HALIM 10.20.11872
update "HALIM"."HALIM_TABLE" set "NAME" = 'change_halim' where "NAME" = '9halim' and ROWID = 'AAATjnAAEAACJW0AAI';
update "HALIM"."HALIM_TABLE" set "NAME" = '9halim' where "NAME" = 'change_halim' and ROWID = 'AAATjnAAEAACJW0AAI';

HALIM 10.20.11872
update "HALIM"."HALIM_TABLE" set "NAME" = 'change_halim' where "NAME" = '10halim' and ROWID = 'AAATjnAAEAACJW0AAJ';
update "HALIM"."HALIM_TABLE" set "NAME" = '10halim' where "NAME" = 'change_halim' and ROWID = 'AAATjnAAEAACJW0AAJ';

HALIM 10.20.11872
commit;



70 rows selected.

SQL>
SQL>
SQL>
SQL>
SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>

Cheers.............

Sunday, August 15, 2010

Audit in oracle Database 10g

Audit in oracle Database 10g
===================================
===========================================
Audit_trail =DB
=======================================

 SQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 15 10:32:16 2010  
 Copyright (c) 1982, 2005, Oracle. All rights reserved.  
 SQL> spool c:/oracle_audit_10g.txt  
 SQL>  
 SQL> set serveroutput on  
 SQL> set sqlprompt 'Halim@dba>';  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>show parameter audit  
 NAME                 TYPE    VALUE               
 ------------------------------------ ----------- ------------------------------   
 audit_file_dest           string   H:\ORACLE\PRODUCT\10.2.0\ADMIN   
 \HALIM\ADUMP            
 audit_sys_operations         boolean   FALSE               
 audit_trail             string   NONE                
 Halim@dba>  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;  
 System altered.  
 Halim@dba>  
 Halim@dba>shutdown immediate  
 Database closed.  
 Database dismounted.  
 ORACLE instance shut down.  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>startup  
 ORACLE instance started.  
 Total System Global Area 591396864 bytes                      
 Fixed Size         1250308 bytes                      
 Variable Size       205523964 bytes                      
 Database Buffers     377487360 bytes                      
 Redo Buffers        7135232 bytes                      
 Database mounted.  
 Database opened.  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>conn sys@halim as sysdba  
 Connected.  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>CREATE USER audit_test IDENTIFIED BY password  
 2  DEFAULT TABLESPACE users  
 3  TEMPORARY TABLESPACE temp  
 4  QUOTA UNLIMITED ON users;  
 User created.  
 Halim@dba>  
 Halim@dba>GRANT connect TO audit_test;  
 Grant succeeded.  
 Halim@dba>GRANT create table, create procedure TO audit_test;  
 Grant succeeded.  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>conn / as sysdba  
 Connected.  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>AUDIT ALL BY audit_test BY ACCESS;  
 Audit succeeded.  
 Halim@dba>AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY audit_test BY ACCESS;  
 Audit succeeded.  
 Halim@dba>AUDIT EXECUTE PROCEDURE BY audit_test BY ACCESS;  
 Audit succeeded.  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>conn audit_test/passowrd  
 ERROR:  
 ORA-01017: invalid username/password; logon denied   
 Warning: You are no longer connected to ORACLE.  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>conn audit_test/password@halim  
 Connected.  
 Halim@dba>  
 Halim@dba>CREATE TABLE test_tab (  
 2  id NUMBER  
 3 );  
 Table created.  
 Halim@dba>  
 Halim@dba>INSERT INTO test_tab (id) VALUES (1);  
 1 row created.  
 Halim@dba>UPDATE test_tab SET id = id;  
 1 row updated.  
 Halim@dba>SELECT * FROM test_tab;  
 ID                                     
 ----------                                     
 1                                     
 Halim@dba>DELETE FROM test_tab;  
 1 row deleted.  
 Halim@dba>  
 Halim@dba>DROP TABLE test_tab;  
 Table dropped.  
 Halim@dba>  
 Halim@dba>set pagesize 1000  
 Halim@dba>set linesize 1000  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>COLUMN username FORMAT A10  
 Halim@dba>COLUMN owner  FORMAT A10  
 Halim@dba>COLUMN obj_name FORMAT A10  
 Halim@dba>COLUMN extended_timestamp FORMAT A35  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>SELECT username,  
 2     extended_timestamp,  
 3     owner,  
 4     obj_name,  
 5     action_name  
 6 FROM  dba_audit_trail  
 7 WHERE owner = 'AUDIT_TEST'  
 8 ORDER BY timestamp;  
 USERNAME  EXTENDED_TIMESTAMP         OWNER   OBJ_NAME  ACTION_NAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
 ---------- ----------------------------------- ---------- ---------- ----------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
 AUDIT_TEST 15-AUG-10 10.47.39.218000 AM +06:00 AUDIT_TEST TEST_TAB  CREATE TABLE                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
 AUDIT_TEST 15-AUG-10 10.47.39.234000 AM +06:00 AUDIT_TEST TEST_TAB  INSERT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
 AUDIT_TEST 15-AUG-10 10.47.39.234000 AM +06:00 AUDIT_TEST TEST_TAB  UPDATE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
 AUDIT_TEST 15-AUG-10 10.47.39.234000 AM +06:00 AUDIT_TEST TEST_TAB  SELECT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
 AUDIT_TEST 15-AUG-10 10.47.39.265000 AM +06:00 AUDIT_TEST TEST_TAB  DELETE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
 AUDIT_TEST 15-AUG-10 10.47.43.843000 AM +06:00 AUDIT_TEST TEST_TAB  DROP TABLE                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
 6 rows selected.  
 Halim@dba>  
 Halim@dba>create view audit_test.view_audit as select * from dba_audit_trail;  
 create view audit_test.view_audit as select * from dba_audit_trail  
 *  
 ERROR at line 1:  
 ORA-01031: insufficient privileges   
 Halim@dba>grant create view to audit_test;  
 Grant succeeded.  
 Halim@dba>create view audit_test.view_audit as select * from dba_audit_trail;  
 create view audit_test.view_audit as select * from dba_audit_trail  
 *  
 ERROR at line 1:  
 ORA-01031: insufficient privileges   
 Halim@dba>grant select on dba_audit_trail to audit_test;  
 Grant succeeded.  
 Halim@dba>create view audit_test.view_audit as select * from dba_audit_trail;  
 View created.  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>desc view_audit  
 Halim@dba>  
 Halim@dba>conn / as sysdba  
 Connected.  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>AUDIT INSERT, UPDATE, DELETE ON sys.aud$ BY ACCESS;  
 Audit succeeded.  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>conn / as sysdba  
 Connected.  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>noaudit all by audit_test;  
 Noaudit succeeded.  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>conn / as sysdba  
 Connected.  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>noaudit all  
 2 /  
 Noaudit succeeded.  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>NOAUDIT session;  
 Noaudit succeeded.  
 Halim@dba>NOAUDIT session BY scott;  
 Noaudit succeeded.  
 Halim@dba>NOAUDIT DELETE ANY TABLE;  
 Noaudit succeeded.  
 Halim@dba>NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE,  
 2   EXECUTE PROCEDURE;  
 Noaudit succeeded.  
 Halim@dba>DELETE FROM SYS.AUD$;  
 261 rows deleted.  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>commit;  
 Commit complete.  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>  
 ============================================================  
 ============================================================  
 Fine Grained Auditing (FGA)  
 ============================================================  
 ============================================================  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>conn audit_test/password  
 Connected.  
 Halim@dba>  
 Halim@dba>CREATE TABLE emp (  
 2  empno   NUMBER(4) NOT NULL,  
 3  ename   VARCHAR2(10),  
 4  job    VARCHAR2(9),  
 5  mgr    NUMBER(4),  
 6  hiredate DATE,  
 7  sal    NUMBER(7,2),  
 8  comm   NUMBER(7,2),  
 9  deptno  NUMBER(2)  
 10 );  
 Table created.  
 Halim@dba>  
 Halim@dba>INSERT INTO emp (empno, ename, sal) VALUES (9999, 'Tim', 1);  
 1 row created.  
 Halim@dba>INSERT INTO emp (empno, ename, sal) VALUES (9999, 'Larry', 50001);  
 1 row created.  
 Halim@dba>COMMIT;  
 Commit complete.  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>conn / as sysdba  
 Connected.  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>BEGIN  
 2  DBMS_FGA.add_policy(  
 3   object_schema  => 'AUDIT_TEST',  
 4   object_name   => 'EMP',  
 5   policy_name   => 'SALARY_CHK_AUDIT',  
 6   audit_condition => 'SAL > 50000',  
 7   audit_column  => 'SAL');  
 8 END;  
 9 /  
 PL/SQL procedure successfully completed.  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>CONN audit_test/password  
 Connected.  
 Halim@dba>SELECT sal FROM emp WHERE ename = 'Tim';  
 SAL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
 ----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
 1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
 Halim@dba>SELECT sal FROM emp WHERE ename = 'Larry';  
 SAL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
 ----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
 50001                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
 Halim@dba>  
 Halim@dba>  
 Halim@dba>conn / as sysdba  
 Connected.  
 Halim@dba>  
 Halim@dba>SELECT sql_text  
 2 FROM  dba_fga_audit_trail;  
 SQL_TEXT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
 --------------------------------------------  
 SELECT sal FROM emp WHERE ename = 'Larry'                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>conn / as sysdba  
 Connected.  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>truncate table fga_log$;  
 Table truncated.  
 Halim@dba>SELECT sql_text FROM dba_fga_audit_trail;  
 no rows selected  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>BEGIN  
 2  DBMS_FGA.add_policy(  
 3   object_schema  => 'AUDIT_TEST',  
 4   object_name   => 'EMP',  
 5   policy_name   => 'SAL_AUDIT',  
 6   audit_condition => NULL, -- Equivalent to TRUE  
 7   audit_column  => 'SAL',  
 8   statement_types => 'SELECT,INSERT,UPDATE,DELETE');  
 9 END;  
 10 /  
 PL/SQL procedure successfully completed.  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>conn audit_test/password  
 Connected.  
 Halim@dba>  
 Halim@dba>SELECT * FROM emp WHERE empno = 9998;  
 no rows selected  
 Halim@dba>INSERT INTO emp (empno, ename, sal) VALUES (9998, 'Bill', 1);  
 1 row created.  
 Halim@dba>UPDATE emp SET sal = 10 WHERE empno = 9998;  
 1 row updated.  
 Halim@dba>DELETE emp WHERE empno = 9998;  
 1 row deleted.  
 Halim@dba>ROLLBACK;  
 Rollback complete.  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>conn / as sysdba  
 Connected.  
 Halim@dba>  
 Halim@dba>SELECT sql_text FROM dba_fga_audit_trail;  
 SQL_TEXT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
 --------------------------------------------------------------------  
 SELECT * FROM emp WHERE empno = 9998                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
 INSERT INTO emp (empno, ename, sal) VALUES (9998, 'Bill', 1)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
 UPDATE emp SET sal = 10 WHERE empno = 9998                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
 DELETE emp WHERE empno = 9998                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
 Halim@dba>  
 Halim@dba>  
 Halim@dba>  
 Halim@dba>conn / as sysdba  
 Connected.  
 Halim@dba>  
 Halim@dba>SELECT sql_text FROM dba_fga_audit_trail;  
 SQL_TEXT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
 ---------------------------------------------------------------------  
 SELECT * FROM emp WHERE empno = 9998                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
 INSERT INTO emp (empno, ename, sal) VALUES (9998, 'Bill', 1)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
 UPDATE emp SET sal = 10 WHERE empno = 9998                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
 DELETE emp WHERE empno = 9998                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
 Halim@dba>BEGIN  
 2  DBMS_FGA.drop_policy(  
 3   object_schema  => 'AUDIT_TEST',  
 4   object_name   => 'EMP',  
 5   policy_name   => 'SAL_AUDIT');  
 6 END;  
 7 /  
 PL/SQL procedure successfully completed.  
 =============================================================================  
 Audit_trail in OS and XML  
 ============================================================================  
 SQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 15 11:43:47 2010  
 Copyright (c) 1982, 2005, Oracle. All rights reserved.  
 SQL> spool c:/audit_os.txt  
 SQL>  
 SQL>  
 SQL>   
 SQL> set sqlprompt 'Halim@dba#> ';  
 Halim@dba#>   
 Halim@dba#>   
 Halim@dba#> conn / as sysdba  
 Connected.  
 Halim@dba#>   
 Halim@dba#>   
 Halim@dba#> show parameter audit  
 NAME                 TYPE    VALUE               
 ------------------------------------ ----------- ------------------------------   
 audit_file_dest           string   H:\ORACLE\PRODUCT\10.2.0\ADMIN   
 \HALIM\ADUMP            
 audit_sys_operations         boolean   FALSE               
 audit_trail             string   DB                 
 Halim@dba#>   
 Halim@dba#>   
 Halim@dba#> alter system set audit_trail=os scope=spfile;  
 System altered.  
 Halim@dba#>   
 Halim@dba#> shutdown immediate  
 Database closed.  
 Database dismounted.  
 ORACLE instance shut down.  
 Halim@dba#>   
 Halim@dba#>   
 Halim@dba#> startup  
 ORACLE instance started.  
 Total System Global Area 591396864 bytes                      
 Fixed Size         1250308 bytes                      
 Variable Size       209718268 bytes                      
 Database Buffers     373293056 bytes                      
 Redo Buffers        7135232 bytes                      
 Database mounted.  
 Database opened.  
 Halim@dba#>   
 Halim@dba#>   
 Halim@dba#>   
 Halim@dba#> show parameter audit  
 NAME                 TYPE    VALUE               
 ------------------------------------ ----------- ------------------------------   
 audit_file_dest           string   H:\ORACLE\PRODUCT\10.2.0\ADMIN   
 \HALIM\ADUMP            
 audit_sys_operations         boolean   FALSE               
 audit_trail             string   OS                 
 Halim@dba#>   
 Halim@dba#>   
 Halim@dba#>   
 Halim@dba#> audit all by audit_test;  
 Audit succeeded.  
 Halim@dba#> create table halim (id number,name varchar2(20));  
 Table created.  
 Halim@dba#> insert into halim  
 2 select rownum, rownum||' '||'halim' from all_objects where rownum<=100;  
 100 rows created.  
 Halim@dba#> commit ;  
 Commit complete.  
 Halim@dba#>   
 Halim@dba#> conn audit_test/password@halim  
 Connected.  
 Halim@dba#>   
 Halim@dba#>   
 Halim@dba#> create table halim (id number,name varchar2(20));  
 Table created.  
 Halim@dba#> insert into halim select rownum, rownum||' '||'halim' from all_objects where rownum<=100;  
 100 rows created.  
 Halim@dba#> commit;  
 Commit complete.  
 Halim@dba#> conn / as sysdba  
 Connected.  
 Halim@dba#>   
 Halim@dba#>   
 Halim@dba#>   
 Halim@dba#> conn system/system  
 Connected.  
 Halim@dba#>   
 Halim@dba#>   
 Halim@dba#> audit connect;  
 Audit succeeded.  
 Halim@dba#>   
 Halim@dba#>   
 Halim@dba#>   
 Halim@dba#>   
 Halim@dba#>   
 Halim@dba#> grant dba to test identified by test;  
 Grant succeeded.  
 Halim@dba#>   
 Halim@dba#> conn test/test@halim  
 Connected.  
 Halim@dba#> conn test/test  
 Connected.  
 Halim@dba#>   
 Halim@dba#> create table t (id number) ;  
 Table created.  
 Halim@dba#> conn / as sysdba  
 Connected.  
 Halim@dba#> audit select table,insert table ,update table by test;  
 Audit succeeded.  
 Halim@dba#>   
 Halim@dba#> conn test/test  
 Connected.  
 Halim@dba#>   
 Halim@dba#>   
 Halim@dba#> select * from t;  
 no rows selected  
 Halim@dba#>   
 Halim@dba#>   
 Halim@dba#>   
 Halim@dba#> prompt 'Its write on Event Viewer/applicate log of OS'  
 'Its write on Event Viewer/applicate log of OS' only for some windows platform   
 ======================================================  
 Halim@dba#>   
 Halim@dba#>   
 Halim@dba#>   
 Halim@dba#>   
 Halim@dba#>   
 Halim@dba#> alter system set audit_trail=XML scope=spfile;  
 System altered.  
 Halim@dba#> shutdown immediate  
 ORA-01031: insufficient privileges  
 Halim@dba#>   
 Halim@dba#> conn / as sysdba  
 Connected.  
 Halim@dba#>   
 Halim@dba#> shutdown immdiate  
 SP2-0717: illegal SHUTDOWN option  
 Halim@dba#> shutdown immediate  
 Database closed.  
 Database dismounted.  
 ORACLE instance shut down.  
 Halim@dba#>   
 Halim@dba#>   
 Halim@dba#> startup  
 ORACLE instance started.  
 Total System Global Area 591396864 bytes                      
 Fixed Size         1250308 bytes                      
 Variable Size       213912572 bytes                      
 Database Buffers     369098752 bytes                      
 Redo Buffers        7135232 bytes                      
 Database mounted.  
 Database opened.  
 Halim@dba#>   
 Halim@dba#>   
 Halim@dba#>   
 Halim@dba#> show parameter audit  
 NAME                 TYPE    VALUE               
 ------------------------------------ ----------- ------------------------------   
 audit_file_dest           string   H:\ORACLE\PRODUCT\10.2.0\ADMIN   
 \HALIM\ADUMP            
 audit_sys_operations         boolean   FALSE               
 audit_trail             string   XML                
 Halim@dba#>   
 Halim@dba#>   
 Halim@dba#> audit select table,insert table ,update table by test;  
 Audit succeeded.  
 Halim@dba#>   
 Halim@dba#> insert into t select rownum from all_objects where rownum<=100;  
 100 rows created.  
 Halim@dba#> commit;  
 Commit complete.  
 Halim@dba#> select * from t;  
 Halim@dba#>   
 Halim@dba#> conn / as sysdba  
 Connected.  
 Halim@dba#>   
 Halim@dba#>   
 Halim@dba#> conn test/test  
 Connected.  
 Halim@dba#> conn / as sysdba  
 Connected.  
 Halim@dba#>   
 Halim@dba#> audit select on test.t;  
 Audit succeeded.  
 Halim@dba#>   
 Halim@dba#> disconnect  
 Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production  
 With the Partitioning, OLAP and Data Mining options  
 Halim@dba#>   
 Halim@dba#> spool off  
 ==============================================================  
 audit_sys_operations = true   
 ==============================================================  
 Halim@dba#>   
 Halim@dba#>   
 Halim@dba#>   
 Halim@dba#> conn / as sysdba  
 Connected.  
 Halim@dba#>   
 Halim@dba#> show parameter audit  
 NAME                 TYPE    VALUE               
 ------------------------------------ ----------- ------------------------------   
 audit_file_dest           string   H:\ORACLE\PRODUCT\10.2.0\ADMIN   
 \HALIM\ADUMP            
 audit_sys_operations         boolean   FALSE               
 audit_trail             string   XML                
 Halim@dba#>   
 Halim@dba#>   
 Halim@dba#> alter system set audit_sys_operations=true scope=both;  
 alter system set audit_sys_operations=true scope=both  
 *  
 ERROR at line 1:  
 ORA-02095: specified initialization parameter cannot be modified   
 Halim@dba#> alter system set audit_sys_operations=true scope=spfile;  
 System altered.  
 Halim@dba#>   
 Halim@dba#> shutdown immediate  
 Database closed.  
 Database dismounted.  
 ORACLE instance shut down.  
 Halim@dba#>   
 Halim@dba#> startup  
 ORACLE instance started.  
 Total System Global Area 591396864 bytes                      
 Fixed Size         1250308 bytes                      
 Variable Size       218106876 bytes                      
 Database Buffers     364904448 bytes                      
 Redo Buffers        7135232 bytes                      
 Database mounted.  
 Database opened.  
 Halim@dba#> alter system set audit_trail=false scope=spfile;  
 System altered.  
 Halim@dba#> shutdown immediate  
 Database closed.  
 Database dismounted.  
 ORACLE instance shut down.  
 Halim@dba#>   
 Halim@dba#>   
 Halim@dba#> startup  
 ORACLE instance started.  
 Total System Global Area 591396864 bytes                      
 Fixed Size         1250308 bytes                      
 Variable Size       218106876 bytes                      
 Database Buffers     364904448 bytes                      
 Redo Buffers        7135232 bytes                      
 Database mounted.  
 Database opened.  
 Halim@dba#>   
 Halim@dba#>   
 Halim@dba#> show parameter audit  
 NAME                 TYPE    VALUE               
 ------------------------------------ ----------- ------------------------------   
 audit_file_dest           string   H:\ORACLE\PRODUCT\10.2.0\ADMIN   
 \HALIM\ADUMP            
 audit_sys_operations         boolean   TRUE                
 audit_trail             string   FALSE               
 Halim@dba#>   
 Halim@dba#> conn / as sysdba  
 Connected.  
 Halim@dba#>   
 Halim@dba#> grant dba to test;  
 Grant succeeded.  
 Halim@dba#>   
 Halim@dba#>   
 Halim@dba#> prompt 'See in windows Event Viewer/applecation'  
 'See in windows Event Viewer/applecation'  
 Halim@dba#>   
 Halim@dba#>   
 Halim@dba#>   
 Halim@dba#> spool off  

Monday, August 2, 2010

which situation EXIST condition is better than IN, and vice versa.

which situation EXIST condition is better than IN, and vice versa.

ANSWER:

Result of the subquery is small Then "IN" is typicaly more appropriate.
and
Result of the subquery is big/large/long Then "EXIST" is more appropriate.



suppose :-


select * from table_1
where id in (select Id from table_2)

Is normaly processed as:
select * from table_1 , ( select distinct y from table_2 ) t2
where table_1.x = table_2.y;

Means the subquery is evaluated, distinct'ed, indexed (or hashed or sorted) and then joined to the original table

and oposite is (EXIST)

select * from table_1 where exists ( select null from table_2 where table_2.y = table_1.x )
That is processed more like:
for x in ( select * from table_1 ) loop
if ( exists ( select null from table_2 where table_2.y = x.x ) then
OUTPUT THE RECORD end
end if
end loop

It always results in a full scan of Table_1 whereas the first query can make use of an index on Table_1(x).

Saturday, July 31, 2010

How to insert large amount of data in oracle table faster way

How to insert large amount of data in oracle table faster way
===========================================

For this can be Use Direct Load Insert
-------------------------------------------------------
Direct Load Insert is a faster way of running an INSERT statement.Direct Load Insert
differs from Conventional Insert in that it bypasses the buffer cache.

To use Direct Load Insert, add the APPEND hint to your INSERT statement. like below.....

INSERT /*+ APPEND*/
INTO target_table
SELECT * FROM source_table

Need to know for Direct load Insert
---------------------------------------------------
1. Data is appended to the table. Existing free space is not re-used.
2. Direct Load Insert only works for INSERT INTO .. SELECT .... Inserts
3. Direct Load Insert uses rollback segments to maintain indexes as the data is loaded
4. Direct Load Insert can be run with a NOLOGGING option making it even faster.
5. Direct Load Insert locks the table in exclusive mode. No other session can insert, update, or delete data, or maintain any indexes.
6. Referential Integrity (Foreign Key) constraints and triggers must be disabled before running Direct Path Insert
7. Direct Load Insert cannot occur on:
o Index Organised Tables
o Tables with LOB Columns
o Tables with Object Columns
o Clustered Tables


DEMO
=====


SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jul 31 12:28:13 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn basel2@test107
Enter password:
Connected.
SQL>
SQL>
SQL>
SQL> set timing on
SQL> set time on
12:28:41 SQL>
12:28:42 SQL>
12:28:42 SQL> create table direct_load_insert as select * from TABLE_OF_VIEW_BACKUP
12:30:57 2 where brancd=0 ;

Table created.

Elapsed: 00:00:04.03
12:31:28 SQL>
12:31:31 SQL> desc direct_load_insert
Name Null? Type
----------------------------------------- -------- ----------------------------
BRANCD VARCHAR2(3)
ACTYPE VARCHAR2(3)
ACTNUM VARCHAR2(12)
CURBAL NUMBER
LONCON VARCHAR2(3)
ACTTIT VARCHAR2(60)
CUSCOD VARCHAR2(10)
SHDESC VARCHAR2(10)
OPNDAT DATE
EXPDAT DATE
SECURITY_BAL NUMBER
SECURITY_TYPE VARCHAR2(4000)
VALDAT DATE
CATGRY VARCHAR2(1)
VALPRD NUMBER(4)
LCAAMT NUMBER(16,3)
REMAMT NUMBER(16,3)
BANCOD VARCHAR2(3)
CMPIND VARCHAR2(1)
REMARK VARCHAR2(50)

12:31:48 SQL>
12:31:50 SQL>
12:31:51 SQL> INSERT /*+ APPEND*/
12:32:19 2 INTO direct_load_insert
12:32:44 3 SELECT * FROM TABLE_OF_VIEW_BACKUP ;

3706656 rows created.

Elapsed: 00:01:13.31
12:34:17 SQL>
12:34:23 SQL>
12:34:23 SQL>
12:34:23 SQL> ROLLBACK ;

Rollback complete.

Elapsed: 00:00:00.06
12:34:53 SQL>
12:34:54 SQL>
12:34:54 SQL> INSERT INTO direct_load_insert
12:35:31 2 SELECT * FROM TABLE_OF_VIEW_BACKUP ;

3706656 rows created.

Elapsed: 00:01:53.36
12:37:30 SQL>
12:38:06 SQL>
12:38:06 SQL> ROLLBACK
12:39:44 2 /

Rollback complete.

Elapsed: 00:01:25.35
12:41:12 SQL>
12:41:14 SQL>
12:41:14 SQL> DROP TABLE direct_load_insert ;

Table dropped.

Elapsed: 00:00:28.17
12:41:57 SQL>
12:41:59 SQL>
12:41:59 SQL>