Get Active Session or logins from Last weeks in Database

  Once a while it’s very difficult to find sessions history on database. one of my required in my daily dba job once while where we need to find who are active users are logged in last few weeks or months. You can achieve this by using different ways. Here is one of the way you can get some details information by using    DBA_HIST_ACTIVE_SESS_HISTORY,  DBA_USERS ,   DBA_HIST_SQLTEXT Views.

You can use below queries to get required details.

 Find All Active Sessions or logged in session in Database in last 30 days:-

SELECT

   h.sample_time,

   u.username,

   h.program,

   h.module,

   s.sql_text

FROM

   DBA_HIST_ACTIVE_SESS_HISTORY h,

   DBA_USERS u,

   DBA_HIST_SQLTEXT s

WHERE  sample_time >= SYSDATE - 30

   AND h.user_id=u.user_id

   AND h.sql_id = s.sql_iD and u.username not in ('SYS','SYSTEM','DBSNMP')

ORDER BY h.sample_time

/

Another query to get more details :-

select

*

from DBA_HIST_ACTIVE_SESS_HISTORY

where

sample_time

between

to_date('20-OCT-2017 10:40:00','DD-MON-YYYY HH24:MI:SS')

and

to_date('02-NOV-2017 10:40:10','DD-MON-YYYY HH24:MI:SS')

and instance_number in (1,3) and SESSION_TYPE <> 'BACKGROUND'

order by sample_time,instance_number,SESSION_ID;

IMPDP - ORA-00001: unique constraint () violated

I was  Loading data into Development Environment   Table using IMPDP  So, somewhere in that process looks like the data which was importing was already got imported and IMPDP started failing with ORA-00001: unique constraint violated. 

I went with DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS parameter to let the IMPORT go. I Know, this option basically defeats the main feature of DIRECT PATH load and import will be slow but figured it would be rather more simpler than trying to find duplicates.   

In my situation I can ignore duplicate records which are already existed. So I used DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS.

Get Source Database (Extract) Information from Target side (Replicate)

           There might be few other ways you can find as I know below one of the way you can get the source database information. If you set it up heart beat table in target & Source side. By logging into target database  where replicates are running there is way too you can find source databases information by querying  heart beat table.  

select * from gguser.ggs_heartbeat;

select SRC_DB,EXTRACT_NAME from gguser.ggs_heartbeat;

-- To find last  Commit information

SELECT

    ggs_heartbeat.src_db AS src_db,

    ggs_heartbeat.extract_name AS extract_name,

    ggs_heartbeat.source_commit AS source_commit,

    ggs_heartbeat.target_commit AS target_commit

FROM

    gguser.ggs_heartbeat

WHERE

    ggs_heartbeat.src_db LIKE 'SUNIL%';