How To Fix the Best Plan from Cursor Cache in Oracle

How To Fix The Best Plan From Cursor Cache in Oracle



How to Get SQL_HANDLE And PLAN_NAME From DBA_SQL_PLAN_BASELINES (Doc ID 2242868.1)


http://anuj-singh.blogspot.com/2011/07/oracle-11g-baseline.html

Oracle version .. 

SQL> def
DEFINE _DATE           = "13-NOV-22" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "rac1" (CHAR)
DEFINE _USER           = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1202000100" (CHAR)


How to Get SQL_HANDLE And PLAN_NAME From DBA_SQL_PLAN_BASELINES (Doc ID 2242868.1)


http://anuj-singh.blogspot.com/2011/07/oracle-11g-baseline.html

SQL> def
DEFINE _DATE           = "13-NOV-22" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "rac1" (CHAR)
DEFINE _USER           = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1202000100" (CHAR)



should be true 

 show parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE


 ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true;



 show parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     TRUE
SQL>





var ENAME varchar2(10)
 begin :ENAME := 'ALLEN'; end;
/

 select * from emp where ENAME=:ENAME ;
 
  EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30


aim to fix above sql 




col sql_text for a50 wrap
col SQL_PLAN_BASELINE for a35
select sql_id, plan_hash_value, exact_matching_signature, sql_plan_baseline,sql_text
  from gv$sql
 where lower(sql_text) like lower('%select * from emp where ENAME=:ENAME%')
     --  and command_type = 3
   and sql_text not like '%from gv$sql%';



SQL_ID          PLAN_HASH_VALUE EXACT_MATCHING_SIGNATURE SQL_PLAN_BASELINE                   SQL_TEXT
------------- ----------------- ------------------------ ----------------------------------- --------------------------------------------------
7j5bb53huv8v1        3956160932     11343619050667859858 SQL_PLAN_9uv51dmr6krwkd8a279cc      select * from emp where ENAME=:ENAME


COLUMN sql_text FORMAT A120
COLUMN sql_id FORMAT A13
COLUMN bind_name FORMAT A10
COLUMN bind_value FORMAT A26
SELECT sql_id, b.LAST_CAPTURED,
b.HASH_VALUE, b.name bind_name, b.value_string bind_value, t.sql_text sql_text,
FROM
gv$sql t JOIN gv$sql_bind_capture b using (sql_id)
WHERE b.value_string is not null
AND sql_id='&sqlid';


select * from table(dbms_xplan.display_cursor('&sqlid',0, format => 'TYPICAL +PEEKED_BINDS'));



 VARIABLE cnt NUMBER
EXECUTE :cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id => '7j5bb53huv8v1',plan_hash_value => '3956160932');
 




before !!
col sql_text for a50 wrap
col enabled for a15
col  accepted for a15
col fixed for a15
col CREATED for a30
select CREATED,sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines
where 1=1
and CREATED >sysdate - interval '1' hour;

CREATED                        SQL_HANDLE           PLAN_NAME                      SQL_TEXT                                           ENABLED         ACCEPTED        FIXED
------------------------------ -------------------- ------------------------------ -------------------------------------------------- --------------- --------------- ---------------
13-NOV-22 12.17.02.000000 PM   SQL_9d6ca16cee695f92 SQL_PLAN_9uv51dmr6krwkd8a279cc select * from emp where ENAME=:ENAME               YES             YES             NO




define sql_id='7j5bb53huv8v1'

SELECT PLAN_TABLE_OUTPUT
FROM V$SQL s, DBA_SQL_PLAN_BASELINES b, 
TABLE(
DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(b.sql_handle,b.plan_name,'basic') 
) t
WHERE s.EXACT_MATCHING_SIGNATURE=b.SIGNATURE
AND b.PLAN_NAME=s.SQL_PLAN_BASELINE
AND s.SQL_ID='&sql_id';


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------
SQL handle: SQL_9d6ca16cee695f92
SQL text: select * from emp where ENAME=:ENAME
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_9uv51dmr6krwkd8a279cc         Plan id: 3634526668
Enabled: YES     Fixed: YES     Accepted: YES     Origin: MANUAL-LOAD-FROM-CURSOR-CACHE
Plan rows: From dictionary
--------------------------------------------------------------------------------

Plan hash value: 3956160932

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|   1 |  TABLE ACCESS FULL| EMP  |
----------------------------------





col PLAN_TABLE_OUTPUT for a100
select * from table (DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE('SQL_9d6ca16cee695f92', format=>'+adaptive'));


 SQL> SQL>
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------
SQL handle: SQL_9d6ca16cee695f92
SQL text: select * from emp where ENAME=:ENAME
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_9uv51dmr6krwkd8a279cc         Plan id: 3634526668
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD-FROM-CURSOR-CACHE
Plan rows: From dictionary
--------------------------------------------------------------------------------

Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    38 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):





var v_num number;
EXEC  :v_num:=dbms_spm.alter_sql_plan_baseline(sql_handle =>'SQL_9d6ca16cee695f92',  plan_name => 'SQL_PLAN_9uv51dmr6krwkd8a279cc',  attribute_name=> 'fixed',  attribute_value=>'YES');  
print v_num;





after !!!
col sql_text for a50 wrap
col enabled for a15
col  accepted for a15
col fixed for a15
col CREATED for a30
select CREATED,sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines
where 1=1
and CREATED >sysdate - interval '1' hour;
 
CREATED                        SQL_HANDLE           PLAN_NAME                      SQL_TEXT                                           ENABLED         ACCEPTED        FIXED
------------------------------ -------------------- ------------------------------ -------------------------------------------------- --------------- --------------- ---------------
13-NOV-22 12.17.02.000000 PM   SQL_9d6ca16cee695f92 SQL_PLAN_9uv51dmr6krwkd8a279cc select * from emp where ENAME=:ENAME               YES             YES             YES






 SELECT s.sql_id,b.sql_handle, b.sql_text, b.plan_name, b.enabled , b.accepted, b.fixed  FROM   dba_sql_plan_baselines b, gv$sql s
 WHERE  s.sql_id='7j5bb53huv8v1'
 AND    s.exact_matching_signature = b.signature;

SQL_ID        SQL_HANDLE           SQL_TEXT                                           PLAN_NAME                      ENABLED         ACCEPTED        FIXED
------------- -------------------- -------------------------------------------------- ------------------------------ --------------- --------------- ---------------
7j5bb53huv8v1 SQL_9d6ca16cee695f92 select * from emp where ENAME=:ENAME               SQL_PLAN_9uv51dmr6krwkd8a279cc YES             YES             YES





Our sql_id and PLAN_HASH_VALUE

set linesize 300 pagesize 300
col sql_text for a50 wrap
col SQL_PLAN_BASELINE for a25
col EXACT_MATCHING_SIGNATURE for 99999999999999999999999
col PLAN_HASH_VALUE for 9999999999999999
select distinct sql_id, plan_hash_value, s.exact_matching_signature, b.enabled, b.accepted, b.fixed ,s.sql_text from gv$sql s,dba_sql_plan_baselines b
where 1=1
--and sql_text like  '%select * from emp where ENAME=:ENAME%'
and sql_id='7j5bb53huv8v1';


SELECT sql_handle, plan_name,ENABLED,ACCEPTED,FIXED,REPRODUCED,OPTIMIZER_COST,to_char(Created,'DD-MON-YY') Created
FROM dba_sql_plan_baselines   WHERE signature IN ( SELECT exact_matching_signature FROM v$sql WHERE sql_id='&SQL_ID')

define sql_id='7j5bb53huv8v1'
SELECT sql_handle, plan_name,ENABLED,ACCEPTED,FIXED,REPRODUCED,OPTIMIZER_COST,to_char(Created,'DD-MON-YY hh:mi') Created
FROM dba_sql_plan_baselines
WHERE signature IN (
SELECT exact_matching_signature FROM v$sql WHERE sql_id='&SQL_ID')




SQL_HANDLE           PLAN_NAME                      ENABLED         ACCEPTED        FIXED           REP OPTIMIZER_COST CREATED
-------------------- ------------------------------ --------------- --------------- --------------- --- -------------- ------------------------------
SQL_9d6ca16cee695f92 SQL_PLAN_9uv51dmr6krwkd8a279cc YES             YES             YES             YES              3 13-NOV-22 12:17


=============


some command !!!!!!!!!


var n number
begin
:n:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'7j5bb53huv8v1', plan_hash_value=>928732588, fixed =>'NO’, enabled=>'YES');
end;
/




var v_num number;
exec :v_num:=dbms_spm.alter_sql_plan_baseline(sql_handle =>'SQL_handle_value',  plan_name => 'plan_name_value',  attribute_name=> 'enabled',  attribute_value=>'YES'); 
print v_num;

var v_num number;
EXEC  :v_num:=dbms_spm.alter_sql_plan_baseline(sql_handle =>'SQL_handle_value',  plan_name => 'plan_name_value',  attribute_name=> 'fixed',  attribute_value=>'YES');  
print v_num;


Disable the SQL Plan in SQL plan Baseline in Oracle

var v_num number;
exec :v_num:=dbms_spm.alter_sql_plan_baseline(sql_handle =>'SQL_handle_value',  plan_name => 'plan_name_value',  attribute_name=> 'enabled',  attribute_value=>'NO'); 
print v_num;

var v_num number;
EXEC  :v_num:=dbms_spm.alter_sql_plan_baseline(sql_handle =>'SQL_handle_value',  plan_name => 'plan_name_value',  attribute_name=> 'fixed',  attribute_value=>'NO');  
print v_num;




ww

set serveroutput on
set line 999 pages 999
select dbms_spm.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_1046c141c5de11a8',plan_name => 'sql_plan_10jq1872xw4d8c079fdff') from dual;




SET SERVEROUTPUT ON  LONG 10000
DECLARE
x clob;
BEGIN
x := dbms_spm.evolve_sql_plan_baseline('SQL_9d6ca16cee695f92','SQL_PLAN_9uv51dmr6krwkd8a279cc',
VERIFY=>'YES',
COMMIT=>'YES');
DBMS_OUTPUT.PUT_LINE(x);
END;
/


set serveroutput on
declare
v_sql_plan_id  pls_integer;
begin
v_sql_plan_id := dbms_spm.alter_sql_plan_baseline(
sql_handle      => 'sys_sql_1046c141c5de11a8',
plan_name       => 'sql_plan_10jq1872xw4d8cf314e9e',
attribute_name  => 'fixed',
attribute_value => 'YES');
end;
/

to check sql ...

from   http://anuj-singh.blogspot.com/2021/02/      SQL Report ....   / SQL info ... 

var sqlid varchar2(30)
 begin :sqlid := '7j5bb53huv8v1'; end;  ---- sql id here!!!!
/

set long 50000 pagesize 500 linesize 300
col frm         heading from 
select * from (select 'gv$sql' frm ,  sql_fulltext from gv$sql where sql_id=:sqlid
               union all
               select 'dba_hist', sql_text from dba_hist_sqltext where sql_id=:sqlid 
	   );



define sql_id='7jycxu86n60qh'

col plan_table_output for a150
select plan_table_output
from table(dbms_xplan.display_cursor('&sql_id', null, 'BASIC'))
union all
select * from table(dbms_xplan.display_awr('&sql_id', null, null, 'ALL'))



-- Purge the Shared Pool 

select 'exec sys.dbms_shared_pool.purge('''||address||', '||hash_value||''', ''c'')'
from gv$sql
where sql_id = :sqlid
--and child_number=&childnr
;


set linesize 300
col begin_interval_time for a28
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value, nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 	avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) 		avg_lio,
(disk_reads_delta/decode(nvl(disk_reads_delta,0),0,1,executions_delta)) 		avg_pio,
(rows_processed_delta/decode(nvl(rows_processed_delta,0),0,1,executions_delta)) 	avg_rows,
(CPU_TIME_DELTA/decode(nvl(CPU_TIME_DELTA,0),0,1,executions_delta))/1000000 		avg_cpu_wait,
(IOWAIT_DELTA/decode(nvl(IOWAIT_DELTA,0),0,1,executions_delta))/1000000 		avg_user_io_wait,
(CLWAIT_DELTA/decode(nvl(CLWAIT_DELTA,0),0,1,executions_delta))/1000000 		avg_clu_wait,
(APWAIT_DELTA/decode(nvl(APWAIT_DELTA,0),0,1,executions_delta))/1000000 		avg_app_wait,
(CCWAIT_DELTA/decode(nvl(CCWAIT_DELTA,0),0,1,executions_delta))/1000000 		avg_concurrent_wait
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = :sql_id --sql_id 
and ss.snap_id = S.snap_id 
and ss.instance_number = S.instance_number 
and executions_delta > 0 order by 1, 2, 3


define sql_id='7jycxu86n60qh'

set lines 1000 pages 9999
col instance_number FOR 9999    HEA 'Inst'
col end_time 			HEA 'End Time'
col plan_hash_value 	        HEA 'Plan|Hash Value'

col rows_per_exec 		HEA 'Rows Per Exec'
col et_secs_per_exec 	HEA 'Elap Secs|Per Exec'
col cpu_secs_per_exec 	HEA 'CPU Secs|Per Exec'
col io_secs_per_exec 	HEA 'IO Secs|Per Exec'
col cl_secs_per_exec 	HEA 'Clus Secs|Per Exec'
col ap_secs_per_exec 	HEA 'App Secs|Per Exec'
col cc_secs_per_exec 	HEA 'Conc Secs|Per Exec'
col pl_secs_per_exec 	HEA 'PLSQL Secs|Per Exec'
col ja_secs_per_exec 	HEA 'Java Secs|Per Exec'
col executions_total   FOR 999,999 HEA 'Execs|Total'
select 'gv$dba_hist_sqlstat' source,h.instance_number,
to_char(CAST(s.begin_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') snap_time,
to_char(CAST(s.end_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') end_time,
h.sql_id,
h.plan_hash_value,
h.executions_total,
to_char(ROUND(h.rows_processed_total / h.executions_total), '999,999,999,999') 		rows_per_exec,
to_char(ROUND(h.elapsed_time_total / h.executions_total / 1e6, 3), '999,990.000') 	et_secs_per_exec,
to_char(ROUND(h.cpu_time_total / h.executions_total / 1e6, 3), '999,990.000') 		cpu_secs_per_exec,
to_char(ROUND(h.iowait_total / h.executions_total / 1e6, 3), '999,990.000') 		io_secs_per_exec,
to_char(ROUND(h.clwait_total / h.executions_total / 1e6, 3), '999,990.000') 		cl_secs_per_exec,
to_char(ROUND(h.apwait_total / h.executions_total / 1e6, 3), '999,990.000') 		ap_secs_per_exec,
to_char(ROUND(h.ccwait_total / h.executions_total / 1e6, 3), '999,990.000') 		cc_secs_per_exec,
to_char(ROUND(h.plsexec_time_total / h.executions_total / 1e6, 3), '999,990.000') 	pl_secs_per_exec,
to_char(ROUND(h.javexec_time_total / h.executions_total / 1e6, 3), '999,990.000') 	ja_secs_per_exec
FROM dba_hist_sqlstat h,dba_hist_snapshot s
WHERE h.sql_id = '&sql_id'
AND h.executions_total > 0
AND s.snap_id = h.snap_id
AND s.dbid = h.dbid
AND s.instance_number = h.instance_number
UNION ALL
SELECT 'gv$sqlarea_plan_hash' source,h.inst_id,
TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') snap_time,
TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') end_time,
h.sql_id,
h.plan_hash_value,
h.executions,
to_char(ROUND(h.rows_processed / h.executions), '999,999,999,999') 				rows_per_exec,
to_char(ROUND(h.elapsed_time / h.executions / 1e6, 3), '999,990.000') 				et_secs_per_exec,
to_char(ROUND(h.cpu_time / h.executions / 1e6, 3), '999,990.000') 				cpu_secs_per_exec,
to_char(ROUND(h.USER_IO_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') 			io_secs_per_exec,
to_char(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') 			cl_secs_per_exec,
to_char(ROUND(h.APPLICATION_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') 			ap_secs_per_exec,
to_char(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') 			cc_secs_per_exec,
to_char(ROUND(h.PLSQL_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') 			pl_secs_per_exec,
to_char(ROUND(h.JAVA_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') 			ja_secs_per_exec
FROM gv$sqlarea_plan_hash h
WHERE h.sql_id = '&sql_id'
--and h.inst_id=inst_id
AND h.executions > 0
order by source ;



col inst 		for 99999999
col sid 		for 9990
col serial# 		for 999990
col username 		for a12
col osuser 		for a16
col program 		for a10 trunc
col Locked 		for a6
col status 		for a1 trunc print
col "hh:mm:ss" 		for a8
col SQL_ID 		for a15
col seq# 								for 99990
col event heading 'Current/LastEvent' 	for a25 trunc
col state head 'State (sec)' 			for a14
 col kill 								for a15
select ''''||sid ||','|| serial#||',@'||inst_id ||'''' kill, username, 
ltrim(substr(osuser, greatest(instr(osuser, '\', -1, 1)+1,length(osuser)-14))) osuser,
substr(program,instr(program,'/',-1)+1,
decode(instr(program,'@'),0,decode(instr(program,'.'),0,length(program),instr(program,'.')-1),instr(program,'@')-1)) program,  decode(lockwait,NULL,' ','L') locked, status, 
to_char(to_date(mod(last_call_et,86400), 'sssss'), 'hh24:mi:ss') "hh:mm:ss",
SQL_ID, seq# , event, 
decode(state,'WAITING','WAITING '||lpad(to_char(mod(SECONDS_IN_WAIT,86400),'99990'),6),'WAITED SHORT TIME','ON CPU','WAITED KNOWN TIME','ON CPU',state) state,substr(module,1,25) module, substr(action,1,20) action
from GV$SESSION 
where type = 'USER'
and audsid != 0    -- to exclude internal processess
and sql_id= :sqlid
order by inst_id, status, last_call_et desc, sid
/

gv$sql with objects

sql_id and objects 



efine sql_id='7jycxu86n60qh'   -------

SET HEADING ON
SET PAGESIZE 1000 LINESIZE 500
COL text FOR A50 wrap 
COL ctext FOR A50 wrap 
col PARSING_SCHEMA_NAME for a20
col LAST_LOAD_TIME  for a27
col FIRST_LOAD_TIME for a27
col OWNER for a20
col OBJECT_NAME for a20
select vs.sql_id,vs.last_load_time, ao.OWNER, parsing_schema_name, first_load_time , ao.OBJECT_NAME, vs.program_line#, executions exe, vs.sqltype, vs.sql_id, vs.rows_processed rows_processed, concurrency_wait_time, elapsed_time/1000000 elapsed_secs, elapsed_time/1000000/(case when executions = 0 then 1 else executions end) elap_per_exec_secs, vs.sql_text ctext
from gv$sql vs, all_objects ao
where vs.PROGRAM_ID = ao.OBJECT_ID and parsing_schema_name not in ('SYS','SYSTEM') 
--and parsing_schema_name in ('')
--and owner not in ('SYS','SYSTEM','DBSNMP','SYSMAN','MDSYS')
and vs.sql_id='&sql_id'
order by vs.last_load_time desc , vs.parsing_schema_name, vs.first_load_time desc , program_id, vs.program_line#;

Oracle SQL Plan Management (SPM) from AWR !!! 

How to Generate an AWR Report and Create Baselines (Doc ID 748642.1)
How to Create A SQL Plan Baseline From A Historical Execution Plan In The Automatic Workload Repository (AWR) [RDBMS Version 12.2 or Higher] (Doc ID 2885167.1)

Oracle version=>12.2 

SQL> def
DEFINE _DATE           = "12-NOV-22" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "rac1" (CHAR)
DEFINE _USER           = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1202000100" (CHAR)
=========================================================


define sql_id='8cnh50qfgwg73'  ----- For this sql !!!!

set lines 1000 pages 9999
col instance_number FOR 9999    	HEA 'Inst'
col end_time 				HEA 'End Time'
col plan_hash_value 	        	HEA 'Plan|Hash Value'
col rows_per_exec 			HEA 'Rows Per Exec'
col et_secs_per_exec 			HEA 'Elap Secs|Per Exec'
col cpu_secs_per_exec 			HEA 'CPU Secs|Per Exec'
col io_secs_per_exec 			HEA 'IO Secs|Per Exec'
col cl_secs_per_exec 			HEA 'Clus Secs|Per Exec'
col ap_secs_per_exec 			HEA 'App Secs|Per Exec'
col cc_secs_per_exec 			HEA 'Conc Secs|Per Exec'
col pl_secs_per_exec 			HEA 'PLSQL Secs|Per Exec'
col ja_secs_per_exec 			HEA 'Java Secs|Per Exec'
col executions_total   FOR 999,999 	HEA 'Execs|Total'
col PARSING_SCHEMA_NAME   for a20
select 'gv$dba_hist_sqlstat' source,h.snap_id,h.instance_number,
h.CON_ID,
to_char(CAST(s.begin_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') 	snap_time,
to_char(CAST(s.end_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') 	end_time,
h.sql_id,
h.plan_hash_value,
h.executions_total,
h.PARSING_SCHEMA_NAME,
to_char(ROUND(h.rows_processed_total / h.executions_total), '999,999,999,999') 		rows_per_exec,
to_char(ROUND(h.elapsed_time_total / h.executions_total / 1e6, 3), '999,990.000') 	et_secs_per_exec,
to_char(ROUND(h.cpu_time_total / h.executions_total / 1e6, 3), '999,990.000') 		cpu_secs_per_exec,
to_char(ROUND(h.iowait_total / h.executions_total / 1e6, 3), '999,990.000') 		io_secs_per_exec,
to_char(ROUND(h.clwait_total / h.executions_total / 1e6, 3), '999,990.000') 		cl_secs_per_exec,
to_char(ROUND(h.apwait_total / h.executions_total / 1e6, 3), '999,990.000') 		ap_secs_per_exec,
to_char(ROUND(h.ccwait_total / h.executions_total / 1e6, 3), '999,990.000') 		cc_secs_per_exec,
to_char(ROUND(h.plsexec_time_total / h.executions_total / 1e6, 3), '999,990.000') 	pl_secs_per_exec,
to_char(ROUND(h.javexec_time_total / h.executions_total / 1e6, 3), '999,990.000') 	ja_secs_per_exec
FROM dba_hist_sqlstat h,dba_hist_snapshot s
WHERE 1=1
and h.sql_id = '&sql_id'
AND h.executions_total > 0
AND s.snap_id = h.snap_id
AND s.dbid = h.dbid
AND s.instance_number = h.instance_number
--and PARSING_SCHEMA_NAME!='SYS'
;

                                                                                                      Plan    Execs                                       Elap Secs    CPU Secs     IO Secs      Clus Secs    App Secs     Conc Secs    PLSQL Secs   Java Secs
SOURCE                 SNAP_ID  Inst     CON_ID SNAP_TIME        End Time         SQL_ID        Hash Value    Total PARSING_SCHEMA_NAME  Rows Per Exec    Per Exec     Per Exec     Per Exec     Per Exec     Per Exec     Per Exec     Per Exec     Per Exec
------------------- ---------- ----- ---------- ---------------- ---------------- ------------- ---------- -------- -------------------- ---------------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------
gv$dba_hist_sqlstat       9720     1          0 09-11-2022 20:00 09-11-2022 21:00 8cnh50qfgwg73 2772691065   17,496 SVCDBEM7ADM                         1        1.990        0.638        1.227        0.417       0.000         0.000        0.000        0.000
gv$dba_hist_sqlstat       9748     1          0 11-11-2022 00:00 11-11-2022 01:00 8cnh50qfgwg73 2772691065   10,368 SVCDBEM7ADM                         1        1.907        0.634        1.147        0.417       0.000         0.000        0.000        0.000
gv$dba_hist_sqlstat       9770     1          0 11-11-2022 22:00 11-11-2022 23:00 8cnh50qfgwg73 2772691065      384 SVCDBEM7ADM                         1        1.910        0.648        1.148        0.408       0.000         0.000        0.000        0.000
gv$dba_hist_sqlstat       9578     1          0 03-11-2022 23:00 04-11-2022 00:00 8cnh50qfgwg73 2772691065    9,984 SVCDBEM7ADM                         1        1.913        0.633        1.155        0.415       0.000         0.000        0.000        0.000
gv$dba_hist_sqlstat       9633     1          0 06-11-2022 05:00 06-11-2022 06:00 8cnh50qfgwg73 2772691065    3,840 SVCDBEM7ADM                         1        1.874        0.634        1.127        0.402       0.000         0.000        0.000        0.000
gv$dba_hist_sqlstat       9640     1          0 06-11-2022 12:00 06-11-2022 13:00 8cnh50qfgwg73 2772691065    6,528 SVCDBEM7ADM                         1        1.867        0.639        1.114        0.405       0.000         0.000        0.000        0.000
gv$dba_hist_sqlstat       9738     1          0 10-11-2022 14:00 10-11-2022 15:00 8cnh50qfgwg73 2772691065    6,528 SVCDBEM7ADM                         1        1.924        0.633        1.165        0.416       0.000         0.000        0.000        0.000
gv$dba_hist_sqlstat       9745     1          0 10-11-2022 21:00 10-11-2022 22:00 8cnh50qfgwg73 2772691065    9,216 SVCDBEM7ADM                         1        1.907        0.631        1.149        0.417       0.000         0.000        0.000        0.000


or

define sql_id='8cnh50qfgwg73'  ----- For this sql !!!!

SET LINESIZE 2000 PAGESIZE 20000
SET LONG 99999
     select
        SNAP_ID,
        TO_CHAR(begin_interval_time,'DD-MON-YYYY HH24') begin_interval_time,
        TO_CHAR(end_interval_time,'DD-MON-YYYY HH24') end_interval_time,
        SQL_ID,
        round((round((avg(ELAPSED_TIME_DELTA)/1000000),2)/(case when avg(EXECUTIONS_DELTA)=0 then 1 else avg(EXECUTIONS_DELTA) end)),2) as ELAPSED_TIME_SECS,
        round((round((avg(CPU_TIME_DELTA)/1000000),2)/(case when avg(EXECUTIONS_DELTA)=0 then 1 else avg(EXECUTIONS_DELTA) end)),2) 	as CPU_TIME_SECS,
        round((avg(BUFFER_GETS_DELTA)/(case when avg(EXECUTIONS_DELTA)=0 then 1 else avg(EXECUTIONS_DELTA) end)),2) 			as GETS_PER_EXEC
     from  dba_hist_snapshot natural join dba_hist_sqlstat natural join dba_hist_sqltext
     where
      (elapsed_time_delta > 0 or elapsed_time_delta is not null)
      and SQL_ID =  '&sql_id'
     group by
        SNAP_ID,
        TO_CHAR(begin_interval_time,'DD-MON-YYYY HH24'),
        TO_CHAR(end_interval_time,'DD-MON-YYYY HH24'),
        SQL_ID
     order by snap_id asc
/


  SNAP_ID BEGIN_INTERVAL_TIME     END_INTERVAL_TIME       SQL_ID         ELAPSED_TIME_SECS CPU_TIME_SECS GETS_PER_EXEC
---------- ----------------------- ----------------------- -------------- ----------------- ------------- -------------
      9574 03-NOV-2022 19          03-NOV-2022 20          8cnh50qfgwg73               1.86           .63      10988.01
      9575 03-NOV-2022 20          03-NOV-2022 21          8cnh50qfgwg73               1.89           .63      10966.74
      9576 03-NOV-2022 21          03-NOV-2022 22          8cnh50qfgwg73               1.85           .62      10978.95
      9577 03-NOV-2022 22          03-NOV-2022 23          8cnh50qfgwg73               1.94           .66      10980.97
      9578 03-NOV-2022 23          04-NOV-2022 00          8cnh50qfgwg73                1.9           .65      10982.64
      9579 04-NOV-2022 00          04-NOV-2022 01          8cnh50qfgwg73                1.9           .65      10983.89




-- to check sql text !!!!
col sql_text for a50 wrap
select CON_ID,sql_text from dba_hist_sqltext
where 1=1 
and sql_id ='&sql_id'
and rownum<3;


    CON_ID SQL_TEXT
---------- --------------------------------------------------
         0 SELECT NVL(SUM(BYTES),0) FROM SYS.DBA_FREE_SPACE W
           HERE TABLESPACE_NAME = :B1



--- change the value for sql_id and plan_hash_value
 
 variable x number
begin
       :x := dbms_spm.load_plans_from_awr( begin_snap=>9738,end_snap=>9745,
                               basic_filter=>q'[ sql_id='8cnh50qfgwg73' and plan_hash_value='2772691065' ]' );
    end;
    /



 print x

     X
----------
         1




set linesize 300
col CREATOR for a20
col PLAN_NAME for a30
col PARSING_SCHEMA_NAME for a25
col CREATED for a30
col ENABLED for a15
col ACCEPTED for a15
col REPRODUCED for a15
select SQL_HANDLE,PLAN_NAME,CREATOR,ORIGIN,PARSING_SCHEMA_NAME, ENABLED,ACCEPTED,REPRODUCED,CREATED
from dba_sql_plan_baselines
where 1=1
--and origin like 'MANUAL-LOAD%'
and CREATED >sysdate -1
-- and SQL_HANDLE='SQL_7a5adea0a1422e62'
order by created desc;


SQL_HANDLE                     PLAN_NAME                      CREATOR              ORIGIN                        PARSING_SCHEMA_NAME       ENABLED         ACCEPTED        REPRODUCED      CREATED
------------------------------ ------------------------------ -------------------- ----------------------------- ------------------------- --------------- --------------- --------------- ------------------------------
SQL_bb8610f7a061889d           SQL_PLAN_br1hhyyh6324xf1ad1f6a SYS                  MANUAL-LOAD-FROM-AWR          SVCDBEM7ADM               YES             YES             NO              12-NOV-22 03.14.49.000000 AM


or

col sql_id         format a14
col sql_handle     format a22
col plan_name      format a32
col sql_text       format a40
col ENABLED for a15
col ACCEPTED for a15
col SQL_TEXT for a40 wrap
select
        DBMS_SQL_TRANSLATOR.SQL_ID(sql_text) as sql_id,
        sql_handle                                    ,
        plan_name                                     ,
        enabled                                       ,
        accepted ,
        SQL_TEXT 
from        dba_sql_plan_baselines
where     DBMS_SQL_TRANSLATOR.SQL_ID(sql_text) = '8cnh50qfgwg73';



SQL_ID         SQL_HANDLE             PLAN_NAME                        ENABLED         ACCEPTED        SQL_TEXT
-------------- ---------------------- -------------------------------- --------------- --------------- ----------------------------------------
8cnh50qfgwg73  SQL_bb8610f7a061889d   SQL_PLAN_br1hhyyh6324xf1ad1f6a   YES             YES             SELECT NVL(SUM(BYTES),0) FROM SYS.DBA_FR
                                                                                                       EE_SPACE WHERE TABLESPACE_NAME = :B1



====

with sql text !!!!


define sql_id='8cnh50qfgwg73'
 

set linesize 400
col CREATOR for a20
col PLAN_NAME for a30
col PARSING_SCHEMA_NAME for a25
col CREATED for a30
col ENABLED for a15
col ACCEPTED for a15
col REPRODUCED for a15
col SQL_TEXT for a50 wrap
select sql_id,plan_name,SQL_HANDLE,PLAN_NAME,CREATOR,ORIGIN,PARSING_SCHEMA_NAME, ENABLED,ACCEPTED,REPRODUCED,CREATED,substr(sa.SQL_TEXT,1,50) SQL_TEXT
from dba_sql_plan_baselines bl, dba_hist_sqltext sa
where DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(bl.sql_text) = DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sa.sql_text)
and sa.sql_id = '&sql_id'
and origin   like 'MANUA%'
and created   > sysdate -1;



SQL_ID        PLAN_NAME                      SQL_HANDLE                     PLAN_NAME                      CREATOR              ORIGIN                        PARSING_SCHEMA_NAME       ENABLED         ACCEPTEDREPRODUCED      CREATED
------------- ------------------------------ ------------------------------ ------------------------------ -------------------- ----------------------------- ------------------------- --------------- --------------- --------------- ------------------------------
8cnh50qfgwg73 SQL_PLAN_br1hhyyh6324xf1ad1f6a SQL_bb8610f7a061889d           SQL_PLAN_br1hhyyh6324xf1ad1f6a SYS                  MANUAL-LOAD-FROM-AWR          SVCDBEM7ADM               YES             YES    NO               12-NOV-22 03.14.49.000000 AM





-- to check 

col PLAN_TABLE_OUTPUT for a120
select t2.*
from dba_sql_plan_baselines t1,
table( dbms_xplan.display_sql_plan_baseline(t1.sql_handle,t1.plan_name) ) t2
where 1=1
and SQL_HANDLE ='&SQL_HANDLE' 
--and PLAN_NAME='SQL_PLAN_br1hhyyh6324xf1ad1f6a'
;



PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------
SQL handle: SQL_bb8610f7a061889d
SQL text: SELECT NVL(SUM(BYTES),0) FROM SYS.DBA_FREE_SPACE WHERE
          TABLESPACE_NAME = :B1
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_br1hhyyh6324xf1ad1f6a         Plan id: 4054654826
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD-FROM-AWR
Plan rows: From dictionary
--------------------------------------------------------------------------------

Plan hash value: 2772691065




======


Check if possible!!!!!!

alter session set current_schema=SVCDBEM7ADM;



 var B1 varchar2(10);
 begin :B1 := 'abcdf'; end;
/

set autotrace traceonly explain
SELECT NVL(SUM(BYTES),0) FROM SYS.DBA_FREE_SPACE WHERE TABLESPACE_NAME = :B1


.
.
.
.
.
.


Note
-----
   - SQL plan baseline "SQL_PLAN_br1hhyyh6324xf1ad1f6a" used for this statement   <<<<<<<<<<< 
   - this is an adaptive plan

=======

2nd method   

 show parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     TRUE



define sql_id='87gaftwrm2h68'

col BEGIN_INTERVAL_TIME for a30
SELECT SS.SNAP_ID,
     SS.INSTANCE_NUMBER,
     BEGIN_INTERVAL_TIME,
     SQL_ID,
     PLAN_HASH_VALUE,OPTIMIZER_COST,
     DISK_READS_TOTAL,
     BUFFER_GETS_TOTAL,
     ROWS_PROCESSED_TOTAL,
     CPU_TIME_TOTAL,
     ELAPSED_TIME_TOTAL,
     IOWAIT_TOTAL,
     NVL (EXECUTIONS_DELTA, 0) EXECS,
       (  ELAPSED_TIME_DELTA  / DECODE (NVL (EXECUTIONS_DELTA, 0), 0, 1, EXECUTIONS_DELTA))  / 1000000     AVG_ETIME,
     (  BUFFER_GETS_DELTA     / DECODE (NVL (BUFFER_GETS_DELTA, 0), 0, 1, EXECUTIONS_DELTA))  AVG_LIO
FROM DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
WHERE     1=1
and SQL_ID = '&SQL_ID'
     AND SS.SNAP_ID = S.SNAP_ID
     AND SS.INSTANCE_NUMBER = S.INSTANCE_NUMBER
     AND EXECUTIONS_DELTA > 0
ORDER BY 1, 2, 3;

  SNAP_ID INSTANCE_NUMBER BEGIN_INTERVAL_TIME            SQL ID           PLAN_HASH_VALUE OPTIMIZER_COST DISK_READS_TOTAL BUFFER_GETS_TOTAL ROWS_PROCESSED_TOTAL CPU_TIME_TOTAL ELAPSED_TIME_TOTAL IOWAIT_TOTAL      EXECS  AVG_ETIME    AVG_LIO
---------- --------------- ------------------------------ ---------------- --------------- -------------- ---------------- ----------------- -------------------- -------------- ------------------ ------------ ---------- ---------- ----------
      9819               1 13-NOV-22 11.00.22.179 PM      87gaftwrm2h68         1072382624              3               16           2861552               135012       67293418           67261553      1537478       4476 .000033331 2.02033065
      9819               2 13-NOV-22 11.00.22.135 PM      87gaftwrm2h68         1072382624              3               26           3421346               112091       56183976           56243522       403516       6090 .000027044 2.06962233
      9820               1 14-NOV-22 12.00.30.958 AM      87gaftwrm2h68         1072382624              3               16           2872967               135125       67518772           67434327      1538665       5650 .000030579 2.02035398
      



 --Create STS.

BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(
    sqlset_name => 'STS_87gaftwrm2h68',
    description => 'SQL Tuning Set for loading plan into SQL Plan Baseline');
END;




--Load STS 

DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P)
    FROM TABLE(
       dbms_sqltune.select_workload_repository(begin_snap=>9819, end_snap=>9820,basic_filter=>'sql_id = ''87gaftwrm2h68''',attribute_list=>'ALL')
              ) p;
     DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'STS_87gaftwrm2h68', populate_cursor=>cur);
  CLOSE cur;
END;




set linesize 2000
col SQL_TEXT for a50 wrap
col x for a200
SELECT
  first_load_time          ,
  executions as execs              ,
  parsing_schema_name      ,
  elapsed_time  / 1000000 as elapsed_time_secs  ,
  cpu_time / 1000000 as cpu_time_secs           ,
  buffer_gets              ,
  disk_reads               ,
  direct_writes            ,
  rows_processed           ,
  fetches                  ,
  optimizer_cost           ,
  sql_plan                ,
  plan_hash_value          ,
  sql_id                   ,
  sql_text
   FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name => 'STS_87gaftwrm2h68') 
             );




FIRST_LOAD_TIME          EXECS PARSING_SCHEMA_NAME       ELAPSED_TIME_SECS CPU_TIME_SECS BUFFER_GETS DISK_READS DIRECT_WRITES ROWS_PROCESSED    FETCHES OPTIMIZER_COST
------------------- ---------- ------------------------- ----------------- ------------- ----------- ---------- ------------- -------------- ---------- --------------
SQL_PLAN(STATEMENT_ID, PLAN_ID, TIMESTAMP, REMARKS, OPERATION, OPTIONS, OBJECT_NODE, OBJECT_OWNER, OBJECT_NAME, OBJECT_ALIAS, OBJECT_INSTANCE, OBJECT_TYPE, OPTIMIZER, SEARCH_COLUMNS, ID, PARENT_ID, DEPTH, POSITION, COST, CARDINALITY, BYTES, OTHER_TAG, PARTITION_START, PARTITION_STOP, PARTITION_ID, DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE, ACCESS_PREDICATES, FILTER_PREDICATES, PROJECTION, TIME, QBLOCK_NAME, OTHER_XML)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PLAN_HASH_VALUE SQL ID           SQL_TEXT
--------------- ---------------- --------------------------------------------------
                         12521 SYS                                 .387451        .43431       25561          0             0            443      12521              3
SQL_PLAN_TABLE_TYPE(SQL_PLAN_ROW_TYPE(NULL, NULL, '02-JUN-17', NULL, 'SELECT STATEMENT', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'CHOOSE', 0, 0, NULL, 0, 3, 3, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL), SQL_PLAN_ROW_TYPE(NULL, NULL, '02-JUN-17', NULL, 'TABLE ACCESS', 'BY INDEX ROWID BATCHED', NULL, 'SYS', 'OBJ$', 'O@SEL$1', NULL, 'TABLE', NULL, 0, 1, 0, 1, 1, 3, 1, 107, NULL, NULL, NULL, NULL, NULL, 21954, 3, NULL, NULL, NULL, NULL, 1, 'SEL$1', '<other_xml><info type="db_version">12.2.0.1</info><info type="parse_schema"><![C'), SQL_PLAN_ROW_TYPE(NULL, NULL, '02-JUN-17', NULL, 'INDEX', 'RANGE SCAN', NULL, 'SYS', 'I_OBJ1', 'O@SEL$1', NULL, 'INDEX (UNIQUE)', NULL, 1, 2, 1, 2, 1, 2, 1, NULL, NULL, NULL, NULL, NULL, NULL, 14443, 2, NULL, NULL, NULL, NULL, 1, 'SEL$1', NULL))
     1072382624 87gaftwrm2h68    select o.owner#,o.name,o.namespace,o.remoteowner,o
                                 .linkname,o.subname from obj$




DECLARE
my_plans pls_integer;
BEGIN
  my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
    sqlset_name => 'STS_87gaftwrm2h68', 
    basic_filter=>'plan_hash_value = ''1072382624'''
    );
END;






col sql_id         format a14
col sql_handle     format a22
col plan_name      format a32
col sql_text       format a40
col ENABLED for a15
col ACCEPTED for a15
col SQL_TEXT for a40 wrap
select
        DBMS_SQL_TRANSLATOR.SQL_ID(sql_text) as sql_id,
        sql_handle                                    ,
        plan_name                                     ,
        enabled                                       ,
        accepted ,
       FIXED,
ORIGIN,
        SQL_TEXT 
from        dba_sql_plan_baselines
where     DBMS_SQL_TRANSLATOR.SQL_ID(sql_text) = '87gaftwrm2h68'
and origin   like 'MANUA%'
--and created   > sysdate -3
;




SQL ID         SQL_HANDLE             PLAN_NAME                        ENABLED         ACCEPTED        FIX ORIGIN                        SQL_TEXT
-------------- ---------------------- -------------------------------- --------------- --------------- --- ----------------------------- ----------------------------------------
87gaftwrm2h68  SQL_3e0587805c3ad254   SQL_PLAN_3w1c7h1f3pnkn77dddc30   YES             YES             NO  MANUAL-LOAD-FROM-STS          select o.owner#,o.name,o.namespace,o.rem
                                                                                                                                         oteowner,o.linkname,o.subname from obj$

============

3rd  method



define sql_id='3kqrku32p6sfn'  ----- For this sql !!!!

set lines 1000 pages 9999
col instance_number FOR 9999    	HEA 'Inst'
col end_time 				HEA 'End Time'
col plan_hash_value 	        	HEA 'Plan|Hash Value'
col rows_per_exec 			HEA 'Rows Per Exec'
col et_secs_per_exec 			HEA 'Elap Secs|Per Exec'
col cpu_secs_per_exec 			HEA 'CPU Secs|Per Exec'
col io_secs_per_exec 			HEA 'IO Secs|Per Exec'
col cl_secs_per_exec 			HEA 'Clus Secs|Per Exec'
col ap_secs_per_exec 			HEA 'App Secs|Per Exec'
col cc_secs_per_exec 			HEA 'Conc Secs|Per Exec'
col pl_secs_per_exec 			HEA 'PLSQL Secs|Per Exec'
col ja_secs_per_exec 			HEA 'Java Secs|Per Exec'
col executions_total   FOR 999,999 	HEA 'Execs|Total'
col PARSING_SCHEMA_NAME   for a20
select 'gv$dba_hist_sqlstat' source,h.snap_id,h.instance_number,
h.CON_ID,
to_char(CAST(s.begin_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') 	snap_time,
to_char(CAST(s.end_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') 	end_time,
h.sql_id,
h.plan_hash_value,
h.executions_total,
h.PARSING_SCHEMA_NAME,
to_char(ROUND(h.rows_processed_total / h.executions_total), '999,999,999,999') 		rows_per_exec,
to_char(ROUND(h.elapsed_time_total / h.executions_total / 1e6, 3), '999,990.000') 	et_secs_per_exec,
to_char(ROUND(h.cpu_time_total / h.executions_total / 1e6, 3), '999,990.000') 		cpu_secs_per_exec,
to_char(ROUND(h.iowait_total / h.executions_total / 1e6, 3), '999,990.000') 		io_secs_per_exec,
to_char(ROUND(h.clwait_total / h.executions_total / 1e6, 3), '999,990.000') 		cl_secs_per_exec,
to_char(ROUND(h.apwait_total / h.executions_total / 1e6, 3), '999,990.000') 		ap_secs_per_exec,
to_char(ROUND(h.ccwait_total / h.executions_total / 1e6, 3), '999,990.000') 		cc_secs_per_exec,
to_char(ROUND(h.plsexec_time_total / h.executions_total / 1e6, 3), '999,990.000') 	pl_secs_per_exec,
to_char(ROUND(h.javexec_time_total / h.executions_total / 1e6, 3), '999,990.000') 	ja_secs_per_exec
FROM dba_hist_sqlstat h,dba_hist_snapshot s
WHERE 1=1
and h.sql_id = '&sql_id'
AND h.executions_total > 0
AND s.snap_id = h.snap_id
AND s.dbid = h.dbid
AND s.instance_number = h.instance_number
--and PARSING_SCHEMA_NAME!='SYS'
;



                                                                                                    Plan    Execs                                       Elap Secs    CPU Secs     IO Secs      Clus Secs    App Secs     Conc Secs    PLSQL Secs   Java Secs
SOURCE                 SNAP_ID  Inst     CON_ID SNAP_TIME        End Time         SQL_ID        Hash Value    Total PARSING_SCHEMA_NAME  Rows Per Exec    Per Exec     Per Exec     Per Exec     Per Exec     Per Exec     Per Exec     Per Exec     Per Exec
------------------- ---------- ----- ---------- ---------------- ---------------- ------------- ---------- -------- -------------------- ---------------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------
gv$dba_hist_sqlstat       9656     1          0 07-11-2022 04:00 07-11-2022 05:00 3kqrku32p6sfn 1774581179       15 SYS                                 0        0.144        0.139        0.000        0.000       0.000         0.000        0.000        0.000
gv$dba_hist_sqlstat       9650     1          0 06-11-2022 22:00 06-11-2022 23:00 3kqrku32p6sfn 1774581179        3 SYS                                 0        0.125        0.121        0.002        0.000       0.000         0.000        0.000        0.000
gv$dba_hist_sqlstat       9651     1          0 06-11-2022 23:00 07-11-2022 00:00 3kqrku32p6sfn 1774581179        2 SYS                                 0        0.290        0.286        0.000        0.000       0.000         0.000        0.000        0.000
gv$dba_hist_sqlstat       9653     1          0 07-11-2022 01:00 07-11-2022 02:00 3kqrku32p6sfn 1774581179        3 SYS                                 0        0.165        0.160        0.000        0.000       0.000         0.000        0.000        0.000
gv$dba_hist_sqlstat       9655     1          0 07-11-2022 03:00 07-11-2022 04:00 3kqrku32p6sfn 1774581179       11 SYS                                 0        0.150        0.146        0.000        0.000       0.000         0.000        0.000        0.000







define v_sql_id='3kqrku32p6sfn'
define v_fixed='YES'
define v_enabled='YES'
define v_begin_snap=9650
define v_end_snap=9651
define v_plan_hash_value=1774581179

set serveroutput on
 
declare
    rc integer;
    baseline_ref_cur  DBMS_SQLTUNE.SQLSET_CURSOR;
    v_sql_handle      varchar2(30);
    v_plan_name       varchar2(30);

begin
 
-- Step 1 : Create SQL Tuning SET
dbms_sqltune.create_sqlset(
  sqlset_name => '&v_sql_id'||'_spm',
  description => 'SQL Tuning Set to create SQL baseline for '||'&v_sql_id');
 

-- Step 2 : Select sql_id and plan_hash_value from AWR

open baseline_ref_cur for
select VALUE(p) from table(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
  begin_snap => '&v_begin_snap',
  end_snap => '&v_end_snap',
  basic_filter => 'sql_id='||CHR(39)||'&v_sql_id'||CHR(39)||' and plan_hash_value=&v_plan_hash_value',
  attribute_list => 'ALL')) p;
 
-- Step 3 : Load the AWR cursor into SQLSET

DBMS_SQLTUNE.LOAD_SQLSET(
  sqlset_name=>'&v_sql_id'||'_spm',
  populate_cursor=> baseline_ref_cur);

--+ Step 3+: Close cursor and check

close baseline_ref_cur;

select count(*)
  into rc
  from dba_sqlset_statements
 where sqlset_name     = '&v_sql_id' || '_spm'
   and sql_id          = '&v_sql_id'
   and plan_hash_value = &v_plan_hash_value;

 if rc = 0
   then DBMS_SQLTUNE.drop_sqlset('fxgzfhx4fr9rv'||'_spm');
        raise NO_DATA_FOUND;
 end if;

-- Step 4 : Create baseline; that is loading plans from sqlset into SPM

rc := dbms_spm.load_plans_from_sqlset(
 sqlset_name  => '&v_sql_id'||'_spm',
 basic_filter => 'sql_id='||CHR(39)||'&v_sql_id'||CHR(39)||' and plan_hash_value=&v_plan_hash_value',
 fixed        => '&v_fixed',
 enabled      => '&v_enabled');

--+ Step 5: Get baseline names

   select sql_handle, plan_name
     into v_sql_handle, v_plan_name
     from dba_sql_plan_baselines bl, dba_hist_sqltext sa
    where DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(bl.sql_text) = DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sa.sql_text)
      and sa.sql_id = '&v_sql_id'
      --and origin    = 'MANUAL-LOAD'
      and created   > sysdate - 15/24/60/60;


   dbms_output.put_line('');
   dbms_output.put_line('Baseline '||v_sql_handle||' '||v_plan_name||' was created from AWR');
   dbms_output.put_line('for SQL_ID='||'&v_sql_id'||', SQL_PLAN_HASH='||'&v_plan_hash_value');

end;
/




Baseline SQL_80f7845a69e12ff7 SQL_PLAN_81xw4b9ny2bzrf28fb0a6 was created from AWR
for SQL_ID=3kqrku32p6sfn, SQL_PLAN_HASH=1774581179





col sql_text 	for a50 wrap
col enabled 	for a15
col  accepted 	for a15
col fixed 	for a15
col CREATED 	for a30
col PLAN_NAME 	for a30
select CREATED,sa.sql_id,sql_handle, plan_name,origin, enabled, accepted, fixed, sa.sql_text   from dba_sql_plan_baselines bl, dba_hist_sqltext sa
    where DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(bl.sql_text) = DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sa.sql_text)
      and sa.sql_id = '&v_sql_id'
      --and origin    = 'MANUAL-LOAD'
      and created   > sysdate - 1;






CREATED                        SQL_ID        SQL_HANDLE                     PLAN_NAME                      ORIGIN                        ENABLED         ACCEPTED        FIXED           SQL_TEXT
------------------------------ ------------- ------------------------------ ------------------------------ ----------------------------- --------------- --------------- --------------- --------------------------------------------------
15-NOV-22 05.50.22.000000 AM   3kqrku32p6sfn SQL_80f7845a69e12ff7           SQL_PLAN_81xw4b9ny2bzrf28fb0a6 MANUAL-LOAD-FROM-STS          YES             YES             YES             MERGE /*+ OPT_PARAM('_parallel_syspls_obey_force'
                                                                                                                                                                                         'false') */ INTO OPTSTAT_USER_

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)
/

=====

set linesize 200 pages 9999
column sid format 9999999
column RT format a10
column ET format a10
column opname format a30
column target format a20
column pct_complete format 09D00 heading '%%%%'
col kill for a17
select
  ''''||sid ||','|| serial#||',@'||inst_id ||'''' kill ,con_id, sql_id, to_char(start_time, 'DD/MM/YYYY HH24:MI:SS') start_time, opname,  round((100 * sofar)/totalwork, 2)  pct_complete,
  ( extract(day from (systimestamp + numtodsinterval(elapsed_seconds, 'second') - systimestamp)) || ' ' ||
    extract(hour from (systimestamp + numtodsinterval(elapsed_seconds, 'second') - systimestamp)) || ':' ||
    extract(minute from (systimestamp + numtodsinterval(elapsed_seconds, 'second') - systimestamp))  || ':' ||
    round(extract(second from (systimestamp + numtodsinterval(elapsed_seconds, 'second') - systimestamp)))  
  ) ET,
  ( extract(day from (systimestamp + numtodsinterval(time_remaining, 'second') - systimestamp))  || ' ' ||
    extract(hour from (systimestamp + numtodsinterval(time_remaining, 'second') - systimestamp))  || ':' ||
    extract(minute from (systimestamp + numtodsinterval(time_remaining, 'second') - systimestamp))  || ':' ||
    round(extract(second from (systimestamp + numtodsinterval(time_remaining, 'second') - systimestamp))) 
  ) RT
from  gv$session_longops
where   time_remaining > 0
;

====

without con_id

set linesize 200 pages 9999
column sid format 9999999
column RT format a10
column ET format a10
column opname format a30
column target format a20
column pct_complete format 09D00 heading '%%%%'
col kill for a17
select
  ''''||sid ||','|| serial#||',@'||inst_id ||'''' kill 
--,con_id
, sql_id, to_char(start_time, 'DD/MM/YYYY HH24:MI:SS') start_time, opname,  round((100 * sofar)/totalwork, 2)  pct_complete,
  ( extract(day from (systimestamp + numtodsinterval(elapsed_seconds, 'second') - systimestamp)) || ' ' ||
    extract(hour from (systimestamp + numtodsinterval(elapsed_seconds, 'second') - systimestamp)) || ':' ||
    extract(minute from (systimestamp + numtodsinterval(elapsed_seconds, 'second') - systimestamp))  || ':' ||
    round(extract(second from (systimestamp + numtodsinterval(elapsed_seconds, 'second') - systimestamp)))  
  ) ET,
  ( extract(day from (systimestamp + numtodsinterval(time_remaining, 'second') - systimestamp))  || ' ' ||
    extract(hour from (systimestamp + numtodsinterval(time_remaining, 'second') - systimestamp))  || ':' ||
    extract(minute from (systimestamp + numtodsinterval(time_remaining, 'second') - systimestamp))  || ':' ||
    round(extract(second from (systimestamp + numtodsinterval(time_remaining, 'second') - systimestamp))) 
  ) RT
from  gv$session_longops
where   time_remaining > 0
;

=======

define 1='dgq1rfu9bzasw'
select sql_id,dbms_lob.substr(SQL_FULLTEXT,4000) sql_text from v$sql where sql_id like '%&&1%'
and rownum <2
union all
select sql_id,dbms_lob.substr(sql_text,4000) sql_text from dba_hist_sqltext where sql_id like '%&&1%'
and rownum <2
;

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

How to Recover a Table from an Oracle 12c RMAN Backup

How to Recover a Table from Oracle 12c via RMAN Backup ?


on same server .
Table recovery failed with RMAN-05057: Table OWNER.TABLE_NAME not found (Doc ID 2764271.1)


table being recovered resides in a pluggable database, include the keywords
"OF PLUGGABLE DATABASE <pdb_name>" in the script/command as below:
RECOVER TABLE OWNER.TABLE_NAME
  UNTIL TIME "to_date('08032021 1540', 'ddmmyyyy hh24mi')"
  AUXILIARY DESTINATION '/<path>'
  REMAP TABLE OWNER.<table name>:<new table name>;

=========

 
RMAN> RECOVER TABLE VIHAANX.TEST_RESTORE05 OF PLUGGABLE DATABASE PDB9 UNTIL TIME "to_date('05/10/2022 07:15:40','dd/mm/yyyy hh24:mi:ss')"
AUXILIARY DESTINATION '/u01/app/oracle/aux'
REMAP TABLE 'VIHAANX'.'TEST_RESTORE05':'TEST_RESTORE55';

Starting recover at 05-10-2022 08:34:46
using channel ORA_DISK_1
RMAN-05026: warning: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace PDB9:SYSTEM
Tablespace UNDOTBS1
Tablespace PDB9:UNDOTBS1
Tablespace UNDOTBS2
Tablespace PDB9:PDB9_UNDOTBS1

Creating automatic instance, with SID='wlan'

initialization parameters used for automatic instance:
db_name=VIHCDBD8
db_unique_name=wlan_pitr_PDB9_VIHCDBD8
compatible=12.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=21696M
processes=200
db_create_file_dest=/u01/app/oracle/aux
log_archive_dest_1='location=/u01/app/oracle/aux'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used


starting up automatic instance VIHCDBD8

Oracle instance started

Total System Global Area   22749904896 bytes

Fixed Size                    19421832 bytes
Variable Size               3019900280 bytes
Database Buffers           19662897152 bytes
Redo Buffers                  47685632 bytes
Automatic instance created

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('05/10/2022 07:15:40','dd/mm/yyyy hh24:mi:ss')";
# restore the controlfile
restore clone controlfile;

# mount the controlfile
sql clone 'alter database mount clone database';

# archive current online log
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET until clause

Starting restore at 05-10-2022 08:35:57
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=318 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/VIHAAN8/cf_c-3962735431-20221005-00
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/VIHAAN8/cf_c-3962735431-20221005-00 tag=TAG20221005T071537
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/aux/VIHCDBD8/controlfile/o1_mf_kmtykgqc_.ctl
Finished restore at 05-10-2022 08:35:59

sql statement: alter database mount clone database

sql statement: alter system archive log current

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('05/10/2022 07:15:40','dd/mm/yyyy hh24:mi:ss')";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  1 to new;
set newname for clone datafile  5 to new;
set newname for clone datafile  4 to new;
set newname for clone datafile  8 to new;
set newname for clone datafile  13 to new;
set newname for clone datafile  21 to new;
set newname for clone datafile  3 to new;
set newname for clone datafile  6 to new;
set newname for clone tempfile  1 to new;
set newname for clone tempfile  6 to new;
set newname for clone tempfile  8 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 5, 4, 8, 13, 21, 3, 6;

switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/aux/VIHCDBD8/datafile/o1_mf_temp_tes_%u_.tmp in control file
renamed tempfile 6 to /u01/app/oracle/aux/VIHCDBD8/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 8 to /u01/app/oracle/aux/VIHCDBD8/A3144982890B1187E05324F56A40A1C1/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 05-10-2022 08:36:06
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/aux/VIHCDBD8/A3144982890B1187E05324F56A40A1C1/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00008 to /u01/app/oracle/aux/VIHCDBD8/A3144982890B1187E05324F56A40A1C1/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00021 to /u01/app/oracle/aux/VIHCDBD8/A3144982890B1187E05324F56A40A1C1/datafile/o1_mf_pdb9_und_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/aux/VIHCDBD8/A3144982890B1187E05324F56A40A1C1/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/VIHAAN8/20221004_vihcdbd8_2493_1_1117186896
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/VIHAAN8/20221004_vihcdbd8_2493_1_1117186896 tag=TAG20221004T094135
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:11:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/aux/VIHCDBD8/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/aux/VIHCDBD8/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00013 to /u01/app/oracle/aux/VIHCDBD8/datafile/o1_mf_undotbs2_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/aux/VIHCDBD8/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/VIHAAN8/20221004_vihcdbd8_2496_1_1117189672
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/VIHAAN8/20221004_vihcdbd8_2496_1_1117189672 tag=TAG20221004T094135
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:35
Finished restore at 05-10-2022 08:50:05

datafile 1 switched to datafile copy
input datafile copy RECID=144 STAMP=1117270205 file name=/u01/app/oracle/aux/VIHCDBD8/datafile/o1_mf_system_kmtz72fj_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=145 STAMP=1117270205 file name=/u01/app/oracle/aux/VIHCDBD8/A3144982890B1187E05324F56A40A1C1/datafile/o1_mf_system_kmtykz50_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=146 STAMP=1117270206 file name=/u01/app/oracle/aux/VIHCDBD8/datafile/o1_mf_undotbs1_kmtz72jl_.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=147 STAMP=1117270206 file name=/u01/app/oracle/aux/VIHCDBD8/A3144982890B1187E05324F56A40A1C1/datafile/o1_mf_undotbs1_kmtykz5y_.dbf
datafile 13 switched to datafile copy
input datafile copy RECID=148 STAMP=1117270206 file name=/u01/app/oracle/aux/VIHCDBD8/datafile/o1_mf_undotbs2_kmtz72gq_.dbf
datafile 21 switched to datafile copy
input datafile copy RECID=149 STAMP=1117270206 file name=/u01/app/oracle/aux/VIHCDBD8/A3144982890B1187E05324F56A40A1C1/datafile/o1_mf_pdb9_und_kmtykz4l_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=150 STAMP=1117270207 file name=/u01/app/oracle/aux/VIHCDBD8/datafile/o1_mf_sysaux_kmtz72ck_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=151 STAMP=1117270207 file name=/u01/app/oracle/aux/VIHCDBD8/A3144982890B1187E05324F56A40A1C1/datafile/o1_mf_sysaux_kmtykz6w_.dbf

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('05/10/2022 07:15:40','dd/mm/yyyy hh24:mi:ss')";
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone 'PDB9' "alter database datafile
 5 online";
sql clone "alter database datafile  4 online";
sql clone 'PDB9' "alter database datafile
 8 online";
sql clone "alter database datafile  13 online";
sql clone 'PDB9' "alter database datafile
 21 online";
sql clone "alter database datafile  3 online";
sql clone 'PDB9' "alter database datafile
 6 online";
# recover and open database read only
recover clone database tablespace  "SYSTEM", "PDB9":"SYSTEM", "UNDOTBS1", "PDB9":"UNDOTBS1", "UNDOTBS2", "PDB9":"PDB9_UNDOTBS1", "SYSAUX", "PDB9":"SYSAUX";
sql clone 'alter database open read only';
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  1 online

sql statement: alter database datafile  5 online

sql statement: alter database datafile  4 online

sql statement: alter database datafile  8 online

sql statement: alter database datafile  13 online

sql statement: alter database datafile  21 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  6 online

Starting recover at 05-10-2022 08:50:14
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 2964 is already on disk as file +DATA/VIHCDBD8/ARCHIVELOG/2022_10_05/thread_1_seq_2964.4150.1117264719
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=2953
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=2954
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=2955
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=2956
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=2957
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=2958
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=2959
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=2960
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=2961
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=2962
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=2963
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/VIHAAN8/20221005_vihcdbd8_2503_1_1117264491
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/VIHAAN8/20221005_vihcdbd8_2503_1_1117264491 tag=TAG20221005T071451
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55
archived log file name=/u01/app/oracle/aux/1_2953_1085481127.dbf thread=1 sequence=2953
archived log file name=/u01/app/oracle/aux/1_2954_1085481127.dbf thread=1 sequence=2954
archived log file name=/u01/app/oracle/aux/1_2955_1085481127.dbf thread=1 sequence=2955
archived log file name=/u01/app/oracle/aux/1_2956_1085481127.dbf thread=1 sequence=2956
archived log file name=/u01/app/oracle/aux/1_2957_1085481127.dbf thread=1 sequence=2957
archived log file name=/u01/app/oracle/aux/1_2958_1085481127.dbf thread=1 sequence=2958
archived log file name=/u01/app/oracle/aux/1_2959_1085481127.dbf thread=1 sequence=2959
archived log file name=/u01/app/oracle/aux/1_2960_1085481127.dbf thread=1 sequence=2960
archived log file name=/u01/app/oracle/aux/1_2961_1085481127.dbf thread=1 sequence=2961
archived log file name=/u01/app/oracle/aux/1_2962_1085481127.dbf thread=1 sequence=2962
archived log file name=/u01/app/oracle/aux/1_2963_1085481127.dbf thread=1 sequence=2963
archived log file name=+DATA/VIHCDBD8/ARCHIVELOG/2022_10_05/thread_1_seq_2964.4150.1117264719 thread=1 sequence=2964
media recovery complete, elapsed time: 00:06:22
Finished recover at 05-10-2022 08:57:42

sql statement: alter database open read only

contents of Memory Script:
{
sql clone 'alter pluggable database  PDB9 open read only';
}
executing Memory Script

sql statement: alter pluggable database  PDB9 open read only

contents of Memory Script:
{
   sql clone "create spfile from memory";
   shutdown clone immediate;
   startup clone nomount;
   sql clone "alter system set  control_files =
  ''/u01/app/oracle/aux/VIHCDBD8/controlfile/o1_mf_kmtykgqc_.ctl'' comment=
 ''RMAN set'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
# mount database
sql clone 'alter database mount clone database';
}
executing Memory Script

sql statement: create spfile from memory

database closed
database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area   22749904896 bytes

Fixed Size                    19421832 bytes
Variable Size               3019900280 bytes
Database Buffers           19662897152 bytes
Redo Buffers                  47685632 bytes

sql statement: alter system set  control_files =   ''/u01/app/oracle/aux/VIHCDBD8/controlfile/o1_mf_kmtykgqc_.ctl'' comment= ''RMAN set'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area   22749904896 bytes

Fixed Size                    19421832 bytes
Variable Size               3019900280 bytes
Database Buffers           19662897152 bytes
Redo Buffers                  47685632 bytes

sql statement: alter database mount clone database

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('05/10/2022 07:15:40','dd/mm/yyyy hh24:mi:ss')";
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile  73 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  73;

switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 05-10-2022 08:59:39
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=190 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00073 to /u01/app/oracle/aux/WLAN_PITR_PDB9_VIHCDBD8/A3144982890B1187E05324F56A40A1C1/datafile/o1_mf_testx_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/VIHAAN8/20221004_vihcdbd8_2493_1_1117186896
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/VIHAAN8/20221004_vihcdbd8_2493_1_1117186896 tag=TAG20221004T094135
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 05-10-2022 09:00:07

datafile 73 switched to datafile copy
input datafile copy RECID=153 STAMP=1117270808 file name=/u01/app/oracle/aux/WLAN_PITR_PDB9_VIHCDBD8/A3144982890B1187E05324F56A40A1C1/datafile/o1_mf_testx_kmtzxyso_.dbf

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('05/10/2022 07:15:40','dd/mm/yyyy hh24:mi:ss')";
# online the datafiles restored or switched
sql clone 'PDB9' "alter database datafile
 73 online";
# recover and open resetlogs
recover clone database tablespace  "PDB9":"TESTX", "SYSTEM", "PDB9":"SYSTEM", "UNDOTBS1", "PDB9":"UNDOTBS1", "UNDOTBS2", "PDB9":"PDB9_UNDOTBS1", "SYSAUX", "PDB9":"SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  73 online

Starting recover at 05-10-2022 09:00:08
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 2964 is already on disk as file +DATA/VIHCDBD8/ARCHIVELOG/2022_10_05/thread_1_seq_2964.4150.1117264719
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=2953
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=2954
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=2955
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=2956
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=2957
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=2958
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=2959
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=2960
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=2961
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=2962
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=2963
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/VIHAAN8/20221005_vihcdbd8_2503_1_1117264491
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/VIHAAN8/20221005_vihcdbd8_2503_1_1117264491 tag=TAG20221005T071451
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:56
archived log file name=/u01/app/oracle/aux/1_2953_1085481127.dbf thread=1 sequence=2953
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/aux/1_2953_1085481127.dbf RECID=4550 STAMP=1117270831
archived log file name=/u01/app/oracle/aux/1_2954_1085481127.dbf thread=1 sequence=2954
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/aux/1_2954_1085481127.dbf RECID=4558 STAMP=1117270870
archived log file name=/u01/app/oracle/aux/1_2955_1085481127.dbf thread=1 sequence=2955
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/aux/1_2955_1085481127.dbf RECID=4555 STAMP=1117270866
archived log file name=/u01/app/oracle/aux/1_2956_1085481127.dbf thread=1 sequence=2956
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/aux/1_2956_1085481127.dbf RECID=4559 STAMP=1117270871
archived log file name=/u01/app/oracle/aux/1_2957_1085481127.dbf thread=1 sequence=2957
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/aux/1_2957_1085481127.dbf RECID=4557 STAMP=1117270869
archived log file name=/u01/app/oracle/aux/1_2958_1085481127.dbf thread=1 sequence=2958
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/aux/1_2958_1085481127.dbf RECID=4556 STAMP=1117270869
archived log file name=/u01/app/oracle/aux/1_2959_1085481127.dbf thread=1 sequence=2959
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/aux/1_2959_1085481127.dbf RECID=4549 STAMP=1117270828
archived log file name=/u01/app/oracle/aux/1_2960_1085481127.dbf thread=1 sequence=2960
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/aux/1_2960_1085481127.dbf RECID=4554 STAMP=1117270865
archived log file name=/u01/app/oracle/aux/1_2961_1085481127.dbf thread=1 sequence=2961
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/aux/1_2961_1085481127.dbf RECID=4551 STAMP=1117270838
archived log file name=/u01/app/oracle/aux/1_2962_1085481127.dbf thread=1 sequence=2962
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/aux/1_2962_1085481127.dbf RECID=4553 STAMP=1117270839
archived log file name=/u01/app/oracle/aux/1_2963_1085481127.dbf thread=1 sequence=2963
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/aux/1_2963_1085481127.dbf RECID=4552 STAMP=1117270838
archived log file name=+DATA/VIHCDBD8/ARCHIVELOG/2022_10_05/thread_1_seq_2964.4150.1117264719 thread=1 sequence=2964
media recovery complete, elapsed time: 00:00:05
Finished recover at 05-10-2022 09:01:23

database opened

contents of Memory Script:
{
sql clone 'alter pluggable database  PDB9 open';
}
executing Memory Script

sql statement: alter pluggable database  PDB9 open

contents of Memory Script:
{
# create directory for datapump import
sql 'PDB9' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/u01/app/oracle/aux''";
# create directory for datapump export
sql clone 'PDB9' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/u01/app/oracle/aux''";
}
executing Memory Script

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/aux''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/aux''

Performing export of tables...
   EXPDP> Starting "SYS"."TSPITR_EXP_wlan_Aguy":
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   EXPDP> . . exported "VIHAANX"."TEST_RESTORE05"                  5.062 KB       1 rows
   EXPDP> Master table "SYS"."TSPITR_EXP_wlan_Aguy" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_wlan_Aguy is:
   EXPDP>   /u01/app/oracle/aux/tspitr_wlan_64020.dmp
   EXPDP> Job "SYS"."TSPITR_EXP_wlan_Aguy" successfully completed at Wed Oct 5 09:05:47 2022 elapsed 0 00:00:45
Export completed


contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script

Oracle instance shut down

Performing import of tables...
   IMPDP> Master table "SYS"."TSPITR_IMP_wlan_viim" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_wlan_viim":
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   IMPDP> . . imported "VIHAANX"."TEST_RESTORE55"                  5.062 KB       1 rows
   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
   IMPDP> Job "SYS"."TSPITR_IMP_wlan_viim" successfully completed at Wed Oct 5 09:06:08 2022 elapsed 0 00:00:11
Import completed


Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/app/oracle/aux/VIHCDBD8/A3144982890B1187E05324F56A40A1C1/datafile/o1_mf_temp_kmtztktj_.tmp deleted
auxiliary instance file /u01/app/oracle/aux/VIHCDBD8/datafile/o1_mf_temp_kmtzt8dm_.tmp deleted
auxiliary instance file /u01/app/oracle/aux/VIHCDBD8/datafile/o1_mf_temp_tes_kmtzt8np_.tmp deleted
auxiliary instance file /u01/app/oracle/aux/WLAN_PITR_PDB9_VIHCDBD8/onlinelog/o1_mf_6_kmv0143r_.log deleted
auxiliary instance file /u01/app/oracle/aux/WLAN_PITR_PDB9_VIHCDBD8/onlinelog/o1_mf_5_kmv01430_.log deleted
auxiliary instance file /u01/app/oracle/aux/WLAN_PITR_PDB9_VIHCDBD8/onlinelog/o1_mf_4_kmv0142y_.log deleted
auxiliary instance file /u01/app/oracle/aux/WLAN_PITR_PDB9_VIHCDBD8/onlinelog/o1_mf_1_kmv01422_.log deleted
auxiliary instance file /u01/app/oracle/aux/WLAN_PITR_PDB9_VIHCDBD8/A3144982890B1187E05324F56A40A1C1/datafile/o1_mf_testx_kmtzxyso_.dbf deleted
auxiliary instance file /u01/app/oracle/aux/VIHCDBD8/A3144982890B1187E05324F56A40A1C1/datafile/o1_mf_sysaux_kmtykz6w_.dbf deleted
auxiliary instance file /u01/app/oracle/aux/VIHCDBD8/datafile/o1_mf_sysaux_kmtz72ck_.dbf deleted
auxiliary instance file /u01/app/oracle/aux/VIHCDBD8/A3144982890B1187E05324F56A40A1C1/datafile/o1_mf_pdb9_und_kmtykz4l_.dbf deleted
auxiliary instance file /u01/app/oracle/aux/VIHCDBD8/datafile/o1_mf_undotbs2_kmtz72gq_.dbf deleted
auxiliary instance file /u01/app/oracle/aux/VIHCDBD8/A3144982890B1187E05324F56A40A1C1/datafile/o1_mf_undotbs1_kmtykz5y_.dbf deleted
auxiliary instance file /u01/app/oracle/aux/VIHCDBD8/datafile/o1_mf_undotbs1_kmtz72jl_.dbf deleted
auxiliary instance file /u01/app/oracle/aux/VIHCDBD8/A3144982890B1187E05324F56A40A1C1/datafile/o1_mf_system_kmtykz50_.dbf deleted
auxiliary instance file /u01/app/oracle/aux/VIHCDBD8/datafile/o1_mf_system_kmtz72fj_.dbf deleted
auxiliary instance file /u01/app/oracle/aux/VIHCDBD8/controlfile/o1_mf_kmtykgqc_.ctl deleted
auxiliary instance file tspitr_wlan_64020.dmp deleted
Finished recover at 05-10-2022 09:06:11




====

alter session set container=PDB9 ;

Session altered.


 select count(*) from VIHAANX.TEST_RESTORE55 ;

  COUNT(*)
----------
         1