largest segment (table or index) in each Tablespace

column tablespace format a18
column segment format a23
select a.ts tablespace,min(a.seg) || decode(count(*),1,'(none)’,2,’ ‘,’ +’)
segment,
to_char(sum(a.segb)/decode(count(*),1,1,count(*)-1),’9,999,999,999′)
” SEG BYTES”,
to_char(sum(a.tsb),’9,999,999,999,999′) ” TS BYTES”,
to_char(sum(a.segb)/decode(count(*),1,1,count(*)-1)/decode(sum(a.tsb),0,1,
sum(a.tsb))*100,’990.99′)
” PCT”
from (
select tablespace_name ts,segment_name seg,
sum(bytes) segb,000000000000 tsb
from dba_segments d
where tablespace_name not in (‘SYSTEM’,’RBS’,’TEMP’,’TOOLS’,’TSMSYS’,’DBSNMP’,’OUTLN’,’UNDOTBS’)
group by tablespace_name,segment_name
having sum(bytes) = (
select max(sum(bytes))
from dba_segments c
where c.tablespace_name = d.tablespace_name
group by c.segment_name)
union all
select tablespace_name,’~’,0,sum(bytes)
from dba_segments
where tablespace_name not in (‘SYSTEM’,’RBS’,’TEMP’,’TOOLS’,’TSMSYS’,’DBSNMP’,’OUTLN’,’UNDOTBS’)
group by tablespace_name) a
group by a.ts;
set linesize 80 termout on heading on pagesize 24 timing on feedback 6
set termout on verify on echo on showmode both

Find the largest segment (table or index) for each owner

column owner format a18 trunc
column segment format a23 trunc
set line 5000
select a.ow owner,min(a.seg) || decode(count(*),1,'(none)’,2,’ ‘,’ +’)
segment,
to_char(sum(a.segb)/decode(count(*),1,1,count(*)-1),’9,999,999,999′)
” SEG BYTES”,
to_char(sum(a.owb),’9,999,999,999,999′) ” OWNER BYTES”,
to_char(sum(a.segb)/decode(count(*),1,1,count(*)-1)/decode(sum(a.owb),0,1,
sum(a.owb))*100,’990.99′)
” PCT”
from (
select owner ow,segment_name seg,
sum(bytes) segb,000000000000 owb
from dba_segments d
where owner not in (‘SYS’,’SYSTEM’,’SCOTT’,’TSMSYS’,’DBSNMP’,’OUTLN’)
group by owner,segment_name
having sum(bytes) = (
select max(sum(bytes))
from dba_segments c
where c.owner = d.owner
group by c.segment_name)
union all
select owner,’~’,0,sum(bytes)
from dba_segments
where owner not in (‘SYS’,’SYSTEM’,’SCOTT’,’TSMSYS’,’DBSNMP’,’OUTLN’)
group by owner) a
group by a.ow;
set linesize 80 termout on heading on pagesize 24 timing on feedback 6
set termout on verify on echo on showmode both

Undo information on database sessions

SET LINESIZE 200

COLUMN username FORMAT A15

SELECT s.username,
s.sid,
s.serial#,
t.used_ublk,
t.used_urec,
rs.segment_name,
r.rssize,
r.status
FROM v$transaction t,
v$session s,
v$rollstat r,
dba_rollback_segs rs
WHERE s.saddr = t.ses_addr
AND t.xidusn = r.usn
AND rs.segment_id = t.xidusn
ORDER BY t.used_ublk DESC;

Autosys Scheduler Cheatsheet

SUB-COMMANDS

insert_job: Saves a brand-new job to the database
update_job: PERMANENTLY changes the definition of a pre-
existing job
override_job: TEMPORARILY changes the definition of a pre-
existing job
delete_job: Deletes a single job from the database
delete_box: Deletes a box as well as all the contents

ATTRIBUTES

job_type: b, c, f (command is default)
machine: Name of machine (or IP) where job is to be
run
command: Command to be executed (.exe, .sh, .bat)
watch_file: File being monitored by file watcher
box_name: Used to nest a job inside a box
std_out_file: Redirects output from a command job to a text
file
std_err_file: Redirects error messages to a text file
condition: Used to structure job dependencies (success,
failure, terminated, done, notrunning, exit code,
and value)
min_run_alarm: Causes job to issue an alarm if it finishes too
quickly
max_run_alarm: Causes a job to issue an alarm if it runs too
long
alarm_if_fail: States whether a job will issue an alarm if it
fails
date_conditions: Toggle which must be set in order for date/time
attributes to be recognized by AutoSys
run_calendar: Specifies the calendar a job will run off of
[cannot be used with days_of_week]
days_of_week: Specifies exact days a job will run [cannot be used with run_calendar]
start_times: Exact time each day a job will run [cannot be
used with start_mins]
start_mins: Minutes after each hour a job will execute
[cannot be used with start_times]
exclude_calendar: Specifies a calendar with days specified upon
which a job will not execute
watch_interval: Steady state for file watchers
watch_file_min_size: Minimum size a file must be before a file watcher
can evaluate to success
box_success: Specifies custom success condition for a box
box_failure: Specifies custom failure condition for a box
max_exit_success: Specifies maximum exit code which will be
evaluated as a success
box_terminator: “If I fail, I kill the box I’m in”
job_terminator: “If the box I’m in fails or gets killed, I kill
myself”
term_run_time: “I kill myself after this many minutes”
chk_files: Resource check that verifies a minimum amount
of file space is available before running job
heartbeat_interval: Specifies frequency in minutes at which job’s
command is expected to issue a “heartbeat”
profile: Specifies a file which contains custom
environment variables to be used by a single job
std_in_file: Specifies a file to be used as input for a job
n_retrys: Specifies how many times a job should attempt to
re-run itself after an application-level failure
timezone: Specifies which timezone a job should use when
running
auto_delete: Specifies how many HOURS after successful
completion a job should delete itself from the
database
auto_hold: Used only with jobs in boxes. When the box goes
to a RUNNING state, the job will automatically
go ON_HOLD
permission: Extend edit or execute permissions to others
run_window: Specifies when a job can start
avg_runtime: *Only accessible through JIL* Specifies how long
a job takes to run, on average

SAP Transation Codes

Following are some of the SAP transaction codes that can be used to view the database details in SAP GUI.
DataBase SAP Transaction Codes

NMON Analyzer: AIX server performance stats

This is a useful application provided by IBM

NMON_Analyser is designed to complement NMON (Nigel’s Monitor) in analysing and reporting performance problems; it produces graphs for virtually all sections of output created using the “spreadsheet output” mode of NMON as well as doing some additional analyses for ESS, EMC and FAStT subsystems. It will also work with files produced by topasout and with other tools that produce data in “NMON” format. It is written in VBA for Excel and will work with the 2002 or 2003 editions.

NMON UserGuide v33

NMON Excel File