Friday, June 26, 2015

Oracle Apps documentation: R12 docs and 11i Docs

Oracle provides several User and implementation guides for each module, below are the important links that provide them


R12:
http://docs.oracle.com/cd/B34956_01/current/html/docset.html


11i:
http://docs.oracle.com/cd/B25284_01/current/html/docset.html

Oracle Etrm link:

https://etrm.oracle.com/


Wednesday, June 24, 2015

Oracle Global Temporary Tables

Global Temporary Tables (GTT)
Applications often use some form of temporary data store for processes that are to complicated to complete in a single pass. Often, these temporary stores are defined as database tables or PL/SQL tables. From Oracle 8i onward, the maintenance and management of temporary tables can be delegated to the server by using Global Temporary Tables.

Creation of Global Temporary Tables
The data in a global temporary table is private, such that data inserted by a session can only be accessed by that session. The session-specific rows in a global temporary table can be preserved for the whole session, or just for the current transaction. The ON COMMIT DELETE ROWS clause indicates that the data should be deleted at the end of the transaction.

CREATE GLOBAL TEMPORARY TABLE XXX_temp_table (
  COL1  NUMBER,
  COL2  NUMBER
) ON COMMIT DELETE ROWS;


In contrast, the ON COMMIT PRESERVE ROWS clause indicates that rows should be preserved until the end of the session.
CREATE GLOBAL TEMPORARY TABLE XXX_temp_table (
  COL1  NUMBER,
  COL2  NUMBER
) ON COMMIT PRESERVE ROWS;


Important points
If the TRUNCATE statement is issued against a temporary table, only the session specific data is truncated. There is no affect on the data of other sessions.

Data in temporary tables is stored in temp segments in the temp tablespace.

Data in temporary tables is automatically deleted at the end of the database session, even if it ends abnormally.

Indexes can be created on temporary tables. The content of the index and the scope of the index is the same as the database session.

Views can be created against temporary tables and combinations of temporary and permanent tables.
Temporary tables can have triggers associated with them.

Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.

Statistics on temporary tables are common to all sessions. Oracle 12c allows session specific statistics.

There are a number of restrictions related to temporary tables but these are version specific.

Wednesday, June 17, 2015

Oracle Apps V$Session Tables: Find Session info

V$SESSION
This view lists session information for each current session.

V$PROCESS
This view contains information about the currently active processes. While the LATCHWAIT column indicates what latch a process is waiting for, the LATCHSPIN column indicates what latch a process is spinning on. On multi-processor machines, Oracle processes will spin on a latch before waiting on it.

V$SESSION_WAIT
V$SESSION_WAIT displays the current or last wait for each session.

V$SESSION_LONGOPS
This view displays the status of various operations that run for longer than 6 seconds (in absolute time).


Oracle standard scripts.
Session_waits.sql script uses the v$session_wait view to display a list of the events currently being waited on by active sessions.  This is useful when trying to discover what each active session is currently doing.


session_waits.sql

SET LINESIZE 200
SET PAGESIZE 1000

COLUMN username FORMAT A20
COLUMN event FORMAT A30
COLUMN wait_class FORMAT A15

SELECT NVL(s.username, '(oracle)') AS username,
       s.sid,
       s.serial#,
       sw.event,
       sw.wait_class,
       sw.wait_time,
       sw.seconds_in_wait,
       sw.state
FROM   v$session_wait sw,
       v$session s
WHERE  see code depot for full script
s.sid = sw.sid
ORDER BY sw.seconds_in_wait DESC;  

longops.sql
The use of the dbms_application_info package to register long operations was discussed in a previous section.  The longops.sql scripts uses the v$session_longops view to identify and monitor sessions performing long operations, including those registered using the dbms_application_info package.


COLUMN sid FORMAT 999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A30
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10

SELECT s.sid,
       s.serial#,
       s.machine,
       ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
       ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
       ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM   v$session s,
       v$session_longops sl
WHERE s.sid     = sl.sid
AND    s.serial# = sl.serial#;


Sessions.sql
The sessions.sql script uses the v$session view to provide information about all the sessions currently connected to the database instance.  In addition, the v$process view is used to provide the associated operating system process id (SPID) for each session.

SET LINESIZE 500
SET PAGESIZE 1000

COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20

SELECT NVL(s.username, '(oracle)') AS username,
       s.osuser,
       s.sid,
       s.serial#,
       p.spid,
       s.lockwait,
       s.status,
       s.module,
       s.machine,
       s.program,
       TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM   v$session s,
       v$process p
WHERE s.paddr = p.addr
ORDER BY s.username, s.osuser;