How to Audit User Activity in Oracle Database

Step 1.

SQL> alter system set audit_trail=db scope=spfile;
System altered.

SQL> alter system set audit_sys_operations=true scope=spfile;

Step 2. stop Database

Step 3. start database.

Step 4 check parameter
SQL> show parameter audit_
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string          /d011/db11i/db/oaproddb/10.2.0/rdbms/audit
audit_sys_operations            boolean     TRUE
audit_syslog_level                string
audit_trail                            string           DB

Step 5. Log in as sysdba.

 SQL> audit all by apps by access;
     
OR
        Give the audit permission for delete and drop objects.

        SQL> audit DELETE ANY TABLE,DROP ANY INDEX,DROP ANY PROCEDURE,DROP ANY TABLE,DROP ANY VIEW,ALTER ANY PROCEDURE,ALTER ANY INDEX by apps
by access whenever successful;

Step 7. Check the operation (log in as sysdba);

 SQL> SELECT username, extended_timestamp, owner,obj_name ,action_name FROM dba_audit_trail WHERE  owner = 'APPS' and extended_timestamp=sysdate -1 ORDER BY timestamp;

 SQL> SELECT count(1) FROM dba_audit_trail WHERE  owner = 'APPS' and extended_timestamp=sysdate -1 ORDER BY timestamp;

 SQL> SELECT count(1) FROM dba_audit_trail WHERE  owner = 'APPS' and extended_timestamp=sysdate - 1

SELECT count(1) FROM dba_audit_trail WHERE  owner = 'APPS' ORDER BY timestamp;

Step 8. Check how many no of record in audit tables;
select count(1) from sys.aud$;

        

PL/SQL code sending email with single text attachment.

Step:-1
create a sequence,

create sequence file_id start with 1 increment by 1 nomaxvalue;



create a clob table,

create table file_test
(file_id number,
txt clob);

Step:-2

Create a procedure for populating the clob table with text file that needs to sent,

create or replace PROCEDURE load_file_swarup ( pfname VARCHAR2) IS
l_size number;
l_file_ptr bfile;
l_blob clob;
begin
l_file_ptr:= bfilename('KROSS_TEST', pfname);
dbms_lob.fileopen(l_file_ptr);
l_size:= dbms_lob.getlength(l_file_ptr);
insert into file_test ( file_id, txt )
values ( file_id.nextval, empty_clob() )
returning txt into l_blob;
dbms_lob.loadfromfile(l_blob, l_file_ptr, l_size);
commit;
dbms_lob.close(l_file_ptr);
end; 

Step:-3,

execute the procedure to populate the clob table,

begin
load_file_swarup('myfile.txt');
end;

Step:-4,

Create the procedure for sending the emaill with attachment,

CREATE OR REPLACE PROCEDURE send_mail (p_to          IN VARCHAR2,
                                       p_from        IN VARCHAR2,
                                       p_subject     IN VARCHAR2,
                                       p_text_msg    IN VARCHAR2 DEFAULT NULL,
                                       p_attach_name IN VARCHAR2 DEFAULT NULL,
                                       p_attach_mime IN VARCHAR2 DEFAULT NULL,
                                       p_attach_clob IN CLOB DEFAULT NULL,
                                       p_smtp_host   IN VARCHAR2,
                                       p_smtp_port   IN NUMBER DEFAULT 25)
AS
  l_mail_conn   UTL_SMTP.connection;
  l_boundary    VARCHAR2(50) := '----=*#abc1234321cba#*=';
  l_step        PLS_INTEGER  := 24573;
BEGIN
  l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
  UTL_SMTP.helo(l_mail_conn, p_smtp_host);
  UTL_SMTP.mail(l_mail_conn, p_from);
  UTL_SMTP.rcpt(l_mail_conn, p_to);

  UTL_SMTP.open_data(l_mail_conn);
 
  UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/mixed; boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);
 
  IF p_text_msg IS NOT NULL THEN
    UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);

    UTL_SMTP.write_data(l_mail_conn, p_text_msg);
    UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
  END IF;

  IF p_attach_name IS NOT NULL THEN
    UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'Content-Type: ' || p_attach_mime || '; name="' || p_attach_name || '"' || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'Content-Disposition: attachment; filename="' || p_attach_name || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

    FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_attach_clob) - 1 )/l_step) LOOP
      UTL_SMTP.write_data(l_mail_conn, DBMS_LOB.substr(p_attach_clob, l_step, i * l_step + 1));
    END LOOP;

    UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
  END IF;
 
  UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || '--' || UTL_TCP.crlf);
  UTL_SMTP.close_data(l_mail_conn);

  UTL_SMTP.quit(l_mail_conn);
END;

Step:- 5,

Execute the above mentioned procedure to send the mail finaly with one text attachment,

DECLARE
  l_clob CLOB ;
BEGIN
select a.TXT  into l_clob
from file_test a;
  send_mail(p_to          => 'swarup.ghosh@xyz.com',
            p_from        => 'faheiz.alam@xyz.com',
            p_subject     => 'Test Message',
            p_text_msg    => 'This is a test message.',
            p_attach_name => 'myfile.txt',
            p_attach_mime => 'text/plain',
            p_attach_clob => l_clob,
            p_smtp_host   => 'webmail.conacent.com');
END;

Code for excute a data loader from oracle forms.

In "When Button Pressed " Trigger of a button in the form, write the below mentioned code,

DECLARE

   usid            VARCHAR2 (10)   := 'apps';

   pwd             VARCHAR2 (10)   := 'wipl10';

   db              VARCHAR2 (10)   := 'dev';

   msqlldr         VARCHAR2 (300);

   ctrl_filename   VARCHAR2 (300)

               := '/c03/DEV/apps/apps_st/appl/payapps/12.0.0/bin/my_test.ctl';

   data_filename   VARCHAR2 (300)

               := '/c03/DEV/apps/apps_st/appl/payapps/12.0.0/bin/my_data.csv';

   fname           VARCHAR2 (1000);

BEGIN

   msqlldr :=

         'sqlldr'

      || ' userid='

      || usid

      || '/'

      || pwd

      || '@'

      || db

      || ' control='

      || ctrl_filename

      || ' data='

      || data_filename;

   HOST (msqlldr, no_screen);

END;

Usid :- is the user id of the database.

pwd:- is the password of the database

db:- the database sid.

ctrl_filename:- is the control file location along with the control file name in the server

data_filename:- is the control file location along with the control file name in the server

Code for Calling a XML report from Oracle Forms.

DECLARE
   lc_boolean        BOOLEAN;
   ln_request_id     NUMBER;
   lc_boolean1       BOOLEAN;
   lc_boolean2       BOOLEAN;
BEGIN

      -- Initialize Apps 
      fnd_global.apps_initialize (>USER_ID<
                                 ,>RESP_ID<
                                 ,>RESP_APPL_ID<
                                 );
  
  lc_boolean2 :=
               fnd_request.add_layout (
                            template_appl_name   => 'Template Application',
                            template_code        => 'Template Code',
                            template_language    => 'en', --Use language from template definition
                            template_territory   => 'US', --Use territory from template definition
                            output_format        => 'PDF/EXCEL' --Use output format from template definition
                                    );
   ln_request_id :=
      fnd_request.submit_request ('FND',                -- application
                                  'COCN_PGM_SHORT_NAME',-- program short name
                                  '',                  
                                  '',                  
                                  FALSE,               
                                  CHR (0)              
                                 );
   COMMIT;

   IF ln_request_id = 0
   THEN
      dbms.output.put_line ('Concurrent request failed to submit');
   END IF;
END;

JSP Page Personalization in ORACLE EBS

REQUIREMENT: How to show Item Number at PO Requisition Approval Notification

Set the following profile options at user level

1. Disable Self-Service Personal = No

2. Personalize Self-Service Defn = Yes

3. FND: Personalization Region Link Enabled = Yes

Click on Personalize "Requisition Lines"

How to find the text attachemnts attached to a Requisition Header

     /* Step:-1 -> Find the requisition */
     
      select b.REQUISITION_HEADER_ID from po_requisition_headers_all b where   trunc(b.CREATION_DATE)='25-MAR-2013'
     
      /*Step:- 2 - >   Find The document id (Here PK1_VALUE is the requisition_header_id) */
     
      select a.PK1_VALUE ,a.DOCUMENT_ID ,a.* from apps.FND_ATTACHED_DOCUMENTS a where a.PK1_VALUE =95355(requisition_header_id)
     
      /* Step :- 3 ->  Find the media_id */
     
      select a.MEDIA_ID ,a.* from fnd_documents a where a.DOCUMENT_ID=160936
     
      /*Step:- 4 -> Find the  Input Text in the requisition */
     
      select a.SHORT_TEXT,a.MEDIA_ID from fnd_documents_short_text a where a.MEDIA_ID=2;

How to attach multiple templates (.rtf) to a single Report

1.      Create your multiple RTFs.

2.     Now go to XML Publisher Administrator and create a Data Definition.

3. We need to create multiple templates against the same Data Definition.

       Similarly, you need to create multiple template against the same Data Definition ( e.g.           KCT000RCONA  here).

4.     Now run the Report from SRS window.

Submit the request and you will get output according to your selected template.

      

       

      

SORTING AUTOCREATE DATA

At the time of Auto create if data are coming without maintaining any order you can sort them in order. The process is showing below.

HOW TO INCREASE NUMBER OF LINES IN APPROVAL NOTIFICATION

Put the number that you want to display in approval notification in the following profile option.
"PO: Notification Lines Display Limit"

Enable Trace for long running Concurrent Request in Apps 11i/R12

Enable Trace for long running Concurrent Request in Apps 11i/R12

This post covers overview of How to troubleshoot long running concurrent request in Oracle
Apps 11i/R12

Step 1 : Check Concurrent Request ID of long running concurrent request from front end
Step 2 : Find SID, SERIAL# and SPID by running SQL (given below)
Step 3 : Enable event 10046 trace with level 12 using oradebug ( for 15-20 minute)
Step 4 : Disable trace (once you are happy with trace size)
Step 5 : Convert raw trace to TKPROF using various sort options like fchela, prsela, execpu
Step 6 : Check TKPROF out file to find root cause of slow concurrent request

Step 1 : Check Request ID from Find Concurrent request screen (In my case Request ID is 1145)

Step 2 : Run below command to find SPID, provide concurrent request ID (1145 in my case)
when prompted

SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID;


REQUEST_ID   SID    SERIAL#             OSUSER                       PROCESS           SPID
—————----------------------------------------------------------------------------
1145              514     28                  applmgr                     17794           2987.
.
Step 3.1 : Check and confirm SPID on Database Node
$ ps-ef | grep 2987
proddb 2987 1 0 13:30:43 ?
0:03 oracledbr12 (LOCAL=NO)

Step 3.2 : Set OSPID (2987 in my case) for ORADEBUG
SQL> oradebug setospid 2987

Step 3.3 : Enable trace for 10046 event with level 12
SQL> oradebug event 10046 trace name context forever, level 12
LEVEL 12 – Both Binds and Waits
LEVEL 8 – Only WAITS
LEVEL 4 – Only BIND Variables

Step 3.4 : Locate Trace file as
SQL>oradebug tracefile_name
/oracle/apps/proddb/10.2.0/admin/prod_CSDoracle/udump/ prod _ora_2987.trc
Wait for 15-20 minutes


Step 4 : Disable trace
SQL> oradebug event 10046 trace name context off


Step 5: Create tkprof file like
$ tkprof prod _ora_2987.trc prod _ora_2987.txt explain=apps/<****> sort=(exeela,fchela)
sys=no


Step 6 : Check TKPROF file to find root cause of slow concurrent request


We can follow the Metalink Note: 296559.1 to know more about tracing.