OPatch failed with error code 73, The Central Inventory is corrupted:

Details of error as follows - 

oracle@orademodb01 ~

ORCL: opatch lsinv

Oracle Interim Patch Installer version 12.2.0.1.21

Copyright (c) 2021, Oracle Corporation.  All rights reserved.

Oracle Home       : /ora/db/tech_st/12.1.0.2

Central Inventory : /home/oracle/oraInventory

   from           : /ora/db/tech_st/12.1.0.2/oraInst.loc

OPatch version    : 12.2.0.1.21

OUI version       : 12.1.0.2.0

Log file location : /ora/db/tech_st/12.1.0.2/cfgtoollogs/opatch/opatch2021-09-28_16-16-01PM_1.log

List of Homes on this system:

  Home name= ORCL_Home, Location= "/ora/db/tech_st/12.1.0.2"

LsInventorySession failed: RawInventory gets null OracleHomeInfo

OPatch failed with error code 73

oracle@orademodb01 ~

Solution:

Turned out my inventory had not been updated with my new Oracle Home. When I looked in the inventory.xml file on my server, there was no entry for the installation there.

To fix this, add the new OH to your inventory. From your OH that is missing, do the following:

oracle@orademodb01 /ora/db/tech_st/12.1.0.2

ORCL: cat oraInst.loc

inventory_loc=/home/oracle/oraInventory

inst_group=oinstall

oracle@orademodb01 /ora/db/tech_st/12.1.0.2

ORCL: cat /etc/oraInst.loc

inventory_loc=/home/oracle/oraInventory

inst_group=oinstall

oracle@orademodb01 /ora/db/tech_st/12.1.0.2

ORCL: cd $ORACLE_HOME/oui/bin

oracle@orademodb01 /ora/db/tech_st/12.1.0.2/oui/bin

ORCL: ./runInstaller -invPtrLoc /ora/db/tech_st/12.1.0.2/oraInst.loc -attachHome ORACLE_HOME=/ora/db/tech_st/12.1.0.2 ORACLE_HOME_NAME="ORCL"

Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 15517 MB    Passed

The inventory pointer is located at /ora/db/tech_st/12.1.0.2/oraInst.loc

'AttachHome' was successful.

oracle@orademodb01 /ora/db/tech_st/12.1.0.2/oui/bin

DB Startup issue after memory change in the server level

Faced an issue after changing the DB memory parameters (SGA and PGA) and got the below error while startup.

ORA-27106: System Pages Not Available To Allocate Memory

Issue: Found that the "USE_LARGE_PAGES" setting to "ONLY"

Fix: Parameter USE_LARGE_PAGES set to "TRUE" and started database, DB came up. 

OEM account dbsnmp locked.

 Use Case:  

We have changed passwords as part of regular DBA activity.  OEM account dbsnmp got locked after change.

 

Fix:

1. Change passwords for users sys and system
2. Run orapwd
3. In OEM, update config for sys: Setup>Security>Named Credentials
4. Change password for user dbsnmp
5. Quickly update OEM config for dbsnmp: Setup>Security>Monitoring Credentials

 

If the dbsnmp account still keeps locking, bounce the db and try steps 4 and 5 again.

 Good Luck.

 

 

Find locks on object

Couple of different queries listed to find locks on objects / tables ...etc.

1. How to find any session is locked on a specific object:

Use case, one of the user trying to update a particular object, which is hanging to update.  Used below query to see who is accessing that object and cleared it.

Query:

select a.sid, s.serial#, s.status from v$access a, v$session s where a.owner='&Schema_Name' AND a.object='&Object_Name' and a.sid=s.sid;

Example:

select a.sid, s.serial#, s.status from v$access a, v$session s where a.owner='APPS' AND a.object='XXC_FS_RWP_BEE_PKG' and a.sid=s.sid;

2. Find Locks on Table Level:

select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.status,
   b.osuser,
   b.machine
from
   v$locked_object a ,
   v$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id;

How to find database growth on a Monthly Basis

SQL query to find database growth month wise and year.  

select to_char(CREATION_TIME,'RRRR') Year, to_char(CREATION_TIME,'MM') Month, round(sum(bytes)/1024/1024/1024) GB

from   v$datafile

group by  to_char(CREATION_TIME,'RRRR'),   to_char(CREATION_TIME,'MM')

order by   1, 2;

Sample Output:

YEAR MO         GB

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

2019 02         96

2019 03         65

2019 04        239

2019 05         32

2019 06         64

2019 07         95

2019 09        315

2019 10         31

2019 11        375

2020 04        313

10 rows selected.

ORA-01940: cannot drop a user that is currently connected

When I was trying to drop a schema, I got below error.

SQL> drop user obi_dw cascade;

drop user obi_dw cascade

*

ERROR at line 1:

ORA-01940: cannot drop a user that is currently connected

Find out the session for connected user.

SQL> SELECT sid, serial#,status,username, logon_time,(last_call_et/60/60/24) "Days Inactive" FROM v$session WHERE username ='OBI_DW';

       SID    SERIAL# STATUS   USERNAME                       LOGON_TIM Days Inactive

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

        10         15 INACTIVE OBI_DW                         11-MAY-20    .000381944

Kill the session.

SQL> alter system kill session '10,15';

System altered.

Try to drop the user. 

SQL> drop user obi_dw cascade;

User dropped.

SQL>

ORA-01555 caused by SQL statement

Q: Why do we get ORA-01555 error?.

Ans: Suppose there are two users(User1 and User2). User1 is executing long update statement on oracle data without commit and at the same time User2 is also selecting same data which is getting updated by User1.

Both Queries are still running and no one gets output yet.

After some time, Query execution of User1 gets completed and he commits the data. Now what does happen with User2?. 

He gets ORA-01555 error. The reason behind this is ,"User2 doesn't get old image of the data", because User1 has changed the data.

Solution:

Increasing UNDO tablespace size is not the solution for this.

Run Undo advisory

Increase UNDO Retension Period

Enable Undo Retention Guarantee

Run this Select Query during non-business hours where heavy transactions are not happening.

Tune the Query

Kill all inactive sessions in a Database.

If you want to kill all inactive sessions in the database, it is hard to find and kill one by one.  Use below statement to create kill statements at a time and run them to kill all inactive session.

select 'alter system kill session '||sid||','||serial#|| ';' from v$session where status='INACTIVE';

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Issue Details:

When I'm trying to update a row in a table got the below error.

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Solution:

Use below query to find who is accessing the table.

SELECT a.object, a.type, a.sid,s.serial#, s.username,s.program, s.logon_time, s.osuser
FROM v$access a, v$session s
WHERE a.sid = s.sid
AND a.owner = '&OWNER'
AND a.object = '&Object_Name';

Check the session details and contact user to close his session or wait for the job complete.  If user requested to cancel his session, kill the session using below command.

SQL> alter system kill session '&SID,&Serial';

Then we tried our update statement, it works fine.

ORA-28007: the password cannot be reused

Issue:

SQL> alter user user1 identified by password1;
alter user user1 identified by password1
*
ERROR at line 1:
ORA-28007: the password cannot be reused


Solution:

1. Find the profile name:

SQL> SQL> select username,profile,account_status from dba_users where username='USER1';

USERNAME   PROFILE         ACCOUNT_STATUS
---------- --------------- ---------------
USER1     ADMINISTRATOR   OPEN

2. Find the profile values:

SQL> select * from dba_profiles where profile='ADMINISTRATOR';

PROFILE              RESOURCE_NAME                    RESOURCE LIMIT                COM
-------------------- -------------------------------- -------- -------------------- ---
ADMINISTRATOR        COMPOSITE_LIMIT                  KERNEL   DEFAULT              NO
ADMINISTRATOR        SESSIONS_PER_USER                KERNEL   DEFAULT              NO
ADMINISTRATOR        CPU_PER_SESSION                  KERNEL   DEFAULT              NO
ADMINISTRATOR        CPU_PER_CALL                     KERNEL   DEFAULT              NO
ADMINISTRATOR        LOGICAL_READS_PER_SESSION        KERNEL   DEFAULT              NO
ADMINISTRATOR        LOGICAL_READS_PER_CALL           KERNEL   DEFAULT              NO
ADMINISTRATOR        IDLE_TIME                        KERNEL   DEFAULT              NO
ADMINISTRATOR        CONNECT_TIME                     KERNEL   DEFAULT              NO
ADMINISTRATOR        PRIVATE_SGA                      KERNEL   DEFAULT              NO
ADMINISTRATOR        FAILED_LOGIN_ATTEMPTS            PASSWORD 5                    NO
ADMINISTRATOR        PASSWORD_LIFE_TIME               PASSWORD 90                   NO
ADMINISTRATOR        PASSWORD_REUSE_TIME              PASSWORD 180                  NO
ADMINISTRATOR        PASSWORD_REUSE_MAX               PASSWORD UNLIMITED            NO
ADMINISTRATOR        PASSWORD_VERIFY_FUNCTION         PASSWORD VERIFY_FUNCTION_11G  NO
ADMINISTRATOR        PASSWORD_LOCK_TIME               PASSWORD 7                    NO
ADMINISTRATOR        PASSWORD_GRACE_TIME              PASSWORD 14                   NO

16 rows selected.

SQL>

3. Alter the profile values:

SQL> alter profile ADMINISTRATOR limit password_verify_function NULL;

Profile altered.

SQL> alter profile ADMINISTRATOR limit PASSWORD_REUSE_TIME UNLIMITED;

Profile altered.

4. Change the password:

SQL> alter user user1 identified by password1;

User altered.

SQL>


5. After changing the password restore the default/old settings:

SQL> alter profile ADMINISTRATOR  limit password_verify_function VERIFY_FUNCTION_11G;

Profile altered.

SQL> alter profile ADMINISTRATOR  limit PASSWORD_REUSE_TIME 180;

Profile altered.

SQL> 

ORA-28003: password verification for the specified password failed ORA-20009: Password must be 8 characters in length

Issue:

SQL> alter user USER1 identified by password1;
alter user USER1 identified by password1
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20009: Password must be 8 characters in length

Solution:

SQL> select profile from dba_users where username='USER1';

PROFILE
------------------------------
APPLICATION_PASSWORD_EXP

SQL> select RESOURCE_NAME,RESOURCE_TYPE,LIMIT FROM DBA_PROFILES WHERE PROFILE='APPLICATION_PASSWORD_EXP' AND RESOURCE_NAME='PASSWORD_VERIFY_FUNCTION';

RESOURCE_NAME                    RESOURCE LIMIT
-------------------------------- -------- ----------------------------------------
PASSWORD_VERIFY_FUNCTION         PASSWORD PROD_PWD_LIMIT

SQL> ALTER PROFILE APPLICATION_PASSWORD_EXP LIMIT PASSWORD_VERIFY_FUNCTION NULL;

Profile altered.

SQL> alter user USER1 identified by password1;

User altered.

SQL> ALTER PROFILE APPLICATION_PASSWORD_EXP LIMIT PASSWORD_VERIFY_FUNCTION PROD_PWD_LIMIT;

Profile altered.

Oracle Database 19c: [INFO] [INS-32183] Use of clone.pl is deprecated in this release.

I was trying to clone oracle 19c ORACLE_HOME binaries from one server to another server.  Though it says deprecated, clone.pl worked end of the day.

Source the Environment on Target Host:

export ORACLE_SID=ORCL
export ORACLE_BASE=/ora/db/ORCL
export ORACLE_HOME=/ora/db/19.0.0
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:$ORACLE_HOME/bin:.

Run Clone Script:

$ORACLE_HOME/perl/bin/perl \
$ORACLE_HOME/clone/bin/clone.pl \
ORACLE_BASE=$ORACLE_BASE \
ORACLE_HOME=$ORACLE_HOME \
OSDBA_GROUP=oinstall \
OSOPER_GROUP=oinstall \
ORACLE_HOME_NAME=ORCL_HOME

Command Line output for clone script:

[oracle@mydbserver ~]$ $ORACLE_HOME/perl/bin/perl \
> $ORACLE_HOME/clone/bin/clone.pl \
> ORACLE_BASE=$ORACLE_BASE \
> ORACLE_HOME=$ORACLE_HOME \
> OSDBA_GROUP=oinstall \
> OSOPER_GROUP=oinstall \
> ORACLE_HOME_NAME=ORCL_HOME


[INFO] [INS-32183] Use of clone.pl is deprecated in this release. Clone operation is equivalent to performing a Software Only installation from the image.
You must use /ora/db/19.0.0/runInstaller script available to perform the Software Only install. For more details on image based installation, refer to help documentation.

Starting Oracle Universal Installer...

You can find the log of this install session at:
 /ora/db/oraInventory/logs/cloneActions2020-02-23_06-56-11AM.log
..................................................   5% Done.
..................................................   10% Done.
..................................................   15% Done.
..................................................   20% Done.
..................................................   25% Done.
..................................................   30% Done.
..................................................   35% Done.
..................................................   40% Done.
..................................................   45% Done.
..................................................   50% Done.
..................................................   55% Done.
..................................................   60% Done.
..................................................   65% Done.
..................................................   70% Done.
..................................................   75% Done.
..................................................   80% Done.
..................................................   85% Done.
..........
Copy files in progress.

Copy files successful.

Link binaries in progress.
..........
Link binaries successful.

Setup files in progress.
..........
Setup files successful.

Setup Inventory in progress.

Setup Inventory successful.
..........
Finish Setup successful.
The cloning of O19CAPEX_HOME was successful.
Please check '/ora/db/oraInventory/logs/cloneActions2020-02-23_06-56-11AM.log' for more details.

Setup Oracle Base in progress.

Setup Oracle Base successful.
..................................................   95% Done.

As a root user, execute the following script(s):
        1. /ora/db/19.0.0/root.sh



..................................................   100% Done.
[oracle@mydbserver ~]$

ORA-29548: Java system class reported: release of Java system classes in the database (12.1.0.2.190716 1.6) does not match that of the oracle executable (12.1.0.2.200114 1.6) - USER ( APPS ) has been detected in FND_WEB_SEC.GET_OP_VALUE." then follow below steps.

Issue Details:

We have applied Java VM PSU (July 2019) on our 12c (12.1.0.2) database in 2019.  Now we applied JavaVM PSU (Jan 2020).  After patch one of the user reported their functionality failing with below error.

 "Oracle error -29548: ORA-29548: Java system class reported: release of Java system classes in the database (12.1.0.2.190716 1.6) does not match that of the oracle executable (12.1.0.2.200114 1.6) - USER ( APPS ) has been detected in FND_WEB_SEC.GET_OP_VALUE."


Solution:

It is always better to check below two queries after we apply JavaVM patches.

SQL> select dbms_java.get_jdk_version() from dual;
select dbms_java.get_jdk_version() from dual
                                        *
ERROR at line 1:
ORA-29548: Java system class reported: release of Java system classes in the
database (12.1.0.2.190716 1.6) does not match that of the oracle executable
(12.1.0.2.200114 1.6)

SQL> select dbms_java.longname('TEST') from dual;
select dbms_java.longname('TEST') from dual
                                       *
ERROR at line 1:
ORA-29548: Java system class reported: release of Java system classes in the
database (12.1.0.2.190716 1.6) does not match that of the oracle executable
(12.1.0.2.200114 1.6)
SQL>

 To fix the above error, run the below command as SYS user.

SQL> @?/javavm/install/update_javavm_db.sql
SQL> SET FEEDBACK 1
SQL> SET NUMWIDTH 10
SQL> SET LINESIZE 80
SQL> SET TRIMSPOOL ON
SQL> SET TAB OFF
SQL> SET PAGESIZE 100
SQL>
SQL> alter session set "_ORACLE_SCRIPT"=true;

Session altered.

SQL>
SQL> -- If Java is installed, do CJS.
SQL>
SQL> -- If CJS can deal with the SROs inconsistent with the new JDK,
SQL> -- the drop_sros() call here can be removed.
SQL> call initjvmaux.drop_sros();

Call completed.

SQL>
SQL> create or replace java system;
  2  /

Java created.

SQL>
SQL> update dependency$
  2    set p_timestamp=(select stime from obj$ where obj#=p_obj#)
  3    where (select stime from obj$ where obj#=p_obj#)!=p_timestamp and
  4          (select type# from obj$ where obj#=p_obj#)=29  and
  5          (select owner# from obj$ where obj#=p_obj#)=0;

0 rows updated.

SQL>
SQL> commit;

Commit complete.

SQL>


Now run the above two queries and test the issue still persists or not.

SQL> alter session set "_ORACLE_SCRIPT"=false;

Session altered.

SQL>
SQL> select dbms_java.get_jdk_version() from dual;

DBMS_JAVA.GET_JDK_VERSION()
--------------------------------------------------------------------------------
1.6.0_211

1 row selected.

SQL> select dbms_java.longname('TEST') from dual;

DBMS_JAVA.LONGNAME('TEST')
--------------------------------------------------------------------------------
TEST

1 row selected.

SQL>

datapatch -verbose failed.

Issue Details:

We have applied JAN 2020 PSU patch 30340202 on our 12.1.0.2 database.

As post patch step, we need to run datapatch -versbose, when we run that it failed with below error.


cd $ORACLE_HOME/OPatch
./datapatch -verbose

oracle@mydbserver /ora/db/tech_st/12.1.0.2/OPatch
ORCL-DB: ./datapatch -verbose
SQL Patching tool version 12.1.0.2.0 Production on Wed Feb 19 09:52:12 2020
Copyright (c) 2012, 2016, Oracle.  All rights reserved.

Log file for this invocation: /ora/db/tech_st/12.1.0.2/cfgtoollogs/sqlpatch/sqlpatch_10280_2020_02_19_09_52_12/sqlpatch_invocation.log

Connecting to database...OK
Bootstrapping registry and package to current versions...done

Queryable inventory could not determine the current opatch status.
Execute 'select dbms_sqlpatch.verify_queryable_inventory from dual'
and/or check the invocation log
/ora/db/tech_st/12.1.0.2/cfgtoollogs/sqlpatch/sqlpatch_10280_2020_02_19_09_52_12/sqlpatch_invocation.log
for the complete error.
Prereq check failed, exiting without installing any patches.

Please refer to MOS Note 1609718.1 and/or the invocation log
/ora/db/tech_st/12.1.0.2/cfgtoollogs/sqlpatch/sqlpatch_10280_2020_02_19_09_52_12/sqlpatch_invocation.log
for information on how to resolve the above errors.

SQL Patching tool complete on Wed Feb 19 09:52:40 2020



Upon checking we got the below error in the database.

SQL> select dbms_sqlpatch.verify_queryable_inventory from dual;

VERIFY_QUERYABLE_INVENTORY
--------------------------------------------------------------------------------
ORA-20001: Latest xml inventory is not loaded into table



Solution:

This is due ot java, java is pointing to operating system local java(/usr/bin/java) not to the $ORACLE_HOME/jdk/jre/bin/java.

 oracle@mydbserver /ora/db/tech_st/12.1.0.2/OPatch
ORCL-DB: which java
/usr/bin/java
oracle@mydbserver /ora/db/tech_st/12.1.0.2/OPatch
ORCL-DB: export PATH=$ORACLE_HOME/jdk/jre/bin:$PATH:.
oracle@mydbserver /ora/db/tech_st/12.1.0.2/OPatch
ORCL-DB: which java
/ora/db/tech_st/12.1.0.2/jdk/jre/bin/java
oracle@mydbserver /ora/db/tech_st/12.1.0.2/OPatch


Retested the issue and again it failed with same error.

When we check with Oracle Support team, they suggest to run datapatch verbose as shown below.

datapatch -apply 30340202 -force -verbose -bundle_series DBPSU

Issue fixed !!!

Typical DB Environment File Looks Like...

Assumptions that we have Oracle Database 12c Release 1.

Typical environment file in Database Node looks like....

$ cat ORCL.env
export PS1='\[\e[32m\]\u@\h \[\e[33m\]\w\[\e[0m\]\nORCL: '
export ORACLE_SID=ORCL
export ORACLE_BASE=/ora/db/tech_st
export ORACLE_HOME=/ora/db/tech_st/12.1.0.2
export TNS_ADMIN=$ORACLE_HOME/network/admin/<ORCL_HOSTNAME>
export PERL5LIB=/ora/db/tech_st/12.1.0.2/perl/lib/5.14.1
export LD_LIBRARY_PATH=/ora/db/tech_st/12.1.0.2/lib:/ora/db/tech_st/12.1.0.2/ctx/lib:/lib:/usr/lib
export PATH=$PATH:/ora/db/tech_st/12.1.0.2/appsutil/scripts/<ORCL_HOSTNAME>:$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$ORACLE_HOME/OPatch:.
export ORA_NLS10=/ora/db/tech_st/12.1.0.2/nls/data/9idata

Blocking Session Details

How to find blocking / locking session details:

set lines 145
col sid noprint

select distinct
       s1.sid,s1.username || '@' || s1.machine ||
       ' ( INST=' || s1.inst_id || ' SID=' || s1.sid||','||s1.serial# ||'  '||s1.status||': '||s1.last_call_et||' SQLID: '||s1.sql_id||' )  is blocking '
       || s2.username || '@' || s2.machine || ' ( INST=' || s1.inst_id || ' SID=' || s2.sid || ' ) ' AS blocking_status
  from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where s1.sid=l1.sid
   and s2.sid=l2.sid
   and l1.BLOCK=1
   and l2.request > 0
   and l1.id1 = l2.id1
   and l2.id2 = l2.id2
   and l1.inst_id = s1.inst_id
order by s1.sid;

Output:

BLOCKING_STATUS
-------------------------------------------------------------------------------------------------------------------------------------------------
APPS@mydbserver ( INST=1 SID=6323,26775  ACTIVE: 2494 SQLID: 08pfumd9shm53 )  is blocking APPS@mydbserver ( INST=1 SID=6323 )

1 row selected.

SQL>

Drop Unused UNDO Tablespace

1) Check how many UNDO tablespaces are available on your database:

SQL> select tablespace_name, contents from dba_tablespaces where contents = 'UNDO';

TABLESPACE_NAME                CONTENTS
------------------------------ ---------
UNDOTBS1                       UNDO
UNDOTBS2                       UNDO


2) Check which UNDO tablespace is being used currently:

SQL> show parameter undo_tablespace

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS1


3) Validate that, any object that it is using other undo_tablespace than current undo_tablespace.

set pagesize 100
set lines 200
set long 999
col username for a9
SELECT a.name,b.status , d.username , d.sid , d.serial#
FROM   v$rollname a,v$rollstat b, v$transaction c , v$session d
WHERE  a.usn = b.usn
AND    a.usn = c.xidusn
AND    c.ses_addr = d.saddr
AND    a.name IN (
  SELECT segment_name
  FROM dba_segments
 WHERE tablespace_name = 'UNDOTBS2');

4) If none of the object is using second undo_tablespace, then use below command to drop the unused undo_tablespace.

SQL> DROP TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES;

Database Size and Free Space

Check your database size and free space:

col 'Database Size' for a15
col 'Free Space' for a15
Select round(sum(used.bytes) / 1024 / 1024/1024 ) || ' GB' "Database Size",round(free.p / 1024 / 1024/1024) || ' GB' "Free Space"
from (select bytes from v$datafile
      union all
      select bytes from v$tempfile
      union all
      select bytes from v$log) used, (select sum(bytes) as p from dba_free_space) free group by free.p;

DBA Directory

1) How to check existing DBA Directories in a database:

col OWNER for a10
col DIRECTORY_NAME for a35
col DIRECTORY_PATH for a75
set lines 200
select * from dba_directories;

2) Create a new DBA Directory or modify existing directory path:

create or replace directory DMPDIR as '/u01/datapump';

3) Grant required privileges to the DBA Directory to access by a user.

grant read,write on directory DMPDIR to <username>;

4) Grant required privileges to the DBA Directory to access by every user.

grant read,write on directory DMPDIR to public;

Find Session(SID and Serial#) details using Process ID:

Find database session details using a process id(PID).

SELECT S.CLIENT_IDENTIFIER, s.status , s.TYPE , s.username , s.osuser , s.server ,
s.machine , s.module , s.logon_time , s.process , p.spid,
p.pid, s.SID, s.audsid, SYSDATE - (s.last_call_et / 86400) 
FROM gv$session s, gv$process p
WHERE s.paddr = p.addr(+) AND s.process ='&process';