Category Archives: Oracle

11g, Oracle, Oracle 10g, oracle 9i

Oracle – Determining top system wait events

Leave a comment

Oracle monitors the wait events which occur in a database in two ways: Session level and System level. Session level wait events records events which affect a single user activities within the database. System level wait events records all events which affect the entire database system and all user activities within it. When considering which to use during active performance issues, it is normally best to concentrate on session level which would show which user is actually causing the problem. However, situations such as loss of a hard disk would appear across all session and thus it would be best to monitor from a system level. In this article we will examine how to find the top system wait events occurring. This procedure will work regardless of OS.

1. Logon to you Oracle database server.

2. Logon to SQLPLUS with sysdba privileges.


mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Mon Feb 01 22:14:23 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

3. For this procedure we will be using the SYS view of V_$SYSTEM_EVENT, to see what is causing possible database slowdown. Below is a description of the view:


SQL> desc V_$SYSTEM_EVENT
Name Null? Type
—————————————– ——– —————————-
EVENT VARCHAR2(64)
TOTAL_WAITS NUMBER
TOTAL_TIMEOUTS NUMBER
TIME_WAITED NUMBER
AVERAGE_WAIT NUMBER
TIME_WAITED_MICRO NUMBER
EVENT_ID NUMBER
WAIT_CLASS_ID NUMBER
WAIT_CLASS# NUMBER
WAIT_CLASS VARCHAR2(64)

4. First, let us look at retrieving the TOTAL_WAITS for a particular event, execute the following SQL: select event, TOTAL_WAITS from V_$SYSTEM_EVENT where TOTAL_WAITS>1 order by TOTAL_WAITS desc


SQL> select event, TOTAL_WAITS from V_$SYSTEM_EVENT where TOTAL_WAITS>1 order by TOTAL_WAITS desc
2 ;

EVENT TOTAL_WAITS
——————————- ———–
rdbms ipc message 59943
PX Idle Wait 19430
db file sequential read 12983
control file sequential read 12177
SQL*Net more data from dblink 9250

5. Second, you see the TOTAL_TIMEOUTS (halt of work) due to wait events.


select event, TOTAL_TIMEOUTS from V_$SYSTEM_EVENT where TOTAL_TIMEOUTS>1 order
by TOTAL_TIMEOUTS desc;

SQL> select event, TOTAL_TIMEOUTS from V_$SYSTEM_EVENT where TOTAL_TIMEOUTS>1
order by TOTAL_TIMEOUTS desc;

EVENT TOTAL_TIMEOUTS
———————– ————–
rdbms ipc message 59949
PX Idle Wait 19701
pmon timer 6339
PX qref latch 1954
jobq slave wait 1408

6. Third, we see the total TIME_WAITED for a type of event, recorded in milisecond.


select event, TIME_WAITED from V_$SYSTEM_EVENT where TIME_WAITED>1 order by TIME_WAITED desc;

SQL> select event, TIME_WAITED from V_$SYSTEM_EVENT where TIME_WAITED>1 order by
TIME_WAITED desc;

EVENT TIME_WAITED
——————— ———–
rdbms ipc message 16215043
PX Idle Wait 3884509
pmon timer 1853307
smon timer 1755205
jobq slave wait 425531

7. Finally, we get the average time in miliseconds of a wait event.


select event, AVERAGE_WAIT from V_$SYSTEM_EVENT where average_wait>.1
AVERAGE_WAIT order by desc;

SQL> select event, TIME_WAITED from V_$SYSTEM_EVENT where TIME_WAITED>1 order by TIME_WAITED desc;

EVENT TIME_WAITED
————————- ———–
rdbms ipc message 16215043
PX Idle Wait 3884509
pmon timer 1853307
smon timer 1755205
jobq slave wait 425531

The above information can be used to fine tune the Oracle RDBMS and improve the performance of your system.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, Oracle, Oracle 10g, oracle 9i

Oracle – Determining the top session wait events since instance startup

Leave a comment

The Oracle RDBMS provides the use of the following view to determine most costly wait events since instance startup: V$SESSION_WAIT_HISTORY. This view can be used to determine possible problem areas in the database. This article covers how to determine the top wait events in the Oracle database since system startup. This procedure will work regardless of OS.

1. Logon to you Oracle database server.

2. Logon to SQLPLUS with sysdba privileges.


mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Tues Jan 26 20:41:52 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

3. Execute the following SQL to extract the top wait events in the database since startup.


select distinct(a.event) as event, (select count(*) from v$session_wait_history
b where a.event=b.event) as tot_waits
from v$session_wait_history a order by tot_waits desc;

SQL> select distinct(a.event) as event, (select count(*) from
v$session_wait_history b where a.event=b.event) as tot_waits
from v$session_wait_history a order by tot_waits desc; 2

EVENT TOT_WAITS
————————– ———-
jobq slave wait 90
rdbms ipc message 81
PX Deq: Execution Msg 54
PX Deq: Table Q Normal 20
…
…
…

15 rows selected.

SQL>

4. This information can be used to further inspect the system to determine the cause of performance loss.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle

Oracle SPFILE vs INIT FILE:

Leave a comment

Oracle 9i and later version of the RDBMS have provided us with an increasing ability to dynamically control initialization parameters through the use of a SPFILE instead of an INIT file. By definition the differences between the two files are:

INIT File – OS text file that defines the initialization parameters for the database. You may be able to dynamically change initialization parameters in a database instance but the changes will not survive a restart of the database. The text init file must be updated manually for any changes to take affect upon restart.

SPFILE – OS binary file that defines the initialization parameters for the database. Dynamic changes to a database instance can be stored in the SPFILE to take affect upon restart and immediately. Non-Dynamic changes can be recorded to the SPFILE, to take affect at restart without having to enter OS layer.

This article will cover determining what you are using (an SPFILE or INIT file) and how oracle determines which to use.

Determination of what you are using:

1. Logon to your Oracle database server as the Oracle software owner.

2. Logon to SQLPLUS with sysdba privileges.

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Thu Jan 21 19:31:43 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

3. Use the following command to determine if you are using an spfile: show parameter {initalization_parameter}

In below example, you are using an SPFILE because the VALUE file is not null and defines an SPFILE:

SQL> show parameter spfile

NAME TYPE VALUE
———————————— ———– ——————————
spfile string
/opt/app/oracle/10.2.0/dbs/spfileorcl.ora

In this next example you are using an init file because the VALUE of SPFILE is null.

NAME TYPE VALUE
———————————— ———– ——————————
spfile string

This completes the determining the use of SPFILE or INIT file.

How Oracle handles SPFILE and INIT files during startup. (10g and higher)

1. First, Oracle looks for a ‘pfile={file and full path}’ clause in your start command and if it is present uses that file regardless of the existence of any other SPFILE or INIT file.

2. Second, Oracle looks for an spfile{sid}.ora file in the directory
$ORACLE_HOME/dbs and if found it will use it.

3. Finally, Oracle looks for an init{sid}.ora file in the directory
$ORACLE_HOME/dbs and if found it will use it.

4. If none of these exist, the Oracle instance will not start.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, audit, Oracle

Removal of Oracle auditing on specific user account:

Leave a comment

Oracle provides the ability to audit your database activities on a multitude of level which provides the administrator the ability to find suspicious activity. In most cases the DBA knows which user account they suspect is causing a problem, thus they only wish to monitor that account. However, in a heavily used system, the auditing process can produce a large amount of data and should be discontinued once it is obsolete. This article covers the removal of audit definitions for a specific user account on an Oracle RDBMS. This procedure will work on any OS.

NOTE: The initialization parameter AUDIT_TRAIL controls auditing at the entire database level and can be set to three definitions: 1. DB – audit trail in the database; 2. OS – audit trail on the OS; and 3. none – no auditing. In this procedure AUDIT_TRAIL must be set to DB or OS and the procedure does not shutdown auditing at the database level.

1. Auditing definitions for user accounts are stored in views:
DBA_OBJ_AUDIT_OPTS, DBA_PRIV_AUDIT_OPTS, and DBA_STMT_AUDIT_OPTS.

2. Logon to your Oracle database server as the Oracle software owner.

3. Logon to SQLPLUS with sysdba privileges.

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Fri Jan 15 19:18:09 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

4. In this procedure we will audit the account LJCATT, execute the following command to begin the auditing process for your specified user account: audit session by {user_name};

SQL> audit session by ljcatt;

Audit succeeded.

SQL>

5. Perform a select from the views DBA_PRIV_AUDIT_OPTS to see the audit policies defined by the command in step 4 and as you can see the account is setup for auditing.

SQL> select user_name, privilege from dba_priv_audit_opts;

USER_NAME PRIVILEGE
— —-
LJCATT ALTER SYSTEM
LJCATT AUDIT SYSTEM
LJCATT CREATE SESSION

SQL>

6. Once you have completed your analysis, you want to remove this audit policy, execute the following PL/SQL block.

————————————————————–
Beginning removal of Oracle auditing definitions for a specific user account PL/SQL Block
——————————————————————

set serveroutput on

declare

v_ct number;

begin

–this block removes all auditing from an oracle RDBMS system.

for v_stmt in(select ‘noaudit all by ‘ || user_name as stmt from
sys.dba_priv_audit_opts)
loop
execute immediate(v_stmt.stmt);
end loop;

end;
/

——————————————————————
End removal of Oracle auditing definitions for a specific user account PL/SQL Block
——————————————————————
——————————————————————
OUPUT
——————————————————————

SQL> set serveroutput on
SQL>
SQL> declare
2
3 v_ct number;
4
5 begin
6
7 –this block removes all auditing from an oracle RDBMS system.
8
9 for v_stmt in(select ‘noaudit all by ‘ || user_name as stmt from
sys.dba_priv_audit_opts)
10 loop
11 execute immediate(v_stmt.stmt);
12 end loop;
13
14 end;
15 /

PL/SQL procedure successfully completed.

SQL>
——————————————————————
End of OUPUT
——————————————————————

7. Once completed re-execute the following SQL to verify that the auditing definitions have been removed from the system: select user_name, privilege from dba_priv_audit_opts;

SQL> select user_name, privilege from dba_priv_audit_opts;

no rows selected

SQL>

That completes removal of all Oracle auditing for a specific user account in the RDBMS.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, Oracle, Oracle 10g, oracle 9i

Oracle – Determining the top session wait events

Leave a comment

The Oracle RDBMS provides the use of the following view to determine most costly wait events currently being experience by users: V$SESSION_WAIT. V$SESSION_WAIT contains all the current wait events which are active. This view can be used to determine what is causing an issue while experiencing a system slow down. This article covers how to determine the top wait events in the Oracle database. This procedure will work regardless of OS.

1. Logon to you Oracle database server.

2. Logon to SQLPLUS with sysdba privileges.


mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Sun Jan 10 10:28:02 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

3. Execute the following SQL to extract the current top wait events in the database.


select distinct(a.event) as event, (select count(*) from v$session_wait b where
a.event=b.event) as tot_waits
from v$session_wait a order by tot_waits desc;

SQL> select distinct(a.event) as event, (select count(*) from v$session_wait b
where a.event=b.event) as tot_waits
from v$session_wait a order by tot_waits desc; 2

EVENT TOT_WAITS
————————- ———-
rdbms ipc message 9
Streams AQ: waiting for time management or cleanup tasks 2
SQL*Net message to client 1
…
…
…

7 rows selected.

SQL>

4. This information can be used to further inspect the system to determine the cause of performance loss.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

hp-ux, itanium, linux, Oracle, Oracle 10g, oracle 9i

ORACLE SID not defined in the OS layer:

Leave a comment

This article covers the error which will occur in a Linux or UNIX environment if the OS parameter ORACLE_SID is not properly set and how to resolve this error.

1. With the ORACLE_SID OS parameter set to null, attempt to start SQLPLUS as sysdba. You will receive the following error – ORA-12162.

mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Wed Jan 6 14:55:56 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

ERROR:
ORA-12162: TNS:net service name is incorrectly specified

Enter user-name:

2. In the OS layer define your database sid with the OS parameter ORACLE_SID.

mylinux:> export ORACLE_SID=orcl
mylinux:>

3. Attempt to start SQLPLUS as sysdba.

mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Wed Jan 6 14:57:54 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

4. Oracle SQLPLUS starts normally after the ORACLE_SID parameter is set at the OS layer correctly.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, Oracle, Oracle 10g, oracle 9i

Oracle – Killing a session which is causing performance issues

Leave a comment

The Oracle RDBMS provides the DBA with the ability to kill a user’s session. On occasion you will find a particular account which is causing performance issues or performing an action which may be harmful to the database. In this situation, it may be best to kill the session. This article covers the use of “alter system” command to kill a users session.

1. Logon to you Oracle database server.

2. Logon to SQLPLUS with sysdba privileges.


mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Sat Jan 02 08:15:39 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

3. In this example, we know the session causing the issue is owned by the user LJCATT. To kill the session we must identify the SID and SERIAL# of the session owned by LJCATT. Execute the following SQL to get the SID and SERIAL# from his session.


SQL> select SID, SERIAL#, username from v$session where username=’LJCATT’;

SID SERIAL# USERNAME
———- ———- ——————————
3979 108 LJCATT

SQL>

4. To kill the session use the following command: alter system kill session ‘SID,SERIAL#’ immediate;


SQL> alter system kill session ‘3979, 108′ immediate;

System altered.

SQL>

5. Re-execute the SQL in step 3 to verify that the session has been killed.

SQL> select SID, SERIAL#, username from v$session where username=’LJCATT’;

no rows selected

SQL>

NOTE: If the user has performed a large amount of updates, insert, or deletes; the kill operation may take awhile to complete, due to rollback operations.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, Oracle, Oracle 10g

Oracle – Currently executing long SQL operations.

Leave a comment

The Oracle RDBMS provides multiple views detailing the current activities within the database, which can be used to perform analysis on performance. This article covers the user of the views: V$SQL, V$SESSION_LONGOPS, and V$SESSION to determine the longest currently occurring SQL statements within our database and who is executing them. This procedure will work on any OS.

1. Logon to you Oracle database server.

2. Logon to SQLPLUS with sysdba privileges.


mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Sat Dec 26 08:08:39 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

3. Execute the following SQL statement.


select b.username, b.SID, b.SERIAL#, c.totalwork||’ ‘||c.units,
c.time_remaining, a.sql_text from v$sql a,
v$session b,
v$session_longops c
where a.address=b.sql_address
and b.status=’ACTIVE’
and b.SID||b.SERIAL#=c.SID||c.SERIAL#
and c.time_remaining>0 and b.username<>‘SYS’

4. The returned values are as follows:


USERNAME – User executing this statement.
SID – System ID of session executing.
SERIAL# – SERIAL# of session executing.
TOTALWORK+UNITS – Total Work and units of measure of work.
TIME_REMAINING – Estimate in seconds of time remaining.
SQL_TEXT – SQL being executed.

5. This information can be used to determine current performance and possible performance problems.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

linux, Oracle, Oracle 10g, oracle 9i

Enable Support Tracing on Oracle Listener:

Leave a comment

Most Oracle database connects are made through the use of an Oracle process called the LISTENER which monitors a certain machine and port for connection requests to one or more database instances. There are times when a DBA or SA will want to monitor connects being made through the listener for various reasons. There also exist times when you will have to open a SAR or TAR with oracle support, if they suspect you are having issues with your listener connections, they will ask for a SUPPORT level trace of your Oracle listener. This article covers the setup of SUPPORT level tracing of you Oracle Listener and will work on UNIX, Linux, and Windows based machines.

NOTE: Even very well trained DBA’s may have a difficult time interpreting everything generated by this type of trace. When it is generated, it is normally sent to Oracle support, where someone how specializes in the Oracle LISTENER can review it.

1. Logon to your Oracle database server as the Oracle software owner.

2. Open up the LSNRCTL utility.

mylinux:>lsnrctl

LSNRCTL for LINUX: Version 10.2.0.4.0 – Production on 23-DEC-2009 17:17:05

Copyright (c) 1991, 2007, Oracle. All rights reserved.

Welcome to LSNRCTL, type “help” for information.

LSNRCTL>

3. If you are not using the default name of the Oracle listener (LISTENER) then define the Oracle listener name with the following command: set current_listener

LSNRCTL> set current_listener listener_orcl
Current Listener is listener_orcl
LSNRCTL>

4. If you have established a password for your oracle listener set it in the LSNRCTL session with the following command: set password

LSNRCTL> set password oracle
The command completed successfully
LSNRCTL>

5. Turn on support level tracing by specifying 16 or support as the level with the following command: set trc_level

LSNRCTL> set trc_level 16
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
listener_orcl parameter “trc_level” set to support
The command completed successfully
LSNRCTL>

or

LSNRCTL> set trc_level support
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
listener_orcl parameter “trc_level” set to support
The command completed successfully
LSNRCTL>

6. Define the name of the trace file that will be produced by the listener process upon establishment of user connection with the command below: set trc_file

LSNRCTL> set trc_file support_access_trace
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
listener_orcl parameter “trc_file” set to support_access_trace.trc
The command completed successfully
LSNRCTL>

NOTE: You do not have to set a trace directory by default the listener trace files are placed under: $ORACLE_HOME/network/trace

7. Save the changes made to the listener.ora file with the following command: save_config

LSNRCTL> save_config
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
Saved listener_orcl configuration parameters.
Listener Parameter File /opt/app/oracle/10.2.0/network/admin/listener.ora
Old Parameter File /opt/app/oracle/10.2.0/network/admin/listener.bak
The command completed successfully
LSNRCTL>

8. Exit out of LSNRCTL utility, change directories to
$ORACLE_HOME/network/trace, list the files present and you will see that the trace has already begun.

mylinux:> ls -lrt
total 1440
-rw-r—– 1 oracle dba 408755 Dec 23 17:25 listener_orcl.trc
-rw-r—– 1 oracle dba 43347 Dec 23 17:40 user_access_trace.trc
-rw-r—– 1 oracle dba 231352 Dec 23 19:00 admin_access_trace.trc
-rw-r—– 1 oracle dba 35087 Dec 23 19:01
support_access_trace.trc
mylinux:>

9. Logon to the RDBMS as an administrator, regular user and exit.

mylinux:> sqlplus system/devlmgr@orcl

SQL*Plus: Release 10.2.0.4.0 – Production on Wed Dec 23 19:03:18 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 –
64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
mylinux:> sqlplus joe/joe@orcl

SQL*Plus: Release 10.2.0.4.0 – Production on Wed Dec 23 19:03:33 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 –
64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
mylinux:>

10. Perform another listing of the files under directory
$ORACLE_HOME/network/admin and you will see that the file support_access_trace.trc has grown by recording the connection from the users in step 9.

mylinux:> ls -lrt
total 1568
-rw-r—– 1 oracle dba 408755 Dec 23 17:25 listener_orcl.trc
-rw-r—– 1 oracle dba 43347 Dec 23 17:40 user_access_trace.trc
-rw-r—– 1 oracle dba 231352 Dec 23 19:00 admin_access_trace.trc
-rw-r—– 1 oracle dba 103777 Dec 23 19:03
support_access_trace.trc
mylinux:>

11 This information can be used to show where connections in you database are coming from and other statistical information about the network connections being established. However, it is designed to be used by Oracle Support.

Larry J. Catt, OCP 91, 10g
oracle@allcompute.com
www.allcompute.com

11g, audit, Oracle

Viewing all auditing occurring within an Oracle RDBMS:

Leave a comment

Oracle provides the ability to audit your database activities on a multitude of level which providing the administrator the ability to find suspicious activity. This article covers the display of all auditing which is currently defined in an Oracle RDBMS. This procedure will work regardless of OS type.

NOTE: The initialization parameter AUDIT_TRAIL controls auditing at the entire database level and can be set to three definitions: 1. DB – audit trail in the database; 2. OS – audit trail on the OS; and 3. none – no auditing. In this procedure AUDIT_TRAIL must be set to DB or OS and the procedure does not shutdown auditing at the database level.

1. Auditing definitions can be seen in three views: DBA_OBJ_AUDIT_OPTS, DBA_PRIV_AUDIT_OPTS, and DBA_STMT_AUDIT_OPTS

2. Logon to your Oracle database server as the Oracle software owner.

3. Logon to SQLPLUS with sysdba privileges.

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Sat Dec 17 07:11:59 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

4. Execute the following PL/SQL block to display all auditing which is currently defined in your ORACLE RDBMS.

————————————————————–
Beginning of PL/SQL Block
———————————————————————–

set serveroutput on

declare

v_ct number;

begin

–this block removes all auditing from an oracle RDBMS system.

for v_stmt in(select ‘audit all by ‘ || user_name as stmt from
sys.dba_priv_audit_opts)
loop
dbms_output.put_line(v_stmt.stmt);
end loop;

for v_stmt in(select ‘audit ‘ ||audit_option||’ by ‘ ||user_name as stmt from
sys.dba_stmt_audit_opts)
loop
dbms_output.put_line(v_stmt.stmt);
end loop;

for v_stmt in(select ‘audit all on ‘ ||owner||’.’||object_name as stmt from
sys.dba_obj_audit_opts)
loop
dbms_output.put_line(v_stmt.stmt);
end loop;

end;
/
———————————————————————–
end of PL/SQL Block
———————————————————————–

———————————————————————–
Beginning of PL/SQL Block output
———————————————————————–

SQL> set serveroutput on
SQL>
SQL> declare
2
3 v_ct number;
4
5 begin
6
7 –this block removes all auditing from an oracle RDBMS system.
8
9 for v_stmt in(select ‘audit all by ‘ || user_name as stmt from
sys.dba_priv_audit_opts)
10 loop
11 dbms_output.put_line(v_stmt.stmt);
12 end loop;
13
14 for v_stmt in(select ‘audit ‘ ||audit_option||’ by ‘ ||user_name as stmt
from sys.dba_stmt_audit_opts)
15 loop
16 dbms_output.put_line(v_stmt.stmt);
17 end loop;
18
19 for v_stmt in(select ‘audit all on ‘ ||owner||’.’||object_name as stmt from
sys.dba_obj_audit_opts)
20 loop
21 dbms_output.put_line(v_stmt.stmt);
22 end loop;
23
24 end;
25 /
audit all by LJCATT
audit all by LJCATT
audit CREATE SESSION by LJCATT
audit CREATE TABLE by LJCATT
audit all on LJCATT.TEST

PL/SQL procedure successfully completed.

SQL>
———————————————————————–
Ending of PL/SQL Block output
———————————————————————–

This completes viewing of all auditing occurring within an Oracle RDBMS.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com