UTL_HTTP and SSL (HTTPS) using Oracle Wallets

 

1- Install Wallets in Oracle

In order to make connections to a secured resource, we need to get the necessary certificate.

Using the browser, go to the URL you are attempting to access from PL/SQL.

In this case "https://allotaxi.com.lb/".

Click the lock icon in the URL bar to display the certificate menu and click on the "Connection" tab.

Click the "Certificate information" link and click the "Certification Path" tab on the resulting dialog.

For the root node in the "Certification path", highlight the node and click the "View Certificate" button.

On the resulting dialog, click the "Details" tab and click the "Copy to File..." button to save the certificate information.

On the resulting wizard, do the following.

●     Click the "Next" button on the welcome screen.

●     Select the "wallet (.CER)" option and click the "Next" button. Other formats work, but I've found this to be the most consistent.

●     Enter suitable file name and click the "Next" button.

●     Click the "Finish" button.

2- Create an Oracle Wallet Containing the Certificates

Create a new location to hold the wallet.

$ mkdir -p /u01/app/oracle/admin/12c/wallet

Create a new wallet.

$ orapki wallet create -wallet /u01/app/oracle/admin/12c/wallet -pwd WalletPasswd123 -auto_login

Test Secured Connection.

We are now ready to access the secured resource, but we must provide the UTL_HTTP package with the wallet details so it can make the secured connections. This is done using the UTL_HTTP.SET_WALLET procedure.

SET SERVEROUTPUT ON

EXEC UTL_HTTP.set_wallet('file:/u01/app/oracle/admin/12c/wallet', 'WalletPasswd123');

EXEC show_html_from_url('https://allotaxi.com.lb/');

 

... HTML output removed ...

 

PL/SQL procedure successfully completed.

 

SQL>

3- Authentication

If you are accessing a site that requires authentication, you will need to do one of two things depending on the type of authentication used.

If the site uses basic authentication, simply specify the credentials in the call to SHOW_HTOM_FROM_URL, which will use them in the UTL_HTTP.SET_AUTHENTICATION call.

SET SERVEROUTPUT ON

EXEC UTL_HTTP.set_wallet('file:/u01/app/oracle/admin/12c/wallet', 'WalletPasswd123');

EXEC show_html_from_url('https://allotaxi.com.lb/', 'username', 'password');

 

... HTML output removed ...

 

PL/SQL procedure successfully completed.

 

SQL>

If the page uses digest authentication, then you will need to will need to install the digest_auth_api package.

Test code.

SET SERVEROUTPUT ON

EXEC show_html_from_url('https://allotaxi.com.lb/', 'username', 'password', '/u01/app/oracle/admin/12c/wallet', 'WalletPasswd123');

 

... HTML output removed ...

 

PL/SQL procedure successfully completed.

 

SQL>

4- Firewall port 443 needs to be opened for outbound.

Configure WebLogic Server to Capture Client IP Addresses Behind a Load Balancer

 

Customer Recommended How to Configure WebLogic Server to Capture Client IP Addresses Behind a Load Balancer Using Extended Log Format (Doc ID 1602379.1)

1- Log into your WLS machine, and run the $WL_HOME/server/bin/setWLSEnv.sh script to initialize the necessary environment variables, such as location of java, and javac (compiler) that you will need.

In a text editor, create a file called, for example, "GWXFF.java" with the following:

[oracle@test~]$ cd /u01/install/PANSOFT/abd/scripts/
[oracle@test~]$ vi GWXFF.java
[oracle@test scripts]$ cat GWXFF.java

import weblogic.servlet.logging.CustomELFLogger;
import weblogic.servlet.logging.FormatStringBuffer;
import weblogic.servlet.logging.HttpAccountingInfo;
 
/* This example outputs the X-Forwarded-For
 * field into a custom field called GWXFF
 * */
public class GWXFF implements CustomELFLogger{
 public void logField(HttpAccountingInfo metrics, FormatStringBuffer buff) {
  buff.appendValueOrDash(metrics.getHeader("X-Forwarded-For"));
 }
}

2-Save this file. Notice the relation between the class name and the name of the file.

[oracle@test bin]$ cd /u01/install/APPS/fs2/FMW_Home/wlserver_10.3/server/bin

[oracle@test bin]$ . setWLSEnv.sh

CLASSPATH=/u01/install/APPS/fs2/FMW_Home/patch_wls1036/profiles/default/sys_manifest_classpath/weblogic_patch.jar:/u01/install/APPS/fs2/EBSapps/comn/util/jdk64/lib/tools.jar:/u01/install/APPS/fs2/FMW_Home/wlserver_10.3/server/lib/weblogic_sp.jar:/u01/install/APPS/fs2/FMW_Home/wlserver_10.3/server/lib/weblogic.jar:/u01/install/APPS/fs2/FMW_Home/modules/features/weblogic.server.modules_10.3.6.0.jar:/u01/install/APPS/fs2/FMW_Home/wlserver_10.3/server/lib/webservices.jar:/u01/install/APPS/fs2/FMW_Home/modules/org.apache.ant_1.7.1/lib/ant-all.jar:/u01/install/APPS/fs2/FMW_Home/modules/net.sf.antcontrib_1.1.0.0_1-0b2/lib/ant-contrib.jar:/u01/install/APPS/fs2/EBSapps/comn/util/jdk32/lib/dt.jar:/u01/install/APPS/fs2/EBSapps/comn/util/jdk32/lib/tools.jar:/u01/install/APPS/fs2/EBSapps/comn/util/jdk32/jre/lib/rt.jar:/u01/install/APPS/fs2/EBSapps/comn/shared-libs/ebs-3rdparty/WEB-INF/lib/ebs3rdpartyManifest.jar:/u01/install/APPS/fs2/FMW_Home/Oracle_EBS-app1/shared-libs/ebs-fmw/WEB-INF/lib/ebsFMWManifest.jar:/u01/install/APPS/fs2/FMW_Home/Oracle_EBS-app1/shared-libs/ebs-appsborg/WEB-INF/lib/ebsAppsborgManifest.jar:/u01/install/APPS/fs2/FMW_Home/oracle_common/modules/oracle.uix_11.1.1/uix2.jar:/u01/install/APPS/fs2/EBSapps/comn/java/classes:/u01/install/APPS/fs2/EBSapps/10.1.2/forms/java:/u01/install/APPS/fs2/EBSapps/10.1.2/forms/java/frmall.jar:/u01/install/APPS/fs2/EBSapps/10.1.2/jlib/ewt3.jar:/u01/install/APPS/fs2/EBSapps/10.1.2/j2ee/OC4J_BI_Forms/applications/formsapp/formsweb/WEB-INF/lib/frmsrv.jar

 

PATH=/u01/install/APPS/fs2/FMW_Home/wlserver_10.3/server/bin:/u01/install/APPS/fs2/FMW_Home/modules/org.apache.ant_1.7.1/bin:/u01/install/APPS/fs2/EBSapps/comn/util/jdk64/jre/bin:/u01/install/APPS/fs2/EBSapps/comn/util/jdk64/bin:/u01/install/APPS/fs_ne/EBSapps/appl/ad/bin:/u01/install/APPS/fs2/FMW_Home/modules/org.apache.ant_1.7.1/bin:/u01/install/APPS/fs2/inst/apps/QA_ebsqaapp01/admin/scripts:/u01/install/APPS/fs2/FMW_Home/webtier/perl/bin:/u01/install/APPS/fs2/EBSapps/10.1.2/bin:/u01/install/APPS/fs2/EBSapps/appl/fnd/12.0.0/bin:/u01/install/APPS/fs2/EBSapps/appl/ad/12.0.0/bin:/u01/install/APPS/fs2/EBSapps/comn/util/jdk32/jre/bin:/u01/install/APPS/fs2/EBSapps/comn/util/jdk32/bin::/u01/install/APPS/fs2/EBSapps/10.1.2/OPatch:/u01/install/APPS/fs2/EBSapps/10.1.2/bin:/usr/bin:/usr/sbin:/u01/install/APPS/fs2/EBSapps/comn/util/jdk32/jre/bin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin

 

Your environment has been set.

3- Compile this file as follows

[oracle@test scripts]$ javac GWXFF.java

You will now have a file called GWXFF.class.

[oracle@test scripts]$ ll

-rw-r-----. 1 oracle oinstall  675 Jun 11 11:29 GWXFF.class

-rw-r-----. 1 oracle oinstall  441 Jun 11 11:29 GWXFF.java

4- Next, create a jar package out of the GWXFF.class file.

[oracle@test scripts]$ jar cvf GWXFF.jar GWXFF.class

added manifest

adding: GWXFF.class(in = 675) (out= 370)(deflated 45%)

[oracle@test scripts]$ ll

total 28

-rwxr-xr-x. 1 oracle oinstall 4866 May 29 09:58 cmclean.sql

-rw-r-----. 1 oracle oinstall  675 Jun 11 11:29 GWXFF.class

-rw-r-----. 1 oracle oinstall  825 Jun 11 13:49 GWXFF.jar

-rw-r-----. 1 oracle oinstall  441 Jun 11 11:29 GWXFF.java

-rwxr-xr-x. 1 oracle oinstall  266 May 29 10:12 startprimary.sh

-rwxr-xr-x. 1 oracle oinstall  445 May 29 10:09 stopprimary.sh

Place this jar file at a location that is visible to the managed servers.

/u01/install/PANSOFT/abd/scripts/GWXFF.jar

5- Add the Custom jar File to the CLASSPATH.

If you are starting managed servers from the admin console, and you do NOT have start scripts enabled for Node Manager, follow these steps:

1. Log into the Admin Console as the WebLogic Admin user. Navigate to Environment -> Servers -> Click on name of managed server. Then select Configuration and Server Start.
2. Under "Class Path", add the absolute path to the GWXFF.jar file from above.
3. Click Save, you will be prompted to restart the server. But do not restart it yet.

If you are starting managed servers separately from the admin console or have start scripts enabled, add the absolute path to the GWXFF.jar to the classpath in the managed server start script.

6- Configuring Extended Log Format.

1. While still logged into the Admin console, browse to Servers -> Managed Server -> Logging -> HTTP -> Advanced and select Format.
2. Change from "Common" to "Extended."
3. In the Extended Logging Fields, add "x-GWXFF" to the fields already there.
4. Click save.
5. You may be informed that these changes do not require a restart, when in fact they do.
6. Restart the managed server.

7- Bounce the managed server to which changes have made and kindly test if Ip tracing is enabled.

When attempting to clone E-Business Suite script adcfgclone.pl fails with the following error

Error:- 

The JDK wasn't found in directory /u01/oracle/proderp/fs2/EBSapps/comn/util/jdk64.

Please edit the startWebLogic.sh script so that the JAVA_HOME

variable points to the location of your JDK.

 

CLASSPATH=/u01/oracle/proderp/fs1/FMW_Home/Oracle_EBS-app1/shared-libs/ebs-appsborg/WEB-INF/lib/ebsAppsborgManifest.jar:/u01/oracle/proderp/fs1/FMW_Home/Oracle_EBS-app1/shared-libs/ebs-fmw/WEB-INF/lib/ebsFMWManifest.jar:/u01/oracle/proderp/fs1/EBSapps/comn/shared-libs/ebs-3rdparty/WEB-INF/lib/ebs3rdpartyManifest.jar:/u01/oracle/proderp/fs1/FMW_Home/oracle_common/modules/oracle.uix_11.1.1/uix2.jar:/u01/oracle/proderp/fs1/EBSapps/comn/java/classes:/u01/oracle/proderp/fs1/EBSapps/10.1.2/forms/java/frmall.jar:/u01/oracle/proderp/fs1/EBSapps/10.1.2/jlib/ewt3.jar:/u01/oracle/proderp/fs1/EBSapps/10.1.2/j2ee/OC4J_BI_Forms/applications/formsapp/WEB-INF/lib/frmall.jar:/u01/oracle/proderp/fs1/FMW_Home/utils/config/10.3/config-launch.jar::/u01/oracle/proderp/fs1/FMW_Home/wlserver_10.3/common/derby/lib/derbynet.jar:/u01/oracle/proderp/fs1/FMW_Home/wlserver_10.3/common/derby/lib/derbyclient.jar:/u01/oracle/proderp/fs1/FMW_Home/wlserver_10.3/common/derby/lib/derbytools.jar::

/u01/oracle/proderp/fs1/FMW_Home/wlserver_10.3/common/bin/wlst.sh: line 24: /u01/oracle/proderp/fs2/EBSapps/comn/util/jdk64/bin/java: No such file or directory

FINE : Oct 18, 2020 14:12:13 - [WLSTCommandUtil:executePyScript] Error while executing the wlst script /tmp/CLONINGCLIENT-3172440716856000683/tmp1603019533838.py

FINE : Oct 18, 2020 14:12:13 - [CloneLogger:unSetSystemOutErrToCloneLogger] Invoking unsetSystemOutErrToCloneLogger 1

FINE : Oct 18, 2020 14:12:13 - [CloneLogger:unSetSystemOutErrToCloneLogger] Invoking unsetSystemOutErrToCloneLogger 2

FINE : Oct 18, 2020 14:12:13 - CLONE-22264   Time taken to restore sourceid "J2EECOMPONENT@EBS_domain" is "1,763" milliseconds.

FINE : Oct 18, 2020 14:12:13 - [ApplyCloneRequest:applyArchive] One cloner object was not executed properly, so rest were not executed.

INFO : Oct 18, 2020 14:12:13 - CLONE-21037   Time taken to restore all cloners from archive was "1" seconds.

INFO : Oct 18, 2020 14:12:13 - CLONE-21006   Total time taken by T2P process was 1 seconds.

 

INFO : Oct 18, 2020 14:12:13 - CLONE-21276 

 

Solution/Workaround:

 

1) Recreate OraInventory.

2) extract EBSapps tar under run and patch system(Fs1 & Fs2).

3) run adcfgclone.

 

Clone Obiee 12c

Follow these steps:

1) Restore database. 

2) Install OBIEE 12c on new environment.

3) Import a clean state or Empty BAR file.

4) Ensure that all the services are up and running.

5) Export BAR file from existing OBIEE 12c environment.

6) Import the BAR file (of the existing OBIEE 12c environment) into the new OBIEE 12c environment. 

Backup the source database instance -cold backup

Backup on-premise database and Oracle home 

 

-           SQL> show pdbs

 

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY NO

         3 ORCLPDB                        MOUNTED

         4 PDBOBIEE                       MOUNTED

         5 PDBOBIEE11G                    MOUNTED

         6 PDBOBIEEP12                    MOUNTED

-           SQL> alter pluggable database all close;

 

Pluggable database altered.

 

Transfer Datafiles and Oracle-home backup to Backup folder

[obiee@pan backup]$ ls

dbhome_1.tar.gz  oradata

[obiee@pan backup]$ ls -ltr

total 3549404

-rw-rw-r-- 1 obiee obiee 3634586360 May 12  2019 dbhome_1.tar.gz

drwxr-x--- 3 obiee obiee         18 May 12  2019 oradata

[obiee@pan backup]$

Transfer Datafiles and Oracle-home backup to Oracle OBIEE Cloud server

 

 

[obiee@pan backup]$scp -rp backup obiee@hostname:/DATA

Export BAR file from existing OBIEE 12c environment.

 

Export BI Application Archive file

[obiee@pan bin]$ pwd

/OBIEE/middleware/oracle/oracle_common/common/bin

 [obiee@pan bin]$ ./wlst.sh

Initializing WebLogic Scripting Tool (WLST) ...

Welcome to WebLogic Server Administration Scripting Shell

Type help() for help on available commands

 

wls:/offline> exportServiceInstance('/OBIEE/middleware/oracle/user_projects/domains/bi12','ssi','/DATA/data_backup12/workDir','/DATA/data_backup12/exportDir')

 

Output  BI Application Archive file

 

[obiee@pan 20190720_150429]$ pwd

/DATA/data_backup12/exportDir/20190720_150429

[obiee@pan 20190720_150429]$ ls

ssi.bar

[obiee@pan 20190720_150429]$

 

Transfer BAR files to Oracle OBIEE Cloud server

[obiee@pan backup]$scp -rp /DATA/data_backup12 obiee@hostname:/OBIEE

 Restore Oracle Home and database on Oracle OBIEE cloud

 Create Directories

 

mkdir -p /DATA/oradb/product/12.2.0

mkdir -p /DATA/oradb/oraInventory

mkdir -p /DATA/oradata/orcl

mkdir -p /DATA/orcl/datafile

vi  /etc/oraInst.loc

inventory_loc=/DATA/oradb/oraInventory

inst_group=dba

Copy Datafiles from backup to /DATA/oradata/orcl and /DATA/orcl/datafile

[obiee@pan backup]$tar -xvzf oradata.tar.gz /Data

[obiee@pan backup]$ tar -xvzf oracl.tar.gz /Data

 

Unzip Oracle Home

[obiee@pan 12.2.0]$ pwd

/DATA/oradb/product/12.2.0

[obiee@pan 12.2.0]$tar -xvzf dbhome_1.tar.gz

Startup database using PFILE

 

[obiee@pan dbs]$ sqlplus / as  sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 2 12:40:03 2019

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

Connected to an idle instance.

SQL> !ls

hc_orcl.dat  init.ora  initorcl.ora  lkORCL  orapworcl  spfileorcl.ora

 

SQL> !pwd

/DATA/oradb/product/12.2.0/dbhome_1/dbs

SQL> startup pfile='/DATA/oradb/product/12.2.0/dbhome_1/dbs/initorcl.ora';

ORACLE instance started.

 

Total System Global Area 1.9663E+10 bytes

Fixed Size                  8802784 bytes

Variable Size            3154120224 bytes

Database Buffers         1.6442E+10 bytes

Redo Buffers               58302464 bytes

Database mounted.

Database opened.

SQL> alter pluggable database all open;

 

Pluggable database altered.

 

SQL> show pdbs

 

    CON_ID CON_NAME                       OPEN MODE RESTRICTED

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

         2 PDB$SEED                       READ ONLY NO

         3 ORCLPDB                        READ WRITE NO

         4 PDBOBIEE                       READ WRITE NO

         5 PDBOBIEE11G                    READ WRITE NO

         6 PDBOBIEEP12                    READ WRITE NO

SQL>

 

Reload listener

 lsnrctl status LISTENER

Oracle Business Intelligence 12c (12.2.1.3.0) Installation

 

Pre-Requisite

• Oracle Enterprise Edition Database installation
• JDK 8 Update 51 Installation
• Fusion Middleware 12.2.1 Installation
• Following are the series of steps covered in this section
• Download & Unzip OBIEE 12.2.1
• OBIEE 12.2.1 Installation
• Create BI Schemas using RCU utility
• Set Environment Variable for BI
• Create a BI Domain
• Verify Oracle Business Intelligence URL’s
• Start & Stop BI Server

 

 Import the BAR file into the new OBIEE 12c environment.

Import the BAR file (of the existing OBIEE 12c environment) into the new OBIEE 12c environment. 

 

[obiee@pan2 ~]$ . middleware.env

[obiee@pan2~]$ more middleware.env

export MW_HOME=/OBIEE/middleware/oracle

export WLS_HOME=$MW_HOME/wlserver

export WL_HOME=$WLS_HOME

export DOMAIN_HOME=$MW_HOME/user_projects/domains/bi12

# Set to the appropriate JAVA_HOME.

export JAVA_HOME=/OBIEE/JAVA/jdk1.8.0_221

export PATH=$JAVA_HOME/bin:$PATH

[obiee@pan2 ~]$ cd $MW_HOME

[obiee@pan2 oracle]$ cd oracle_common/common/bin

[obiee@pan2 bin]$ ls

[obiee@pan2 bin]$ ./wlst.sh

Initializing WebLogic Scripting Tool (WLST) ...

Welcome to WebLogic Server Administration Scripting Shell

Type help() for help on available commands

wls:/offline> importServiceInstance('/OBIEE/middleware/oracle/user_projects/domains/bi12','ssi','/OBIEE/importDir/ssi.bar')

 

 Dweblogic.security.allowCryptoJDefaultPRNG=true.>

 

Imported private key demokey.pem and certificate democert.pem

into a new keystore DemoIdentity.jks of type jks under alias demoidentity

[obiee@pan2 bin]

 

Stop OBIEE service and Start

• Obiee clone completed.

How to find out currently running databases in RAC setup

You can query for these background processes to tell if you are using RAC.

select
   name,
   value
from
   v$parameter
where
   name='cluster_database';
NAME                  VALUE
--------------------- ---------------------
cluster_database      TRUE

How can check previous databases in Oracle through command line

Open a command window.

Configure the operating system environment variables.

Ensure that the environment variables ORACLE_HOME and ORACLE_SID are set properly. The commands to use to set these environment variables depend on the shell you use to interface with the operating system. For example:

(bash or ksh) export ORACLE_SID=orcl

(csh or tcsh) setenv ORACLE_SID orcl

You can set these with the scripts coraenv (for the C shell) and oraenv (for other shells). These scripts are typically located in the /usr/local/bin directory.

Ensure that the $ORACLE_HOME/bin directory is in your PATH environment variable.

You can also edit the profile file for your default shell in the home directory of the software owner, for example /home/oracle, so that these environment variables are set every time you log in as that user.

Start SQL*Plus using a command in the following format:

sqlplus {username | /} [as sysdba]

An example of this command is:

$ sqlplus / AS SYSDBA

Enter password: password

For username, you can use the SYS or SYSTEM administrative users. At the prompt, enter the password that you set up during installation. If you use the SYS user, you must include AS SYSDBA after the username.

SQL*Plus connects you to the default database instance (Microsoft Windows) or the database instance specified by environment variables (Linux and UNIX).

To check Lock on object

 col object_name for a30

col program for a30

col process for a10

col action for a35

set lines 200 pages 300

select p.spid, s.action,s.process, lo.locked_mode , sid, s.serial#, s.LAST_CALL_ET/60, do.object_name,s.status

from  V$session s, V$process p, V$locked_object lo, dba_objects do

  where

  s.paddr = p.addr

  and s.sid=lo.session_id

  and lo.object_id = do.object_id

  and do.object_name in ('OE_ORDER_LINES_ALL','OE_ORDER_HEADERS_ALL')

  and s.LAST_CALL_ET/60 >15

SELECT b.session_id AS sid,

NVL(b.oracle_username, '(oracle)') AS username,

a.owner AS object_owner,

a.object_name,

Decode(b.locked_mode, 0, 'None',

1, 'Null (NULL)',

2, 'Row-S (SS)',

3, 'Row-X (SX)',

4, 'Share (S)',

5, 'S/Row-X (SSX)',

6, 'Exclusive (X)',

b.locked_mode) locked_mode,

b.os_user_name

FROM dba_objects a,

v$locked_object b

WHERE a.object_id = b.object_id

and a.object_name = 'WSH_NEW_DELIVERIES'

ORDER BY 1, 2, 3, 4;

To get Program name for session waiting on Wait event

 set pagesize 24 linesize 200 echo off feedback 1 wrap on verify off

column reqid format 9999999999 heading "Request Id"

column sesid format a10 heading "Session"

column ospid format a10 heading "OS Pid"

column USER_CONCURRENT_PROGRAM_NAME format a45 heading "Program Name"

column user_name format a20 heading "Requestor"

select

   a.request_id reqid,a.status_code,a.phase_code,e.USER_CONCURRENT_PROGRAM_NAME,f.user_name,

   d.sid||','||d.serial# sesid,c.spid ospid,d.event

from

   applsys.fnd_concurrent_requests a,

   applsys.fnd_concurrent_processes b,

   v$process c, v$session d,

   apps.fnd_concurrent_programs_tl e,

   apps.fnd_user f

where

   a.controlling_manager=b.concurrent_process_id

   and c.spid=a.oracle_process_id

   and c.addr=d.paddr

   and e.concurrent_program_id=a.concurrent_program_id

   and a.requested_by = f.user_id

   and d.sid in (select sid from v$session where event like '%&event%')

   and a.phase_code='R'

   and b.instance_number=(select instance_number from v$instance)

   and e.language=USERENV('LANG');

Temp usage by sessions

SELECT   S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,

S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,

COUNT(*) sort_ops

FROM   v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P

WHERE  T.session_addr = S.saddr

AND  S.paddr = P.addr

AND   T.tablespace = TBS.tablespace_name

GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,

 S.program, TBS.block_size, T.tablespace having (SUM (T.blocks) * TBS.block_size / 1024 / 1024) > 5000

ORDER BY sid_serial;

checking responsibility of user

 select distinct frtl.responsibility_name,fu.user_name,fu.user_id,furgd.start_date Resp_start,furgd.end_date resp_end,fu.start_date USER_start,fu.end_date USER_end
from
apps.fnd_user fu,
apps.fnd_responsibility_tl frtl,
apps.fnd_user_resp_groups_direct furgd
where
fu.user_id=furgd.user_id and
frtl.responsibility_id=furgd.responsibility_id and
fu.user_name='&User'
and frtl.language='US'
and furgd.end_date is null order by 1;

TO CHECK THE USER PRIVILIGES

 select
lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
(
/* THE USERS */
select
null grantee,
username granted_role
from
dba_users
where
username like upper('&username')
/* THE ROLES TO ROLES RELATIONS */
union
select
grantee,
granted_role
from
dba_role_privs
/* THE ROLES TO PRIVILEGE RELATIONS */
union
select
grantee,
privilege
from
dba_sys_privs
)
start with grantee is null
connect by grantee = prior granted_role;

To check the locks

select SESSION_ID,OWNER,NAME,MODE_HELD from dba_ddl_locks  where NAME='&obj';  

select SESSION_ID,OWNER,NAME,MODE_HELD from dba_dml_locks  where NAME='&obj';

Find the CPU usage by session

select ss.username, se.SID, VALUE/100 cpu_usage_seconds from v$session ss, v$sesstat se, v$statname sn

where se.STATISTIC# = sn.STATISTIC#

and NAME like '%CPU used by this session%'

and se.SID = ss.SID

and ss.status='ACTIVE' and ss.username is not null

order by VALUE desc;