Category Archives: Scripts

space.ksh



#!/bin/ksh
#############################################################################
#                                                                           #
# FILENAME: space.ksh [ORACLE_SID]                                          #
#                                                                           #
# Purpose: Generate a space report for an Oracle instance.                  #
#                                                                           #
#          This report will work for Versions 8i, 9i,10g,11g,12c of Oracle. #
#                                                                           #
# Parameters: Passing the ORACLE_SID is optional.  If left off, it will     #
#             run the space report for the currently-defined SID, otherwise #
#             it will source in the environment for the argument passed.    #
#                                                                           #
#                      *** DO NOT HAND EDIT THIS FILE ***                   #
#      *** IF YOU NEED A CHANGE TO THIS FILE, CONTACT INFRASTRUCTURE ***    #
#############################################################################
#                                                                           #
#  Revision History:                                                        #
#                                                                           #
#  REV   DATE         BY        DESCRIPTION                                 #
#  ---  ------  --------------  ------------------------------------------- #
#                                                                           #
REV=1.2                                                                     #
#############################################################################
#############################################################################################################################################################################################

initialize ()
{
KEEP=30                                 # Number of log files to keep online
LOGDATE=$(date '+%m%d%Y_%H%M%S')
D=$(date '+%Y%m%d')                     # Date extension, No Minutes
DAY=$(date '+%m%d%Y_%H%M')              # Date extension

OUTPUT=$DBA/output/$DB_NAME             # Output directory
LOG=$OUTPUT/space_$LOGDATE.log          # Set up log file

if [[ ! -d $OUTPUT ]] ; then            # Test if standard output dir exists
  check_error 10
fi

if [[ -s $LOG ]] ; then                 # Log file exists
  /bin/rm -f $LOG                       # Clean up
fi

touch $LOG                              # Create the file
chmod 700 $LOG                          # Lock down permissions
}


# ----------------------------------------------------------------------------
check_newer_version()
{
# Check if Newer Version exists
if [[ ${STD:-0} = 0 ]] ; then           # If STD is not set
  check_error 20
fi

CHECK_NEWER_VERSION=$STD/check_newer_version.ksh
if [[ -s $CHECK_NEWER_VERSION ]] ; then
  $CHECK_NEWER_VERSION $1               # Check if newer file exists
else
  check_error 30
fi
}


# ---------------------------------------------------------------------------
check_error()
{
MESSAGE=$1
print " "
case $MESSAGE in
   5) # Note: cannot tee to log as log has not been created yet
      print "\nError: Invalid number of parameters."
      print "  Usage: ddl_extract.ksh <SID>" ;;

   6) # Note: cannot tee to log as log has not been created yet
      print "\nError: Oracle environment is not set."
      print "Possibly due to invalid ORACLE_SID argument passed." ;;

  10) # Note: cannot tee to log as log has not been created yet
      print "\nDirectory does not exist: $OUTPUT"
      print "Error: Oracle standard environment is not set." ;;

  20) # Note: cannot tee to log as log has not been created yet
      print "Environment variable STD... NOT defined."
      print "Please verify Oracle standard environment" ;;

  30) # Note: cannot tee to log as log has not been created yet
      print "Missing standard script - $CHECK_NEWER_VERSION"
      print "Please configure the environment using the Oracle standards" ;;

  40) print "\nError: Could not execute sqlplus" | tee -a $LOG ;;

  50) print "\nError: The file $STD/space.sql does not exist." | tee -a $LOG ;;

  85) print "\nThe environment variable ORA_VER is not set"  | tee -a $LOG
      print "Please verify Oracle standard environment"      | tee -a $LOG ;;

  90) print "\nThe database ($ORACLE_SID) is not running."   | tee -a $LOG ;;

   *) print "\nError: Unknown error code passed."            | tee -a $LOG ;;

esac
print "\nExiting..."
exit 1
}


#------------------------------------------------------------------------------
check_db()
{
# The database will be checked to see if it is running.

CHK_DB="$(ps -ef | grep -w ora_smon_$ORACLE_SID | grep -v grep)"

if [ "NULL$CHK_DB" != "NULL" ]; then      # Value of CHK_DB is set
  print "\nThe database ($ORACLE_SID) is up and running.\n" | tee -a $LOG
else
   check_error 90
fi
}

#-----------------------------------------------------------------------------
get_db_type()
{
# Get DB type i.e. CDB or a non-CDB
if [[ "$ORA_VER" == 12* ]]; then
   OUT=`$ORACLE_HOME/bin/sqlplus -s / as sysdba <<-EOF
        set time off veri off head off feed off
        select 'CDB='||cdb from v\\$database;
EOF
`
   CDB=$(print $OUT | grep CDB= | cut -d= -f2)
fi
}
#------------------------------------------------------------------------------
execute_space()
{
# check of $ORA_VER is set
if [[ "NULL$ORA_VER" = "NULL" ]] ; then
  check_error 85
fi

ORAVERNO=$(print $ORA_VER | awk -F. '{print $1$2}')

case $ORAVERNO in
  81|90|91|92|101|102|111|112|121|122) # Oracle 8i, 9i, 10gR1, 10gR2, 11gR2, 12cR1, 12cR2

    # check if space sql script exists
    if [[ ! -s $STD/space.sql ]] ; then    # Check if file exists
      check_error 50
    fi

    print "Running Space Report...\n"
    CDB=NO
    get_db_type
    if [[ "$CDB" == "YES" ]]; then
          sqlplus -s "/ as sysdba" <<!! | tee -a $LOG
@$STD/cdb_space.sql
!!
    else
        sqlplus -s "/ as sysdba" <<!! | tee -a $LOG
@$STD/space.sql
!!
    fi
    SQL_STATUS=$?
    if [[ $SQL_STATUS -eq 1 ]] ; then
      check_error 40
    fi ;;

  *)
    print "\nSpace Report only supports Oracle 8i or higher\n" | tee -a $LOG
esac
}


# ------------------------------[ Main ]-------------------------------------
# ************
# Main Program
# ************

print "\nspace.ksh Rev. $REV\n"

RETCODE=0

case $1 in
  "") # Assume that current ORACLE_SID is the one that we want
      # to do our space report on
      print " " > /dev/null ;;

   *) # Read in environment
      export NEW_ORACLE_SID=$1; . $HOME/.profile > /dev/null 2>&1 ;;
esac

if [ ! -d $ORACLE_HOME ] ; then
  check_error 6
fi

check_newer_version $0                  # Check for newer version of this file
initialize $1 $2 $3 $4 $5 $6 $7 $8 $9

print "\nStarting at `date`" | tee -a $LOG

check_db                                # Check that database is up

execute_space

print "\nDone at `date`" | tee -a $LOG
print "\nLog is: $LOG"

# Purge file logs
$STD/purge_files.ksh $OUTPUT space $KEEP > /dev/null 2>&1

exit $RETCODE



 

QA Common Library

#!/bin/ksh
############################################################################
# Script Name..: qa_common.ksh
# Description..:
# Author…….: Michael Culp
# Date………: 04/20/2013
# Version……: .08
# Modified By..: Michael Culp
# Date Modified: 11/02/2015
# Comments…..:
# Schema owner.:
# Login User…:
# Run Order….:
# Dependent on.:
# Script type..:
# :
# Functions : next_menu
# : get_db_details – gets information from the v$database table
# : creates variables to use in this script
# Dev Notes : function to update the cluster and add clusters from here
# : will be forthcoming, should be in the QA menu
#—————————————————————————
# Features 2 add :
# This script need to check to see if the TNS entries are there for the APEX database
# Add TNS names entries for the BRAIN1D database if not found
# Also create a .env file dbadmin_apex.conf
############################################################################
# This is the menuing system for the DBA general stuff
# automate a lot of the DBA tasks
# Michael Culp 2010
# Make sure the EOF end is aligned on the 1st column
# If you want the sys password used, create the .syspasswd
############################################################################
# Next line uncomment for debugging purposes only
# set -xv
# Look for SQL Plus
############################################################################

qa_init()
{

echo
echo “QA Init function……”
echo

}

qa_dbrd()
{

echo
echo “QA Dashboard function……”
echo

}

qa_sho_cmd()
{

echo
echo “QA Show commands and aliases…..”
echo

}

tablespace.sql


set head off pagesize 0 linesize 500
(select dbname from v$database) || ',' || tablespace_name || ',' || round(current_usage_gb) space_used_gb || ',' || round(current_free_gb)   || ',' || round(maxgb)
from
(
with tbsname as
(
select tablespace_name from dba_tablespaces
),
auto_ex_no as
(
select tablespace_name,bytes maxbytes
from
(
select tablespace_name
     , sum(bytes) bytes
  from dba_data_files a
 where autoextensible='NO'
 group by tablespace_name
)
),
auto_ex_yes as
(
select tablespace_name,bytes maxbytes
from
(
select  tablespace_name,
sum(maxbytes) bytes
from dba_data_files a
where autoextensible='YES'
group by tablespace_name
)
),
auto_ex_temp_no as
(
select tablespace_name,bytes maxbytes
from
(
select tablespace_name,
sum(bytes) bytes
from dba_temp_files a
where autoextensible='NO'
group by tablespace_name
)
),
auto_ex_temp_yes as
(
select tablespace_name,bytes maxbytes
from
(
select tablespace_name,
sum(maxbytes) bytes
from dba_temp_files a
where autoextensible='YES'
group by tablespace_name
)
)
,
real_tablespac_usage as
(
select tablespace_name,
sum(bytes) bytes
from dba_segments
group by tablespace_name
)
select
tbs.tablespace_name,
case
when tbs.tablespace_name='TEMP' THEN
((round(coalesce(ay.maxbytes,0)+coalesce(an.maxbytes,0)+coalesce(ant.maxbytes,0)+coalesce(ayt.maxbytes,0),3)))/1073741824
when tbs.tablespace_name='SYSTEM' THEN
(round(coalesce(ay.maxbytes,0)+coalesce(an.maxbytes,0),3))/1073741824
when tbs.tablespace_name='SYSAUX' THEN
(round(coalesce(ay.maxbytes,0)+coalesce(an.maxbytes,0),3))/1073741824
when  tbs.tablespace_name like 'UNDO%' THEN
(round(coalesce(ay.maxbytes,0)+coalesce(an.maxbytes,0),3))/1073741824
ELSE
(round((coalesce(an.maxbytes,0)+coalesce(ay.maxbytes,0)+coalesce(ant.maxbytes,0)+coalesce(ayt.maxbytes,0)-coalesce(tu.bytes,0)),3))/1073741824
END AS current_usage_gb,
(round((coalesce(tu.bytes,0)),3))/1073741824 current_free_gb,
(round(coalesce(ay.maxbytes,0)+coalesce(an.maxbytes,0)+coalesce(ant.maxbytes,0)+coalesce(ayt.maxbytes,0),3))/1073741824 maxgb
from tbsname tbs
full join auto_ex_yes ay
on tbs.tablespace_name=ay.tablespace_name
full join auto_ex_no an
on tbs.tablespace_name=an.tablespace_name
full join auto_ex_temp_no ant
on tbs.tablespace_name=ant.tablespace_name
full join auto_ex_temp_yes ayt
on tbs.tablespace_name=ayt.tablespace_name
full join real_tablespac_usage tu
on tbs.tablespace_name=tu.tablespace_name
);

table_degrees.sql


set linesize 200
col owner for a39
col table_name for a39
select (select db_unique_name from v$database) dbname,owner, table_name, degree
from dba_tables
where degree > 1
and owner not in
( 'APPQOSSYS', 'DBA_RO','DBSNMP','DIP',
'FOGLIGHT5X','FOGLIGHT5',
'GEMS_READ','ORACLE','ORACLE_OCM',
'OUTLN','SQLTXADMIN','SQLTXPLAIN','SYS',
'SYSTEM','WMSYS','TRCANLZR','PERFSTAT',
'SITEADM','QUEST','INETDBA','ORACHK_CM','FLOW_FILES','APEX_040200'
)
order by owner;

check_ac.sql adv compression


&nbsp;

set head off
set feedback off
set serveroutput on
whenever sqlerror exit;
set termout on
set scan off
set linesize 128

declare

v_hostname sys.v$instance.host_name%TYPE;
v_dbname sys.v$database.db_unique_name%TYPE;
v_db_role sys.v$database.database_role%TYPE;
ex_custom exception;
rowcnt number := 0;
v_message varchar2(50);
v_installed number := 0;
v_used number := 0;
v_ac_cnt number := 0;

sql_stmt1 varchar2(1000) := 'WITH TABLE_COMPRESSION as (' ||
'        SELECT OWNER' ||
'        FROM DBA_TABLES' ||
'        WHERE COMPRESS_FOR IN (''FOR ALL OPERATIONS'', ''OLTP'', ''ADVANCED'')' ||
'        UNION ALL' ||
'        SELECT TABLE_OWNER AS OWNER' ||
'        FROM DBA_TAB_PARTITIONS' ||
'        WHERE COMPRESS_FOR IN (''FOR ALL OPERATIONS'', ''OLTP'', ''ADVANCED'')' ||
'        UNION ALL' ||
'       SELECT TABLE_OWNER AS OWNER' ||
'       FROM DBA_TAB_SUBPARTITIONS' ||
'       WHERE COMPRESS_FOR IN (''FOR ALL OPERATIONS'', ''OLTP'', ''ADVANCED'')' ||
'       )' ||
'       SELECT' ||
'       (SELECT COUNT(*) FROM V$OPTION WHERE PARAMETER = ''Advanced Compression'' and VALUE = ''TRUE'') as Installed,' ||
'       COALESCE((SELECT COUNT(*) FROM TABLE_COMPRESSION WHERE OWNER NOT IN (''SYSMAN'')),0) as Used' ||
'     FROM DUAL';

-- AdvancedCompression_ByLogArchiveCompression
sql_stmt2 varchar2(1000) := 'WITH LOGARCHIVE_COMPRESSION as (' ||
'    SELECT NAME' ||
'    FROM V$PARAMETER' ||
'    WHERE UPPER(name) LIKE ''%LOG_ARCHIVE_DEST%'' AND UPPER(value) LIKE ''%COMPRESSION=ENABLE%''' ||
'    )' ||
'    SELECT' ||
'    (SELECT COUNT(*) FROM V$OPTION WHERE PARAMETER = ''Advanced Compression'' and VALUE = ''TRUE'') as Installed,' ||
'    COALESCE((SELECT COUNT(*) FROM LOGARCHIVE_COMPRESSION),0) as Used' ||
'    FROM DUAL';

-- advancedCompression_ByFeatureName11gR2
sql_stmt3 varchar2(1000) := 'WITH OPTION_DATA as (' ||
'    SELECT ' ||
'        DECODE(CURRENTLY_USED,''TRUE'',1,0) as USED' ||
'    FROM DBA_FEATURE_USAGE_STATISTICS' ||
'    WHERE NAME IN (''HeapCompression'', ''Backup ZLIB Compression'',' ||
'    ''Backup HIGH Compression'', ''Backup LOW Compression'', ''Backup MEDIUM Compression'',' ||
'    ''SecureFile Compression (user)'', ''SecureFile Deduplication (user)'')' ||
'    )' ||
'    SELECT ' ||
'        (SELECT COUNT(*) FROM V$OPTION WHERE PARAMETER = ''Advanced Compression'' and VALUE = ''TRUE'') as Installed,' ||
'        COALESCE((SELECT SUM(USED) FROM OPTION_DATA),0) as Used' ||
'    FROM DUAL';

--AdvancedCompression_ByFeatureDataPumpExport 11.2
sql_stmt4 varchar2(1000) := 'WITH OPTION_DATA as ( ' ||
'    SELECT  ' ||
'        1 as USED' ||
'    FROM' ||
'        DBA_FEATURE_USAGE_STATISTICS' ||
'    WHERE name = ''Oracle Utility Datapump (Export)''' ||
'        AND FEATURE_INFO IS NOT NULL' ||
'        AND REGEXP_LIKE(lower(to_char(FEATURE_INFO)), ''compression used: [1-9]\d* times'')' ||
'    )' ||
'    SELECT ' ||
'        (SELECT COUNT(*) FROM V$OPTION WHERE PARAMETER = ''Advanced Compression'' and VALUE = ''TRUE'') as Installed,' ||
'        COALESCE((SELECT SUM(USED) FROM OPTION_DATA),0) as Used' ||
'    FROM DUAL';

BEGIN
select host_name into v_hostname from v$instance;
select db_unique_name into v_dbname from v$database;
select decode(database_role, 'PRIMARY', 'P', 'S') into v_db_role from v$database                                       ;

if v_db_role <> 'P' then
raise ex_custom;
end if;

&nbsp;

EXECUTE IMMEDIATE sql_stmt1 INTO v_installed,v_used;

if v_used > 0 then
v_message := v_message || 'TableCompression';
v_ac_cnt := v_ac_cnt+1;
end if;

-- AdvancedCompression_ByLogArchiveCompression
EXECUTE IMMEDIATE sql_stmt2 INTO v_installed,v_used;

if v_used > 0 then
v_message := v_message || 'LogArchiveCompression';
v_ac_cnt := v_ac_cnt+1;
end if;

EXECUTE IMMEDIATE sql_stmt3 INTO v_installed,v_used;

if v_used > 0 then
v_message := v_message || 'BackupCompression';
v_ac_cnt := v_ac_cnt+1;
end if;

EXECUTE IMMEDIATE sql_stmt4 INTO v_installed,v_used;

if v_used > 0 then
v_message := v_message || 'DataPumpCompression';
v_ac_cnt := v_ac_cnt+1;
end if;

if v_ac_cnt > 0 then
DBMS_OUTPUT.put_line(v_hostname ||
':' || v_dbname ||
':' || v_db_role ||
':' || v_message ||
':' || v_used);
else
DBMS_OUTPUT.put_line(v_hostname ||
':' || v_dbname ||
':' || v_db_role ||
':' || 'AdvancedCompression_Not_Used' ||
':' || v_used);
end if;

&nbsp;

exception
when ex_custom then
DBMS_OUTPUT.put_line(v_hostname ||
':' || v_dbname ||
':' || v_db_role ||
':' || 'Standby_Database' ||
':' || 'Standby_Database');
when NO_DATA_FOUND then
DBMS_OUTPUT.put_line(v_hostname ||
':' || v_dbname ||
':' || v_db_role ||
':' || 'No_AC' ||
':' || 'No_AC');

end;
/

Cleanup Script


#!/bin/ksh
############################################################################
# Script Name..: cleanup_audit_files.ksh
# Description..: Script to delete .aud and .xml logs older than 1 day.
# Author.......: 
# Date.........: 02/06/2014
# Version......:
# Modified By..:
# Date Modified:
# Comments.....:
# Schema owner.:
#    alter session set current
# Login User...:
# Run Order....:
# Dependent on.:
# Script type..:
#
############################################################################

date
# Whatever the CRS directory is
find /oracle_crs/product/11.2.0/crs_2/rdbms/audit/ -name "*.aud" -mtime +1 -type f -print -delete

find /oracle/admin/*/adump/ -name "*.aud" -mtime +1 -type f -print -delete
find /oracle/admin/*/adump/ -name "*.xml" -mtime +1 -type f -print -delete

find /ora01/adump/ -name "*.aud" -mtime +1 -type f -print -delete
find /ora01/adump/ -name "*.xml" -mtime +1 -type f -print -delete

find /ora01/audit/ -name "*.aud" -mtime +1 -type f -print -delete
find /ora01/audit/ -name "*.xml" -mtime +1 -type f -print -delete
find /ora01/audit_maint/ -name "*.aud" -mtime +1 -type f -print -delete
find /ora01/audit_maint/ -name "*.xml" -mtime +1 -type f -print -delete
find /ora01/audit_test/ -name "*.aud" -mtime +1 -type f -print -delete
find /ora01/audit_test/ -name "*.xml" -mtime +1 -type f -print -delete
find /oracle_crs/product/11.2.0/crs_2/rdbms/audit/ -name "*.aud.tar.gz" -mtime +1 -type f -print -delete
find /oracle/admin/*/adump/ -name "*.aud.tar.gz" -mtime +1 -type f -print -delete
find /ora01/adump/ -name "*.aud.tar.gz" -mtime +1 -type f -print -delete
find /ora01/audit/ -name "*.aud.tar.gz" -mtime +1 -type f -print -delete
find /ora01/audit_maint/ -name "*.aud.tar.gz" -mtime +1 -type f -print -delete
find /ora01/audit_test/ -name "*.aud.tar.gz" -mtime +1 -type f -print -delete

date

Install SQLT Script


#!/bin/ksh
#
#   Script to install SQLT
#
# -- usage: /oracle/admin/scripts/install_sqlt.ksh <DBNAME>
#
####################################################################################

DBNAME=$1
APP_ID=$2


log=/oracle/admin/scripts/install_sqlt_$DBNAME.log
mkdir -p /ora01/SQLT/$DBNAME
cp /oracle/admin/scripts/sqlt.zip  /ora01/SQLT/$DBNAME

cd /ora01/SQLT/$DBNAME
unzip sqlt.zip
cd /ora01/SQLT/$DBNAME/sqlt/install

sqlplus -s "/ as sysdba" <<EOF
@sqdrop.sql
exit
EOF

sqlplus -s "/ as sysdba" <<EOF
@sqcinternal.sql
exit
EOF


exit

View Segments Script


#!/bin/ksh
############################################################################
# Script Name..:
# Description..:
# Author.......: Michael Culp
# Date.........: 04/ /2013
# Version......:
# Modified By..:
# Date Modified:
# Comments.....: You must have ASM parameters set for this to run properly
#              : A Diskgroup was setup called ACFS_DATA_DG01 to house the
#              : the new filesystems
# Schema owner.:
#    alter session set current
# Login User...:
# Run Order....:
# Dependent on.:
# Script type..:
#
############################################################################

sqlplus -s "/ as sysdba" <<EOF
set lines 150
set pages 150
-- spool <some file name>


-- col fs_name format a25
-- col vol_device format a25

select segment_name
     , blocks
     , extents
  from dba_segments
 where segment_name in ( 'SOMETABLE_01', 'SOMETABLE_02' )
and owner ='MRCODS_OWNER';


-- spool off
EOF

 

sp_who.sql

/* ####################################################################### */
/*                                                                         */
/* FILENAME: sp_who.sql                                                    */
/*                                                                         */
/* Purpose: Shows who is in the database                                   */
/*                                                                         */
/* Parameters: None                                                        */
/*                                                                         */
/*                     *** DO NOT HAND EDIT THIS FILE ***                  */
/* ####################################################################### */
/*                                                                         */
/* Revision History:                                                       */
/*                                                                         */
/* REV   DATE         BY        DESCRIPTION                                */
/* ---  ------  --------------  ------------------------------------------ */
/* 1.0                          INFRASTRUCTURE Initial Relase              */
/*                                                                         */
/*                     *** DO NOT HAND EDIT THIS FILE ***                  */
/*                                                                         */
/* ####################################################################### */
set pages 30 head off feed off

select 'User Status for Oracle Database is ', name from v\$database;
set head on

prompt
prompt All currently running processes
prompt ===============================

set pages 60 lines 132

col sid      format 999 heading "SID"
col Typ      format a3
col process  format a10 heading "Client PID"
col spid     format a10 heading "Server PID"
col username format a10 heading "ORA User"
col osuser   format a8  heading "OS User"
col term     format a7  heading "TTY"
col stat     format a2  heading "ST"
col lw       format a2  heading "LK"
col com      format a8  heading "Command"
col mach     format a10 heading "Host"
col latchw   format a2  heading "LA"

select  s.sid,
        s.serial#,
        nvl(decode(s.sql_hash_value,0,' ','*'),'?')||nvl(s.process,'?') process,
        nvl(p.spid,'?') spid,
        s.username username, nvl(substr(s.osuser,1,8),'?') osuser,
        replace(replace(nvl(substr(s.terminal,1,7),'?'),'PC',''),'tty','') term,
        decode(s.status,'ACTIVE','R','INACTIVE','W','KILLED','Z') stat,
        decode(s.lockwait,NULL,'N','Y') lw,
        nvl(decode(s.command,1,'CRT TABL',2,'INSERT',3,'SELECT',4,'CRT CLST',
                5,'ALT CLST',6,'UPDATE',7,'DELETE',8,'DROP',9,'CRT INDX',
                10,'DRP INDX',11,'ALT INDX',12,'DRP TABL',15,'ALT TABL',
                17,'GRANT',18,'REVOKE',19,'CRT SYNM',20,'DRP SYNM',21,
                'CRT VIEW',22,'DRP VIEW',26,'LOC TBLE',27,'NOTHING',28,
                'RENAME',29,'COMMENT',30,'AUDIT',31,'NOAUDIT',32,'CRT XTDB',
                33,'DRP XTDB',34,'CRT DBSE',35,'ALT DBSE',36,'CRT RSEG',
                37,'ALT RSEG',38,'DRP RSEG',39,'CRT TBSP',40,'ALT TBSP',
                41,'DRP TBSP',42,'ALT SESS',43,'ALT USER',44,'COMMIT',
                45,'ROLLBACK',46,'SVEPOINT','IDLE'),'?') com,
        substr(nvl(s.machine,'?'),1,9) mach,
        decode(p.latchwait,NULL,'N','Y') latchw,
        decode(least(instr(p.program,'TCP'),1),1,'TCP',
        decode(least(instr(p.program,'TNS'),1),1,'TNS',
        decode(least(instr(p.program,'Pip'),1),1,'Pip','   '))) Type
 from v\$session s, v\$process p
where addr = paddr
  and p.background is NULL
union
select  s.sid,
        s.serial#,
        nvl(decode(s.sql_hash_value,0,' ','*'),'?')||nvl(s.process,'?') process,
        nvl(p.spid,'?') spid,
        p.username username, nvl(substr(s.osuser,1,8),'?') osuser,
        replace(replace(substr(p.terminal,1,7),'PC',''),'tty','') term,
        decode(s.status,'ACTIVE','R','INACTIVE','W','KILLED','Z') stat,
        decode(s.lockwait,NULL,'N','Y') lw,
        nvl(decode(s.command,1,'CRT TABL',2,'INSERT',3,'SELECT',4,'CRT CLST',
                5,'ALT CLST',6,'UPDATE',7,'DELETE',8,'DROP',9,'CRT INDX',
                10,'DRP INDX',11,'ALT INDX',12,'DRP TABL',15,'ALT TABL',
                17,'GRANT',18,'REVOKE',19,'CRT SYNM',20,'DRP SYNM',21,
                'CRT VIEW',22,'DRP VIEW',26,'LOC TBLE',27,'NOTHING',28,
                'RENAME',29,'COMMENT',30,'AUDIT',31,'NOAUDIT',32,'CRT XTDB',
                33,'DRP XTDB',34,'CRT DBSE',35,'ALT DBSE',36,'CRT RSEG',
                37,'ALT RSEG',38,'DRP RSEG',39,'CRT TBSP',40,'ALT TBSP',
                41,'DRP TBSP',42,'ALT SESS',43,'ALT USER',44,'COMMIT',
                45,'ROLLBACK',46,'SVEPOINT'),'?') com,
        substr(nvl(s.machine,'?'),1,9) mach,
        decode(p.latchwait,NULL,'N','Y') latchw,
        decode(least(instr(p.program,'TCP'),1),1,'TCP',
        decode(least(instr(p.program,'TNS'),1),1,'TNS',
        decode(least(instr(p.program,'Pip'),1),1,'Pip','   '))) Type
from v\$session s, v\$process p
where addr = paddr
and p.background is not NULL
order by 1;

prompt
prompt Rollback Segment Usage
prompt ======================

set lines 150

col name    format a10
col usn     format 99
col sid     format 999
col xacts   format 9999
col extents format 999999
col extends format 99999
col waits   format 9999
col wraps   format 9999999
col shrnk   format 999999
col name    format a12
col osusr   format a6
col status  format a9
col writes  format 9999999999999

select r.usn, r.name, s.osuser osusr,
       s.sid, x.extents, x.xacts,
       x.extends, x.waits, x.shrinks shrnk,
       x.wraps, x.writes, x.status
from   v\$rollstat x,
       v\$rollname r,
       v\$session s,
       v\$transaction t
where  t.addr = s.taddr (+)
  and  x.usn (+) = r.usn
  and  t.xidusn (+) = r.usn
order by r.usn;
exit;

longops.sql


-- -----------------------------------------------------------------------------------
-- File Name    : https://oracle-base.com/dba/monitoring/longops.sql
-- Author       : Tim Hall
-- Description  : Displays information on all long operations.
-- Requirements : Access to the V$ views.
-- Call Syntax  : @longops
-- Last Modified: 03/07/2003
-- -----------------------------------------------------------------------------------

COLUMN sid FORMAT 999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A30
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10

SELECT s.sid
,      s.serial#
,      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 v\$session s,
       v\$session_longops sl
 WHERE s.sid = sl.sid
   AND s.serial# = sl.serial#;