Scripts

Script to set max_extents to unlimited on tablespace

select 'alter tablespace '||tablespace_name||' default storage (maxextents unlimited);'
from dba_tablespaces 
where max_extents <> 2147483645
and tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS1','TEMP')
order by tablespace_name;

Script to get SID, SERIAL# and Unix PID based on an oracle username
Note: change LUSIFER for the username that you’re looking

SELECT s.sid, s.serial#, s.username, s.osuser, p.spid, s.machine, p.terminal, s.program
FROM v$session s, v$process p
WHERE s.paddr = p.addr
and s.username = 'LUSIFER';

Script to find PID, SID and SERIAL# for a KILLED session

SELECT s.sid, s.serial#, s.username, s.osuser, p.spid, s.machine, p.terminal, s.program
FROM v$session s, v$process p
WHERE s.paddr = p.addr
and s.status ='KILLED'
/

MetaScript to create Script to kill sessions with KILLED status

select 'alter system kill session '||''''||sid||','||serial#||''''||' immediate;' 
from v$session where status='KILLED'
/

Script to get all the code to clone an username
Note: change LUSIFER for the username that you’re looking

SET LONG 100000 PAGESIZE 0 head off verify off feedback off linesize 132
exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
SELECT dbms_metadata.get_ddl('USER','LUFERNAN') FROM dual
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','LUFERNAN') from dual
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','LUFERNAN') from dual;
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','LUFERNAN') from dual;

Script to get the DB size

col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/

Script to get the TBS size (DICT & LOCAL Management)

select df.tablespace_name "Tablespace",
df.totalspace "Total MB",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name
order by 5;

Script to get the TBS size (ALL LOCAL Management 11.1+)
Opt 1

select tablespace_name, 
used_space used_blocks, 
(used_space*8192)/(1024*1024) used_mb, 
tablespace_size tablespace_blocks, 
(tablespace_size*8192)/(1024*1024) tablespace_mb,
used_percent
from dba_tablespace_usage_metrics;

Opt 2

select t1.contents,
t0.tablespace_name,
round(t0.tablespace_size*t1.block_size/1024/1024,2) total_mb,
round(t0.used_space*t1.block_size/1024/1024,2) used_mb,
round(t0.used_percent, 2) used_percent
from dba_tablespace_usage_metrics t0,
dba_tablespaces t1
where t0.tablespace_name = t1.tablespace_name
order by 1, 5;

Script to get the details from all the data and temp files

select DECODE(2, 1, 'DATAFile',2, 'TEMPFile') FileType, file_id, tablespace_name, 
file_name, bytes/1024/1024 as MB, status
from dba_temp_files 
union all
select DECODE(1, 1, 'DATAFile',2, 'TEMPFile') FileType, file_id, tablespace_name, 
file_name, bytes/1024/1024 as MB, status
from dba_data_files
order by filetype,file_id,tablespace_name
/

Script to get the details from all backups (full, incremental and archivelog)

col STATUS format a9
col hrs format 999.99

select SESSION_KEY, INPUT_TYPE, STATUS, 
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time, 
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time, 
elapsed_seconds/3600 hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key
/

Script to get the details (I/O Throughput, Size) from backups (Incremental)

col STATUS for a10
COL START_TIME FOR A20
COL END_TIME FOR A20
COL TIME_TAKEN_DISPLAY FOR A10
COL INPUTTHROUGHPUT FOR A10
COL OUTPUTTHROUGHPUT FOR A10
col INPUTSIZE for a10
col OUTPUTSIZE for a10

select INPUT_TYPE AS BACKUPTYPE, STATUS,
INPUT_BYTES_DISPLAY AS INPUTSize,INPUT_BYTES_PER_SEC_DISPLAY AS INPUTThroughput,
OUTPUT_BYTES_DISPLAY AS OUTPUTSize,OUTPUT_BYTES_PER_SEC_DISPLAY AS OUTPUTThroughput,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time, 
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time, 
TIME_TAKEN_DISPLAY
from V$RMAN_BACKUP_JOB_DETAILS
where INPUT_TYPE like '%INCR%'
order by session_key
/

Script to monitoring RMAN proccess

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
  AND OPNAME NOT LIKE '%aggregate%'
  AND TOTALWORK != 0
  AND SOFAR <> TOTALWORK
/

Sentence to enable trace on specific session
Note: you need SID & SERIAL#

execute dbms_system.set_sql_trace_in_session( SID, SERIAL#, true);

Script to get Temporary Usage per Tablespace

SET PAGESIZE 60
SET LINESIZE 300
COL TABLESPACE_SIZE FOR 999,999,999,999
COL ALLOCATED_SPACE FOR 999,999,999,999
COL FREE_SPACE FOR 999,999,999,999
 
SELECT *
FROM   dba_temp_free_space
/

Script to show Temporary Tablespace Sort Usage

SELECT 
   A.tablespace_name tablespace, 
   D.mb_total,
   SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
   D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM 
   v$sort_segment A,
(
SELECT 
   B.name, 
   C.block_size, 
   SUM (C.bytes) / 1024 / 1024 mb_total
FROM 
   v$tablespace B, 
   v$tempfile C
WHERE 
   B.ts#= C.ts#
GROUP BY 
   B.name, 
   C.block_size
) D
WHERE 
   A.tablespace_name = D.name
GROUP by 
   A.tablespace_name, 
   D.mb_total
/

Script to show Tables with Next Extent greater than Free Space on Tablespace

select distinct a.owner,b.tablespace_name,a.Table_name,a.next_extent NETABLE,d.fs_max_extent
  from dba_tables a, dba_tablespaces b, (select max(bytes) fs_max_extent, tablespace_name
                                           from sys.dba_free_space
                                          group by tablespace_name) d
   where a.tablespace_name = b.tablespace_name
     and d.tablespace_name = b.tablespace_name
     and a.next_extent > d.fs_max_extent
  order by a.owner,b.tablespace_name,a.table_name;
/

Script to show SQL Text
Note: You have to pass the SID

select substr(username,1,10) usr,
       substr(sid,1,5) sid,
       sql_text
  from v$session,v$sqltext
 where sql_address = address and sid in (&sid) order by sid, piece
/

Undo Management
Check the overall status for undo segments

select tablespace_name, 
       status, 
       sum(blocks) * 8192/1024/1024 MB
from dba_undo_extents
group by tablespace_name, status
/

Undo Blocks per Second

SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) "UNDO_BLOCK_PER_SEC"
FROM v$undostat
/

Optimal Undo Retention/Needed Undo Size for given Database Activity

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MB]",
       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
       ROUND((d.undo_size / (to_number(f.value) * g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]",
       (to_number(e.value) * to_number(f.value) * g.undo_block_per_sec)/(1024*1024) "Needed Undo Size [MB]"
  FROM (
       SELECT SUM(a.bytes) undo_size
          FROM v$datafile a,
               v$tablespace b,
               dba_tablespaces c
         WHERE c.contents = 'UNDO'
           AND c.status = 'ONLINE'
           AND b.name = c.tablespace_name
           AND a.ts# = b.ts#
       ) d,
       v$parameter e,
       v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
              undo_block_per_sec
         FROM v$undostat
       ) g
WHERE e.name = 'undo_retention'
  AND f.name = 'db_block_size'
/

To find Percentage Usage of Undo Tablespace which considers Expired Space

SELECT d.tablespace_name, 
            round(((NVL(f.bytes,0) + (a.maxbytes - a.bytes))/1048576+ u.exp_space),2) as max_free_mb, 
            round(((a.bytes - (NVL(f.bytes,0)+ (1024*1024*u.exp_space)))*100/a.maxbytes),2) used_pct
FROM sys.dba_tablespaces d, 
     (select tablespace_name, 
             sum(bytes) bytes, 
             sum(greatest(maxbytes,bytes)) maxbytes 
             from sys.dba_data_files
             group by tablespace_name) a,
     (select tablespace_name, sum(bytes) bytes from sys.dba_free_space group by tablespace_name) f ,
     (select tablespace_name , 
             sum(blocks)*8/(1024) exp_space 
             from dba_undo_extents 
             where status NOT IN ('ACTIVE','UNEXPIRED')
             group by  tablespace_name) u
WHERE d.tablespace_name = a.tablespace_name(+) 
AND d.tablespace_name = f.tablespace_name(+)
AND d.tablespace_name=u.tablespace_name 
AND d.contents = 'UNDO' 
AND u.tablespace_name = (select UPPER(value) from v$parameter where name = 'undo_tablespace')
/

To grant privileges to a new user based in current user’s privileges (clone privileges)

declare
  v_base_user varchar2(30) := '&BASE_USERNAME';
  v_new_user varchar2(30):= '&NEW_USERNAME';
begin
  --ROLE PRIVS
  for i in ( select 1 ord, 'grant '||granted_role||' to '||v_new_user||
                              case when ADMIN_OPTION = 'YES' then ' WITH ADMIN OPTION' else null end sqltext
              from dba_role_privs 
              where grantee = v_base_user
              union
              select 2 ord, 'alter user '||v_new_user||' default role '||
                      listagg(granted_role,',') within group (order by granted_role) sqltext
              from dba_role_privs
              where grantee = v_base_user
              and default_role = 'YES'
              order by 1)
   loop
    dbms_output.put_line(i.sqltext);
    begin
        execute immediate i.sqltext;
    exception
      when others then
        dbms_output.put_line(sqlerrm);
    end;
  end loop;
  
  --TABLE PRIVS
  for i in (select 'grant '||privilege||' on '||owner||'."'||table_name||'" to '||v_new_user||
               case when grantable = 'YES' then ' with grant option' else null end sqltext
            from dba_tab_privs
            where grantee = v_base_user )
  loop
    dbms_output.put_line(i.sqltext);
    begin
        execute immediate i.sqltext;
    exception
      when others then
        dbms_output.put_line(sqlerrm);
    end;
  end loop;
  
  --SYSTEM PRIVS            
  for i in (select 'grant '||privilege||' to '||v_new_user||
              case when ADMIN_OPTION = 'YES' then ' WITH ADMIN OPTION' else null end sqltext
            from dba_sys_privs
            where grantee = v_base_user )
  loop
    dbms_output.put_line(i.sqltext);
    begin
        execute immediate i.sqltext;
    exception
      when others then
        dbms_output.put_line(sqlerrm);
    end;
  end loop;

exception
  when others then 
      dbms_output.put_line(sqlerrm);

end;

To do a scp using nohup in background typing the password

lusifer@orasvr01:/backups # nohup time scp orasvr02:/backups/TEST/nov1617/*.dmp . > nohup.out 2>&1
&
[1] 27603
lusifer@orasvr01:/backups # fg
nohup time scp orasvr02:/backups/TEST/nov1617/*.dmp . > nohup.out 2>&1
Password:

[1]+  Stopped                 nohup time scp orasvr02:/backups/TEST/nov1617/*.dmp . > nohup.out 2>&1
lusifer@orasvr01:/backups # bg
[1]+ nohup time scp orasvr02:/backups/TEST/nov1617/*.dmp . > nohup.out 2>&1 &
lusifer@orasvr01:/backups #
lusifer@orasvr01:/backups # ps -afe | grep scp
lusifer  28013      1  0 17:06 ?        00:00:00 time scp orasvr02:/backups/TEST/nov1617/*.dmp .
lusifer  28014  28013  5 17:06 ?        00:00:17 scp orasvr02:/backups/TEST/nov1617/*.dmp .
lusifer  28015  28014 34 17:06 ?        00:01:46 /usr/bin/ssh -x -oForwardAgent=no -oPermitLocalCommand=no -oClearAllForwardings=yes -- orasvr02scp -f /backups/TEST/nov1617/*.dmp