UNDO Percentage Query
select a.tablespace_name, SIZEMB, USAGEMB, (SIZEMB - USAGEMB) FREEMB
from (select sum(bytes) / 1024 / 1024 SIZEMB, b.tablespace_name
from dba_data_files a, dba_tablespaces b
where a.tablespace_name = b.tablespace_name
and b.contents = 'UNDO'
group by b.tablespace_name) a,
(select c.tablespace_name, sum(bytes) / 1024 / 1024 USAGEMB
from DBA_UNDO_EXTENTS c
where status <> 'EXPIRED'
group by c.tablespace_name) b
where a.tablespace_name = b.tablespace_name;
check patch applied or not using oracle command
-- For upto 11g
COLUMN action_time FORMAT A20
COLUMN action FORMAT A20
COLUMN version FORMAT A10
COLUMN comments FORMAT A30
COLUMN bundle_series FORMAT A10
SELECT TO_CHAR(action_time, 'DD-MON-YYYY HH24:MI:SS') AS action_time,
action,
version, id, comments, bundle_series
FROM sys.registry$history
ORDER by action_time;
--From 12c onwards
col action_time for a28
col action for a8
col version for a8
col comments for a30
col status for a10
set line 999 pages 999
select patch_id, version, status, Action,Action_time from dba_registry_sqlpatch order by action_time;
Multiple kill session example
SCHEMAS
select 'alter system kill session '''||SID||','||SERIAL#||''' immediate;' from v$session where username='<schema_name>';
Session with username not sys
set pagesize 200
select 'alter system kill session '''|| sid||','||serial#||''' immediate ;' from v$session where type <> 'BACKGROUND' and username <> 'SYS';
SQL ID
SQL>select 'alter system kill session '''||SID||','||SERIAL#||''' immediate;' from v$session where sql_id='1amhfbhjmfn6y';