OracleImpQueries

#To check sessions

                select * from v$session where username = 'IPIARM_MASTER' – and machine = ‘’

#To kill session
       
        ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

#To identify long running queries

select S.USERNAME, s.sid, s.serial#,s.machine, s.osuser, t.sql_id, sql_text
from v$sqltext_with_newlines t,V$SESSION s
where t.address =s.sql_address
and t.hash_value = s.sql_hash_value
and s.status = 'ACTIVE'
and s.username <> 'SYSTEM'
order by s.sid,t.piece

#To check locks

SELECT LPAD(' ',DECODE(l.xidusn,0,3,0)) || l.oracle_username "User Name",l.os_user_name,l.session_id,o.owner, o.object_name, o.object_type FROM v$locked_object l, dba_objects o WHERE l.object_id = o.object_id and
owner = 'IPIARM_MASTER' ORDER BY o.object_id, 1 desc;

SELECT * FROM NN_NPG_REGION_INFO;

select * from nn_counts

select alt_car_id,  to_char(max(nvl(MODIFICATION_DT,CREATION_DT)),'YYYY-MM-DD HH24:MI:SS') from all_src_stage_nn GROUP BY alt_car_id;

select alt_car_id, count(*) from all_src_stage_nn GROUP BY alt_car_id;

select count(*) from all_src_stage_nn_audit

select to_char(AUDIT_DATE,'YYYYMMDD HH24MI'), count(*) from all_src_stage_nn_audit GROUP BY to_char(AUDIT_DATE,'YYYYMMDD HH24MI')  order by 2 desc

select * from nn_counts


//explain plan
explain plan for (SELECT COUNT(*) INTO VAL_FLAG_SERVICE FROM SERVICE WHERE SERVICE_ID);

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());


//set line

set lines 9999
set trimspool on
spool myfile.txt
select * from user_tables;
spool off