Top for Oracle (oratop)

Oratop provides a unix-top like display for oracle. Check out the following screenshot...

System: bloo System time: 18:25:50 16-APR-07

Instance: scr10 Inst start-up time: 08:50:17 04-APR-07

18 sessions: 1 incactive, 17 active, 15 system, 0 killed

SID/SERIAL# USERNAME OS USER LASTCALL STATE MACHINE COMMAND

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

152,3 REP_OWNER oracle 18:25:47 active bloo n/a

158,26924 SYS oracle 18:25:50 active bloo sqlplus@bloo (T

173,26831 ANDY oracle 18:23:21 inactive bloo TOAD

Press CTRL-c to exit

If you want to install oratop, there are two parts to it; a stored procedure and a shell script.

First create the stored procedure by running the following code in sqlplus while logged in as sys (just
cut&paste it into sqlplus):

oratop_proc.sql

Next, using your favourite text editor, create a file called oratop. Ideally you should place this file in a
directory on your path such as /home/oracle/bin or /usr/local/bin. Anyway, cut and paste the following
into it:

#!/bin/sh

if [ -n "$ORACLE_SID" ]; then

while [ 1 ]; do

clear

hsize=`stty size|cut -f2 -d" "`

vsize=`stty size|cut -f1 -d" "`

sqlplus -S "/ as sysdba" << SQL

set feedback off

set lines 200

set serveroutput on size 9999

exec shutabortcom_oratop(${hsize}, ${vsize})

SQL

echo "Press CTRL-c to exit"

sleep 5

done

else

echo ORACLE_SID not set

fi

Save the file and make it executable with the following command:

chmod 700 oratop

To run it type ./oratop or just oratop if you put it on the path.

aoe

#!/usr/bin/ksh
#
=============================================================================
# File Name: aoe
#
# Function: Configures the oracle environment
#
# Usage: . aoe [SID] [-]
#
# Remarks: aoe is a simple front end to the oracle oraenv utility.
# If a SID is not specified at the command line, a menu will be
# displayed, listing all available entries in the oratab.
# Once a selection has been made oraenv is called and if one exists
# an optional config script will also be executed.
# The optional config script must be located in
# $optional_script_dir and be named as follows: aoe_<SID>
# If - is specified as the last parameter, aoe will
# launch sqlplus "/ as sysdba".
#
# Notes: It is worth adding the following to you .profile (or
.bash_profile):
# alias aoe=". aoe"
# Doing so removes the need to prefix the command with ". "
#
# Change History:
#
# Date Ver Author Change
# ======== ==== ===============
===============================================
# 15/06/06 1.0 Andy Barry Initial version.
# 30/08/06 1.1 Andy Barry Added the option to run sqlplus "/ as sysdba"
# when "-" is specified as the last parameter.
oratab=/etc/oratab
oraenv=/usr/local/bin/oraenv
optional_script_dir=/home/oracle/bin

# work out which echo options need to be used

c=`echo "\c"`; [ "$c" = "" ] && { c="\c"; n=""; } || { c=""; n="-n"; }

if [ $# -eq 0 ] || [[ $# -eq 1 && $1 = "-" ]]; then

# if no sid has been specified, display the menu

echo "Advanced OraEnv"

echo "==============="
item=1
for database in $( grep -v "#" ${oratab}|grep -v "*"|cut -f1 -d:|awk '$0!
~/^$/ {print $0}' ); do
echo $n "${item}) $database${c}"
# if the entry matches the current ORALCE_SID setting mark it with a '*'
if [ "${database}" == "${ORACLE_SID}" ]; then
echo " *"
else
echo
fi
item=$((item + 1))
done

# get the users selection

echo
echo $n "Choose: $c"
read selection

# find the correct sid for the selection

item=1
ORACLE_SID=q1w2e3r4t5y6u7i8o9p
for database in $( grep -v "#" ${oratab}|grep -v "*"|cut -f1 -d:|awk '$0!
~/^$/ {print $0}' ); do
if [ $item -eq $selection ];then
ORACLE_SID=$database
fi
item=$((item + 1))
done
else
# if a sid was specified make sure it's in the oratab
if [ `grep -v "#" ${oratab}|grep -v "*"|grep "${1}:"|wc -l` -eq 1 ];then
ORACLE_SID=$1
else
ORACLE_SID=q1w2e3r4t5y6u7i8o9p
fi
fi

# set the oracle environment

if [ $ORACLE_SID != "q1w2e3r4t5y6u7i8o9p" ]; then
export ORAENV_ASK=NO
. $oraenv
export ORAENV_ASK=YES
echo "ORACLE_SID : ${ORACLE_SID}"
echo "ORACLE_HOME : ${ORACLE_HOME}"
echo "LD_LIBRARY_PATH : ${LD_LIBRARY_PATH}"
echo "TNS_ADMIN : ${TNS_ADMIN}"

# look for and, if found, run the optional config script

if [ -a "${optional_script_dir}/aoe_${ORACLE_SID}" ]; then
echo "Executing optional config file..."
. ${optional_script_dir}/aoe_${ORACLE_SID}
echo "Completed execution of optional config file."
fi

# optionaly run sqlplus "/ as sysdba"

if [[ $# -eq 1 && $1 = "-" ]] || [[ $# -eq 2 && $2 = "-" ]]; then
sqlplus "/ as sysdba"
fi

else
echo "aoe error: Invalid SID or selection"
unset ORACLE_SID
unset ORACLE_HOME
fi

table_conf.sql

-- table_conf.sql
-- Andy Barry
-- 17/06/07

set lines 100 pages 999

set verify off
set feedback off

undefine owner
undefine table

accept owner prompt 'Enter owner:'

accept table prompt 'Enter table name:'

prompt
prompt ======= General info ==============================================

col last_analyzed format a15

select to_char(last_analyzed, 'dd/mm/yy hh24:mi') last_analyzed
, num_rows
from dba_tables
where owner = '&&owner'
and table_name = '&&table'
/

col table_name format a20

select segment_name table_name
, sum(ceil(bytes / 1024 / 1024)) "SIZE_MB"
from dba_segments
where segment_name like '&&table'
and segment_type in ('TABLE','TABLE PARTITION')
group by segment_name
/

select tablespace_name
from dba_tables
where owner = '&&owner'
and table_name = '&&table'
and tablespace_name is not null
/

col tablespace_name format a20

col num_rows format 999,999,999
select p.partition_name
, p.tablespace_name
, p.num_rows
, ceil(s.bytes / 1024 / 1204) mb
from dba_tab_partitions p
, dba_segments s
where p.table_owner = s.owner
and p.partition_name = s.partition_name
and p.table_name = s.segment_name
and p.table_owner = '&owner'
and p.table_name = '&table'
order by partition_position
/

prompt
prompt ======= Indexes ===================================================

select i.index_name
, nvl(i.tablespace_name, '(partitioned)') tablespace_name
, ceil(s.bytes / 1048576) "Size MB"
from dba_indexes i
, dba_segments s
where i.index_name = s.segment_name
and i.owner = '&owner'
and table_name like '&table'
order by 2, 1
/

select ti.index_name
, pi.partition_name
, pi.tablespace_name
, pi.status
from dba_indexes ti , dba_ind_partitions pi
where ti.partitioned = 'YES'
and ti.table_name = '&&table'
and pi.index_owner = '&&owner'
and pi.index_owner = ti.owner
and pi.index_name = ti.index_name
order by 1, pi.partition_position
/

prompt
prompt ======= Constraints ===============================================

col type format a10

col cons_name format a30
select decode(constraint_type,
'C', 'Check',
'O', 'R/O View',
'P', 'Primary',
'R', 'Foreign',
'U', 'Unique',
'V', 'Check view') type
, constraint_name cons_name
, status
, last_change
from dba_constraints where owner like '&&owner'
and table_name like '&&table' order by 1
/

prompt
prompt ======= Triggers ==================================================

select trigger_name , trigger_type , status

from dba_triggers where owner = '&&owner' and table_name = '&&table'
order by status, trigger_name
/

undefine owner
undefine table
set verify on feedback on

user_conf.sql

-- user_conf.sql
-- Andy Barry
-- 20/02/06

set lines 100 pages 999

set verify off
set feedback off

undefine user

accept userid prompt 'Enter username:'

select username
, default_tablespace , temporary_tablespace
from dba_users where username = '&userid'
/
select tablespace_name
, decode(max_bytes, -1, 'unlimited'
, ceil(max_bytes / 1024 / 1024) || 'M' ) "QUOTA"
from dba_ts_quotas where username = upper('&&userid')
/

select granted_role || ' ' || decode(admin_option, 'NO', '', 'YES', 'with

admin option') "ROLE"
from dba_role_privs where grantee = upper('&&userid')
/

select privilege || ' ' || decode(admin_option, 'NO', '', 'YES', 'with admin
option') "PRIV"
from dba_sys_privs where grantee = upper('&&userid')
/

undefine user
set verify on
set feedback on

user_clone.sql

-- user_clone.sql
-- Andy Barry
-- 20/02/06

set lines 999 pages 999

set verify off
set feedback off
set heading off

select username
from dba_users
order by username
/

undefine user

accept userid prompt 'Enter user to clone: '

accept newuser prompt 'Enter new username: '
accept passwd prompt 'Enter new password: '

select username
, created
from dba_users
where lower(username) = lower('&newuser')
/

accept poo prompt 'Continue? (ctrl-c to exit)'

spool /tmp/user_clone_tmp.sql

select 'create user ' || '&newuser' ||

' identified by ' || '&passwd' ||
' default tablespace ' || default_tablespace ||
' temporary tablespace ' || temporary_tablespace || ';' "user"
from dba_users where username = '&userid'
/

select 'alter user &newuser quota '||

decode(max_bytes, -1, 'unlimited'
, ceil(max_bytes / 1024 / 1024) || 'M') ||
' on ' || tablespace_name || ';'
from dba_ts_quotas where username = '&&userid'
/

select 'grant ' ||granted_role || ' to &newuser' ||

decode(admin_option, 'NO', ';', 'YES', ' with admin option;') "ROLE"
from dba_role_privs where grantee = '&&userid'
/

select 'grant ' || privilege || ' to &newuser' ||

decode(admin_option, 'NO', ';', 'YES', ' with admin option;') "PRIV"
from dba_sys_privs where grantee = '&&userid'
/

spool off

undefine user

set verify on
set feedback on
set heading on

@/tmp/user_clone_tmp.sql

!rm /tmp/user_clone_tmp.sql

user_drop_objs.sql

-- drop_userobjs.sql
--
-- Andy Barry
-- 26/07/05
--
set trimspool on wrap off
set heading off feedback off
set verify off
set pages 1000 lines 1000

select '&&username' from dual;

spool /tmp/dropuserobjs_&username..sql

select 'alter table ' || owner || '.' || table_name || ' drop primary key
cascade;'
from dba_constraints where owner = trim(upper('&username'))
and constraint_type = 'P'
/
select 'alter table ' || owner || '.' || table_name || ' drop constraint ' ||
constraint_name || ' cascade;'
from dba_constraints where owner = trim(upper('&username'))
and constraint_type = 'U'
/

select 'drop table ' || owner || '.' || table_name || ';'

from dba_tables where owner = trim(upper('&username'))
/

select 'drop ' || object_type || ' '|| owner || '.' || object_name || ';'
from dba_objects where owner = trim(upper('&username'))
and object_type not in ('TABLE', 'INDEX', 'DATABASE LINK', 'JAVA CLASS', 'JAVA
RESOURCE')
/

-- drop java objects

select 'drop ' || object_type || ' '|| owner || '."' || object_name || '";'
from dba_objects where owner = trim(upper('&username'))
and object_type in ('JAVA CLASS', 'JAVA RESOURCE')
/

spool off

set heading on feedback on

@/tmp/dropuserobjs_&username

user_truncate_objs.sql

-- truncate_user.sql
--
-- Andy Barry
-- 26/07/05
--
set trimspool on wrap off
set heading off feedback off
set verify off
set pages 1000 lines 1000

select '&&username' from dual;

spool /tmp/truncate_&username..sql

select 'alter table ' || owner || '.' || table_name || ' drop primary key
cascade;'
from dba_constraints where owner = trim(upper('&username'))
and constraint_type = 'P'
/

select 'alter table ' || owner || '.' || table_name || ' drop constraint ' ||
constraint_name || ' cascade;'
from dba_constraints where owner = trim(upper('&username'))
and constraint_type = 'U'
/

select 'truncate table ' || owner || '.' || table_name || ' reuse storage;'
from dba_tables where owner = trim(upper('&username'))
/

select 'drop sequence ' || sequence_owner || '.' || sequence_name || ';'

from dba_sequences where sequence_owner = trim(upper('&username'))
/

spool off

set heading on feedback on

@/tmp/truncate_&username

login.sql

define gname = 'not connected'

column global_name new_value gname
set pages 999
set termout off
select lower(user) || '@' || lower(substr(global_name, 1, instr(global_name,
'.', 1) - 1)) global_name from global_name;
set termout on
set sqlprompt '&&gname> '

cr_hot_backup.sql

set lines 999 pages 999

set verify off
set feedback off
set heading off

spool begin_backup.sql

select 'alter tablespace ' || tablespace_name || ' begin backup;' tsbb

from dba_tablespaces where contents != 'TEMPORARY'
order by tablespace_name
/
spool off

spool end_backup.sql

select 'alter tablespace ' || tablespace_name || ' end backup;' tseb

from dba_tablespaces where contents != 'TEMPORARY'
order by tablespace_name
/
spool off