Friday, 5 August 2011

Oracle Unix Pid to oracle sql..

http://anuj-singh.blogspot.com/2017/08/oracle-process-info.html

Unix pid to sql
Unix pid to oraclesql
pid to sql

------

set serveroutput on size 50000
set echo off feed off veri off
accept 1 prompt 'Enter Unix process id: '


DECLARE
v_sid number;
s sys.v_$session%ROWTYPE;
p sys.v_$process%ROWTYPE;
BEGIN
begin
select sid into v_sid
from sys.v_$process p, sys.v_$session s
where p.addr = s.paddr
and (p.spid = &&1
or s.process = '&&1');
exception
when no_data_found then
dbms_output.put_line('Unable to find process id &&1!!!');
return;
when others then
dbms_output.put_line(sqlerrm);
return;
end;

select * into s from sys.v_$session where sid = v_sid;
select * into p from sys.v_$process where addr = s.paddr;

dbms_output.put_line('=====================================================================');
dbms_output.put_line('SID/Serial : '|| s.sid||','||s.serial#);
dbms_output.put_line('Foreground : '|| 'PID: '||s.process||' - '||s.program);
dbms_output.put_line('Shadow : '|| 'PID: '||p.spid||' - '||p.program);
dbms_output.put_line('Terminal : '|| s.terminal || '/ ' || p.terminal);
dbms_output.put_line('OS User : '|| s.osuser||' on '||s.machine);
dbms_output.put_line('Ora User : '|| s.username);
dbms_output.put_line('Status Flags: '|| s.status||' '||s.server||' '||s.type);
dbms_output.put_line('Tran Active : '|| nvl(s.taddr, 'NONE'));
dbms_output.put_line('Login Time : '|| to_char(s.logon_time, 'Dy HH24:MI:SS'));
dbms_output.put_line('Last Call : '|| to_char(sysdate-(s.last_call_et/60/60/24), 'Dy HH24:MI:SS') || ' - ' || to_char
(s.last_call_et/60, '990.0') || ' min');
dbms_output.put_line('Lock/ Latch : '|| nvl(s.lockwait, 'NONE')||'/ '||nvl(p.latchwait, 'NONE'));
dbms_output.put_line('Latch Spin : '|| nvl(p.latchspin, 'NONE'));
dbms_output.put_line('Current SQL statement:');
for c1 in ( select * from sys.v_$sqltext
where HASH_VALUE = s.sql_hash_value order by piece) loop
dbms_output.put_line(chr(9)||c1.sql_text);
end loop;
dbms_output.put_line('Previous SQL statement:');
for c1 in ( select * from sys.v_$sqltext
where HASH_VALUE = s.prev_hash_value order by piece) loop
dbms_output.put_line(chr(9)||c1.sql_text);
end loop;
dbms_output.put_line('Session Waits:');
for c1 in ( select * from sys.v_$session_wait where sid = s.sid) loop
dbms_output.put_line(chr(9)||c1.state||': '||c1.event);
end loop;
-- dbms_output.put_line('Connect Info:');
-- for c1 in ( select * from sys.v_$session_connect_info where sid = s.sid) loop
-- dbms_output.put_line(chr(9)||': '||c1.network_service_banner);
-- end loop;
dbms_output.put_line('Locks:');
for c1 in ( select
decode(l.type,
-- Long locks
'TM', 'DML/DATA ENQ', 'TX', 'TRANSAC ENQ',
'UL', 'PLS USR LOCK',
-- Short locks
'BL', 'BUF HASH TBL', 'CF', 'CONTROL FILE',
'CI', 'CROSS INST F', 'DF', 'DATA FILE ',
'CU', 'CURSOR BIND ',
'DL', 'DIRECT LOAD ', 'DM', 'MOUNT/STRTUP',
'DR', 'RECO LOCK ', 'DX', 'DISTRIB TRAN',
'FS', 'FILE SET ', 'IN', 'INSTANCE NUM',
'FI', 'SGA OPN FILE',
'IR', 'INSTCE RECVR', 'IS', 'GET STATE ',
'IV', 'LIBCACHE INV', 'KK', 'LOG SW KICK ',
'LS', 'LOG SWITCH ',
'MM', 'MOUNT DEF ', 'MR', 'MEDIA RECVRY',
'PF', 'PWFILE ENQ ', 'PR', 'PROCESS STRT',
'RT', 'REDO THREAD ', 'SC', 'SCN ENQ ',
'RW', 'ROW WAIT ',
'SM', 'SMON LOCK ', 'SN', 'SEQNO INSTCE',
'SQ', 'SEQNO ENQ ', 'ST', 'SPACE TRANSC',
'SV', 'SEQNO VALUE ', 'TA', 'GENERIC ENQ ',
'TD', 'DLL ENQ ', 'TE', 'EXTEND SEG ',
'TS', 'TEMP SEGMENT', 'TT', 'TEMP TABLE ',
'UN', 'USER NAME ', 'WL', 'WRITE REDO ',
'TYPE='||l.type) type,
decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.lmode) ) lmode,
decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.request) ) lrequest,
decode(l.type, 'MR', o.name,
'TD', o.name,
'TM', o.name,
'RW', 'FILE#='||substr(l.id1,1,3)||
' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2,
'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2,
'WL', 'REDO LOG FILE#='||l.id1,
'RT', 'THREAD='||l.id1,
'TS', decode(l.id2, 0, 'ENQUEUE', 'NEW BLOCK ALLOCATION'),
'ID1='||l.id1||' ID2='||l.id2) objname
from sys.v_$lock l, sys.obj$ o
where sid = s.sid
and l.id1 = o.obj#(+) ) loop
dbms_output.put_line(chr(9)||c1.type||' H: '||c1.lmode||' R: '||c1.lrequest||' - '||c1.objname);
end loop;
dbms_output.put_line('=====================================================================');
END;
/

undef 1

/*

FINA: ORA7> @\tmp\u
Enter Unix process id: 13263
=====================================================================
SID/Serial : 67,558
Foreground : PID: 13262 - runform30@bel2 (TNS interface)
Shadow : PID: 13263 - oracle@bel2 (TNS V1-V2)
Terminal : ttyq5/ ttyq5
OS User : e8677 on bel2
Ora User : ADMN
Status Flags: INACTIVE DEDICATED USER
Tran Active : NONE
Login Time : Mon 14:15:14
Last Call : Mon 14:38:05 - 1.2 min
Lock/ Latch : NONE/ NONE
Latch Spin : NONE
Current SQL statement:
SELECT TO_CHAR(DECODE(LENGTH(:b1),8,TO_DATE(:b2,'DDMMRRRR'),9,TO
_DATE(:b3,'DDMONRRRR'),10,TO_DATE(:b4,'DD/MM/RRRR'),11,TO_DATE(:
b5,'DD-MON-RRRR')),'DD-MON-RRRR') FROM DUAL
Previous SQL statement:
Session Waits:
WAITING: SQL*Net message from client
Locks:
=========================

Oracle 10g +

set serveroutput on size 50000 echo off feed off veri off
accept 1 prompt 'Enter Unix process id: '

DECLARE
v_sid number;
s v$session%ROWTYPE;
p v$process%ROWTYPE;
BEGIN
begin
select sid into v_sid from v$process p, v$session s
where p.addr = s.paddr
and (p.spid = &&1
or s.process = '&&1');
exception
when no_data_found then
dbms_output.put_line('Unable to find process id &&1!!!');
return;
when others then
dbms_output.put_line(sqlerrm);
return;
end;

select * into s from v$session where sid = v_sid;
select * into p from v$process where addr = s.paddr;

dbms_output.put_line('=====================================================================');
dbms_output.put_line('SID/Serial   : '|| s.sid||','||s.serial#);
dbms_output.put_line('Foreground   : '|| 'PID: '||s.process||' - '||s.program);
dbms_output.put_line('Shadow       : '|| 'PID: '||p.spid||' - '||p.program);
dbms_output.put_line('Terminal     : '|| s.terminal || '/ ' || p.terminal);
dbms_output.put_line('OS User      : '|| s.osuser||' on '||s.machine);
dbms_output.put_line('Ora User     : '|| s.username);
dbms_output.put_line('Status Flags : '|| s.status||' '||s.server||' '||s.type);
dbms_output.put_line('Tran Active  : '|| nvl(s.taddr, 'NONE'));
dbms_output.put_line('Login Time   : '|| to_char(s.logon_time, 'Dy HH24:MI:SS'));
dbms_output.put_line('Last Call    : '|| to_char(sysdate-(s.last_call_et/60/60/24), 'Dy HH24:MI:SS') || ' - ' || to_char (s.last_call_et/60, '990.0') || ' min');
dbms_output.put_line('Lock/ Latch  : '|| nvl(s.lockwait, 'NONE')||'/ '||nvl(p.latchwait, 'NONE'));
dbms_output.put_line('Latch Spin   : '|| nvl(p.latchspin, 'NONE'));
dbms_output.put_line('Session status  : '|| s.status);
dbms_output.put_line('Session event   : '|| s.event);
dbms_output.put_line('Blocking Session: '|| s.blocking_session);
dbms_output.put_line('Sql Id       : '|| s.sql_id);
dbms_output.put_line('Prev Sql Id  : '|| s.PREV_SQL_ID);
dbms_output.put_line('Current SQL statement:');
for c1 in ( select * from v$sqltext
where HASH_VALUE = s.sql_hash_value order by piece) loop
dbms_output.put_line(chr(9)||c1.sql_text);
end loop;

dbms_output.put_line('Previous SQL statement:');
for c1 in ( select * from v$sqltext where HASH_VALUE = s.prev_hash_value order by piece) loop
dbms_output.put_line(chr(9)||c1.sql_text);
end loop;
-- dbms_output.put_line('Connect Info:');
-- for c1 in ( select * from v$session_connect_info where sid = s.sid) loop
-- dbms_output.put_line(chr(9)||': '||c1.network_service_banner);
-- end loop;

dbms_output.put_line('Locks:');
for c1 in ( select
decode(l.type,
-- Long locks
'TM', 'DML/DATA ENQ', 'TX', 'TRANSAC ENQ',
'UL', 'PLS USR LOCK',
-- Short locks
'BL', 'BUF HASH TBL', 'CF', 'CONTROL FILE',
'CI', 'CROSS INST F', 'DF', 'DATA FILE ',
'CU', 'CURSOR BIND ',
'DL', 'DIRECT LOAD ', 'DM', 'MOUNT/STRTUP',
'DR', 'RECO LOCK ', 'DX', 'DISTRIB TRAN',
'FS', 'FILE SET ', 'IN', 'INSTANCE NUM',
'FI', 'SGA OPN FILE',
'IR', 'INSTCE RECVR', 'IS', 'GET STATE ',
'IV', 'LIBCACHE INV', 'KK', 'LOG SW KICK ',
'LS', 'LOG SWITCH ',
'MM', 'MOUNT DEF ', 'MR', 'MEDIA RECVRY',
'PF', 'PWFILE ENQ ', 'PR', 'PROCESS STRT',
'RT', 'REDO THREAD ', 'SC', 'SCN ENQ ',
'RW', 'ROW WAIT ',
'SM', 'SMON LOCK ', 'SN', 'SEQNO INSTCE',
'SQ', 'SEQNO ENQ ', 'ST', 'SPACE TRANSC',
'SV', 'SEQNO VALUE ', 'TA', 'GENERIC ENQ ',
'TD', 'DLL ENQ ', 'TE', 'EXTEND SEG ',
'TS', 'TEMP SEGMENT', 'TT', 'TEMP TABLE ',
'UN', 'USER NAME ', 'WL', 'WRITE REDO ',
'TYPE='||l.type) type,
decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',4, 'S', 5, 'RSX', 6, 'X',to_char(l.lmode) ) lmode,
decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',4, 'S', 5, 'RSX', 6, 'X',to_char(l.request) ) lrequest,
decode(l.type, 'MR', o.name,
'TD', o.name,
'TM', o.name,
'RW', 'FILE#='||substr(l.id1,1,3)||' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2,'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2,
'WL', 'REDO LOG FILE#='||l.id1,
'RT', 'THREAD='||l.id1,
'TS', decode(l.id2, 0, 'ENQUEUE', 'NEW BLOCK ALLOCATION'),
'ID1='||l.id1||' ID2='||l.id2) objname
from v$lock l, obj$ o
where sid = s.sid
and l.id1 = o.obj#(+) ) loop
dbms_output.put_line(chr(9)||c1.type||' H: '||c1.lmode||' R: '||c1.lrequest||' - '||c1.objname);
end loop;

dbms_output.put_line('=====================================================================');

END;
/
undef 1

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

SID to SQL


 set serveroutput on
define inst_id=1
define sid=2650

DECLARE
 v_sid number;
 vs_cnt number;
 s sys.gv_$session%ROWTYPE;
 p sys.gv_$process%ROWTYPE;
 cursor cur_c1 is select sid from sys.gv_$process p,sys.gv_$session s where p.addr = s.paddr and sid = &SID 
 and s.inst_id='&inst_id' and p.inst_id=s.inst_id;
BEGIN
 dbms_output.put_line('=====================================================================');
 select nvl(count(sid),0) into vs_cnt from sys.gv_$process p, sys.gv_$session s where p.addr = s.paddr and sid = &SID 
 and s.inst_id='&inst_id' and p.inst_id=s.inst_id;
 open cur_c1;
 LOOP
 FETCH cur_c1 INTO v_sid;
 EXIT WHEN (cur_c1%NOTFOUND);
 select * into s from sys.gv_$session where sid = v_sid and inst_id='&inst_id';
 select * into p from sys.gv_$process where addr = s.paddr and inst_id='&inst_id';
 dbms_output.put_line('INST_ID : '|| s.inst_id);
 dbms_output.put_line('SID/Serial : '|| s.sid||','||s.serial#);
 dbms_output.put_line('Foreground : '|| 'PID: '||s.process||' - '||s.program);
 dbms_output.put_line('Shadow : '|| 'PID: '||p.spid||' - '||p.program);
 dbms_output.put_line('Terminal : '|| s.terminal || '/ ' || p.terminal);
 dbms_output.put_line('OS User : '|| s.osuser||' on '||s.machine);
 dbms_output.put_line('Ora User : '|| s.username);
 dbms_output.put_line('Details : '|| s.action||' - '||s.module);
 dbms_output.put_line('Status Flags: '|| s.status||' '||s.server||' '||s.type);
 dbms_output.put_line('Tran Active : '|| nvl(s.taddr, 'NONE'));
 dbms_output.put_line('Login Time : '|| to_char(s.logon_time, 'Dy HH24:MI:SS'));
 dbms_output.put_line('Last Call : '|| to_char(sysdate-(s.last_call_et/60/60/24), 'Dy HH24:MI:SS') || ' - ' || to_char(s.last_call_et/60, '99999990.0') || ' min');
 dbms_output.put_line('Lock/ Latch : '|| nvl(s.lockwait, 'NONE')||'/ '||nvl(p.latchwait, 'NONE'));
 dbms_output.put_line('Latch Spin : '|| nvl(p.latchspin, 'NONE'));
 dbms_output.put_line('Current SQL statement:');
 for c1 in ( select * from sys.gv_$sqltext where HASH_VALUE = s.sql_hash_value and inst_id='&inst_id' order by piece)
 loop
 dbms_output.put_line(chr(9)||c1.sql_text);
 end loop;
 dbms_output.put_line('Previous SQL statement:');
 for c1 in ( select * from sys.gv_$sqltext where HASH_VALUE = s.prev_hash_value and inst_id='&inst_id' order by piece)
 loop
 dbms_output.put_line(chr(9)||c1.sql_text);
 end loop;
 dbms_output.put_line('Session Waits:');
 for c1 in ( select * from sys.gv_$session_wait where sid = s.sid and inst_id='&inst_id')
 loop
 dbms_output.put_line(chr(9)||c1.state||': '||c1.event);
 end loop;
 dbms_output.put_line('Connect Info:');
 for c1 in ( select * from sys.gv_$session_connect_info where sid = s.sid and inst_id='&inst_id') loop
 dbms_output.put_line(chr(9)||': '||c1.network_service_banner);
 end loop;
 dbms_output.put_line('Locks:');
 for c1 in ( select /*+ RULE */ decode(l.type,
 -- Long locks
 'TM', 'DML/DATA ENQ', 'TX', 'TRANSAC ENQ',
 'UL', 'PLS USR LOCK',
 -- Short locks
 'BL', 'BUF HASH TBL', 'CF', 'CONTROL FILE',
 'CI', 'CROSS INST F', 'DF', 'DATA FILE ',
 'CU', 'CURSOR BIND ',
 'DL', 'DIRECT LOAD ', 'DM', 'MOUNT/STRTUP',
 'DR', 'RECO LOCK ', 'DX', 'DISTRIB TRAN',
 'FS', 'FILE SET ', 'IN', 'INSTANCE NUM',
 'FI', 'SGA OPN FILE',
 'IR', 'INSTCE RECVR', 'IS', 'GET STATE ',
 'IV', 'LIBCACHE INV', 'KK', 'LOG SW KICK ',
 'LS', 'LOG SWITCH ',
 'MM', 'MOUNT DEF ', 'MR', 'MEDIA RECVRY',
 'PF', 'PWFILE ENQ ', 'PR', 'PROCESS STRT',
 'RT', 'REDO THREAD ', 'SC', 'SCN ENQ ',
 'RW', 'ROW WAIT ',
 'SM', 'SMON LOCK ', 'SN', 'SEQNO INSTCE',
 'SQ', 'SEQNO ENQ ', 'ST', 'SPACE TRANSC',
 'SV', 'SEQNO VALUE ', 'TA', 'GENERIC ENQ ',
 'TD', 'DLL ENQ ', 'TE', 'EXTEND SEG ',
 'TS', 'TEMP SEGMENT', 'TT', 'TEMP TABLE ',
 'UN', 'USER NAME ', 'WL', 'WRITE REDO ',
 'TYPE='||l.type) type,
 decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
 4, 'S', 5, 'RSX', 6, 'X',
 to_char(l.lmode) ) lmode,
 decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
 4, 'S', 5, 'RSX', 6, 'X',
 to_char(l.request) ) lrequest,
 decode(l.type, 'MR', o.name,
 'TD', o.name,
 'TM', o.name,
 'RW', 'FILE#='||substr(l.id1,1,3)||
 ' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2,
 'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2,
 'WL', 'REDO LOG FILE#='||l.id1,
 'RT', 'THREAD='||l.id1,
 'TS', decode(l.id2, 0, 'ENQUEUE', 'NEW BLOCK ALLOCATION'),
 'ID1='||l.id1||' ID2='||l.id2) objname
 from sys.gv_$lock l, sys.obj$ o
 where sid = s.sid
 and inst_id='&inst_id'
 and l.id1 = o.obj#(+) )
 loop
 dbms_output.put_line(chr(9)||c1.type||' H: '||c1.lmode||' R: '||c1.lrequest||' - '||c1.objname);
 end loop;
 dbms_output.put_line('=====================================================================');
 END LOOP;

close cur_c1;
exception
 when no_data_found then
 dbms_output.put_line('Unable to find process id &&SID for the instance number '||'&inst_id'||' !!!');
 dbms_output.put_line('=====================================================================');
 return;
 when others then
 dbms_output.put_line(sqlerrm);
 return;
END;
/

with sql id


define 1=58982


set serveroutput on size 50000
DECLARE
v_sid number;
s sys.v_$session%ROWTYPE;
p sys.v_$process%ROWTYPE;
BEGIN
begin
select distinct sid into v_sid
from sys.v_$process p, sys.v_$session s
where p.addr = s.paddr
and (p.spid = &&1
or s.process = '&&1')
and rownum<2;
exception
when no_data_found then
dbms_output.put_line('Unable to find process id &&1!!!');
return;
when others then
dbms_output.put_line(sqlerrm);
return;
end;

select * into s from sys.v_$session where sid = v_sid;
select * into p from sys.v_$process where addr = s.paddr;

dbms_output.put_line('=====================================================================');
dbms_output.put_line('SID/Serial : '|| s.sid||','||s.serial#);
dbms_output.put_line('Foreground : '|| 'PID: '||s.process||' - '||s.program);
dbms_output.put_line('Sql_id/Previous: '|| 'Sql_id: '||s.SQL_ID||' - '||s.PREV_SQL_ID);
dbms_output.put_line('Shadow : '|| 'PID: '||p.spid||' - '||p.program);
dbms_output.put_line('Terminal : '|| s.terminal || '/ ' || p.terminal);
dbms_output.put_line('OS User : '|| s.osuser||' on '||s.machine);
dbms_output.put_line('Ora User : '|| s.username);
dbms_output.put_line('Status Flags: '|| s.status||' '||s.server||' '||s.type);
dbms_output.put_line('Tran Active : '|| nvl(s.taddr, 'NONE'));
dbms_output.put_line('Login Time : '|| to_char(s.logon_time, 'Dy HH24:MI:SS'));
dbms_output.put_line('Last Call : '|| to_char(sysdate-(s.last_call_et/60/60/24), 'Dy HH24:MI:SS') || ' - ' || to_char
(s.last_call_et/60, '990.0') || ' min');
dbms_output.put_line('Lock/ Latch : '|| nvl(s.lockwait, 'NONE')||'/ '||nvl(p.latchwait, 'NONE'));
dbms_output.put_line('Latch Spin : '|| nvl(p.latchspin, 'NONE'));
dbms_output.put_line('Current SQL statement:');
for c1 in ( select * from sys.v_$sqltext
where HASH_VALUE = s.sql_hash_value order by piece) loop
dbms_output.put_line(chr(9)||c1.sql_text);
end loop;
dbms_output.put_line('Previous SQL statement:');
for c1 in ( select * from sys.v_$sqltext
where HASH_VALUE = s.prev_hash_value order by piece) loop
dbms_output.put_line(chr(9)||c1.sql_text);
end loop;
dbms_output.put_line('Session Waits:');
for c1 in ( select * from sys.v_$session_wait where sid = s.sid) loop
dbms_output.put_line(chr(9)||c1.state||': '||c1.event);
end loop;
-- dbms_output.put_line('Connect Info:');
-- for c1 in ( select * from sys.v_$session_connect_info where sid = s.sid) loop
-- dbms_output.put_line(chr(9)||': '||c1.network_service_banner);
-- end loop;
dbms_output.put_line('Locks:');
for c1 in ( select
decode(l.type,
-- Long locks
'TM', 'DML/DATA ENQ', 'TX', 'TRANSAC ENQ',
'UL', 'PLS USR LOCK',
-- Short locks
'BL', 'BUF HASH TBL', 'CF', 'CONTROL FILE',
'CI', 'CROSS INST F', 'DF', 'DATA FILE ',
'CU', 'CURSOR BIND ',
'DL', 'DIRECT LOAD ', 'DM', 'MOUNT/STRTUP',
'DR', 'RECO LOCK ', 'DX', 'DISTRIB TRAN',
'FS', 'FILE SET ', 'IN', 'INSTANCE NUM',
'FI', 'SGA OPN FILE',
'IR', 'INSTCE RECVR', 'IS', 'GET STATE ',
'IV', 'LIBCACHE INV', 'KK', 'LOG SW KICK ',
'LS', 'LOG SWITCH ',
'MM', 'MOUNT DEF ', 'MR', 'MEDIA RECVRY',
'PF', 'PWFILE ENQ ', 'PR', 'PROCESS STRT',
'RT', 'REDO THREAD ', 'SC', 'SCN ENQ ',
'RW', 'ROW WAIT ',
'SM', 'SMON LOCK ', 'SN', 'SEQNO INSTCE',
'SQ', 'SEQNO ENQ ', 'ST', 'SPACE TRANSC',
'SV', 'SEQNO VALUE ', 'TA', 'GENERIC ENQ ',
'TD', 'DLL ENQ ', 'TE', 'EXTEND SEG ',
'TS', 'TEMP SEGMENT', 'TT', 'TEMP TABLE ',
'UN', 'USER NAME ', 'WL', 'WRITE REDO ',
'TYPE='||l.type) type,
decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.lmode) ) lmode,
decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.request) ) lrequest,
decode(l.type, 'MR', o.name,
'TD', o.name,
'TM', o.name,
'RW', 'FILE#='||substr(l.id1,1,3)||
' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2,
'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2,
'WL', 'REDO LOG FILE#='||l.id1,
'RT', 'THREAD='||l.id1,
'TS', decode(l.id2, 0, 'ENQUEUE', 'NEW BLOCK ALLOCATION'),
'ID1='||l.id1||' ID2='||l.id2) objname
from sys.v_$lock l, sys.obj$ o
where sid = s.sid
and l.id1 = o.obj#(+) ) loop
dbms_output.put_line(chr(9)||c1.type||' H: '||c1.lmode||' R: '||c1.lrequest||' - '||c1.objname);
end loop;
dbms_output.put_line('=====================================================================');
END;
/

undef 1

====


set linesize 500 pagesize 300
col SESSION  for a50
SELECT to_char(ssn.sid, '9999') || ' - ' || nvl(ssn.username, nvl(bgp.name, 'background')) ||
nvl(lower(ssn.machine), ins.host_name) "SESSION",
to_char(prc.spid, '999999999') "PID/THREAD",
to_char((se1.value/1024)/1024, '999G999G990D00') || ' MB' " CURRENT SIZE",
to_char((se2.value/1024)/1024, '999G999G990D00') || ' MB' " MAXIMUM SIZE",ssn.sql_id,prev_sql_id
FROM v$sesstat se1, v$sesstat se2, v$session ssn, v$bgprocess bgp, v$process prc,
v$instance ins, v$statname stat1, v$statname stat2
WHERE se1.statistic# = stat1.statistic# and stat1.name = 'session pga memory'
AND se2.statistic# = stat2.statistic# and stat2.name = 'session pga memory max'
AND se1.sid = ssn.sid
AND se2.sid = ssn.sid
AND ssn.paddr = bgp.paddr
AND ssn.paddr = prc.addr (+)
order by 3 desc;



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

 cat pid.sql

set serveroutput on size 100000
set echo off feed off veri off
accept 1 prompt 'Enter Unix process id: '

DECLARE
    v_found BOOLEAN := FALSE;
BEGIN
    FOR rec IN (
        SELECT s.sid
          FROM sys.v_$process p
          JOIN sys.v_$session s ON p.addr = s.paddr
         WHERE p.spid = '&&1' OR s.process = '&&1'
         ORDER BY CASE WHEN p.spid = '&&1' THEN 1 ELSE 2 END
    ) LOOP
        v_found := TRUE;

        DECLARE
            s sys.v_$session%ROWTYPE;
            p sys.v_$process%ROWTYPE;
        BEGIN
            SELECT * INTO s FROM sys.v_$session WHERE sid = rec.sid;
            SELECT * INTO p FROM sys.v_$process WHERE addr = s.paddr;

            dbms_output.put_line('=====================================================================');
            dbms_output.put_line('SID/Serial   : ' || s.sid || ',' || s.serial#);
            dbms_output.put_line('Foreground   : ' || 'PID: ' || s.process || ' - ' || s.program);
            dbms_output.put_line('Shadow       : ' || 'PID: ' || p.spid || ' - ' || p.program);
            dbms_output.put_line('Terminal     : ' || s.terminal || '/ ' || p.terminal);
            dbms_output.put_line('OS User      : ' || s.osuser || ' on ' || s.machine);
            dbms_output.put_line('Ora User     : ' || s.username);
            dbms_output.put_line('Status Flags : ' || s.status || ' ' || s.server || ' ' || s.type);
            dbms_output.put_line('Tran Active  : ' || NVL(s.taddr, 'NONE'));
            dbms_output.put_line('Login Time   : ' || TO_CHAR(s.logon_time, 'Dy HH24:MI:SS'));
            dbms_output.put_line('Last Call    : ' || TO_CHAR(SYSDATE - (s.last_call_et / 86400), 'Dy HH24:MI:SS') || ' - ' || TO_CHAR(s.last_call_et / 60, '990.0') || ' min');
            dbms_output.put_line('Lock/ Latch  : ' || NVL(s.lockwait, 'NONE') || '/ ' || NVL(p.latchwait, 'NONE'));
            dbms_output.put_line('Latch Spin   : ' || NVL(p.latchspin, 'NONE'));

            dbms_output.put_line('Current SQL statement:');
            FOR c1 IN (SELECT * FROM sys.v_$sqltext WHERE hash_value = s.sql_hash_value ORDER BY piece) LOOP
                dbms_output.put_line(CHR(9) || c1.sql_text);
            END LOOP;

            dbms_output.put_line('Previous SQL statement:');
            FOR c1 IN (SELECT * FROM sys.v_$sqltext WHERE hash_value = s.prev_hash_value ORDER BY piece) LOOP
                dbms_output.put_line(CHR(9) || c1.sql_text);
            END LOOP;

            dbms_output.put_line('Session Waits:');
            FOR c1 IN (SELECT * FROM sys.v_$session_wait WHERE sid = s.sid) LOOP
                dbms_output.put_line(CHR(9) || c1.state || ': ' || c1.event);
            END LOOP;

            dbms_output.put_line('Locks:');
            FOR c1 IN (
                SELECT DECODE(l.type,
                              'TM', 'DML/DATA ENQ', 'TX', 'TRANSAC ENQ',
                              'UL', 'PLS USR LOCK',
                              'BL', 'BUF HASH TBL', 'CF', 'CONTROL FILE',
                              'CI', 'CROSS INST F', 'DF', 'DATA FILE ',
                              'CU', 'CURSOR BIND ', 'DL', 'DIRECT LOAD ',
                              'DM', 'MOUNT/STRTUP', 'DR', 'RECO LOCK ',
                              'DX', 'DISTRIB TRAN', 'FS', 'FILE SET ',
                              'IN', 'INSTANCE NUM', 'FI', 'SGA OPN FILE',
                              'IR', 'INSTCE RECVR', 'IS', 'GET STATE ',
                              'IV', 'LIBCACHE INV', 'KK', 'LOG SW KICK ',
                              'LS', 'LOG SWITCH ',  'MM', 'MOUNT DEF ',
                              'MR', 'MEDIA RECVRY', 'PF', 'PWFILE ENQ ',
                              'PR', 'PROCESS STRT', 'RT', 'REDO THREAD ',
                              'SC', 'SCN ENQ ',     'RW', 'ROW WAIT ',
                              'SM', 'SMON LOCK ',   'SN', 'SEQNO INSTCE',
                              'SQ', 'SEQNO ENQ ',   'ST', 'SPACE TRANSC',
                              'SV', 'SEQNO VALUE ', 'TA', 'GENERIC ENQ ',
                              'TD', 'DLL ENQ ',     'TE', 'EXTEND SEG ',
                              'TS', 'TEMP SEGMENT', 'TT', 'TEMP TABLE ',
                              'UN', 'USER NAME ',   'WL', 'WRITE REDO ',
                              'TYPE=' || l.type) AS type,
                       DECODE(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX', 4, 'S', 5, 'RSX', 6, 'X', TO_CHAR(l.lmode)) AS lmode,
                       DECODE(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX', 4, 'S', 5, 'RSX', 6, 'X', TO_CHAR(l.request)) AS lrequest,
                       DECODE(l.type,
                              'MR', o.name,
                              'TD', o.name,
                              'TM', o.name,
                              'RW', 'FILE#=' || SUBSTR(l.id1, 1, 3) || ' BLOCK#=' || SUBSTR(l.id1, 4, 5) || ' ROW=' || l.id2,
                              'TX', 'RS+SLOT#' || l.id1 || ' WRP#' || l.id2,
                              'WL', 'REDO LOG FILE#=' || l.id1,
                              'RT', 'THREAD=' || l.id1,
                              'TS', DECODE(l.id2, 0, 'ENQUEUE', 'NEW BLOCK ALLOCATION'),
                              'ID1=' || l.id1 || ' ID2=' || l.id2) AS objname
                  FROM sys.v_$lock l
                  LEFT JOIN sys.obj$ o ON l.id1 = o.obj#
                 WHERE l.sid = s.sid
            ) LOOP
                dbms_output.put_line(CHR(9) || c1.type || ' H: ' || c1.lmode || ' R: ' || c1.lrequest || ' - ' || c1.objname);
            END LOOP;
            dbms_output.put_line('=====================================================================');
        END;
    END LOOP;

    IF NOT v_found THEN
        dbms_output.put_line('Unable to find process id &&1!!!');
    END IF;
END;
/

undef 1

Wednesday, 3 August 2011

Oracle adrci purge

adrci purge

7days

1hr=60 minutes
1day = 24 hr
1 week =7 days

select 60*24*7 week_to_min from dual;

60*24*7
----------
10080 <<<------- min

oracle_sid=aptdb

adrci> set home aptdb
adrci> purge -age 10080 -type ALERT
adrci> purge -age 10080 -type TRACE
adrci> purge -age 10080 -type incident
adrci> purge -age 10080 -type hm
adrci> purge -age 10080 -type utscdmp
adrci> purge -age 10080 -type cdump

to set auto purge

select 168/7 from dual;

168/7
----------
24 ----- hr

168 is hr

adrci>set control (SHORTP_POLICY = 168) now set to 7 days
adrci>set control (LONGP_POLICY = 168 now set to 7 days

Oracle 11g adrci from prompt

Oracle 11g alert through adrci

Oracle 11g adrci

# adrci.cmd <<<<<
--------------------------------

# ADRCI script to find alert log errors
SPOOL /tmp/alert_log_errors.txt
ECHO "ALERT LOG ERRORS:"; SET HOME diag/rdbms/aptdb/aptdb; SHOW ALERT -TERM -P "MESSAGE_TEXT LIKE '%ORA-%'"
SPOOL OFF

---------------------------------

$adrci -script=adrci.cmd

-bash-3.2$ ls -ltr /tmp/alert_log_errors.txt
-rw-r----- 1 oracle dba 9766 Aug 3 14:50 /tmp/alert_log_errors.txt

What is a Oracle thread ?

Oracle thread
What is a Oracle threads ?

set of redo log files calls thread and redo written by an instance is called a thread of redo .
in case of RAC each instance mush have own threads ( redo logs ).

V$THREAD displays thread information from the control file.

col INSTANCE format a20
SELECT thread#, instance, status FROM v$thread

THREAD# INSTANCE STATUS
---------- -------------------- ------
1 aptdb OPEN

select thread#, sequence#, status from v$log;

THREAD# SEQUENCE# STATUS
---------- ---------- ----------------
1 343 CURRENT
1 341 INACTIVE
1 342 INACTIVE

select thread#, status, enabled, sequence#,checkpoint_time from v$thread ;

THREAD# STATUS ENABLED SEQUENCE# CHECKPOIN
---------- ------ -------- ---------- ---------
1 OPEN PUBLIC 343 03-AUG-11

to disable unwanted threads in case RAC to single instance

ALTER DATABASE DISABLE THREAD 2;
ALTER DATABASE DISABLE THREAD 3;

SELECT thread#, first_change#,TO_CHAR(first_time,'MM-DD-YY HH12:MIPM'),next_change# FROM v$log_history;

THREAD# FIRST_CHANGE# TO_CHAR(FIRST_TI NEXT_CHANGE#
---------- ------------- ---------------- ------------
1 777615 06-01-11 03:26PM 797997
1 797997 06-01-11 03:27PM 804296
1 804296 06-01-11 03:27PM 807110
1 807110 06-01-11 03:28PM 813496
1 813496 06-01-11 03:29PM 821860
1 821860 06-01-11 03:30PM 833008
1 833008 06-01-11 03:31PM 839603
1 839603 06-01-11 03:32PM 852610
1 852610 06-01-11 03:33PM 864323
1 864323 06-01-11 03:34PM 877186
1 877186 06-01-11 03:35PM 889533
1 889533 06-01-11 03:36PM 893633
1 893633 06-01-11 03:38PM 898156
1 898156 06-01-11 03:40PM 902166
1 902166 06-01-11 03:42PM 912259

select item,units,sofar from v$recovery_progress
where start_time = ( select max(start_time) from v$recovery_progress)
and item = 'Checkpoint Time per Log' ;

select THREAD#,GROUPS,CURRENT_GROUP#,SEQUENCE#,CHECKPOINT_CHANGE#,ENABLE_CHANGE#,DISABLE_CHANGE# LAST_REDO_SEQUENCE#,TO_CHAR(LAST_REDO_TIME,'MM-DD-YY HH12:MIPM') last_redo , to_char(OPEN_TIME , 'MM-DD-YY HH12:MIPM') open_time from v$thread ;

THREAD# GROUPS CURRENT_GROUP# SEQUENCE# CHECKPOINT_CHANGE# ENABLE_CHANGE# LAST_REDO_SEQUENCE# LAST_REDO OPEN_TIME
---------- ---------- -------------- ---------- ------------------ -------------- ------------------- ---------------- ----------------
1 3 1 343 5847523 1 0 08-03-11 11:52AM 07-11-11 02:05PM

Tuesday, 2 August 2011

Oracle flashback info

Oracle Oracle flashback info  ...


Oracle flashback Hourly info http://anuj-singh.blogspot.com/2023/     Oracle flashback Hourly info
https://anuj-singh.blogspot.com/search?q=Oracle+flashback+Hourly+info

How to create, use and drop a Guaranteed restore point in a Data Guard configuration (Doc ID 2338328.1)

ALTER SYSTEM SET db_recovery_file_dest_size =100G SCOPE=BOTH;  


set linesize 300
COL name  FORMAT A50  HEADING 'Parameter'
COL value FORMAT A32  HEADING 'Setting'
SELECT decode(name,'db_flashback_retention_target','db_flashback_retention_target_In_Min->',name) name ,value FROM v$parameter 
WHERE NAME LIKE '%flash%' OR NAME LIKE '%recovery%' or name like 'log_archive_dest_1'
ORDER BY NAME;

OR 

prompt -- ----------------------------------------------------------------------- ---
prompt -- Flash Recovery Area ---
prompt -- ----------------------------------------------------------------------- ---
prompt
set heading off
select ' db_recovery_file_dest '||value from V$PARAMETER where name='db_recovery_file_dest'
union
select ' db_recovery_file_dest_size (G) '||to_char(value/1024/1024/1024) from V$PARAMETER where name='db_recovery_file_dest_size'
union
select ' db_flashback_retention_target (minutes) '||value||' (hours : '||value/60||')' from V$PARAMETER where name='db_flashback_retention_target'
union
select ' Flashback size estimated (G) '||to_char(round(ESTIMATED_FLASHBACK_SIZE/1024/1024/1024)) From V$FLASHBACK_DATABASE_LOG
; 


set line 300
select FILE_TYPE,PERCENT_SPACE_RECLAIMABLE,NUMBER_OF_FILES,PERCENT_SPACE_USED,
case when ((100 - PERCENT_SPACE_USED) <10 .00="" then="">90.00)% full ##'
           else 'Good'
           end as "ATTENTION-FLASHBACK LOG"
           from v$flash_recovery_area_usage
where 1=1 
-- and FILE_TYPE='FLASHBACK LOG'
;


set linesize 300
set numf 999999999999999
alter session set nls_date_format='dd-mm-yyyy hh24:mi';
col FLASHBACK_SIZE_H for a18
col ESTIMATED_FLASHBACK_SIZE_H for a22
select 
 OLDEST_FLASHBACK_SCN   ,   
 OLDEST_FLASHBACK_TIME   ,  
 RETENTION_TARGET  ,        
 FLASHBACK_SIZE ,  
dbms_xplan.FORMAT_SIZE(  FLASHBACK_SIZE) FLASHBACK_SIZE_H    ,   
 ESTIMATED_FLASHBACK_SIZE , 
dbms_xplan.FORMAT_SIZE(ESTIMATED_FLASHBACK_SIZE) ESTIMATED_FLASHBACK_SIZE_H,
 CON_ID                    
from 
V$FLASHBACK_DATABASE_LOG
;



OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK RETENTION_TARGET   FLASHBACK_SIZE FLASHBACK_SIZE_H   ESTIMATED_FLASHBACK_SIZE ESTIMATED_FLASHBAC CON_ID
-------------------- ---------------- ---------------- ---------------- ------------------ ------------------------ ------------------ ----------------
       1234726450723 08-10-2025 04:24             1440     481036337152 448G                           164428210176 153G                   0




set linesize 300
col FILE_TYPE for a20
col PERCENT_SPACE_USED for 999.99 heading 'PERCENT_SPACE_USED%'   
SELECT NVL(FRAU.FILE_TYPE, 'Total:') FILE_TYPE,
       SUM(ROUND(FRAU.PERCENT_SPACE_USED / 100 * RFD.SPACE_LIMIT / 1024 / 1024 / 1024,3)) USED_GB,
	   SUM(ROUND(FRAU.PERCENT_SPACE_RECLAIMABLE / 100 * RFD.SPACE_LIMIT / 1024 / 1024 / 1024,3)) RECLAIM_GB,
       SUM(FRAU.PERCENT_SPACE_USED) PERCENT_SPACE_USED,
       SUM(FRAU.PERCENT_SPACE_RECLAIMABLE) PERCENT_SPACE_RECLAIMABLE,
       SUM(ROUND(FRAU.PERCENT_SPACE_RECLAIMABLE / 100 * RFD.SPACE_LIMIT / 1024 / 1024 / 1024,3)) RECLAIM_GB,
       SUM(FRAU.NUMBER_OF_FILES) NUMBER_OF_FILES,
       SUM(FRAU.PERCENT_SPACE_USED) PERCENT_SPACE_USED
  FROM V$FLASH_RECOVERY_AREA_USAGE FRAU, V$RECOVERY_FILE_DEST RFD
GROUP BY ROLLUP(FILE_TYPE);



set numf 99999.99
col DiskCnt for 9999
col "Group" for 999
col "Group Name" for a12
SELECT g.group_number  "Group" ,  g.name   "Group Name" 
,      count(*)        "DiskCnt" 
,  g.state  "State" ,  g.type  "Type" , g.total_mb/1024 "Total GB" 
, g.free_mb/1024  "Free GB"      
FROM v$asm_disk d, v$asm_diskgroup g 
WHERE d.group_number = g.group_number 
and d.group_number <> 0 
and d.state = 'NORMAL' 
and d.mount_status = 'CACHED' 
GROUP BY g.group_number, g.name, g.state, g.type, g.total_mb, g.free_mb 
ORDER BY 1;


col NAME for a15
column SPACE_LIMIT_gB format 999G999D00
column SPACE_USED_gB format 999G999D00
column SPACE_RECLAIMABLE_GB format 999G999D00
select name, SPACE_LIMIT/1024/1024/1024 SPACE_LIMIT_gb, SPACE_USED/1024/1024/1024 SPACE_USED_gb
, SPACE_RECLAIMABLE/1024/1024/1024 SPACE_RECLAIMABLE_GB  from v$recovery_file_dest;


col SPACE_LIMIT_GB for a14
 SELECT  TO_CHAR(SPACE_LIMIT/1024/1024/1024)  SPACE_LIMIT_gb,  TO_CHAR(SPACE_USED/1024/1024/1024, '999,999,999,999') AS "Used_Gb", 
TO_CHAR(SPACE_LIMIT/1024/1024/1024 - SPACE_USED/1024/1024/1024 + SPACE_RECLAIMABLE/1024/1024/1024, '999,999,999,999') AS "Free_Gb", 
 ROUND((SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1) AS "Persent Used" 
 FROM V$RECOVERY_FILE_DEST;




set linesize 300
col NAME for a40
col value for a60 wrap
select (select value from v$parameter where NAME='log_archive_dest_1') value ,a.name,
  round((a.space_limit/1024/1024/1024),2) as Total_in_Gb,  round((a.space_used/1024/1024/1024),2) as flash_used_in_Gb, 
  round((a.space_reclaimable/1024/1024/1024), 2) as flash_reclaimable_Gb, sum(b.percent_space_used)  as "PERCENT_OF_SPACE_USED%"
from   v$recovery_file_dest a, v$flash_recovery_area_usage b
group by  a.name,space_limit, space_used ,   space_reclaimable ;




col TOTAL_gB   for 999999999
col FREE_gB    for 999999999.99
col USED_gB  for 999999999.99
col name for a15

set linesize 300
col "used (%)" for a25
select
name
,total_mb/1024 total_gb
,free_mb/1024 free_gb
,used_mb/1024 used_gb
,round(free_mb*100/total_mb) free_pct
,round(used_mb*100/total_mb) used_pct
,'['
|| rpad (nvl (rpad ('#', round (round(used_mb*100/total_mb) / 10), '#'), '_'),
         10,
         '_'
         )
|| ']'
|| lpad (to_char (round(used_mb*100/total_mb)), 3, ' ')
|| '%' "used (%)"
from (
select
name
,round((total_mb-required_mirror_free_mb)/(decode(type,'HIGH',3,'NORMAL',2,1))) total_mb
,(usable_file_mb) free_mb
,round(((total_mb-required_mirror_free_mb)/(decode(type,'HIGH',3,'NORMAL',2,1)))-usable_file_mb) used_mb
from v$asm_diskgroup
)
;

NAME              TOTAL_GB       FREE_GB       USED_GB  FREE_PCT  USED_PCT used (%)
--------------- ---------- ------------- ------------- --------- --------- -------------------------
DATA11                5000       2304.70       2695.28     46.00     54.00 [#####_____] 54%








col name for a15
col "Size(TB)" for 999.99
col "Used(TB)" for 999.99
col "FREE(TB)" for 999.99
col MAX_DISK_SIZE_GB for 9999
col min_DISK_SIZE_GB for 9999 for 9999

select d.name,d.total_mb/1024/1024 "Size(TB)",d.total_mb/1024/1024 - d.free_mb/1024/1024 "Used(TB)", free_mb/1024/1024 "FREE(TB)" ,100-round((d.FREE_MB/1024*100)/(d.TOTAL_MB/1024))"Utilzed %" 
,max_disk_size_mb/1024 max_disk_size_GB, min_disk_size_mb/1024 min_disk_size_GB, no_of_disk
from v$asm_diskgroup d, ( select di.GROUP_NUMBER, max(di.TOTAL_MB) max_disk_size_mb , min(di.TOTAL_MB) min_disk_size_mb ,count(*) no_of_disk from  v$asm_disk di 
                                   group by di.GROUP_NUMBER ) a
where 1=1
and a.GROUP_NUMBER=d.GROUP_NUMBER
order by 1
;

<10 .00="" then="">

alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';
select min(FIRST_TIME) min_time,max(FIRST_TIME) Max_time from  v$flashback_database_logfile;


set linesize 300
col FILE_TYPE for a30
col PERCENT_SPACE_USED for 999.99 heading 'PERCENT_SPACE_USED%'   
SELECT NVL(FRAU.FILE_TYPE, 'Total:') FILE_TYPE,
       SUM(ROUND(FRAU.PERCENT_SPACE_USED / 100 * RFD.SPACE_LIMIT / 1024 / 1024 / 1024,3)) USED_GB,
       SUM(FRAU.PERCENT_SPACE_USED) PERCENT_SPACE_USED,
       SUM(FRAU.PERCENT_SPACE_RECLAIMABLE) PERCENT_SPACE_RECLAIMABLE,
       SUM(ROUND(FRAU.PERCENT_SPACE_RECLAIMABLE / 100 * RFD.SPACE_LIMIT / 1024 / 1024 / 1024,3)) RECLAIM_GB,
       SUM(FRAU.NUMBER_OF_FILES) NUMBER_OF_FILES,
       SUM(FRAU.PERCENT_SPACE_USED) PERCENT_SPACE_USED
  FROM V$FLASH_RECOVERY_AREA_USAGE FRAU, V$RECOVERY_FILE_DEST RFD
GROUP BY ROLLUP(FILE_TYPE);


show parameter reco 
show parameter flashback_retention 



set linesize 300
col NAME for a40
col value for a60 wrap
select (select value from v$parameter where NAME='log_archive_dest_1') value ,a.name,
  round((a.space_limit/1024/1024/1024),2) as Total_in_Gb,  round((a.space_used/1024/1024/1024),2) as flash_used_in_Gb, 
  round((a.space_reclaimable/1024/1024/1024), 2) as flash_reclaimable_Gb, sum(b.percent_space_used)  as "PERCENT_OF_SPACE_USED%"
from   v$recovery_file_dest a, v$flash_recovery_area_usage b
group by  a.name,space_limit, space_used ,   space_reclaimable ;




set linesize 300
column name format A30
column value format A50
select name, value
from   v$parameter
where  name in ('db_flashback_retention_target', 'db_recovery_file_dest','db_recovery_file_dest_size')
order by name;


set linesize 200
col name for a50
SELECT NAME, (SPACE_LIMIT/1024/1024 /1024) SPACE_LIMIT_GB,round(((SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE)/1024/1024/1024)) AS SPACE_AVAILABLE_GB, ROUND((SPACE_USED - SPACE_RECLAIMABLE) / SPACE_LIMIT * 100, 1) AS PERCENT_FULL
FROM V$RECOVERY_FILE_DEST ;


set pagesize 300 linesize 300
prompt -- status of the Flash Recovery Area ?
TTITLE 'Flash Recovery Area Status'
COL name   FORMAT A40 HEADING 'File Name'
COL spc_lmt_mb   FORMAT 999999999.99 HEADING 'Space|Limit|(GB)'
COL spc_usd_mb   FORMAT 999999999.99 HEADING 'Space|Used|(GB)'
COL spc_rcl_mb   FORMAT 999999999.99 HEADING 'Reclm|Space|(GB)'
COL number_of_files  FORMAT 999999999    HEADING 'Files'
SELECT
name
,trunc(space_limit /(1024*1024*1024),2) spc_lmt_gb
,trunc(space_used /(1024*1024*1024),2) spc_usd_gb
,trunc(space_reclaimable /(1024*1024*1024),2) spc_rcl_gb
,number_of_files
FROM v$recovery_file_dest;

TTITLE 'Database Incarnations|(from V$DATABASE_INCARNATION)'
COL incarnation#        FORMAT 9999999      HEADING 'Incar-|nation'
COL resetlogs_id        FORMAT 99999999999  HEADING 'ResetLogs|ID'
COL prior_incarnation#  FORMAT 9999999      HEADING 'Prior|Incar-|nation'
COL status              FORMAT A16          HEADING 'Status'
COL rsl_dtm             FORMAT A11          HEADING 'Reset|Logs|Timestamp' WRAP
COL prior_rsl_dtm       FORMAT A11          HEADING 'Prior|Reset|Logs|Timestamp' WRAP
COL fba_flag            FORMAT A05          HEADING 'Flash|Back|DB|Alwd?'
SELECT 
     incarnation#
    ,resetlogs_id
    ,resetlogs_change# rsl_chg#
    ,TO_CHAR(resetlogs_time, 'dd-mm-yyyy hh24:mi:ss') rsl_dtm
    ,prior_incarnation#
    ,TO_CHAR(prior_resetlogs_time, 'dd-mm-yyyy hh24:mi:ss') prior_rsl_dtm
    ,status
    ,flashback_database_allowed fba_flag
  FROM v$database_incarnation
;

<10 .00="" then="">



The workaround is to lower the flashback retention target so that all changes fit in the FRA: <<<<<<<<<<<<<  if FRA full 

SQL> alter system set db_flashback_retention_target=1440;

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=???G scope=both;



archive log list;


alter system set log_archive_dest_1='/xxx/backups' scope=memory;

ALTER SYSTEM SET log_archive_dest_1 ="LOCATION=+DATA" SCOPE=BOTH;

Delete archive log file 

run { 
crosscheck backupset of database;
crosscheck backupset of controlfile;
crosscheck backupset of archivelog all;
delete force  noprompt obsolete;
delete expired archivelog all;
delete noprompt obsolete;
delete noprompt obsolete orphan;
crosscheck archivelog all;
delete expired archivelog all;
}

delete noprompt archivelog all completed before 'sysdate - 1/24';     ---1 hour
 
delete noprompt archivelog all completed before 'sysdate - 4';


***************************************
Take Archive log Backup  
nohup rman target / log=Arch.log @arch.rmn &


cat arch.rmn
run {  backup as compressed backupset  format '/XXXX/Arch_%T_@_%s_%p_%t' (archivelog all delete input); }


*****************************************


prompt -- What Flashback options are currently enabled for this database?
TTITLE 'Flashback Options Currently Enabled:'


Oracle flashback info

set numf 99999999999999999999999999

*****


prompt  DB_DATA     Number of bytes of database data read and written during the interval

prompt  REDO_DATA Number of bytes of redo data written during the interval


set linesize 400 
col DB_DATA for 999999999999 
col FLASHBACK_SIZE for 999999999999 
col "FLASH BEGIN" for a14
col "FLASH END" for a14
SELECT TO_CHAR(A.BEGIN_TIME,'DDMMYY HH24:MI') "FLASH BEGIN",TO_CHAR(A.END_TIME,'DDMMYY HH24:MI') "FLASH END",A.DB_DATA "DB-READ-WRITE",a.REDO_DATA ,B.FLASHBACK_SIZE,
 (A.DB_DATA/B.FLASHBACK_SIZE)*100 "% COMPLETE" 
FROM V$FLASHBACK_DATABASE_STAT A, V$FLASHBACK_DATABASE_LOG B;
FLASH BEGIN    FLASH END         DB-READ-WRITE        REDO_DATA FLASHBACK_SIZE       % COMPLETE
-------------- -------------- ---------------- ---------------- -------------- ----------------
100423 12:02   100423 12:15         7406247936       7188010496   599147937792                1
100423 11:02   100423 12:02        31025061888      32838619136   599147937792                5
100423 10:02   100423 11:02        26978738176      29367271936   599147937792                5
100423 09:02   100423 10:02        22409895936      24943251456   599147937792                4

******

 col oldest_flashback_scn 			format 999999999999999 	heading 'oldest|flashback|scn #'
 col oldest_flashback_time 			format a20 				heading 'oldest|flashback|time'
 col retention_target 				format 999999999 		heading 'retention|target'
 col flashback_size 				format 999999999999999 	heading 'flashback|Gb-size'
 col estimated_flashback_size 		format 999999999999 	heading 'estimated|flashback|Gb-size'
select 
--CON_ID,
oldest_flashback_scn,to_char(oldest_flashback_time,'dd-mm-yyyy hh24:mi:ss') oldest_flashback_time,retention_target,flashback_size/1024/1024/1024 flashback_size,estimated_flashback_size /1024/1024/1024 estimated_flashback_size
from v$flashback_database_log;


SELECT CURRENT_SCN FROM V$DATABASE;


SELECT trunc(estimated_flashback_size/1024/1024/1024,2) estimated_flashback_size_GB ,trunc(flashback_size/1024/1024/1024,2) flashback_size_gb  FROM v$flashback_database_log;


db_flashback_retention_target




SELECT ROUND((A.SPACE_LIMIT/1024/1024/1024), 2) AS FLASH_IN_GB,   ROUND((A.SPACE_USED/1024/1024/1024), 2) AS FLASH_USED_IN_GB,   ROUND((A.SPACE_RECLAIMABLE/1024/1024/ 1024), 2) AS FLASH_RECLAIMABLE_GB,  SUM(B.PERCENT_SPACE_USED) as  "PERCENT_OF_SPACE_USED%"
FROM   V$RECOVERY_FILE_DEST A,  V$FLASH_RECOVERY_AREA_USAGE B
GROUP BY  SPACE_LIMIT,   SPACE_USED ,   SPACE_RECLAIMABLE ;



archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +LOGS




The workaround is to lower the flashback retention target so that all changes fit in the FRA:

SQL> alter system set db_flashback_retention_target=1440;



prompt -- Is Flashback Database On ?

TTITLE 'Is Flashback Database Enabled?'
COL name   FORMAT A12 HEADING 'Database'
COL current_scn  FORMAT 9999999999999 HEADING 'Current|SCN #'
COL flashback_on  FORMAT A8 HEADING 'Flash|Back On?'
SELECT
name
,current_scn
,flashback_on
FROM v$database;


set linesize 300 pagesize 300
alter session set nls_date_format='dd-mm-yyy mi:ss';
prompt -- What Flashback Logs are available?
TTITLE 'Current Flashback Logs Available'
COL log#   FORMAT 9999   HEADING 'FLB|Log#'
COL bytes   FORMAT 99999999  HEADING 'Flshbck|Log Size'
COL first_change#  FORMAT 99999999999999  HEADING 'Flshbck|SCN #'
COL first_time   FORMAT A24   HEADING 'Flashback Start Time'

SELECT
LOG#
,trunc(bytes/1024/1024,2) MB
,first_change#
,first_time
FROM v$flashback_database_logfile;

====

with file

alter session set nls_date_format='dd-mm-yyyy hh24:mi';
set linesize 300 pagesize 300
 COL log#   FORMAT 9999   HEADING 'FLB|Log#'
 COL bytes   FORMAT 99999999  HEADING 'Flshbck|Log Size'
 col first_change#  FORMAT 99999999999999  HEADING 'Flshbck|SCN #'
  COL first_time   FORMAT A24   HEADING 'Flashback Start Time'
col NAME for a70
 SELECT
NAME,
    LOG#
   ,trunc(bytes/1024/1024,2) MB
    ,first_change#
    ,first_time
    FROM v$flashback_database_logfile
order by first_time ;

==

col NAME for a30

SELECT MTIME, NAME, OWNER#, OBJ# FROM SYS.OBJ$ WHERE NAME LIKE 'BIN$%'; 

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY HH24:MI:SS'; 

SELECT NAME FROM SYS.OBJ$ WHERE OBJ# IN (SELECT OBJ# FROM SYS.COL_USAGE$ MINUS SELECT OBJ# 
                                         FROM SYS.COL_USAGE$ AS OF TIMESTAMP(SYSDATE - INTERVAL '14' MINUTE)
                                         ); 
SELECT MTIME, OBJ#, OWNER#, NAME FROM SYS.OBJ$ WHERE OBJ#=53137; 

SELECT DROPTIME, OBJ#, OWNER#, ORIGINAL_NAME FROM SYS.RECYCLEBIN$; 

select object_name, droptime from dba_recyclebin;

purge table "BIN$cxP2b6/dEnrgVQBAAAAADQ==$1";


select OBJECT_NAME, ORIGINAL_NAME, TYPE from dba_recyclebin;

col ORIGINAL_NAME  for a40
col "Size in GB" for 999999999999.99
col OBJECT_NAME for a35
SELECT TS_NAME, SUM(SPACE*8192)/1024/1024/1024 AS "Size in GB",OBJECT_NAME, ORIGINAL_NAME, TYPE FROM DBA_RECYCLEBIN 
GROUP BY TS_NAME ,OBJECT_NAME, ORIGINAL_NAME, TYPE
order by 2 desc;


col ts_name for a20
  SELECT TS_NAME,
         TRUNC (SUM (  NVL (SPACE, 0)
                     * (SELECT VALUE
                          FROM V$PARAMETER
                         WHERE NAME LIKE '%db_block_size%')
                     / 1024
                     / 1024
                     / 1024),
                2)
             RECYCLE_SIZE_GB
    FROM DBA_RECYCLEBIN  
 WHERE 1=1
-- and TS_NAME = 'TABLESPACE_NAME'
GROUP BY TS_NAME
/

<10 .00="" then="">

PROMPT How Far Back Can We Flashback To (Time)?
PROMPT

select to_char(oldest_flashback_time,'dd-mon-yyyy hh24:mi:ss') "Oldest Flashback Time" from v$flashback_database_log;

PROMPT
PROMPT How Far Back Can We Flashback To (SCN)?
PROMPT

col oldest_flashback_scn format 99999999999999999999999999
select oldest_flashback_scn from v$flashback_database_log;



PROMPT
PROMPT Flashback Area Usage
SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

PROMPT
set linesize 200
col ROUND(SPACE_LIMIT/1048576) heading "Space Allocated (MB)"  format 9999999999
col round(space_used/1048576) heading "Space Used (MB)"  format 9999999999999
col name Heading "Flashback Location"     format a50
select name, round(space_limit/1048576),round(space_used/1048576) from v$RECOVERY_FILE_DEST;



set linesize 200
col name for a50
SELECT NAME, (SPACE_LIMIT/1024/1024 /1024) SPACE_LIMIT_GB,((SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE)/1024/1024/1024) AS SPACE_AVAILABLE_GB, ROUND((SPACE_USED - SPACE_RECLAIMABLE) / SPACE_LIMIT * 100, 1) AS PERCENT_FULL
FROM V$RECOVERY_FILE_DEST ;
 /




in the MOUNT (exclusive) mode then issue one of the commands:

FLASHBACK DATABASE TO SCN 5964663

FLASHBACK DATABASE TO BEFORE SCN 5964663

FLASHBACK DATABASE TO TIMESTAMP (SYSDATE -1/24)

FLASHBACK DATABASE TO SEQUENCE 12345

FLASHBACK TABLE persons TO SCN 6039341


=====


set heading on  linesize 300
column name format a40 heading "Name"
column sl format 9999999 heading "Space Limit|(Gb)"
column su format 9999999 heading "Space Used|(Gb)"
column sr format 9999999 heading "Space|Reclaimable|(Gb)"
column nf format 9999999 heading "N of Files"

Select
Substr(Name,1,40) name
, Space_Limit/1024/1024/1024 sl
, Space_Used/1024/1024/1024 su
, Space_Reclaimable/1024/1024/1024 sr
, Number_Of_Files nf
From V$RECOVERY_FILE_DEST
;

column ft format a30 heading "File Type"
column psu format 9999999 heading "Space|Used %"
column psr format 9999999 heading "Space|Reclaimable %"

Select 
File_Type ft
, Percent_Space_Used psu
, Percent_Space_Reclaimable psr
, Number_Of_Files nf
From V$FLASH_RECOVERY_AREA_USAGE
;



                                  Space         Space
File Type                        Used % Reclaimable % N. Files
------------------------------ -------- ------------- --------
CONTROL FILE                          0             0        0
REDO LOG                              0             0        0
ARCHIVED LOG                          0             0        0
BACKUP PIECE                          0             0        0
IMAGE COPY                            0             0        0
FLASHBACK LOG                        70            23      219
FOREIGN ARCHIVED LOG                  0             0        0
AUXILIARY DATAFILE COPY               0             0        0

8 rows selected.


-- After that you can resize the FRA with:
-- ALTER SYSTEM SET db_recovery_file_dest_size= 20 G;
 
-- Or change the FRA to a new location :
-- ALTER SYSTEM SET DB_RECOVERY_FILE_DEST=' ';


column os format 99999999999999 heading "Oldest Flashback SCN"
column ot format a25   heading "Oldest Flashback Time"
Select OLDEST_FLASHBACK_SCN os, TO_CHAR(OLDEST_FLASHBACK_TIME, 'DD-MM-YYYYD HH24:MI:SS') ot From  V$FLASHBACK_DATABASE_LOG;



 col oldest_flashback_scn 			format 999999999999999 	heading 'oldest|flashback|scn #'
 col oldest_flashback_time 			format a20 				heading 'oldest|flashback|time'
 col retention_target 				format 999999999 		heading 'retention|target'
 col flashback_size 				format 999999999999999 	heading 'flashback|Gb-size'
 col estimated_flashback_size 		format 999999999999 	heading 'estimated|flashback|Gb-size'
select 
--CON_ID,
oldest_flashback_scn,to_char(oldest_flashback_time,'dd-mm-yyyy hh24:mi:ss') oldest_flashback_time,retention_target,flashback_size/1024/1024/1024 flashback_size,estimated_flashback_size /1024/1024/1024 estimated_flashback_size
from v$flashback_database_log;


select * from V$RESTORE_POINT;

-- How much space is used by Flashback Logs for each GRP?
SELECT NAME, SCN, STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE = 'YES';

How much space do all GRP Flashback Logs use?

SELECT SUM(STORAGE_SIZE) FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE = 'YES';

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


Set heading off;
select '+----------------------------------------+' from dual
union all
select '| Timestamp: '||to_char(systimestamp,'DD-MM-YYYY HH24:MI:SS TZH:TZM') from dual
union all
select '| Machine: '||host_name from v$instance
union all
select '| Version: '||version from v$instance
union all
select '| DBName: '||name from v$database
union all
select '| Instance: '||instance_name from v$instance
union all
select '+----------------------------------------+' from dual;
Set heading on
/

set echo on  feedback on  numwidth 30 pagesize 50000 linesize 300


alter session set nls_date_format = 'DD-MON-RRRR HH24:MI:SS';
select sysdate from dual;


show parameter retention


prompt ## v$database
select flashback_on from v$database;


prompt 	## v$rman_configuration
select * from v$rman_configuration;


prompt 	## v$restore_point
select * from v$restore_point;

prompt 	## V$FLASH_RECOVERY_AREA_USAGE
select * from V$FLASH_RECOVERY_AREA_USAGE;

prompt  ## V$RECOVERY_FILE_DEST
col name for a35
select * from V$RECOVERY_FILE_DEST;

prompt 	## v$flashback_database_log

col VALUE for a30
select * from v$flashback_database_log;

prompt 	##v$flashback_database_logfile
select * from v$flashback_database_logfile;

prompt 	##v$flashback_database_logfile
select min(first_time), min(first_change#) from v$flashback_database_logfile;


set linesize 300
col object_name for a30
col ORIGINAL_NAME for a30
col type for a10
col owner for a10
col CREATETIME for a20
col DROPTIME for a20
select OBJECT_NAME, ORIGINAL_NAME, TYPE,owner,CREATETIME,DROPTIME from dba_recyclebin;






 
 
col  NAME for a50
col value for a40
 
WITH flashback_database_log AS
(SELECT ROUND((SYSDATE - oldest_flashback_time) * 24 * 60, 2) oldest_log_minutes,
retention_target retention_target_minutes,
flashback_size / 1048576 flashback_size_mb,
estimated_flashback_size / 1048576 estimated_flashback_size_mb
FROM v$flashback_database_log),
flashback_database_logfile AS
(SELECT COUNT(*) logs,
SUM(BYTES / 1048576) size_mb,
MIN(first_time) oldest_log,
MAX(first_time) latest_log
FROM v$flashback_database_logfile),
flashback_usage AS
(SELECT file_type,
ROUND(mb_used, 2) mb_used,
ROUND(mb_reclaimable, 2) mb_reclaimable,
DECODE(total_mb, 0, 0, ROUND(mb_used * 100 / total_mb, 2)) percent_space_used,
DECODE(total_mb, 0, 0, ROUND(mb_reclaimable * 100 / total_mb, 2)) percent_space_reclaimable,
number_of_files,
total_mb db_recovery_file_dest_mb,
flashback_retention_target,
oldest_record,
ROUND((sysdate - oldest_record) * 24 * 60, 2) oldest_record_age_sec
FROM (SELECT SUM(DECODE(NAME,
'db_recovery_file_dest_size',
VALUE / 1048576,
0)) total_mb,
SUM(DECODE(NAME, 'db_flashback_retention_target', VALUE, 0)) flashback_retention_target
FROM v$parameter
WHERE NAME IN ('db_recovery_file_dest_size',
'db_flashback_retention_target')),
(SELECT 'FLASHBACKLOG' file_type,
NVL(SUM(BYTES) / 1048576, 0) mb_used,
sum(CASE
WHEN last_time <= (sysdate - (tgt.value / 1440)) THEN
bytes / 1048576
ELSE
0
END) mb_reclaimable,
COUNT(*) number_of_files,
MIN(first_time) oldest_record
FROM (select bytes,
lead(first_time) over(order by first_time asc) last_time,
first_time
from v$flashback_database_logfile) fla_log,
(SELECT value value
FROM v$parameter
WHERE name = 'db_flashback_retention_target') tgt
UNION
SELECT 'BACKUPPIECE' file_type,
NVL(SUM(BYTES / 1048576), 0) mb,
SUM(CASE
WHEN dl.rectype = 13 THEN
(BYTES / 1048576)
ELSE
0
END) reclaimable_mb,
COUNT(*) no_of_files,
MIN(start_time) oldest_record
FROM v$backup_piece bp, x$kccagf dl
WHERE is_recovery_dest_file = 'YES'
AND deleted = 'NO'
AND bp.recid = dl.recid(+)
AND dl.rectype(+) = 13
UNION
SELECT 'ARCHIVELOG' file_type,
NVL(SUM(blocks * block_size) / 1048576, 0) mb,
SUM(CASE
WHEN dl.rectype = 11 THEN
(LOG.blocks * LOG.block_size / 1048576)
ELSE
0
END) reclaimable_mb,
COUNT(*) no_of_files,
MIN(first_time) oldest_record
FROM v$archived_log log, x$kccagf dl
WHERE deleted = 'NO'
AND is_recovery_dest_file = 'YES'
AND dl.recid(+) = log.recid
AND dl.rectype(+) = 11
UNION
SELECT 'ONLINELOG' file_type,
SUM(BYTES / 1048576) mb,
0 reclaimable,
COUNT(*) no_of_files,
MIN(first_time) oldest_record
FROM v$logfile lf,
(SELECT group#, BYTES, first_time
FROM v$standby_log
UNION
SELECT group#, BYTES, first_time FROM v$log) l
WHERE l.group# = lf.group#
AND lf.is_recovery_dest_file = 'YES'
UNION
SELECT 'IMAGECOPY',
NVL(SUM(blocks * (block_size / 1048576)), 0) mb,
0 reclaimable_mb,
COUNT(*) no_of_files,
MIN(creation_time) oldest_record
FROM v$datafile_copy
WHERE deleted = 'NO'
AND is_recovery_dest_file = 'YES'
UNION
SELECT 'CONTROLFILE',
NVL(SUM(block_size * file_size_blks) / 1048576, 0) mb,
0 reclaimable,
COUNT(*) no_of_files,
NULL oldest_record
FROM v$controlfile
WHERE is_recovery_dest_file = 'YES'))
SELECT order_, NAME, VALUE
FROM(
SELECT 0 order_, NAME, VALUE
FROM v$parameter
WHERE NAME LIKE 'db_recovery_file%'
UNION
SELECT 3, 'oldest flashback log (minutes)', TO_CHAR(ROUND(oldest_log_minutes, 2))
FROM flashback_database_log
UNION
SELECT 1, 'retention target (minutes)', TO_CHAR(ROUND(retention_target_minutes, 2))
FROM flashback_database_log
UNION
SELECT 2, 'estimated size for flashback logs (MB)', TO_CHAR(ROUND(estimated_flashback_size_mb, 2))
FROM flashback_database_log
UNION
SELECT 2, 'Current flashback log count', TO_CHAR(logs)
FROM flashback_database_logfile
UNION
SELECT 3, 'Most recent flashback log (minutes)', TO_CHAR(ROUND((SYSDATE - latest_log) * 24 * 60, 2))
FROM flashback_database_logfile
UNION
SELECT 4, 'Total size of all files in MB', TO_CHAR(ROUND(SUM(mb_used), 2))
FROM flashback_usage
UNION
SELECT 5, 'Total size of reclaimable files in MB', TO_CHAR(ROUND(SUM(mb_reclaimable), 2))
FROM flashback_usage
UNION
SELECT 6, 'unused space in MB', TO_CHAR(ROUND(MIN(db_recovery_file_dest_mb) - SUM(mb_used)))
FROM flashback_usage)
ORDER BY order_, NAME;




   ORDER_ NAME                                               VALUE
---------- -------------------------------------------------- ----------------------------------------
         0 db_recovery_file_dest                              /oracle/db/fast_recovery_area
         0 db_recovery_file_dest_size                         5033164800
         2 Current flashback log count                        0
         3 Most recent flashback log (minutes)
         4 Total size of all files in MB                      0
         5 Total size of reclaimable files in MB              0
         6 unused space in MB                                 4800

7 rows selected.


REM srdc_fra_details.sql – collect FRA information



define SRDCNAME='FRA_DETAILS'

set markup html on spool on TERMOUT off

COLUMN SRDCSPOOLNAME NOPRINT NEW_VALUE SRDCSPOOLNAME
select 'SRDC_'||upper('&&SRDCNAME')||'_'||upper(instance_name)||'_'||to_char(sysdate,'YYYYMMDD_HH24MISS') SRDCSPOOLNAME from v$instance;
REM
spool &&SRDCSPOOLNAME..htm
Set heading off;
select '+—————————————————-+' from dual
union all
select '| Diagnostic-Name: '||'&&SRDCNAME' from dual
union all
select '| Timestamp: '||
to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS TZH:TZM') from dual
union all
select '| Machine: '||host_name from v$instance
union all
select '| Version: '||version from v$instance
union all
select '| DBName: '||name from v$database
union all
select '| Instance: '||instance_name from v$instance
union all
select '+—————————————————-+' from dual
/

set echo on feedback on numwidth 30 pagesize 50000
alter session set nls_date_format = 'DD-MON-RRRR HH24:MI:SS';
select sysdate from dual;
show parameter retention;
select flashback_on from v$database;
select * from v$rman_configuration;
select * from v$restore_point;
select * from V$FLASH_RECOVERY_AREA_USAGE;
select * from V$RECOVERY_FILE_DEST;
select * from v$flashback_database_log;
select * from v$flashback_database_logfile;
select min(first_time) from v$flashback_database_logfile;
select min(first_change#) from v$flashback_database_logfile;
set markup html off spool off

sqlplus text ----
set echo on feedback on numwidth 30 
alter session set nls_date_format = 'DD-MON-RRRR HH24:MI:SS';
select sysdate from dual;
show parameter retention;
set pagesize 50000 linesize 400

col VALUE for a40
select flashback_on from v$database;
col NAME  for a40
col VALUE for 40
select * from v$rman_configuration;
select * from v$restore_point;
select * from V$FLASH_RECOVERY_AREA_USAGE;

col NAME for a70
select * from V$RECOVERY_FILE_DEST;
col NAME for a70
select * from v$flashback_database_log;
select * from v$flashback_database_logfile;
select min(first_time) from v$flashback_database_logfile;
select min(first_change#) from v$flashback_database_logfile;

=======





select to_char(oldest_flashback_time,'dd-mon-yyyy hh24:mi:ss') "Oldest Flashback Time" from v$flashback_database_log;  

Oldest Flashback Time
-----------------------------
17-oct-2017 05:22:07


Flashback To (SCN) :

col oldest_flashback_scn format 99999999999999999
select oldest_flashback_scn from v$flashback_database_log;

## First we need to check whether the table name exists in recyclebin or not:
 
SELECT object_name, original_name, createtime FROM recyclebin where original_name='EMP';
 
## Now restore the table with same name:

FLASHBACK TABLE emp TO BEFORE DROP;
 
## Restore that table with a new name:
 
FLASHBACK TABLE anuj.emp TO BEFORE DROP RENAME TO anuj.emp1;


*******************

SELECT * FROM anuj.EMP AS OF TIMESTAMP TO_TIMESTAMP('2023-08-17 10:00:00', 'YYYY-MM-DD HH:MI:SS');

--with parallel
SELECT /*+ parallel 4 */ * FROM anuj.emp AS OF TIMESTAMP TO_TIMESTAMP('2015-10-10 01:00:00', 'YYYY-MM-DD HH:MI:SS');

****************

<10 .00="" then="">

<10 .00="" then="">

<10 .00="" then="">

Oracle 26ai 

show parameter flashback;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_log_dest                string    <<<<<< NEW
db_flashback_log_dest_size           big integer 0
db_flashback_retention_target        integer     1440


13:47:46 SQL> !pwd
/home/oracle

13:48:01 SQL> !mkdir /home/oracle/flash

alter system set db_flashback_log_dest_size =2g scope = both;

alter system set db_flashback_log_dest='/home/oracle/flash' scope = both;

alter database flashback on;

set linesize 255
 col NAMEFL format a70
alter database flashback on
SELECT sequence# as seq, name as namefl, bytes/1024/1024 as size_mb, to_char(first_time, 'DD/MM/RRRR HH24:MI') as firsttime  
from v$flashback_database_logfile order by first_time;


alter session set nls_date_format='dd-mm-yyyy hh24:mi' ;
SELECT
    SYSDATE,
    MIN(first_time) AS first_time,
    ROUND(SYSDATE - MIN(first_time), 2)          AS FilesStoredInDays,
    ROUND((SYSDATE - MIN(first_time)) * 24, 2)   AS FilesStoredInHours,
    ROUND((SYSDATE - MIN(first_time)) * 1440, 2) AS FilesStoredInMinutes
FROM v$flashback_database_logfile;

SYSDATE          FIRST_TIME       FILESSTOREDINDAYS FILESSTOREDINHOURS FILESSTOREDINMINUTES
---------------- ---------------- ----------------- ------------------ --------------------
20-08-2026 10:09 18-08-2026 23:20              1.45              34.82              2089.05

<10 .00="" then="">

Monday, 1 August 2011

Oracle create cvs file for oracle table

coma separate data
Comma Separated data
Comma Separated Variable (CSV) files )

from web
http://www.oracle-base.com/dba/miscellaneous/cvs.sql

CREATE OR REPLACE PACKAGE csv AS
--
-- CREATE OR REPLACE DIRECTORY dba_dir AS '/u01/app/oracle/dba/';
-- ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
--
-- EXEC csv.generate('DBA_DIR', 'generate.csv', p_query => 'SELECT * FROM emp');
--
-- Requirements : UTL_FILE, DBMS_SQL
-- Ammedments :

PROCEDURE generate (p_dir IN VARCHAR2,
p_file IN VARCHAR2,
p_query IN VARCHAR2);
END csv;
/
SHOW ERRORS

CREATE OR REPLACE PACKAGE BODY csv AS
-- --------------------------------------------------------------------------
-- Name : http://www.oracle-base.com/dba/miscellaneous/cvs.sql
-- Author : DR Timothy S Hall
-- Description : Basic CSV API. For usage notes see:
--
-- CREATE OR REPLACE DIRECTORY dba_dir AS '/u01/app/oracle/dba/';
-- ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
--
-- EXEC csv.generate('DBA_DIR', 'generate.csv', p_query => 'SELECT * FROM emp');
--
-- Requirements : UTL_FILE, DBMS_SQL
-- Ammedments :
-- When Who What
-- =========== ======== =================================================
-- 14-MAY-2005 Tim Hall Initial Creation
-- --------------------------------------------------------------------------

g_sep VARCHAR2(5) := ',';

PROCEDURE generate (p_dir IN VARCHAR2,
p_file IN VARCHAR2,
p_query IN VARCHAR2) AS
l_cursor PLS_INTEGER;
l_rows PLS_INTEGER;
l_col_cnt PLS_INTEGER;
l_desc_tab DBMS_SQL.desc_tab;
l_buffer VARCHAR2(32767);

l_file UTL_FILE.file_type;
BEGIN
l_cursor := DBMS_SQL.open_cursor;
DBMS_SQL.parse(l_cursor, p_query, DBMS_SQL.native);

DBMS_SQL.describe_columns (l_cursor, l_col_cnt, l_desc_tab);

FOR i IN 1 .. l_col_cnt LOOP
DBMS_SQL.define_column(l_cursor, i, l_buffer, 32767 );
END LOOP;

l_rows := DBMS_SQL.execute(l_cursor);

l_file := UTL_FILE.fopen(p_dir, p_file, 'w', 32767);

-- Output the column names.
FOR i IN 1 .. l_col_cnt LOOP
IF i > 1 THEN
UTL_FILE.put(l_file, g_sep);
END IF;
UTL_FILE.put(l_file, l_desc_tab(i).col_name);
END LOOP;
UTL_FILE.new_line(l_file);

-- Output the data.
LOOP
EXIT WHEN DBMS_SQL.fetch_rows(l_cursor) = 0;

FOR i IN 1 .. l_col_cnt LOOP
IF i > 1 THEN
UTL_FILE.put(l_file, g_sep);
END IF;

DBMS_SQL.COLUMN_VALUE(l_cursor, i, l_buffer);
UTL_FILE.put(l_file, l_buffer);
END LOOP;
UTL_FILE.new_line(l_file);
END LOOP;

UTL_FILE.fclose(l_file);
EXCEPTION
WHEN OTHERS THEN
IF UTL_FILE.is_open(l_file) THEN
UTL_FILE.fclose(l_file);
END IF;
IF DBMS_SQL.is_open(l_cursor) THEN
DBMS_SQL.close_cursor(l_cursor);
END IF;
RAISE;
END generate;

END csv;
/
SHOW ERRORS

CREATE OR REPLACE DIRECTORY EXTRACT_DIR AS '/tmp';
GRANT READ, WRITE ON DIRECTORY EXTRACT_DIR TO SCOTT;
GRANT EXECUTE ON UTL_FILE TO SCOTT;

EXEC csv.generate('EXTRACT_DIR', 'emp.csv', p_query => 'SELECT * FROM ANUJ where rownum<20');

SQL> !cat /tmp/emp.csv
OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME
SYS,ICOL$,,20,2,TABLE,15-AUG-2009 00:16:51,15-AUG-2009 00:29:27,2009-08-15:00:16:51,VALID,N,N,N,1,
SYS,I_USER1,,46,46,INDEX,15-AUG-2009 00:16:51,15-AUG-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,4,
SYS,CON$,,28,28,TABLE,15-AUG-2009 00:16:51,21-MAR-2011 15:03:17,2009-08-15:00:16:51,VALID,N,N,N,1,
SYS,UNDO$,,15,15,TABLE,15-AUG-2009 00:16:51,15-AUG-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,1,
SYS,C_COBJ#,,29,29,CLUSTER,15-AUG-2009 00:16:51,15-AUG-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,5,
SYS,I_OBJ#,,3,3,INDEX,15-AUG-2009 00:16:51,15-AUG-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,4,
SYS,PROXY_ROLE_DATA$,,25,25,TABLE,15-AUG-2009 00:16:51,15-AUG-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,1,
SYS,I_IND1,,41,41,INDEX,15-AUG-2009 00:16:51,15-AUG-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,4,
SYS,I_CDEF2,,54,54,INDEX,15-AUG-2009 00:16:51,15-AUG-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,4,
SYS,I_OBJ5,,40,40,INDEX,15-AUG-2009 00:16:51,15-AUG-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,4,
SYS,I_PROXY_ROLE_DATA$_1,,26,26,INDEX,15-AUG-2009 00:16:51,15-AUG-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,4,
SYS,FILE$,,17,17,TABLE,15-AUG-2009 00:16:51,15-AUG-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,1,
SYS,UET$,,13,8,TABLE,15-AUG-2009 00:16:51,15-AUG-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,1,
SYS,I_FILE#_BLOCK#,,9,9,INDEX,15-AUG-2009 00:16:51,15-AUG-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,4,
SYS,I_FILE1,,43,43,INDEX,15-AUG-2009 00:16:51,15-AUG-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,4,
SYS,I_CON1,,51,51,INDEX,15-AUG-2009 00:16:51,15-AUG-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,4,
SYS,I_OBJ3,,38,38,INDEX,15-AUG-2009 00:16:51,15-AUG-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,4,
SYS,I_TS#,,7,7,INDEX,15-AUG-2009 00:16:51,15-AUG-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,4,
SYS,I_CDEF4,,56,56,INDEX,15-AUG-2009 00:16:51,15-AUG-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,4,

or

>>>>>cvs.sql

set pagesize 0
set verify off
set feedback off
set linesize 130
accept owner prompt 'Enter table owner -> '
accept tblname prompt 'Enter table name -> '
spool csv2.sql

select 'select ' from sys.dual;
select decode(column_id,1,column_name,
'||'',''||'||column_name)
from sys.dba_tab_columns
where table_name = upper('&&tblname')
and owner = upper('&&owner')
order by column_id;
select 'from &&owner..&&tblname;'
from sys.dual;
spool off
undefine owner
undefine tblname

SQL> @cvs.sql
Enter table owner -> SYS
Enter table name -> ANUJ
select
OWNER
||','||OBJECT_NAME
||','||SUBOBJECT_NAME
||','||OBJECT_ID
||','||DATA_OBJECT_ID
||','||OBJECT_TYPE
||','||CREATED
||','||LAST_DDL_TIME
||','||TIMESTAMP
||','||STATUS
||','||TEMPORARY
||','||GENERATED
||','||SECONDARY
||','||NAMESPACE
||','||EDITION_NAME
from SYS.ANUJ;

spool anujcvs.txt

---- run this sql then
select
OWNER
||','||OBJECT_NAME
||','||SUBOBJECT_NAME
||','||OBJECT_ID
||','||DATA_OBJECT_ID
||','||OBJECT_TYPE
||','||CREATED
||','||LAST_DDL_TIME
||','||TIMESTAMP
||','||STATUS
||','||TEMPORARY
||','||GENERATED
||','||SECONDARY
||','||NAMESPACE
||','||EDITION_NAME
from SYS.ANUJ;
spool off ;

Friday, 29 July 2011

Oracle User's Table Name and with column Name

list of user's table
list of schema table

spool table.sql
select 'prompt '||'Table Name =>'||table_name ||'
desc '||owner||'.'||table_name from dba_tables where owner='XYZ' order by table_name
spool off

@table.sql

Anuj Singh Oracle DBA

Search This Blog

Total Pageviews

Friday, 5 August 2011