Skip to main content

ACTIVE SESSION DETAILS SCRIPT IN ORACLE DATABASE

irut@/home/oracle/dba/scripts: cat active_session_history.sql
spool active_session_history.log
set pages 5000
set lines 500
set trims on
col sample_time format a25
col event format a30
col sql_id format a15
col sql_text format a200
col session_id format 99999
col p1text format a10
col p2text format a30
select sh.inst_id,
       sh.sample_time,
       sh.sql_id,
       sh.session_id,
       sh.blocking_session,
       sh.wait_time,
       sh.time_waited,
       sh.event,
       sh.p1,
       sh.p1text,
       sh.p2,
       sh.p2text,
       (select distinct substr(t.sql_text,1,200)
        from gv$sqlarea t
        where sh.sql_id = t.sql_id) sql_text
from gv$active_session_history sh
where sh.event is not null
--and sh.sample_time > sysdate-10/1440
and sh.session_id=&SID
order by sh.sample_time,sample_id;

spool off;

Comments

Post a Comment

Popular posts from this blog

Registering The Database to RMAN catalog database:

Registering The Database to RMAN catalog database: Need to start RMAN as follows: RMAN target=sys/password@database_to_backup rcvcat=sys/password@recovery_catalog_database Another variation on the command, if the recovery catalog and the database were on the same server, might be as shown: oraenv ORACLE_SID = [KKUY] ? KKUY RMAN rcvcat=sys/password@recovery_catalog_database RMAN> connect target Recovery Manager: Release 8.0.5.1.0 - Production RMAN-06005: connected to target database: KKUY RMAN-06008: connected to recovery catalog database Use the below command to register the database. RMAN>register database; Want to verify if a database is registered in the recovery catalog. To do this, connect to RMAN and issue the command LIST INCARNATION OF DATABASE. RMAN> list incarnation of database; RMAN-03022: compiling command: list RMAN-06240: List of Database Incarnations RMAN-06241: DB Key Inc Key DB Name DB ID      CUR Reset SCN   Reset Time RMAN
Post a Comment
Read more

ORA-39014: One or more workers have prematurely exited.ORA-00018: maximum number of sessions exceeded

ERROR: I was Performing a full database import and during the import I faced the below error. ORA-39014: One or more workers have prematurely exited. ORA-39029: worker 6 with process name "DW07" prematurely terminated ORA-31672: Worker process DW07 died unexpectedly. Job "SYSTEM"."SYS_IMPORT_FULL_04" stopped due to fatal error at 00:59:40 ORA-39014: One or more workers have prematurely exited. SOLUTION:  Run the import with fewer parallel processes, like PARALLEL=2 instead of 8. I was able to run the import successfully. NOTE 1: This errors occurs when there are less session allocation in the database. check the session,process parameters and increase them accordingly. To avoid such errors again. NOTE 2 : Note: Increasing processes parameter increases the amount of shared memory that needs to be reserved & the OS must be configured to support the larger amount of shared memory. So here we first need to increase the Memory & SG
Post a Comment
Read more