Oracle long running SQL ... 

long running SQL 
long running SQL queries

set pages 300 lines 300
col OPNAME for a10
col SID form 9999
col SERIAL form 9999999
col PROGRAM for a10
col USERNAME for a10
col SQL_TEXT for a40
col START_TIME for a10
col LAST_UPDATE_TIME for a10
col TARGET for a25
col MESSAGE for a25
alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';
SELECT 
--inst_id,sid, serial#, 
''''||sid ||','||serial#||',@'||inst_id ||'''' kill,
sql_id, opname, username, target, sofar, totalwork, start_time,last_update_time,round(time_remaining/60,2) "REMAIN MINS", round(elapsed_seconds/60,2) "ELAPSED MINS", round((time_remaining+elapsed_seconds)/60,2) "TOTAL MINS", ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE", message
FROM gv$session_longops
WHERE OPNAME NOT LIKE 'RMAN%' 
AND OPNAME NOT LIKE '%aggregate%' 
AND TOTALWORK != 0 
AND sofar<>totalwork 
AND time_remaining > 0
/




COLUMN sid FORMAT 99999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A50
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10
col kill for a17
SELECT 
--s.sid, s.serial#, 
''''||s.sid ||','||s.serial#||',@'||s.inst_id ||'''' kill,
s.machine,
ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct FROM gv$session s, gv$session_longops sl WHERE s.sid = sl.sid AND s.serial# = sl.serial# AND TOTALWORK != 0;






set linesize 500 pagesize 500
col MESSAGE for a50  
col "USERNAME| SID,SERIAL#,inst" for a40
col "STARTED|MIN_ELAPSED|REMAIN" for a25
 select USERNAME||'| '||''''||sid ||','|| serial#||',@'||inst_id ||'''' "USERNAME| SID,SERIAL#,inst",SQL_ID,round(SOFAR/TOTALWORK*100,2) "%DONE"
        ,to_char(START_TIME,'DD-Mon HH24:MI')||'| '||trunc(ELAPSED_SECONDS/60)||'|'||trunc(TIME_REMAINING/60) "STARTED|MIN_ELAPSED|REMAIN" ,SQL_ID,MESSAGE
        from gv$session_longops
    where SOFAR/TOTALWORK*100 <>'100'
	     and TOTALWORK <> '0'
		-- and MESSAGE not like 'RMAN:%'
        order by "STARTED|MIN_ELAPSED|REMAIN" desc, "USERNAME| SID,SERIAL#,inst";






set lines 500
col opname format a35
col target format a25
col units format a10
col kill for a17
col message for a20
col kill for a17
select * from (
      select
      ''''||sid ||','||serial#||',@'||inst_id ||'''' kill , sql_id,
      opname, target, sofar, totalwork, round(sofar/totalwork, 4)*100 pct, units, round(elapsed_seconds/60,2) elap_min, round(time_remaining/60,2) remaining_min
      ,sql_plan_hash_value, sql_plan_operation, sql_plan_options, sql_plan_line_id,  to_char(sql_exec_start, 'dd-mm-yyyy hh24:mi:ss') sql_exec_start
      ,message
      from gv$session_longops
      WHERE sofar < totalwork
      order by start_time desc)
/

ORA-12537: TNS:connection closed



 sqlplus 'sys@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=xyrac02.***.***.*******.com)(Port=1521))(CONNECT_DATA=(SID=xyrac2))) as sysdba'

SQL*Plus: Release 12.2.0.1.0 Production on Fri Nov 11 06:50:24 2022

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

Enter password:
ERROR:
ORA-12537: TNS:connection closed  <<<< 






[oracle@xyrac02 tmp]$ cd $ORACLE_HOME

[oracle@xyrac02 dbhome_1]$ ls -lrt bin/oracle
-rwxrwsr-x 1 oracle oinstall 408898240 Apr 19  2019 bin/oracle

[oracle@xyrac02 dbhome_1]$ chmod 6751 bin/oracle   <<<< change to !!!!!!!!!!!!!!!!!!!

[oracle@xyrac02 dbhome_1]$ ls -lrt bin/oracle
-rwsr-s--x 1 oracle oinstall 408898240 Apr 19  2019 bin/oracle





 tail -100f $ORACLE_BASE/diag/tnslsnr/$(hostname -s)/listener/trace/listener.log

now working !!!!

sqlplus 'sys@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=xyrac02.***.***.*******.com)(Port=1521))(CONNECT_DATA=(SID=xyrac2))) as sysdba'

SQL*Plus: Release 12.2.0.1.0 Production on Fri Nov 11 06:57:00 2022

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

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Oracle Hub Report

Oracle Performance Hub Report.

Performance Hub Report

Oracle Database - Enterprise Edition - Version 12.1.0.2 and later <<<<<<<<<<<<<<
Monitoring Database Performance Using Performance Hub Report (Doc ID 2436566.1)  


alter session set "_push_join_predicate" = FALSE ;  --- if report running slow try this!!!
@?/rdbms/admin/perfhubrpt.sql
===


 select DBID from v$database;
                                         --     mm/dd/yyyy
@?/rdbms/admin/perfhubrpt.sql all 1825264339 1 '11/06/2022 08:00:00' '11/06/2022 09:00:00' 1_PerfHub_06.html

???

 set pages 0 linesize 32767 trimspool on trim on long 1000000 longchunksize 10000000
 spool perfhub_history.html
 select dbms_perf.report_perfhub(is_realtime=>0,type=>'active',selected_start_time=>to_date('10-SEP-18 04:00:00','dd-MON-YY hh24:mi:ss'),selected_end_time=>to_date('10-SEP-18 05:00:00','dd-MON-YY hh24:mi:ss')) from dual;
 spool off


set pages 0 linesize 32767 trimspool on trim on long 1000000 longchunksize 10000000
spool sql_details_history.html
select dbms_perf.report_sql(sql_id=>'9vkyyg1xj6fgc',is_realtime=>0,type=>'active',selected_start_time=>to_date('10-SEP-18 04:00:00','dd-MON-YY hh24:mi:ss'),selected_end_time=>to_date('10-SEP-18 05:00:00','dd-MON-YY hh24:mi:ss')) from dual;
spool off