DBMS_APPLICATION_INFO

Summary of DBMS_APPLICATION_INFO Subprograms

Table 22-1 DBMS_APPLICATION_INFO Package Subprograms

Subprogram	Description
READ_CLIENT_INFO Procedure	Reads the value of the client_info field of the current session
READ_MODULE Procedure	Reads the values of the module and action fields of the current session
SET_ACTION Procedure	Sets the name of the current action within the current module
SET_CLIENT_INFO Procedure	Sets the client_info field of the session
SET_MODULE Procedure	Sets the name of the module that is currently running to a new module
SET_SESSION_LONGOPS Procedure	Sets a row in the V$SESSION_LONGOPS table

READ_CLIENT_INFO Procedure

This procedure reads the value of the client_info field of the current session.

Syntax

DBMS_APPLICATION_INFO.READ_CLIENT_INFO (
   client_info OUT VARCHAR2); 

Parameters

Table 22-2 READ_CLIENT_INFO Procedure Parameters

Parameter	Description
client_info	Last client information value supplied to the SET_CLIENT_INFO procedure.

READ_MODULE Procedure

This procedure reads the values of the module and action fields of the current session.

Syntax

DBMS_APPLICATION_INFO.READ_MODULE ( 
   module_name OUT VARCHAR2, 
   action_name OUT VARCHAR2); 

Parameters

Table 22-3 READ_MODULE Procedure Parameters

Parameter	Description
module_name	Last value that the module name was set to by calling SET_MODULE.
action_name	Last value that the action name was set to by calling SET_ACTION or SET_MODULE.

Usage Notes

Module and action names for a registered application can be retrieved by querying V$SQLAREA or by calling the READ_MODULE procedure. Client information can be retrieved by querying the V$SESSION view, or by calling the READ_CLIENT_INFO Procedure.

Examples

The following sample query illustrates the use of the MODULE and ACTION column of the V$SQLAREA.

SELECT sql_text, disk_reads, module, action 
FROM v$sqlarea 
WHERE module = 'add_employee'; 

SQL_TEXT DISK_READS MODULE ACTION 
------------------- ---------- ------------------ ---------------- 
INSERT INTO emp 1 add_employee insert into emp 
(ename, empno, sal, mgr, job, hiredate, comm, deptno) 
VALUES 
(name, next.emp_seq, manager, title, SYSDATE, commission, department) 

1 row selected.

SET_ACTION Procedure

This procedure sets the name of the current action within the current module.

Syntax

DBMS_APPLICATION_INFO.SET_ACTION (
   action_name IN VARCHAR2); 

Parameters

Table 22-4 SET_ACTION Procedure Parameters

Parameter	Description
action_name	The name of the current action within the current module. When the current action terminates, call this procedure with the name of the next action if there is one, or NULL if there is not. Names longer than 32 bytes are truncated.

Usage Notes

The action name should be descriptive text about the current action being performed. You should probably set the action name before the start of every transaction.
Set the transaction name to NULL after the transaction completes, so that subsequent transactions are logged correctly. If you do not set the transaction name to NULL, subsequent transactions may be logged with the previous transaction's name.

Example

The following is an example of a transaction that uses the registration procedure:

CREATE OR REPLACE PROCEDURE bal_tran (amt IN NUMBER(7,2)) AS 
BEGIN 

-- balance transfer transaction 

   DBMS_APPLICATION_INFO.SET_ACTION(
      action_name => 'transfer from chk to sav'); 
   UPDATE chk SET bal = bal + :amt 
      WHERE acct# = :acct; 
   UPDATE sav SET bal = bal - :amt 
      WHERE acct# = :acct; 
   COMMIT; 
   DBMS_APPLICATION_INFO.SET_ACTION(null); 

END;  

SET_CLIENT_INFO Procedure

This procedure supplies additional information about the client application.

Syntax

DBMS_APPLICATION_INFO.SET_CLIENT_INFO (
   client_info IN VARCHAR2); 

Parameters

Table 22-5 SET_CLIENT_INFO Procedure Parameters

Parameter	Description
client_info	Supplies any additional information about the client application. This information is stored in the V$SESSION view. Information exceeding 64 bytes is truncated.

Note:

CLIENT_INFO is readable and writable by any user. For storing secured application attributes, you can use the application context feature.

SET_MODULE Procedure

This procedure sets the name of the current application or module.

Syntax

DBMS_APPLICATION_INFO.SET_MODULE ( 
   module_name IN VARCHAR2, 
   action_name IN VARCHAR2); 

Parameters

Table 22-6 SET_MODULE Procedure Parameters

Parameter	Description
module_name	Name of module that is currently running. When the current module terminates, call this procedure with the name of the new module if there is one, or NULL if there is not. Names longer than 48 bytes are truncated.
action_name	Name of current action within the current module. If you do not want to specify an action, this value should be NULL. Names longer than 32 bytes are truncated.

Usage Notes

Example

CREATE or replace PROCEDURE add_employee( 
  name VARCHAR2, 
  salary NUMBER, 
  manager NUMBER, 
  title VARCHAR2, 
  commission NUMBER, 
  department NUMBER) AS 
BEGIN 
  DBMS_APPLICATION_INFO.SET_MODULE( 
    module_name => 'add_employee', 
    action_name => 'insert into emp'); 
  INSERT INTO emp 
    (ename, empno, sal, mgr, job, hiredate, comm, deptno) 
    VALUES (name, emp_seq.nextval, salary, manager, title, SYSDATE, 
            commission, department); 
  DBMS_APPLICATION_INFO.SET_MODULE(null,null); 
END;

SET_SESSION_LONGOPS Procedure

This procedure sets a row in the V$SESSION_LONGOPS view. This is a view that is used to indicate the on-going progress of a long running operation. Some Oracle functions, such as parallel execution and Server Managed Recovery, use rows in this view to indicate the status of, for example, a database backup.
Applications may use the SET_SESSION_LONGOPS procedure to advertise information on the progress of application specific long running tasks so that the progress can be monitored by way of the V$SESSION_LONGOPS view.

Syntax

DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS (
   rindex      IN OUT BINARY_INTEGER,
   slno        IN OUT BINARY_INTEGER,
   op_name     IN     VARCHAR2       DEFAULT NULL,
   target      IN     BINARY_INTEGER DEFAULT 0,
   context     IN     BINARY_INTEGER DEFAULT 0,
   sofar       IN     NUMBER         DEFAULT 0,
   totalwork   IN     NUMBER         DEFAULT 0,
   target_desc IN     VARCHAR2       DEFAULT 'unknown target',
   units       IN     VARCHAR2       DEFAULT NULL)  

set_session_longops_nohint constant BINARY_INTEGER := -1;

Parameters

Table 22-7 SET_SESSION_LONGOPS Procedure Parameters

Parameter	Description
rindex	A token which represents the v$session_longops row to update. Set this to set_session_longops_nohint to start a new row. Use the returned value from the prior call to reuse a row.
slno	Saves information across calls to set_session_longops: It is for internal use and should not be modified by the caller.
op_name	Specifies the name of the long running task. It appears as the OPNAME column of v$session_longops. The maximum length is 64 bytes.
target	Specifies the object that is being worked on during the long running operation. For example, it could be a table ID that is being sorted. It appears as the TARGET column of v$session_longops.
context	Any number the client wants to store. It appears in the CONTEXT column of v$session_longops.
sofar	Any number the client wants to store. It appears in the SOFAR column of v$session_longops. This is typically the amount of work which has been done so far.
totalwork	Any number the client wants to store. It appears in the TOTALWORK column of v$session_longops. This is typically an estimate of the total amount of work needed to be done in this long running operation.
target_desc	Specifies the description of the object being manipulated in this long operation. This provides a caption for the target parameter. This value appears in the TARGET_DESC field of v$session_longops. The maximum length is 32 bytes.
units	Specifies the units in which sofar and totalwork are being represented. It appears as the UNITS field of v$session_longops. The maximum length is 32 bytes.

Example

This example performs a task on 10 objects in a loop. As the example completes each object, Oracle updates V$SESSION_LONGOPS on the procedure's progress.

DECLARE
        rindex    BINARY_INTEGER;
        slno      BINARY_INTEGER;
        totalwork number;
        sofar     number;
        obj       BINARY_INTEGER;
 
      BEGIN
        rindex := dbms_application_info.set_session_longops_nohint;
        sofar := 0;
        totalwork := 10;
 
        WHILE sofar < 10 LOOP
          -- update obj based on sofar
          -- perform task on object target
 
          sofar := sofar + 1;
          dbms_application_info.set_session_longops(rindex, slno,
            "Operation X", obj, 0, sofar, totalwork, "table", "tables");
        END LOOP;
      END;

SENTENCIAS SQL PARA VALIDAR LOS PROCESOS EN EJECUCION

SELECT SQL_TEXT,MODULE,ACTION

FROM V$SQLAREA
WHERE SQL_TEXT = 'SELECT OWNER,COUNT(*) FROM ALL_OBJECTS GROUP BY OWNER ORDER BY OWNER'

SELECT sql_text, disk_reads, module, action 
FROM v$sqlarea 
WHERE module like 'DBA%'

CREATE USER CON ROLE SYSDBA DINAMICO

 set define on 

prompt 'Digite el nombre de usuario:' "&&USERNAME"

CREATE USER "&USERNAME" PROFILE "DBA" IDENTIFIED BY 0r1cl310g PASSWORD EXPIRE DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;

GRANT ADMINISTER ANY SQL TUNING SET TO "&USERNAME" ;

GRANT ADMINISTER DATABASE TRIGGER TO "&USERNAME" ;

GRANT DBA TO "&USERNAME" ;

GRANT ADMINISTER SQL TUNING SET TO "&USERNAME" ;

GRANT ADVISOR TO "&USERNAME" ;

GRANT ALTER ANY INDEX TO "&USERNAME" ;

GRANT ALTER ANY INDEXTYPE TO "&USERNAME" ;

GRANT ALTER ANY MATERIALIZED VIEW TO "&USERNAME" ;

GRANT ALTER ANY PROCEDURE TO "&USERNAME" ;

GRANT ALTER ANY SEQUENCE TO "&USERNAME" ;

GRANT ALTER ANY TABLE TO "&USERNAME" ;

GRANT ALTER ANY TRIGGER TO "&USERNAME" ;

GRANT ALTER DATABASE TO "&USERNAME" ;

GRANT ALTER SESSION TO "&USERNAME" ;

GRANT ALTER SYSTEM TO "&USERNAME" ;

GRANT ALTER TABLESPACE TO "&USERNAME" ;

GRANT ALTER USER TO "&USERNAME" ;

GRANT ANALYZE ANY TO "&USERNAME" ;

GRANT ANALYZE ANY DICTIONARY TO "&USERNAME" ;

GRANT BACKUP ANY TABLE TO "&USERNAME" ;

GRANT BECOME USER TO "&USERNAME" ;

GRANT CREATE ANY DIRECTORY TO "&USERNAME" ;

GRANT CREATE DATABASE LINK TO "&USERNAME" ;

GRANT CREATE JOB TO "&USERNAME" ;

GRANT CREATE PROFILE TO "&USERNAME" ;

GRANT CREATE ROLE TO "&USERNAME" ;

GRANT CREATE PUBLIC DATABASE LINK TO "&USERNAME" ;

GRANT CREATE PUBLIC SYNONYM TO "&USERNAME" ;

GRANT CREATE SESSION TO "&USERNAME" ;

GRANT EXECUTE ANY PROCEDURE TO "&USERNAME" ;

GRANT EXPORT FULL DATABASE TO "&USERNAME" ;

GRANT FLASHBACK ANY TABLE TO "&USERNAME" ;

GRANT GRANT ANY OBJECT PRIVILEGE TO "&USERNAME" ;

GRANT GRANT ANY PRIVILEGE TO "&USERNAME" ;

GRANT GRANT ANY ROLE TO "&USERNAME" ;

GRANT IMPORT FULL DATABASE TO "&USERNAME" ;

GRANT MANAGE TABLESPACE TO "&USERNAME" ;

GRANT RESTRICTED SESSION TO "&USERNAME" ;

GRANT SELECT ANY DICTIONARY TO "&USERNAME" ;

GRANT SELECT ANY SEQUENCE TO "&USERNAME" ;

GRANT SELECT ANY TABLE TO "&USERNAME" ;

GRANT "DBA" TO "&USERNAME" ;

GRANT "EXECUTE_CATALOG_ROLE" TO "&USERNAME" ;

GRANT "EXP_FULL_DATABASE" TO "&USERNAME" ;

GRANT "GATHER_SYSTEM_STATISTICS" TO "&USERNAME" ;

GRANT "IMP_FULL_DATABASE" TO "&USERNAME" ;

GRANT "MGMT_USER" TO "&USERNAME" ;

GRANT "OEM_ADVISOR" TO "&USERNAME" ;

GRANT "OEM_MONITOR" TO "&USERNAME" ;

GRANT "SELECT_CATALOG_ROLE" TO "&USERNAME" ;

PRIVILEGIOS V$SESSION

 SQL> GRANT SELECT ON V_$SESSION TO DBO;

DELETE ALERTAS ENTERPRISE MANAGER

delete from sysman.mgmt_current_severity;

commit;