GUI, Not Really (Scripts)

I am not a strong proponent for GUI’s. I am not opposed to them exactly; I just think you should know how to operate at the command-line level; then you can use a GUI if you so choose. With that typed; here are some of the scripts I use regularly instead of GUI’s:

Remember the old TOP tool in OEM? Basically this script produces similar output for the most part given you session information.

"top.sql" 93 lines, 2369 characters
-----------------------------------------------------------------
-- Author: Sam Fulkerson --
-- Date : 5/16/1999 --
-- Purpose: Display Oracle session information. --
-- Example: sqlplus / @top.sql --
-- Disclaimer: Use is at your sole risk. All info is provided --
-- "as -is", without any warranty, whether express --
-- or implied, of its accuracy, completeness. --
-----------------------------------------------------------------

create table toptab
as
select sid,sum(value) as memory
from v$sesstat
group by sid
/
set pagesize 132 linesize 2048 trimspool on recsep off space 2 wrap off
column username format a10
column sid format 999999
column serial# format 9999999
column osuser format a10
column memory format 999999999999
column command format a12
column status format a8
column machine format a15
column program format a25
spool top.log
select username
,v$session.sid
,serial#
,osuser
,memory
,status
,decode(command ,1, 'Create Table'
,2, 'Insert'
,3, 'Select'
,4, 'Create Cluster'
,5, 'Alter Cluster'
,6, 'Update'
,7, 'Delete'
,8, 'Drop'
,9, 'Create Index'
,10, 'Drop Index'
,11, 'Alter Index'
,12, 'Drop Table'
,13, '---'
,14, '---'
,15, 'Alter Table'
,16, '---'
,17, 'Grant'
,18, 'Revoke'
,19, 'Create Synonym'
,20, 'Drop Synonym'
,21, 'Create View'
,22, 'Drop View'
,23, '---'
,24, '---'
,25, '---'
,26, 'Lock Table'
,27, 'No Operation'
,28, 'Rename'
,29, 'Comment'
,30, 'Audit'
,31, 'Noaudit'
,32, 'Create External Database'
,33, 'Drop External Database'
,34, 'Create Database'
,35, 'Alter Database'
,36, 'Create Rollback Segment'
,37, 'Alter Rollback Segment'
,38, 'Drop Rollback Segment'
,39, 'Create Tablespace'
,40, 'Ater Tablespace'
,41, 'Drop Tablespace'
,42, 'Alter Session'
,43, 'Alter User'
,44, 'Commit'
,45, 'Rollback'
,46, 'Savepoint'
, 'Unknown') as command
,program
,machine
from v$session, toptab
where v$session.sid = toptab.sid
and v$session.sid is not null
order by status, memory desc, username, osuser
/
spool off
set pagesize 0 feedback off
select ' '
from dual
/
select ' '
from dual
/
select ' NOTE: Alter System Kill Session''SID,Serial#'';' from dual
/
drop table toptab;
set pagesize 23 linesize 80 trimspool off space 2 feedback on wrap on
clear columns


This little script allows you to quickly display all of the objects in tablespace and their size:

"segment.sql" 20 lines, 867 characters
-----------------------------------------------------------------
-- Author: Sam Fulkerson --
-- Date : 2/10/2006 --
-- Purpose: Display all segments for a given tablespace. --
-- Parameter: Tablespace Name (Upper, Lower, Mixed Case OK --
-- Example: sqlplus / @segment.sql tablespace_namne --
-- Disclaimer: Use is at your sole risk. All info is provided --
-- "as -is", without any warranty, whether express --
-- or implied, of its accuracy, completeness. --
-----------------------------------------------------------------

set numf 999,999,999,999,999
column segment_name format a32

select segment_name,sum(bytes)
from dba_segments
where tablespace_name=upper('&tspacename')
group by segment_name
order by sum(bytes) asc
/

This script will allow you to do a string search of the sql area.

"tsql1.sql" 72 lines, 2795 characters
-----------------------------------------------------------------
-- Author: Sam Fulkerson --
-- Date : 1/12/2006 --
-- Purpose: Display information about sql currently in the --
-- sql area. --
-- Parameter: String (i.e. insert) Param. is not case senstive --
-- Example: sqlplus / @tsql1.sql string --
-- Disclaimer: Use is at your sole risk. All info is provided --
-- "as -is", without any warranty, whether express --
-- or implied, of its accuracy, completeness. --
-----------------------------------------------------------------


set linesize 132 pagesize 132
column username format a8
column osuser format a6
column command format a6
column sid format 9999
column serial# format 99999
select a.username,a.osuser,a.sid,a.SERIAL#,substr(sql_text,1,85)
,decode(command ,1, 'CrTab'
,2, 'Ins'
,3, 'Sel'
,4, 'CrClus'
,5, 'AltClus'
,6, 'Up'
,7, 'Del'
,8, 'Drop'
,9, 'CrInd'
,10, 'DrInd'
,11, 'AltInd'
,12, 'DrTab'
,13, '---'
,14, '---'
,15, 'AltTab'
,16, '---'
,17, 'Grant'
,18, 'Revoke'
,19, 'CrSyn'
,20, 'DrSyn'
,21, 'CrVi'
,22, 'DrVi'
,23, '---'
,24, '---'
,25, '---'
,26, 'LkTab'
,27, 'NoOper'
,28, 'Rena'
,29, 'Com'
,30, 'Aud'
,31, 'Noaud'
,32, 'CrExtDb'
,33, 'DrExtDb'
,34, 'CrDb'
,35, 'AltDb'
,36, 'CrRolbSeg'
,37, 'AltRolbSeg'
,38, 'DrRolbSeg'
,39, 'CrTsp'
,40, 'AltTsp'
,41, 'DrTsp'
,42, 'AltSes'
,43, 'AltUsr'
,44, 'Commit'
,45, 'Rolbk'
,46, 'Savpo'
, 'Unknown') as command
from v$session a,v$sqlarea b
where upper(b.sql_text) like upper('%&searchstring.%')
and b.address = a.sql_address
order by a.username,a.osuser,b.sql_text,a.sid,a.serial#
/


This script will display detail information about all the tablespaces:

"tsspace.sql" 35 lines, 1522 characters
-----------------------------------------------------------------
-- Author: Sam Fulkerson --
-- Date : 10/06/2004 --
-- Purpose: Display usage information about tablespaces. --
-- Example: sqlplus / @tsspace.sql --
-- Disclaimer: Use is at your sole risk. All info is provided --
-- "as -is", without any warranty, whether express --
-- or implied, of its accuracy, completeness. --
-----------------------------------------------------------------


clear columns
clear breaks
set linesize 132 trimspool on pagesize 66

column total_bytes format 999,999,999,999,999
column bytes_free format 999,999,999,999,999
column bytes_used format 999,999,999,999,999
column largest_free format 999,999,999,999,999
column bytes format 999,999,999,999,999
column tablespace_name format a28

spool tsspace.log

select a.TABLESPACE_NAME,a.BYTES total_bytes,a.BYTES - b.BYTES bytes_used,b.BYTES bytes_free
,b.largest largest_free,round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
from (select TABLESPACE_NAME,sum(BYTES) BYTES
from dba_data_files
group by TABLESPACE_NAME) a, (select TABLESPACE_NAME,sum(BYTES) BYTES,max(BYTES) largest
from dba_free_space
group by TABLESPACE_NAME) b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
order by ((a.BYTES-b.BYTES)/a.BYTES) desc;

spool off


This script will figure out and display how large a table can be in MB’s for a given tablespace based on the extent size feed into the script:

"./nexte.sql" 12 lines, 678 characters
-----------------------------------------------------------------
-- Author: Sam Fulkerson --
-- Date : 4/28/2005 --
-- Purpose: Used to figure out the largest table that can be --
-- created in a tablespace based on an extent size --
-- Parameters: A: Extent_Size (in MB's) B: Tablespace_Name --
-- Example: sqlplus / @nexte.sql 1 tablespace_name --
-----------------------------------------------------------------

select ltrim(to_char(sum(floor((bytes/1048576)/&&1))*&&1,'999,999,999,999')) as TotMBs
from dba_free_space
where tablespace_name = upper


Here is a nice little script for displaying information about the number of commits, logons, etc.

"tinfo.sql" 11 lines, 520 characters
-----------------------------------------------------------------
-- Author: Sam Fulkerson --
-- Date : 9/15/2004 --
-- Purpose: To display info. such as no. of transactions, --
-- commits, aborts, logons current, cumulative, etc. --
-- Example: sqlplus / @tinfo.sql --
-----------------------------------------------------------------

select value,name
from v$sysstat
where statistic# <7;>
"notdparm.sql" 26 lines, 856 characters
-----------------------------------------------------------------
-- Author: Sam Fulkerson --
-- Date : 9/15/2004 --
-- Purpose: To display non-default init. parm's. --
-- Example: sqlplus / @notdparm.sql --
-----------------------------------------------------------------

spool notdpar.lst
set pagesize 999

ttitle center 'Not Default Init. Parameters' skip 1 -
center '----------------------------' skip 2;
col num format 9999 head 'Parameters'
col name format a45 head 'Name' wrap
col type format 9999 head 'Type'
col value format a45 wrap head 'Value'
col isdefault format a9 head 'Default?'

select name,value
from v$parameter
where isdefault = 'FALSE'
order by name;

ttitle off
spool off

Here is a great little script for producing the ddl required to create an object. The 3 variables must be passed in, in upper case for this to work.

"./getddl.sql" 15 lines, 677 characters
-----------------------------------------------------------------
-- Author: Sam Fulkerson --
-- Date : 8/17/2005 --
-- Purpose: Used to create the ddl for object. --
-- Parameters: A: Object Type (i.e. Table) B: Object Name --
-- C: Object owner (variables must be in upper case --
-- Example: sqlplus / @getddl.sql table table_name owner_name --
-----------------------------------------------------------------

set heading off echo off pagesize 999 long 90000

spool getddl_&&amp;amp;amp;amp;amp;amp;amp;3.&&2.sql
select dbms_metadata.get_ddl('&&amp;amp;amp;amp;amp;amp;amp;1','&&2','&&3')
from dual;
spool off

This script will provide info. on the temporary tablespaces:


"././tmp1.sql" 12 lines, 631 characters------------------------------------------------------------------- Author: Sam Fulkerson ---- Date : 4/26/2006 ---- Purpose: To provide info. on the temporary tablespace. ---- Example: sqlplus / @tmp1.sql -------------------------------------------------------------------
set numf 999,999,999,999,999 linesize 132select tablespace_name, SUM(bytes_used) as Used, SUM(bytes_free) as Free, sum(bytes_used + bytes_free) as Totalfrom v$temp_space_headergroup by tablespace_nameorder by tablespace_name;


This script will display info. about who is using temp:


"././tmp2.sql" 23 lines, 963 characters------------------------------------------------------------------- Author: Sam Fulkerson ---- Date : 4/26/2006 ---- Purpose: To provide info. on who is using temporary ---- tablespace. ---- Parameters: Username-- Example: sqlplus / @tmp2.sql -------------------------------------------------------------------
column tablespace format a8
select a.username, b."USER", b.tablespace, b.contents, b.extents, b.blocksfrom sys.v_$session a, sys.v_$sort_usage bwhere a.saddr = b.session_addr;
select a.osuser, a.process, a.username, a.serial#, sum(b.blocks)*c.value/1024 sort_sizefrom sys.v_$session a, sys.v_$sort_usage b, sys.v_$parameter cwhere a.saddr = b.session_addrand c.name = 'db_block_size'and a.osuser like '&&1'group by a.osuser, a.process, a.username, a.serial#, c.value;