Category Archives: 11g

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

Determining actual size of data stored in an Oracle RDBMS.

Leave a comment

When using any RDBMS there exist a distinct difference between the actual size of the data and the physical size of the database. In this article we will demonstrate how to get the actual size of the data stored in an Oracle RDBMS.

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 Mon Mar 01 17:17: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. Select the sum of all bytes allocated in the view DBA_SEGMENTS, returns the actual space being consumed on disk.

SQL> select sum(bytes)/1024/1024 from dba_segments;

SUM(BYTES)/1024/1024
——————–
316865.805

4. This completes the determining the actual size of data in an Oracle database.

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

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

Securing Oracle Listener with a password:

Leave a comment

Normally, connection to an Oracle database is performed through the use of an Oracle LISTENER process which monitors a specific machine and port for request to connect to a one or more database instances. The LISTENER process is control by a utility named LSNRCTL which is located under $ORACLE_HOME/bin. The listener provides the main connection access to most Oracle database systems, thus if it is tempered with, it could prevent use of your database even though the RDBMS is up and running fine. In this article we will review password protecting your oracle LISTENER from unauthorized shutdown. This article was written using LINUX but will work just as well on any OS.

1. Logon to your Oracle database server as the Oracle software owner, switch directories to your $ORACLE_HOME/network/admin and view the file listener.ora with your chose of editor.

mylinux:> cd $ORACLE_HOME/network/admin
mylinux:> cat listener.ora
# LISTENER.ORA Network Configuration File:
/opt/app/oracle/10.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.110)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)

SID_LIST_LISTENER_ORCL =
(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = /opt/app/oracle/10.2.0)
)

(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/app/oracle/10.2.0)
(PROGRAM = extproc)
)
)

mylinux:>

NOTE: There exists no tag for PASSWORDS_{listener_name} = {new_password} which has not been established yet.

2. Exit the listener.ora file and startup the lsnrctl utility.

mylinux:> lsnrctl

LSNRCTL for LINUX: Version 10.2.0.4.0 – Production on 21-FEB-2010 16:30:52

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 {listener_name}

LSNRCTL> set current_listener listener_orcl
Current Listener is listener_orcl
LSNRCTL>

4. If your oracle listener is not currently running, start it up with the command: start

LSNRCTL> start
Starting /opt/app/oracle/10.2.0/bin/tnslsnr: please wait…

TNSLSNR for LINUX: Version 10.2.0.4.0 – Production
System parameter file is /opt/app/oracle/10.2.0/network/admin/listener.ora
Log messages written to
/opt/app/oracle/10.2.0/network/log/listener_orcl.log
Listening on:
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.110)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))

Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
STATUS of the LISTENER
————————
Alias listener_orcl
Version TNSLSNR for LINUX: Version 10.2.0.4.0 – Production
Start Date 21-FEB-2010 16:32:55
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/app/oracle/10.2.0/network/admin/listener.ora
Listener Log File
/opt/app/oracle/10.2.0/network/log/listener_orcl.log
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.110)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Services Summary…
Service “PLSExtProc” has 1 instance(s).
Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
Service “orcl” has 1 instance(s).
Instance “orcl”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
LSNRCTL>

5. To set a password for your listener execute the following lsnrctl command: change_password

NOTE: Hit return when asked for current password if none is set.

LSNRCTL> change_password
Old password:
New password:
Reenter new password:
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
Password changed for listener_orcl
The command completed successfully
LSNRCTL>

6. Save the changes made in the LSNRCTL utility with the 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>

7. Exit out of lsnrctl utility and open up the file listener.ora with you chose of editor.

LSNRCTL> exit
mylinux:> cat listener.ora
# LISTENER.ORA Network Configuration File:
/opt/app/oracle/10.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.110)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)

SID_LIST_LISTENER_ORCL =
(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = /opt/app/oracle/10.2.0)
)

(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/app/oracle/10.2.0)
(PROGRAM = extproc)
)
)

#—-ADDED BY TNSLSNR 21-FEB-2010 16:37:01—
PASSWORDS_listener_orcl = 1DF5C2FD0FE9CFA2
#——————————————–
mylinux:>

NOTE: The tag PASSWORDS_{listener_name} = {new_password} has been added to the file listener.ora and the password is encrypted so it will not look like what you typed. You can shutdown you listener with the password string you original entered or the encrypted string. However, without the password you will not be able to shutdown the LISTENER process.

This completes securing oracle listener with a password.

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

11g, Oracle

Auditing the connections for a particular user:

Leave a comment

Oracle provides the ability to audit your database activities on a multitude of level, providing the administrator the ability to find suspicious activity. In this article we will show how to audit a particular user’s connectivity in an Oracle database. This procedure will work on any OS.

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 Mon Feb 12 04: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. Check the current setting of the initialization parameter AUDIT_TRAIL with the command: show parameter {initialization_parameter}
The initialization parameter AUDIT_TRAIL controls auditing at the 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 we want the definition to be set to DB.

SQL> show parameter AUDIT_TRAIL

NAME TYPE VALUE
———————————— ———– ———————-
Audit_trail string DB
SQL>

4. If the parameter is not set to DB and you are using a spfile, execute the command: alter system set audit_trail=DB scope=spfile; and bounce the database. If you are using an init file, shutdown the database add the string AUDIT_TRIAL=DB and start the database.
NOTE: If you do not know if you are using an SPFILE or INIT file, search ORACLE SPFILE vs INIT File

SQL> alter system set audit_trail=DB scope=spfile;

System altered.

SQL>

5. Restart your database if you had to change the audit_trail parameter.

6. Execute the following command to begin auditing of connection by a particular user: audit session by {user_name}

SQL> audit session by ljcatt;

Audit succeeded.

SQL>

7. Perform the following select from the view DBA_PRIV_AUDIT_OPTS to see that auditing is enabled: select user_name, privilege from dba_priv_audit_opts;

SQL> select user_name, privilege from dba_priv_audit_opts;

USER_NAME PRIVILEGE
—————————— —————————————-
LJCATT CREATE SESSION

SQL>

8. Connect as the user you are auditing.

SQL> connect ljcatt
Enter password:
Connected.
SQL>

9. Reconnect as sysdba.

SQL> connect / as sysdba
Connected.
SQL>

10 Perform a select against the view dba_audit_trail to determine if the connection was recorded.

SQL> select username, returncode, action_name from dba_audit_trail;

USERNAME RETURNCODE ACTION_NAME
—————————— ———- —————————-
LJCATT 0 SESSION REC
LJCATT 0 LOGOFF
SQL>

As seen in the output above, the session reconnection and then log off where both successfully record with a return code of zero. If either action failed, the return code would show the error number produced. This completes auditing of a particular user account in Oracle RDBMS.

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

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

Oracle – Determining the OS layer your database is residing on.

Leave a comment

I have experienced situations where the only connection I had to an Oracle database was SQL*PLUS with no OS layer user account access. Thus, I was not sure of the actual OS my database was installed on. Knowing the OS your database resides on is a key factor in successfully administrating of any RDBMS. This article covers the determination of the operating system your Oracle database uses, through SQL*PLUS. This procedure will work regardless of OS.

1. Logon to your database server with SYSDBA privileges from your remote client.

C:\>sqlplus ljcatt/password5@orcl as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Sat Feb 13 10:02:36 2010

Copyright (c) 1982, 2005, 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>

2. Execute the following SQL statement: select platform_id, platform_name, edition from DBA_REGISTRY_DATABASE;

SQL> column platform_name format a15
SQL> select platform_id, platform_name, edition from DBA_REGISTRY_DATABASE;

PLATFORM_ID PLATFORM_NAME EDITION
———– ————— ——————————
3 HP-UX (64-bit)

SQL>

That completes determination of OS layer type from within the Oracle database.

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

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

Oracle – Determining the CPU patch applied to an Oracle database

Leave a comment

Oracle – Determining the CPU patch applied to an Oracle database

Oracle Corporation releases Critical Patch Updates (CPU) on a quarterly schedule (Jan, Apr, Jul, and Oct) to ensure their database software have the most resent patches. However, I have come across several situations where DBA’s had a difficult time determining the current patch level of their databases. This article covers viewing of the current patch level of an Oracle database. This procedure will work on any OS.

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 Sun Feb 07 08:08: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. Execute the following SQL statement: select action, comments from
DBA_REGISTRY_HISTORY;

SQL> column action format a10
SQL> column comments format a25
SQL>select action, comments from DBA_REGISTRY_HISTORY;

ACTION COMMENTS
———- ————————-
CPU CPUApr2008
CPU view recompilation
UPGRADE Upgraded from 9.2.0.8.0
APPLY CPUApr2009
CPU view recompilation

SQL>

4. As seen above the latest CPU patch for this database is CPUAPR2009.

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

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

Stop 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. However, tracing the Oracle LISTENER process at the user, admin, or support level can consume large amounts of disk space; thus it should only be performed on a temporary basis. This article covers stopping of a listener trace at any the three levels and will work on UNIX, Linux, and Windows based machines.

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 04-FEB-2010 19: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 {listener_name}

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 {listener_password}

LSNRCTL> set password oracle
The command completed successfully
LSNRCTL>

5. Turn off tracing by specifying 0 or off as the level with the following command: set trc_level {level}

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

or

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

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. This completes disabling of the Oracle Listener tracing process.

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

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

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