TKPROF And Oracle Trace

TKPROF And Oracle Trace

The TKPROF program converts Oracle trace files into a more readable form. If you have a problem query you can user TKPROF to get more information. To get the most out of the utility you must enable timed statistics by setting the init.ora parameter or performing the following command:

ALTER SYSTEM SET TIMED_STATISTICS = TRUE;If a suitable plan table is not present one can be created by doing the fooling as the SYS user:

@ORACLE_HOME\rdbms\admin\utlxplan.sql
CREATE PUBLIC SYNONYM PLAN_TABLE FOR SYS.PLAN_TABLE;
GRANT SELECT, INSERT, UPDATE, DELETE ON SYS.PLAN_TABLE TO PUBLIC;With this done we can trace a statement:

ALTER SESSION SET SQL_TRACE = TRUE;

SELECT COUNT(*)
FROM dual;

ALTER SESSION SET SQL_TRACE = FALSE;The resulting trace file will be located in the USER_DUMP_DEST directory. This can then be interpreted using TKPROF at the commmand prompt as follows:

TKPROF explain=user/password@service table=sys.plan_tableThe resulting output file contains the following type of information for all SQL statements processed, including the ALTER SESSION commands:

********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************

SELECT COUNT(*)
FROM dual

call count cpu elapsed disk query current rows
------- ----- ----- ------- ------- ------- ------- -------
Parse 1 0.02 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 1 4 1
------- ----- ----- ------- ------- ------- ------- -------
total 4 0.02 0.02 0 1 4 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 121

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
1 TABLE ACCESS FULL DUAL

Statspack

1. Objetivo
El objetivo de este documento es explicar cómo se instala statspack y cómo se puede configurar statspack para obtener instantáneas de la base de datos en un momento determinado.



2. Instalación statspack
Para realizar la instalación de statspack hay que tener previamente creado un tablespace disponible para la instalación.

- En base de datos ORACLE8 y ORACLE9 podemos elegir un tablespace que tenga espacio suficiente para la instalación aunque lo más recomendable es crear uno nuevo, por ejemplo un tablespace llamado PERFSTAT.

- En bases de datos ORACLE 10g la instalación se puede realizar en el tablespace SYSAUX.

Nos conectamos a la base de datos en la que queremos instalar STATSPACK y ejecutamos con el usuario sys el script $ORACLE_HOME/rdbms/admin./spcreate.sql

Ejemplo: Instalación de statspack en una base de datos oracle9. Nos conectamos con el usuario oracle9, fijamos la variable ORACLE_SID y lanzamos el script spcreate.sql

$ su – oracle9$ export ORACLE_SID=orasite$ sqlplus /nologSQL > connect /as sysdbaSQL > @$ORACLE_HOME/rdbms/admin/spcreate.sql
Durante la instalación se van a solicitar los siguientes datos:
- Contraseña para el usuario perfstat: *******

- Tablespace a instalar: Ponemos SYSAUX o PERFSTAT dependiendo de la versión en la que estemos.(tablespace creado en oracle8 y oracle9 o tablespace de oracle10).

3. Desinstalación statspack
En caso de que queramos desinstalar STATSPACK de la misma forma que lo hemos instalado en vez de ejecutar el fichero spcreate ejecutamos el fichero spdrop.sql

SQL> @$ORACLE_HOME/rdbms/admin./spdrop.sql4. Obtener una foto de la base de datos con statspack
Para ejecutar statspack y obtener una instantánea de la base de datos en ese momento lo realizamos de la siguiente forma:

Nos conectamos a la base de datos con el usuario perfstat y ejectuamos statspack.snap

sql > connect perfstat/*****sql > execute statspack.snap(I_SNAP_LEVEL=>10);Si vamos a obtener instantáneas a menudo es conveniente actualizar las estadísticas de perfstat.

sql > connect perfstat/*****sql > execute dbms_stats.gather_schema_stats(ownname=>'perfstat');5. Obtener un informe statspack
Para obtener un Informe statspack los pasos que hay que seguir son los siguientes (Hay que tener en cuenta que para sacar un informe hay que tener más de una foto y que entre éstas la instancia no se haya caído o parado):

Con el usuario propietario de oracle nos conectamos a la base de datos como perfstat.

Ejecutamos el script $ORACLE_HOME/rdbms/admin/spreport.sql . Al ejecutar este script obtenemos todas las fotos que se han realizado y vamos contestando a las peticiones que este te hace:

Foto inicial
Foto final
Lugar para generar el informe

Ejemplo para obtener un informe.

$ su – oracle9$ export ORACLE_SID=orasite$ sqlplus /nologSQL> connect perfstat/******SQL> @$ORACLE_HOME/rdbms/admin/spreport.sql

VIEW WITH CHECK OPTION

SQL*Plus: Release 10.2.0.1.0 - Production on Mié Abr 11 12:08:57 2007

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

Introduzca el nombre de usuario: carlos@bd01.xxxxxxxx
Introduzca la contraseña:

Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning and Data Mining options

carlos@bd01.xxxxxxxx> SELECT * FROM PRUEBA01;

ID_N C_TXT
---------- -------------------------
30 TREINTA
1 UNO
2 DOS
3 TRES
4 CUATRO
5 CINCO
21 VEINTIUNO
22 VEINTIDOS
23 VEINTITRES
24 VEINTICUATRO
25 VEINTICINCO

11 filas seleccionadas.Un método sencillo es crear una vista que filtre los datos según el criterio requerido:

carlos@bd01.xxxxxxxx> CREATE VIEW PRUEBA01_VW AS
2> SELECT ID_N, C_TXT FROM PRUEBA01
3> WHERE ID_N <= 10;

Vista creada.

carlos@bd01.xxxxxxxx> SELECT * FROM PRUEBA01_VW;

ID_N C_TXT
---------- -------------------------
1 UNO
2 DOS
3 TRES
4 CUATRO
5 CINCOHasta aquí todo fácil. Podemos insertar filas en esa vista sin ningún problema y los datos se almacenarán en la tabla subyacente:

carlos@bd01.xxxxxxxx> INSERT INTO PRUEBA01_VW VALUES (6, 'SEIS');

1 fila creada.

carlos@bd01.xxxxxxxx> SELECT * FROM PRUEBA01_VW;

ID_N C_TXT
---------- -------------------------
1 UNO
2 DOS
3 TRES
4 CUATRO
5 CINCO
6 SEIS

6 filas seleccionadas.

carlos@bd01.xxxxxxxx> SELECT * FROM PRUEBA01;

ID_N C_TXT
---------- -------------------------
30 TREINTA
1 UNO
2 DOS
3 TRES
4 CUATRO
5 CINCO
21 VEINTIUNO
22 VEINTIDOS
23 VEINTITRES
24 VEINTICUATRO
25 VEINTICINCO
6 SEIS

12 filas seleccionadas.Pero aquí pueden empezar los problemas, porque a través de la vista se podrían hacer modificaciones a la tabla de una manera descontrolada:

carlos@bd01.xxxxxxxx> INSERT INTO PRUEBA01_VW VALUES (11,'ONCE');

1 fila creada.

carlos@bd01.xxxxxxxx> SELECT * FROM PRUEBA01_VW;

ID_N C_TXT
---------- -------------------------
1 UNO
2 DOS
3 TRES
4 CUATRO
5 CINCO
6 SEIS

6 filas seleccionadas.

carlos@bd01.xxxxxxxx> SELECT * FROM PRUEBA01;

ID_N C_TXT
---------- -------------------------
30 TREINTA
1 UNO
2 DOS
3 TRES
4 CUATRO
5 CINCO
21 VEINTIUNO
22 VEINTIDOS
23 VEINTITRES
24 VEINTICUATRO
25 VEINTICINCO
6 SEIS
11 ONCE

13 filas seleccionadas.Aquí hemos visto que se puede hacer un ‘INSERT’ ‘a ciegas’: Hemos insertado en la vista una fila que no podemos ver, pero que sí fue insertada en la tabla base. Esto da pie a que puedan hacerse operaciones que pasen inadvertidas.

Incluso más: se pueden dar errores incomprensibles:

carlos@bd01.xxxxxxxx> INSERT INTO PRUEBA01_VW VALUES (21,'VEINTIUNO');
INSERT INTO PRUEBA01_VW VALUES (21,'VEINTIUNO')
*
ERROR en línea 1:
ORA-00001: unique constraint (CARLOS.PRUEBA01_PK) violatedEn este caso y en el anterior (la inserción ‘a ciegas’ y el error) puede ocurrir que el nombre de la vista no nos dé pistas de que no se trata de una tabla ‘ordinaria’, haciendo el comportamiento más extraño a ojos de un observador.

Estos problemas se solicionarán fácilmente si utilizamos la opción ‘WITH CHECK OPTION’ en la creación de la vista. Esta opción hace que las operaciones DML sobre dicha vista pasen un control de integridad que corresponda a los criterios de definición de la propia vista:

carlos@bd01.xxxxxxxx> CREATE OR REPLACE VIEW PRUEBA01_VW AS
2> SELECT ID_N, C_TXT FROM PRUEBA01
3> WHERE ID_N <= 10
4> WITH CHECK OPTION;

Vista creada.

carlos@bd01.xxxxxxxx> SELECT * FROM PRUEBA01_VW;

ID_N C_TXT
---------- -------------------------
1 UNO
2 DOS
3 TRES
4 CUATRO
5 CINCO
6 SEIS

6 filas seleccionadas.Si ahora intentamos hacer una inserción (o modificación) que salga de los criterios de definición de la vista tendremos que:

carlos@bd01.xxxxxxxx> INSERT INTO PRUEBA01_VW VALUES (12,'DOCE');
INSERT INTO PRUEBA01_VW VALUES (12,'DOCE')
*
ERROR en línea 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

carlos@bd01.xxxxxxxx>Por supuesto, esto también es válido para ‘UPDATE’:

carlos@bd01.xxxxxxxx> UPDATE PRUEBA01_VW SET ID_N=50 WHERE ID_N=1;
UPDATE PRUEBA01_VW SET ID_N=50 WHERE ID_N=1
*
ERROR en línea 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

carlos@bd01.xxxxxxxx>En resumen, si se utilizan vistas actualizables como mecanismo de ‘encapsulamiento de visibilidad de datos’ se debería siempre considerar la opción ‘WITH CHECK OPTION’ para evitar resultados no deseados de inconsistencia y reforzar además las referidas políticas de visibilidad (y más importante: de actualización) de los datos

JOINS

Example of a Left Outer Join 1

SQL> -- create demo table
SQL> create table Employee(
2 EMPNO NUMBER(3),
3 ENAME VARCHAR2(15 BYTE),
4 HIREDATE DATE,
5 ORIG_SALARY NUMBER(6),
6 CURR_SALARY NUMBER(6),
7 REGION VARCHAR2(1 BYTE)
8 )
9 /

Table created.

SQL>
SQL> create table job (
2 EMPNO NUMBER(3),
3 jobtitle VARCHAR2(20 BYTE)
4 )
5 /

Table created.

SQL>
SQL> insert into job (EMPNO, Jobtitle) values (1,'Tester');

1 row created.

SQL> insert into job (EMPNO, Jobtitle) values (2,'Accountant');

1 row created.

SQL> insert into job (EMPNO, Jobtitle) values (3,'Developer');

1 row created.

SQL> insert into job (EMPNO, Jobtitle) values (9,'Developer');

1 row created.

SQL>
SQL>
SQL> -- prepare data
SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (1, 'Jason', to_date('19960725','YYYYMMDD'), 1234, 8767, 'E')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (2, 'John', to_date('19970715','YYYYMMDD'), 2341, 3456, 'W')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (3, 'Joe', to_date('19860125','YYYYMMDD'), 4321, 5654, 'E')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (4, 'Tom', to_date('20060913','YYYYMMDD'), 2413, 6787, 'W')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (5, 'Jane', to_date('20050417','YYYYMMDD'), 7654, 4345, 'E')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (6, 'James', to_date('20040718','YYYYMMDD'), 5679, 6546, 'W')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (7, 'Jodd', to_date('20030720','YYYYMMDD'), 5438, 7658, 'E')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (8, 'Joke', to_date('20020101','YYYYMMDD'), 8765, 4543, 'W')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (9, 'Jack', to_date('20010829','YYYYMMDD'), 7896, 1232, 'E')
3 /

1 row created.

SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /

EMPNO ENAME HIREDATE ORIG_SALARY CURR_SALARY R
---------- --------------- --------- ----------- ----------- -
1 Jason 25-JUL-96 1234 8767 E
2 John 15-JUL-97 2341 3456 W
3 Joe 25-JAN-86 4321 5654 E
4 Tom 13-SEP-06 2413 6787 W
5 Jane 17-APR-05 7654 4345 E
6 James 18-JUL-04 5679 6546 W
7 Jodd 20-JUL-03 5438 7658 E
8 Joke 01-JAN-02 8765 4543 W
9 Jack 29-AUG-01 7896 1232 E

9 rows selected.

SQL> select * from job
2 /

EMPNO JOBTITLE
---------- --------------------
1 Tester
2 Accountant
3 Developer
9 Developer

SQL>
SQL> SELECT e.ename, j.jobtitle FROM employee e, job j WHERE e.empno = j.empno (+);

ENAME JOBTITLE
--------------- --------------------
Jason Tester
John Accountant
Joe Developer
Jack Developer
Jane
Joke
James
Jodd
Tom

9 rows selected.

SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /

Table dropped.

SQL> drop table job
2 /

Table dropped.



Example of a Right Outer Join 1

SQL>
SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 EMPNO NUMBER(3),
3 ENAME VARCHAR2(15 BYTE),
4 HIREDATE DATE,
5 ORIG_SALARY NUMBER(6),
6 CURR_SALARY NUMBER(6),
7 REGION VARCHAR2(1 BYTE)
8 )
9 /

Table created.

SQL>
SQL> create table job (
2 EMPNO NUMBER(3),
3 jobtitle VARCHAR2(20 BYTE)
4 )
5 /

Table created.

SQL>
SQL> insert into job (EMPNO, Jobtitle) values (1,'Tester');

1 row created.

SQL> insert into job (EMPNO, Jobtitle) values (2,'Accountant');

1 row created.

SQL> insert into job (EMPNO, Jobtitle) values (3,'Developer');

1 row created.

SQL> insert into job (EMPNO, Jobtitle) values (4,'COder');

1 row created.

SQL>
SQL> insert into job (EMPNO, Jobtitle) values (9,'Developer');

1 row created.

SQL>
SQL>
SQL> -- prepare data
SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (1, 'Jason', to_date('19960725','YYYYMMDD'), 1234, 8767, 'E')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (2, 'John', to_date('19970715','YYYYMMDD'), 2341, 3456, 'W')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (3, 'Joe', to_date('19860125','YYYYMMDD'), 4321, 5654, 'E')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (4, 'Tom', to_date('20060913','YYYYMMDD'), 2413, 6787, 'W')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (5, 'Jane', to_date('20050417','YYYYMMDD'), 7654, 4345, 'E')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (6, 'James', to_date('20040718','YYYYMMDD'), 5679, 6546, 'W')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (7, 'Jodd', to_date('20030720','YYYYMMDD'), 5438, 7658, 'E')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (8, 'Joke', to_date('20020101','YYYYMMDD'), 8765, 4543, 'W')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (9, 'Jack', to_date('20010829','YYYYMMDD'), 7896, 1232, 'E')
3 /

1 row created.

SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /

EMPNO ENAME HIREDATE ORIG_SALARY CURR_SALARY R
---------- --------------- --------- ----------- ----------- -
1 Jason 25-JUL-96 1234 8767 E
2 John 15-JUL-97 2341 3456 W
3 Joe 25-JAN-86 4321 5654 E
4 Tom 13-SEP-06 2413 6787 W
5 Jane 17-APR-05 7654 4345 E
6 James 18-JUL-04 5679 6546 W
7 Jodd 20-JUL-03 5438 7658 E
8 Joke 01-JAN-02 8765 4543 W
9 Jack 29-AUG-01 7896 1232 E

9 rows selected.

SQL> select * from job
2 /

EMPNO JOBTITLE
---------- --------------------
1 Tester
2 Accountant
3 Developer
4 COder
9 Developer

SQL>
SQL>
SQL> SELECT e.ename, j.jobtitle FROM employee e, job j WHERE e.empno (+) = j.empno ;

ENAME JOBTITLE
--------------- --------------------
Jason Tester
John Accountant
Joe Developer
Tom COder
Jack Developer

SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /

Table dropped.

SQL> drop table job
2 /

Table dropped.

SYS_CONTEXT FROM DUAL

http://www.techonthenet.com/oracle/functions/sys_context.php

  1* select sys_context( 'userenv', 'CURRENT_SCHEMA' ) from dual

SQL> /

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')

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

SYS

SQL> ed

Wrote file afiedt.buf

  1* select sys_context( 'userenv', 'HOST' ) from dual

SQL> /

SYS_CONTEXT('USERENV','HOST')

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

localhost.localdomain

SQL> ed

Wrote file afiedt.buf

  1* select sys_context( 'userenv', 'INSTANCE_NAME' ) from dual

SQL> /

SYS_CONTEXT('USERENV','INSTANCE_NAME')

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

acme

select SYS_CONTEXT('USERENV','SESSION_USER'),

    SYS_CONTEXT('USERENV','HOST'),SYS_CONTEXT('USERENV','TERMINAL'),SYS_CONTEXT('USERENV','OS_USER')

    from dual;

OPCIONES

Parameter	Explanation	Oracle 9i	Oracle 10g	Oracle 11g
ACTION	Returns the position in the module	No	Yes	Yes
AUDITED_CURSORID	Returns the cursor ID of the SQL that triggered the audit	Yes	Yes	Yes
AUTHENTICATED_IDENTITY	Returns the identity used in authentication	No	Yes	Yes
AUTHENTICATION_DATA	Authentication data	Yes	Yes	Yes
AUTHENTICATION_METHOD	Returns the method of authentication	No	Yes	Yes
AUTHENTICATION_TYPE	Describes how the user was authenticated. It can be one of the following values: Database, OS, Network, or Proxy	Yes	No	No
BG_JOB_ID	If the session was established by an Oracle background process, this parameter will return the Job ID. Otherwise, it will return NULL.	Yes	Yes	Yes
CLIENT_IDENTIFIER	Returns the client identifier (global context)	Yes	Yes	Yes
CLIENT_INFO	User session information	Yes	Yes	Yes
CURRENT_BIND	Bind variables for fine-grained auditing	No	Yes	Yes
CURRENT_SCHEMA	Returns the default schema used in the current schema	Yes	Yes	Yes
CURRENT_SCHEMAID	Returns the identifier of the default schema used in the current schema	Yes	Yes	Yes
CURRENT_SQL	Returns the SQL that triggered the audit event	Yes	Yes	Yes
CURRENT_SQL_LENGTH	Returns the length of the current SQL statement that triggered the audit event	No	Yes	Yes
CURRENT_USER	Name of the current user	Yes	No	No
CURRENT_USERID	Userid of the current user	Yes	No	No
DB_DOMAIN	Domain of the database from the DB_DOMAIN initialization parameter	Yes	Yes	Yes
DB_NAME	Name of the database from the DB_NAME initialization parameter	Yes	Yes	Yes
DB_UNIQUE_NAME	Name of the database from the DB_UNIQUE_NAME initialization parameter	No	Yes	Yes
ENTRYID	Available auditing entry identifier	Yes	Yes	Yes
ENTERPRISE_IDENTITY	Returns the user's enterprise-wide identity	No	Yes	Yes
EXTERNAL_NAME	External of the database user	Yes	No	No
FG_JOB_ID	If the session was established by a client foreground process, this parameter will return the Job ID. Otherwise, it will return NULL.	Yes	Yes	Yes
GLOBAL_CONTEXT_MEMORY	The number used in the System Global Area by the globally accessed context	Yes	Yes	Yes
GLOBAL_UID	The global user ID from Oracle Internet Directory for enterprise security logins. Returns NULL for all other logins.	No	No	Yes
HOST	Name of the host machine from which the client has connected	Yes	Yes	Yes
IDENTIFICATION_TYPE	Returns the way the user's schema was created	No	Yes	Yes
INSTANCE	The identifier number of the current instance	Yes	Yes	Yes
INSTANCE_NAME	The name of the current instance	No	Yes	Yes
IP_ADDRESS	IP address of the machine from which the client has connected	Yes	Yes	Yes
ISDBA	Returns TRUE if the user has DBA privileges. Otherwise, it will return FALSE.	Yes	Yes	Yes
LANG	The ISO abbreviate for the language	Yes	Yes	Yes
LANGUAGE	The language, territory, and character of the session. In the following format: language_territory.characterset	Yes	Yes	Yes
MODULE	Returns the appplication name set through DBMS_APPLICATION_INFO package or OCI	No	Yes	Yes
NETWORK_PROTOCOL	Network protocol used	Yes	Yes	Yes
NLS_CALENDAR	The calendar of the current session	Yes	Yes	Yes
NLS_CURRENCY	The currency of the current session	Yes	Yes	Yes
NLS_DATE_FORMAT	The date format for the current session	Yes	Yes	Yes
NLS_DATE_LANGUAGE	The language used for dates	Yes	Yes	Yes
NLS_SORT	BINARY or the linguistic sort basis	Yes	Yes	Yes
NLS_TERRITORY	The territory of the current session	Yes	Yes	Yes
OS_USER	The OS username for the user logged in	Yes	Yes	Yes
POLICY_INVOKER	The invoker of row-level security policy functions	No	Yes	Yes
PROXY_ENTERPRISE_IDENTITY	The Oracle Internet Directory DN when the proxy user is an enterprise user	No	Yes	Yes
PROXY_GLOBAL_UID	The global user ID from Oracle Internet Directory for enterprise user security proxy users. Returns NULL for all other proxy users.	No	Yes	Yes
PROXY_USER	The name of the user who opened the current session on behalf of SESSION_USER	Yes	Yes	Yes
PROXY_USERID	The identifier of the user who opened the current session on behalf of SESSION_USER	Yes	Yes	Yes
SERVER_HOST	The host name of the machine where the instance is running	No	Yes	Yes
SERVICE_NAME	The name of the service that the session is connected to	No	Yes	Yes
SESSION_USER	The database user name of the user logged in	Yes	Yes	Yes
SESSION_USERID	The database identifier of the user logged in	Yes	Yes	Yes
SESSIONID	The identifier of the auditing session	Yes	Yes	Yes
SID	Session number	No	Yes	Yes
STATEMENTID	The auditing statement identifier	No	Yes	Yes
TERMINAL	The OS identifier of the current session	Yes	Yes	Yes

SYS_CONTEXT

http://www.techonthenet.com/oracle/functions/sys_context.php

Parameter	Explanation	Oracle 9i	Oracle 10g	Oracle 11g
ACTION	Returns the position in the module	No	Yes	Yes
AUDITED_CURSORID	Returns the cursor ID of the SQL that triggered the audit	Yes	Yes	Yes
AUTHENTICATED_IDENTITY	Returns the identity used in authentication	No	Yes	Yes
AUTHENTICATION_DATA	Authentication data	Yes	Yes	Yes
AUTHENTICATION_METHOD	Returns the method of authentication	No	Yes	Yes
AUTHENTICATION_TYPE	Describes how the user was authenticated. It can be one of the following values: Database, OS, Network, or Proxy	Yes	No	No
BG_JOB_ID	If the session was established by an Oracle background process, this parameter will return the Job ID. Otherwise, it will return NULL.	Yes	Yes	Yes
CLIENT_IDENTIFIER	Returns the client identifier (global context)	Yes	Yes	Yes
CLIENT_INFO	User session information	Yes	Yes	Yes
CURRENT_BIND	Bind variables for fine-grained auditing	No	Yes	Yes
CURRENT_SCHEMA	Returns the default schema used in the current schema	Yes	Yes	Yes
CURRENT_SCHEMAID	Returns the identifier of the default schema used in the current schema	Yes	Yes	Yes
CURRENT_SQL	Returns the SQL that triggered the audit event	Yes	Yes	Yes
CURRENT_SQL_LENGTH	Returns the length of the current SQL statement that triggered the audit event	No	Yes	Yes
CURRENT_USER	Name of the current user	Yes	No	No
CURRENT_USERID	Userid of the current user	Yes	No	No
DB_DOMAIN	Domain of the database from the DB_DOMAIN initialization parameter	Yes	Yes	Yes
DB_NAME	Name of the database from the DB_NAME initialization parameter	Yes	Yes	Yes
DB_UNIQUE_NAME	Name of the database from the DB_UNIQUE_NAME initialization parameter	No	Yes	Yes
ENTRYID	Available auditing entry identifier	Yes	Yes	Yes
ENTERPRISE_IDENTITY	Returns the user's enterprise-wide identity	No	Yes	Yes
EXTERNAL_NAME	External of the database user	Yes	No	No
FG_JOB_ID	If the session was established by a client foreground process, this parameter will return the Job ID. Otherwise, it will return NULL.	Yes	Yes	Yes
GLOBAL_CONTEXT_MEMORY	The number used in the System Global Area by the globally accessed context	Yes	Yes	Yes
GLOBAL_UID	The global user ID from Oracle Internet Directory for enterprise security logins. Returns NULL for all other logins.	No	No	Yes
HOST	Name of the host machine from which the client has connected	Yes	Yes	Yes
IDENTIFICATION_TYPE	Returns the way the user's schema was created	No	Yes	Yes
INSTANCE	The identifier number of the current instance	Yes	Yes	Yes
INSTANCE_NAME	The name of the current instance	No	Yes	Yes
IP_ADDRESS	IP address of the machine from which the client has connected	Yes	Yes	Yes
ISDBA	Returns TRUE if the user has DBA privileges. Otherwise, it will return FALSE.	Yes	Yes	Yes
LANG	The ISO abbreviate for the language	Yes	Yes	Yes
LANGUAGE	The language, territory, and character of the session. In the following format: language_territory.characterset	Yes	Yes	Yes
MODULE	Returns the appplication name set through DBMS_APPLICATION_INFO package or OCI	No	Yes	Yes
NETWORK_PROTOCOL	Network protocol used	Yes	Yes	Yes
NLS_CALENDAR	The calendar of the current session	Yes	Yes	Yes
NLS_CURRENCY	The currency of the current session	Yes	Yes	Yes
NLS_DATE_FORMAT	The date format for the current session	Yes	Yes	Yes
NLS_DATE_LANGUAGE	The language used for dates	Yes	Yes	Yes
NLS_SORT	BINARY or the linguistic sort basis	Yes	Yes	Yes
NLS_TERRITORY	The territory of the current session	Yes	Yes	Yes
OS_USER	The OS username for the user logged in	Yes	Yes	Yes
POLICY_INVOKER	The invoker of row-level security policy functions	No	Yes	Yes
PROXY_ENTERPRISE_IDENTITY	The Oracle Internet Directory DN when the proxy user is an enterprise user	No	Yes	Yes
PROXY_GLOBAL_UID	The global user ID from Oracle Internet Directory for enterprise user security proxy users. Returns NULL for all other proxy users.	No	Yes	Yes
PROXY_USER	The name of the user who opened the current session on behalf of SESSION_USER	Yes	Yes	Yes
PROXY_USERID	The identifier of the user who opened the current session on behalf of SESSION_USER	Yes	Yes	Yes
SERVER_HOST	The host name of the machine where the instance is running	No	Yes	Yes
SERVICE_NAME	The name of the service that the session is connected to	No	Yes	Yes
SESSION_USER	The database user name of the user logged in	Yes	Yes	Yes
SESSION_USERID	The database identifier of the user logged in	Yes	Yes	Yes
SESSIONID	The identifier of the auditing session	Yes	Yes	Yes
SID	Session number	No	Yes	Yes
STATEMENTID	The auditing statement identifier	No	Yes	Yes
TERMINAL	The OS identifier of the current session	Yes	Yes	Yes