Difference between revisions of "Handy scripts"

From dbawiki
Jump to: navigation, search
(→‎Resize the online redo logfiles)
(→‎Increase the max size of a tempfile)
Line 956: Line 956:
 
<pre>
 
<pre>
 
alter database tempfile '/oracle/ENDP1/oradata90/temp01.dbf' autoextend on maxsize unlimited;
 
alter database tempfile '/oracle/ENDP1/oradata90/temp01.dbf' autoextend on maxsize unlimited;
 +
</pre>
 +
===Find out what processes are using the temp tablespace===
 +
Temporary tablespace is used for sorting query results. Find them with this.
 +
<pre>
 +
SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
 +
a.username,a.osuser, a.status
 +
FROM v$session a,v$sort_usage b
 +
WHERE a.saddr = b.session_addr;
 
</pre>
 
</pre>
  

Revision as of 22:51, 15 December 2015

Contents

SQL

Run a command on all databases in oratab

#!/bin/bash
# ==============================================================================
# Name         : all_db_do
# Description  : Loops over all the databases in oratab and executes the
#                specified SQL command - be careful!
#
# Parameters   : -v flag for verbose
#                SQL - command to be executed (in quotes)
#
# Notes        : none
#
# Modification History
# ====================
# When      Who               What
# ========= ================= ==================================================
# 14-JAN-13 Stuart Barkley    Created
# 25-FEB-13 Stuart Barkley    Adapt for Solaris
# 21-MAR-13 Stuart Barkley    Check db is running first
# 29-APR-13 Stuart Barkley    Added verbose flag processing
# ==============================================================================

PROGNAME=`basename $0`
OS=`uname`

AWK=`which awk`
GREP=`which grep`
[[ "${OS}" == "SunOS" ]] && AWK=/usr/xpg4/bin/awk
[[ "${OS}" == "SunOS" ]] && GREP=/usr/xpg4/bin/grep

[[ ! -r /etc/oratab ]] && echo "Oracle not installed or Solaris! If Solaris please create link from /var/opt/oracle/oratab to /etc" && exit

# -------------------------
# get the arguments, if any
# -------------------------
unset VERBOSE
while getopts "v" flag
do
    case "$flag" in
    v) VERBOSE=1;;
    esac
done
shift $((OPTIND-1))

[[ -z $1 ]] && echo "Please enter an SQL command to execute" && exit
SQL=$1

export ORAENV_ASK=NO

for db in `${AWK} -F: 'NF && $1 !~/[#*]/ {print $1}'  /etc/oratab`
do
    if ( [[ $1 != "startup" ]] && [[ `ps -ef | ${GREP} -c -E [o]ra_pmon_${db}` -eq 0 ]] ); then
        [[ ! -z "$VERBOSE" ]] && echo "$db is not running, skipping..."
        continue
    fi
    [[ ! -z "$VERBOSE" ]] && echo "vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv"
    [[ ! -z "$VERBOSE" ]] && echo $db
    ORACLE_SID=$db
    . oraenv >/dev/null
    sqlplus -s "/ as sysdba" <<EOSQL
define SID=$ORACLE_SID
col comp_name format a50
col value for a40
set linesize 1000
set pagesize 100
$SQL
EOSQL
    [[ ! -z "$VERBOSE" ]] && echo "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^"
done

all_db_do "create or replace directory data_pump_dir as '/oracle/export/&SID';"

all_db_do "archive log list"

all_db_do "shutdown immediate"

all_db_do 'alter system set log_archive_dest_1="location=/oracle/arch/&SID" scope=both;'

List invalid objects

set lines 200
set pages 200
col obj format a40
select owner||'.'||object_name obj
,      object_type
from   dba_objects
where  1=1
and    status = 'INVALID';

Recompile all invalid objects

@?/rdbms/admin/utlrp.sql

See how much space is left in the flash recovery area (FRA)

select name
,      to_char (space_limit, '999,999,999,999') as space_limit
,      to_char (space_limit - space_used + space_reclaimable
,               '999,999,999,999')
           as space_available
,      round ( (space_used - space_reclaimable) / space_limit * 100, 1)
           as pct_full
from   v$recovery_file_dest;

or 

# ==============================================
# Check FRA to see how much space is reclaimable
# ==============================================
fra_reclaim_check() {

LOGFILE=${RMAN_LogDir}/${ORACLE_SID}_`date '+%Y%m'`_${V_SCRIPT}.log
sqlplus "/ as sysdba" <<EOSQL > /tmp/results.$$
set vefify off echo off heading off newpage none linesize 1000 echo off
select 'Filesystem:'||name||
       '; Total(Gb):'||round((space_limit/1073741824),2) ||
       '; Reclaimable(Gb):'||round((space_reclaimable/1073741824),2) ||
       '; Reclaimable(%):'||round( ((space_reclaimable/1073741824)/(space_limit/1073741824)*100),2 )
from   v$recovery_file_dest
/
EOSQL
cat /tmp/results.$$ >> $LOGFILE
rm /tmp/results.$$

}

DBA privs tables

DBA_AQ_AGENT_PRIVS
DBA_COL_PRIVS
DBA_PRIV_AUDIT_OPTS
DBA_REPGROUP_PRIVILEGES
DBA_ROLE_PRIVS
DBA_RSRC_CONSUMER_GROUP_PRIVS
DBA_RSRC_MANAGER_SYSTEM_PRIVS
DBA_SYS_PRIVS
DBA_TAB_PRIVS
DBA_WM_SYS_PRIVS
DBA_WORKSPACE_PRIVS

Schedule a shell job

begin
    dbms_scheduler.create_program ( program_name   => 'myshelljob'
                                  , program_action => '/home/oracle/scripts/shell.sh'
                                  , program_type   => 'EXECUTABLE'
                                  , comments       => 'Run a shell'
                                  , enabled        => TRUE
                                  );
end;
/

Start the job

begin
    dbms_sheduler.create_job ( job_name     => 'myshelljob'
                             , program_name => 'myshelljob'
                             , start_date   =>
                             );
/

What statement is a user running?

select a.sid
,      a.serial#
,      b.sql_text
from   v$session a
,      v$sqlarea b
where  a.sql_address = b.address
and    a.username    = '&username'
/

See what statements all users are running

Useful to see what is happening when process limit is reached 

set lines 300 pages 1000

col username for a10
col command for 999 head "CMD"
col lockwait for a10
col status for a8
col schemaname for a12
col osuser for a10
col process for a10
col program for a20
col machine for a10
col action for a10
col module for a10
col identifier for a10
col event for a27
col state for a8
col service_name for a10
col serial# for 999999

select a.username
,      a.command
,      a.lockwait
,      a.status
,      a.schemaname
,      a.osuser
,      a.process
,      a.machine
,      a.program
,      a.type
--,      a.sql_exec_start
--,      a.plsql_object_id
--,      a.plsql_subprogram_id
--,      a.module
--,      a.action
,      a.logon_time
,      a.event
,      a.wait_time
,      a.seconds_in_wait
,      a.state
--,      a.service_name
,      a.serial#
,      b.sql_text
from   v$session a
,      v$sqlarea b
where  a.sql_address = b.address
order  by a.logon_time desc
/


set lines 80

Pretty longops listing

set lines 2000 pages 1000
col opname for a34 head "Job"
col message for a60
col perc_done for a10 head "Done"
col started for a18
col killer for a15
col mins_busy head "Mins busy"
col mins_left head "Mins left"
select sid||','||serial#                       killer
,      opname
,      message
,      round ((sofar/totalwork),4)*100||'%'    perc_done
,      to_char(start_time,'DD-MON-YY HH24:MI') started
,      floor(elapsed_seconds/60)               mins_busy
,      ceil(time_remaining/60)                 mins_left
from   v$session_longops
where  1=1
and    sofar     != totalwork
and    totalwork != 0
and    opname    like 'RMAN%'
/

select * from v$session where module='Data Pump Worker'

or attach to the impdp job and it shows percentage done. 

Get the SID from v$session_longops and plug it into v$session to check the SQL command details.

Current Running SQLs
--------------------
set pages 1000 lines 2000
col program  format a40
col sql_text format a130

select s.sid
,      s.status
,      s.last_call_et
,      s.program
,      sa.sql_id
,      sa.sql_text
from   v$session s
,      v$sqlarea sa
where  s.sql_id = sa.sql_id
and    s.sid = '&sid'
/ 

set pages 1000 lines 2000
col USERNAME for a10
col OSUSER   for a10
col MACHINE  for a10

select s.sid
,      s.serial#
,      p.spid
,      s.username
,      s.osuser
,      s.status
,      s.process fg_pid
,      s.longon_time
,      s.machine
,      p.spid bg_pid
from   gv$session s
,      gv$process p
where  s.addr = p.addr
and    s.sid  = '&sid'
/

$ps -ef | grep <spid>

set pages 1000 lines 2000
SELECT INST_ID
,      SID
,      SERIAL#
,      SQL_ID
,      USERNAME
,      PROGRAM
,      MACHINE
,      SERVICE_NAME
FROM   GV$SESSION
WHERE  SID IN ('<SID_NO.1>','<SID_NO.2>')
/
Active Running SQLs
--------------------
set pages 1000 lines 2000
col SPID     for a10
col PROGRAM  for a15
col OSUSER   for a10
col ACTION   for a10
col EVENT    for a25
col SQL_TEXT for a25
col MACHINE  for a10
col P1TEXT   for a10 
col P2TEXT   for a10
col P3TEXT   for a10  
SELECT b.sid
,      b.serial#
,      a.spid
,      b.sql_id
,      b.program
,      b.osuser
,      b.machine
,      b.type
,      b.event
,      b.action
,      b.p1text
,      b.p2text
,      b.p3text
,      b.state
,      c.sql_text
,      b.logon_time
FROM   v$process a
,      v$session b
,      v$sqltext c
WHERE  a.addr           = b.paddr
AND    b.sql_hash_value = c.hash_value
AND    b.STATUS         = 'ACTIVE'
AND    b.sid            = '&sid'
ORDER  BY a.spid
,      c.piece
/

OR - Use the below Query 

Longops.sql
----------- 
set pages 50000 lines 32767
col OPNAME for a10
col SID form 9999
col SERIAL form 9999999
col PROGRAM for a10
col USERNAME for a10
col SQL_TEXT for a40
col START_TIME for a10
col LAST_UPDATE_TIME for a10
col TARGET for a25
col MESSAGE for a25

alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';

SELECT l.inst_id,l.sid, l.serial#, l.sql_id, l.opname, l.username, l.target, l.sofar, l.totalwork, l.start_time,l.last_update_time,round(l.time_remaining/60,2) "REMAIN MINS", round(l.elapsed_seconds/60,2) "ELAPSED MINS", round((l.time_remaining+l.elapsed_seconds)/60,2) "TOTAL MINS", ROUND(l.SOFAR/l.TOTALWORK*100,2) "%_COMPLETE", l.message,s.sql_text 
FROM gv$session_longops l 
LEFT OUTER JOIN v$sql s on s.hash_value=l.sql_hash_value and s.address=l.sql_address and s.child_number=0
WHERE l.OPNAME NOT LIKE 'RMAN%' AND l.OPNAME NOT LIKE '%aggregate%' AND l.TOTALWORK != 0 AND l.sofar<>l.totalwork AND l.time_remaining > 0
/

Show how much archivelog data is generated per day

col orderby noprint
select trunc(first_time) orderby
,      to_char(first_time,'DD-MON-YYYY') "Date"
,      round(sum(bytes/1024/1024/1024),2) arch_logs_gigs
from (
    select unique (blocks * block_size) bytes,
    thread#,
    sequence#,
    resetlogs_change#,
    first_time
    from v$archived_log
)
group by trunc(first_time)
,      to_char(first_time, 'DD-MON-YYYY')
order by trunc(first_time) desc
/

Kill an Oracle session

disconnect attempts to kill the o/s processes too. kill doesn't. 

col killer for a60
set lines 2000
select 'alter system disconnect session '''||s.sid||','||s.serial#||''' immediate;' killer
,      s.inst_id
,      s.sid
,      to_char(s.logon_time,'DD-MON-YY HH24:MI:SS') logon
,      s.serial#
,      p.spid
,      s.username
,      s.program
from   gv$session s
,      gv$process p
where  1=1
and    p.addr    = s.paddr
and    p.inst_id = s.inst_id
and    s.type   != 'BACKGROUND'
and    s.sid     like '&session_id%'
/

KILLER                                                INST_ID        SID    SERIAL# SPID         USERNAME        PROGRAM
----------------------------------------------------- ------- ---------- ---------- ------------ --------------- ------------------------------------------------
alter system disconnect session '1015,271' immediate;       1       1015        271 64488062     DBSNMP          emagent@solax023 (TNS V1-V3)
alter system disconnect session '1010,4' immediate;         1       1010          4 21627762     WMCORE712       JDBC Connect Client
alter system disconnect session '933,2432' immediate;       1        933       2432 45220466     WM712           imp@solax023 (TNS V1-V3)
alter system disconnect session '1076,3204' immediate;      1       1076       3204 48562912     SYS             rman@solax023 (TNS V1-V3)
alter system disconnect session '1012,6' immediate;         1       1012          6 16646158     WMCORE712       JDBC Connect Client

Check for stale statistics

select m.table_owner
,      m.table_name
,      m.timestamp
,      sum(m.inserts)
,      sum(m.updates)
,      sum(m.deletes)
,      t.num_rows
,      t.last_analyzed
from   sys.dba_tab_modifications m
,      dba_tables t
where  m.table_owner = t.owner
and    m.table_name  = t.table_name
group  by m.table_owner
,      m.table_name
,      m.timestamp
,      t.num_rows
,      t.last_analyzed
order  by 1,2,3
/

or 

select dt.owner||'.'||dt.table_name owner_table
,      dtm.partition_name
,      inserts+updates+deletes modified_rows, num_rows, last_analyzed
,      case when num_rows = 0 then null
            else (inserts+updates+deletes) / num_rows * 100
       end percent_modified
from   dba_tab_modifications dtm
join   dba_tables dt
on     dtm.table_name = dt.table_name
order  by 6 desc
/

How much space is being used by a guaranteed restore point?

show parameter recovery
set numwi 15
set lines 200
col name for a40
col time for a23
col gigs_limit head "Max (but check df -g also!)"

select name
,      time
,      round(storage_size/1024/1024/1024,2) gigs_used
from   v$restore_point
where  1=1
and    guarantee_flashback_database = 'YES'
/

select * from v$flash_recovery_area_usage
/

select name
,      round(space_used/1024/1024/1024,2) gigs_used
,      round(space_limit/1024/1024/1024,2) gigs_limit
,      space_reclaimable
,      number_of_files
from v$recovery_file_dest
/

Get statistics for LOB columns in a table

select 
  max(dbms_lob.getlength(lob_content)) max_len
, avg(dbms_lob.getlength(lob_content)) avg_len
, min(dbms_lob.getlength(lob_content)) min_len
, sum(case when dbms_lob.getlength(lob_content) <= 4000 then 1
else 0 end) "<4k"
, sum(case when dbms_lob.getlength(lob_content) > 4000 then 1
else 0 end) ">4k"
, sum(case when dbms_lob.getlength(lob_content) is null then 1 else
0 end) "is null"
from &tablename
/

badprivs.sql

select grantee, privilege, admin_option
from   sys.dba_sys_privs
where  (privilege like '% ANY %'
or     privilege in ('BECOME USER', 'UNLIMITED TABLESPACE')
or     admin_option = 'YES')
and    grantee not in ('SYS', 'SYSTEM', 'OUTLN', 'AQ_ADMINISTRATOR_ROLE',
                      'DBA', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE',
                      'OEM_MONITOR', 'CTXSYS', 'DBSNMP', 'IFSSYS',
                      'IFSSYS$CM', 'MDSYS', 'ORDPLUGINS', 'ORDSYS',
                      'TIMESERIES_DBA')

Generate a list of tablespaces ready for a migration

set echo off
set lines 300
set pages 0
Set headi off
set feedb off
set long 32000

spool migration_create_target_tablespaces.sql

exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',TRUE);
select DBMS_METADATA.GET_DDL('TABLESPACE',tablespace_name) from dba_tablespaces
/

spool off

List the privileges assigned to users on directories

table_name is the name of the directory... 

SELECT grantor, grantee, table_schema, table_name, privilege FROM all_tab_privs WHERE table_name = 'DATA_PUMP_DIR';

List the privileges assigned to users on packages/procedures

table_name is the name of the package... 

SELECT grantor, grantee, table_schema, table_name, privilege FROM all_tab_privs WHERE upper(table_name) like upper('%UTL_FILE%');

List the system privileges assigned to a user

SELECT LPAD(' ', 2*level) || granted_role "USER PRIVS"
FROM (
  SELECT NULL grantee,  username granted_role
  FROM dba_users
  WHERE username LIKE UPPER('%&uname%')
  UNION
  SELECT grantee, granted_role
  FROM dba_role_privs
  UNION
  SELECT grantee, privilege
  FROM dba_sys_privs)
START WITH grantee IS NULL
CONNECT BY grantee = prior granted_role;

or 

SELECT path
FROM (
  SELECT grantee,
         sys_connect_by_path(privilege, ':')||':'||grantee path
  FROM (
    SELECT grantee, privilege, 0 role
    FROM dba_sys_privs
    UNION ALL
    SELECT grantee, granted_role, 1 role
    FROM dba_role_privs)
  CONNECT BY privilege=prior grantee
  START WITH role = 0)
WHERE grantee IN (
   SELECT username
   FROM dba_users
   WHERE lock_date IS NULL
   AND password != 'EXTERNAL'
   AND username != 'SYS')
OR grantee='PUBLIC'

or

for a migration from 10g... 

set headi off
set feedb off
set long  9999999
set pages 0
set lines 300

spool migration_create_target_users_grants.sql

select dbms_metadata.get_ddl('USER', username) || '/' ddl
from dba_users
where  1=1
and    default_tablespace not in ('SYSTEM','SYSAUX')
/

select 'grant '||privilege|| ' to ' || grantee || ';'
from   dba_sys_privs
where  1=1
and    grantee not in (
       select username
       from   dba_users
       where  1=1
       and    default_tablespace in ('SYSTEM','SYSAUX')
)
union all
select 'grant '||granted_role|| ' to ' || grantee || ';'
from   dba_role_privs
where  1=1
and    grantee not in (
       select username
       from   dba_users
       where  1=1
       and    default_tablespace in ('SYSTEM','SYSAUX')
)
/

spool off

or for a migration on 11g...! 

set head off
set pages 0
set long 9999999
col ddl for a1000


spool migration_create_users_grants.sql

SELECT DBMS_METADATA.GET_DDL('USER', USERNAME) || ';' DDL
FROM DBA_USERS
where  1=1
and    default_tablespace not in ('SYSTEM','SYSAUX')
and    upper(username) like '%'||upper('&&username')||'%'
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', USERNAME) || ';' DDL
FROM DBA_USERS
where  1=1
and    default_tablespace not in ('SYSTEM','SYSAUX')
and    upper(username) like '%'||upper('&&username')||'%'
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', USERNAME) || ';' DDL
FROM DBA_USERS
where  1=1
and    default_tablespace not in ('SYSTEM','SYSAUX')
and    upper(username) like '%'||upper('&&username')||'%'
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', USERNAME) || ';' DDL
FROM DBA_USERS
where  1=1
and    default_tablespace not in ('SYSTEM','SYSAUX')
and    upper(username) like '%'||upper('&&username')||'%'
/


spool off

Move files between disks

set pages 1000
set lines 1000

select 'alter database rename file ''' || 
       name || ''' to ''' || 
       replace(name || ''';', '/u01', '/u02')
from   v$datafile
/
 
select 'alter database rename file ''' || 
       member || ''' to ''' || 
       replace(member || ''';', '/u01', '/u02')
from   v$logfile
/

Pass parameter/argument into perl one-liner script from shell

Trick? Just use ARGV to pass them in... 

FREQ=$(perl -e 'my @CAL=split(";",$ARGV[0]);shift @CAL;print $CAL[$ARGV[1]];' -- "$LINE" $offset)

or 

DUMP_DATE=`perl -MTime::Local -e 'print(timelocal(0,$ARGV[0],$ARGV[1],$ARGV[2],$ARGV[3],$ARGV[4]))' -- $MI $HH $DD $MM $YYYY`

or export the variable from shell and access it via the ENV hash 

export db=ENDP1
perl -p -i -e 's!DSMI_LOG.+$!DSMI_LOG           /oracle/$ENV{db}/admin/tdpoerror_$ENV{db}!' ${db}/admin/tdpo.opt

Return epoch seconds in Perl

perl -e 'print time();'

or to convert a specific day of the year to epoch seconds 

use POSIX;
my $epochsecs = mktime(0,0,0, $jday, 0, $year-1900);

or for a specific date (careful though - month is 0-11 not 1-12!...

use Time::Local;
#                          SS MM HH DD MM YYYY
my $epochsecs = timelocal (30,10,19,03,04,2014);

or more long-winded but delimited by anything...
Supply the date/time in any delimited format. Eg. YYYY-MM-DD:HH:MI 

#!/usr/bin/perl -w
use Time::Local;

sub date2epoch { 
  my($s) = @_;
  my($year, $month, $day, $hour, $minute, $second);

  if($s =~ m{^\s*(\d{1,4})\W*0*(\d{1,2})\W*0*(\d{1,2})\W*0*
                 (\d{0,2})\W*0*(\d{0,2})\W*0*(\d{0,2})}x) {
    $year = $1;  $month = $2;   $day = $3;
    $hour = $4;  $minute = $5;  $second = $6;
    $hour |= 0;  $minute |= 0;  $second |= 0;  # defaults.
    $year = ($year<100 ? ($year<70 ? 2000+$year : 1900+$year) : $year);
    return timelocal($second,$minute,$hour,$day,$month-1,$year);  
  }
  return -1;
}

Return date from epoch seconds in Perl

perl -e 'print scalar (localtime (1243269270))'

Return epoch seconds from julian day number in Perl

use POSIX;
my ($year, $jday) = (2012,60);
my $epochsecs = mktime(0,0,0, $jday, 0, $year-1900);

Extract specific pieces of a date from an epoch timestamp in Perl

use POSIX;
# return localtime into an array
my @tm = localtime $epochsecs;

my $yyyymmdd = strftime "%Y%m%d", @tm;
print $yyyymmdd."\n";

Return epoch seconds in Shell (linux)

date -d "2014/04/03 18:34:30" "+%s"

Return date from epoch seconds in Shell (linux)

date -d @1396542870

Return epoch seconds in PL/SQL

You cannot just use sysdate as that is timezone sensitive... (epoch seconds are in UTC/GMT) 

select round((cast(sys_extract_utc(systimestamp) as date) - to_date('01-JAN-1970 00:00:00','DD-MON-YYYY HH24:MI:SS'))*24*60*60) from dual;
or
select round((vd.created - to_date('01-JAN-1970 00:00:00','DD-MON-YYYY HH24:MI:SS'))*24*60*60)  created from v$database vd;

How long has this instance been up (in minutes)? 

select round((cast(sys_extract_utc(systimestamp) as date) - startup_time)*24*60) up_mins from v$instance;

Return date from epoch seconds in SQL

select to_char(to_date('19700101','YYYYMMDD') + ((&epoch)/24/60/60),'DD-MON-YYYY HH24:MI:SS') utc_date from dual;

A database overview using the sys.plato package

exec plato.help;

or 

exec plato.complete('HTM');

List datafiles for a tablespace

select *
from   dba_data_files
where  1=1
and    tablespace_name ='&ts_name';

Show graph of available and free space in tablespaces

-- =============================================================================
-- File Name    : http://www.oracle-base.com/dba/monitoring/ts_free_space.sql
-- Author       : Tim Hall
-- Description  : Displays a list of tablespaces and their used/full status.
-- Requirements : Access to the DBA views.
-- Call Syntax  : @ts_free_space.sql
-- Last Modified: 13-OCT-2012 - Created. Based on ts_full.sql
--
-- Modification History
-- ====================
-- When      Who               What
-- ========= ================= =================================================
-- 13-NOV-13 Stuart Barkley    Added cols free_pct and used_pct
-- =============================================================================
SET PAGESIZE 140 lines 180
COLUMN used_pct FORMAT A11
COLUMN max_used_pct FORMAT A11

    SELECT tablespace_name                                                                      tablespace_name
    ,      size_mb                                                                              size_mb
    ,      free_mb                                                                              free_mb
    ,      TRUNC((free_mb/size_mb) * 100)                                                       free_pct
    ,      RPAD(' '|| RPAD('X',ROUND((size_mb-free_mb)/size_mb*10,0), 'X'),11,'-')              used_pct
    ,      max_size_mb                                                                          max_size_mb
    ,      max_free_mb                                                                          max_free_mb
    ,      TRUNC((max_free_mb/max_size_mb) * 100)                                               max_free_pct
    ,      RPAD(' '|| RPAD('X',ROUND((max_size_mb-max_free_mb)/max_size_mb*10,0), 'X'),11,'-')  max_used_pct
    from   (
           select a.tablespace_name                                         tablespace_name
           ,      b.size_mb                                                 size_mb
           ,      a.free_mb                                                 free_mb
           ,      b.max_size_mb                                             max_size_mb
           ,      a.free_mb + (b.max_size_mb - b.size_mb)                   max_free_mb
           from   (
                  select tablespace_name                                    tablespace_name
                  ,      trunc(sum(bytes)/1024/1024)                        free_mb
                  from   dba_free_space
                  group  by tablespace_name
                  ) a
           ,      (
                  select tablespace_name                                    tablespace_name
                  ,        trunc(sum(bytes)/1024/1024)                      size_mb
                  ,        trunc(sum(greatest(bytes,maxbytes))/1024/1024)   max_size_mb
                  from   dba_data_files
                  group by tablespace_name
                  ) b
           ,      v$instance vi
           where  a.tablespace_name = b.tablespace_name
           )
    order  by tablespace_name;

set lines 80

Another method for calculating free space in tablespace using segments

select df.tablespace_name "Tablespace"
,      totalusedspace "Used MB"
,      (df.totalspace - tu.totalusedspace) "Free MB"
,      df.totalspace "Total 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;

Work out maximum possible size of a datafile

select round( (4*1024*1024*value) / (1024*1024*1024) ,2) maxfilesizeingigabytes from v$parameter where name='db_block_size';

or, as tablespaces can have block sizes differing from that of the init.ora parameter... 

select round( (4*1024*1024*block_size) / (1024*1024*1024) ,2) maxfilesizeingigabytes from dba_tablespaces where tablespace_name='&tablespace_name';

Show size and maxsize at datafile level

datafiles 

set pages 100 lines 200
col file_name for a60
select sum(bytes)/1024/1024      mbytes_alloc
,      sum(maxbytes)/1024/1024   mbytes_max
,      file_name
from   sys.dba_data_files
group  by file_name
/

tempfiles 

set pages 100 lines 200
col file_name for a60
select sum(bytes)/1024/1024      mbytes_alloc
,      sum(maxbytes)/1024/1024   mbytes_max
,      file_name
from   sys.dba_temp_files
group  by file_name
/

Resize the online redo logfiles

Switching logs too often? Redolog files too small?

  • Show current log groups
set lines 200
select group#, bytes/1024/1024 size_M, status, archived from v$log;

col member for a55
select group#, type, member from v$logfile order by 1;
  • Create additional (bigger) groups
define SID=TIBQFW
alter database add logfile group 6  '/oracle/&SID/oradata1/redo1.log' size 1G;
alter database add logfile group 7  '/oracle/&SID/oradata2/redo2.log' size 1G;
alter database add logfile group 8  '/oracle/&SID/oradata3/redo3.log' size 1G;
alter database add logfile group 9  '/oracle/&SID/oradata1/redo4.log' size 1G;
alter database add logfile group 10 '/oracle/&SID/oradata2/redo5.log' size 1G;

or if you use multiple members... 

alter database add logfile group 6 ('/oracle/&SID/oradata1/redo/redo01a.log', '/oracle/&SID/oradata1/redo01b') size 50M;
alter database add logfile group 7 ('/oracle/&SID/oradata1/redo/redo02a.log', '/oracle/&SID/oradata1/redo02b') size 50M;
alter database add logfile group 8 ('/oracle/&SID/oradata1/redo/redo03a.log', '/oracle/&SID/oradata1/redo03b') size 50M;
  • Switch archivelogs until the new ones are current and the old ones are inactive
alter system switch logfile;

If the old logs are not yet inactive, checkpoint the database 

alter system checkpoint;
  • Drop the old groups
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
alter database drop logfile group 4;
alter database drop logfile group 5;

Increase the existing size of a datafile

alter database datafile '&full_path_of_datafile' resize &new_meg_size.M;

Increase the max size of a tempfile

alter database tempfile '/oracle/ENDP1/oradata90/temp01.dbf' autoextend on maxsize unlimited;

Find out what processes are using the temp tablespace

Temporary tablespace is used for sorting query results. Find them with this. 

SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
a.username,a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr;

Increase the maximum size of a datafile

Although this can be set lower than existing size, try not to. It makes the reports look weird! 

alter database
      datafile '&full_path_of_datafile'
      autoextend on
      maxsize &new_meg_size.m;

Find schemas (users) with objects created outside their default tablespace

Indexes could be a notable exception if they are created in an index tablespace 

set lines 200
col owner           for a20
col segment_name    for a45
col tablespace_name for a32
select owner
,      segment_name
,      tablespace_name
from   dba_segments
where  exists (select username from dba_users where username = owner)
and    tablespace_name != (select default_tablespace from dba_users where username = owner)
and    owner = '&owner'
/

Add a datafile to a tablespace

'unlimited' will default to the maximum datafile size for the current db_block_size 

alter tablepace &tablespace_name
      add datafile '&full_path_of_datafile'
      size 100M
      autoextend on
      next 100M
      maxsize unlimited

List all datafiles with their size

set pages 1000 lines 2000
col filetype for a15
col name for a60
break on report
compute sum of gig on report

select filetype
,      name
,      gig
from   (
       select 'datafile' filetype
       ,      name
       ,      bytes/1024/1024 gig
       from   v$datafile
       union all
       select 'tempfile' filetype
       ,      name
       ,      bytes/1024/1024
       from   v$tempfile
       union all
       select 'logfile' filetype
       ,      lf.member "name"
       ,      l.bytes/1024/1024
       from   v$logfile lf
       ,      v$log     l
       where  1=1
       and    lf.group# = l.group#
       ) used
,      (
       select sum(bytes)/1024/1024
       from   dba_free_space
       ) free

Database size

There are different interpretations of what constitutes the size of a database...
Is it the space allocated to the datafiles or only the space taken up by the data? Are tempfiles included? Are redo logs included? etc... 

set lines 132
col dbname for a9  heading "Database"
col dbsize for a15 heading "Size"
col dbused for a15 heading "Used space"
col dbfree for a15 heading "Free space"
with used_space as
(
select  bytes
from    v$datafile
union   all
select  bytes
from    v$tempfile
union   all
select  bytes
from    v$log
)
, free_space as
(
select sum(bytes) sum_bytes
from dba_free_space
)
select vd.name                                                                                            dbname
,      round(sum(used_space.bytes)/1024/1024/1024 ) || ' GB'                                              dbsize
,      round(sum(used_space.bytes)/1024/1024/1024 ) - round(free_space.sum_bytes/1024/1024/1024) || ' GB' dbused
,      round(free_space.sum_bytes/1024/1024/1024) || ' GB'                                                dbfree
from   free_space
,      used_space
,      v$database vd
group  by vd.name
,      free_space.sum_bytes
/

Drop datafile from temp tablespace

Database is completely stuck because the temporary tablespace has grown to fill all available room.
Look around for at least some log files to delete in order to get connected (audit files, sqlnet logs, listener logs)
Once connected, change the default temporary tablespace to a small one so we can remove the original. 

create temporary tablespace temp2 tempfile '/oracle/RMANV12/oradata2/temp2.dbf' size 5M;

alter database default temporary tablespace temp2;

I found trying to drop the datafile like this didn't work, just hung there (probably active tranactions)... 

drop tablespace temp including contents and datafiles;

So did it like this... 

alter database tempfile '/oracle/RMANV12/oradata2/temp.dbf' drop including datafiles;

Database altered.

drop tablespace temp including contents and datafiles;

Tablespace dropped.

Then to be neat and tidy, rename the temp2 tablespace back to temp. 

create temporary tablespace temp tempfile '/oracle/RMANV12/oradata2/temp.dbf' size 256M reuse autoextend on next 128M maxsize 1G;
alter database default temporary tablespace temp;
drop tablespace temp2 including contents and datafiles;

Although the last statement will not work until any transactions using it have been rolled back or committed.

List files that are in hot-backup mode

set lines 100
set pages 100
col name format a60

select df.name
,      b.status
,      to_char(time, 'hh24:mi:ss dd/mm/yyyy') time
from   v$datafile   df
,      v$backup     b
where  1=1
and    df.file# = b.file#
and    b.status = 'ACTIVE'
order  by b.file#;

Waiting for snapshot control file enqueue

Script to identify sessions causing RMAN to hang because of 'waiting for snapshot control file enqueue' message 

set lines 2000
col killer     for a70
col program    for a20
col module     for a20
col action     for a20
col logon_time for a20

select 'alter system disconnect session '''||s.sid||','||s.serial#||''' immediate;' killer
,      username
,      program
,      module
,      action
,      logon_time
from   v$session s
,      v$enqueue_lock l
where  l.sid  = s.sid
and    l.type = 'CF'
and    l.id1  = 0
and    l.id2  = 2
/

Info on blocking processes

select s1.username || '@' || s1.machine
       || ' ( SID=' || s1.sid || ' )  is blocking '
       || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) '  blocking_status
from   v$lock l1
,      v$session s1
,      v$lock l2
,      v$session s2
where  s1.sid     = l1.sid
and    s2.sid     = l2.sid
and    l1.block   = 1
and    l2.request > 0
and    l1.id1     = l2.id1
and    l2.id2     = l2.id2
/

session doing the blocking 

select *
from  v$lock l1
where 1=1
and   block = 1 ;

sessions being blocked 

select *
from   v$lock l1
where  1=1
and    id2 = 85203

info on session doing the blocking 

select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
from   v$session
where  sid = 234

select do.object_name
,      row_wait_obj#
,      row_wait_file#
,      row_wait_block#
,      row_wait_row#
,      dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# ) row_id
from   v$session   s
,      dba_objects do
where  sid=234
and    s.ROW_WAIT_OBJ# = do.OBJECT_ID;

Show locked objects

set lines 100
set pages 999
col username   format a20
col sess_id    format a10
col object     format a25
col mode_held  format a10
select oracle_username||' ('||s.osuser||')' username
,      s.sid||','||s.serial# sess_id
,      owner||'.'||object_name object
,      object_type
,      decode ( l.block
              , 0, 'Not Blocking'
              , 1, 'Blocking'
              , 2, 'Global'
              ) status
,      decode ( v.locked_mode
              , 0, 'None'
              , 1, 'Null'
              , 2, 'Row-S (SS)'
              , 3, 'Row-X (SX)'
              , 4, 'Share'
              , 5, 'S/Row-X (SSX)'
              , 6, 'Exclusive', TO_CHAR(lmode)
              ) mode_held
from   v$locked_object v
,      dba_objects d
,      v$lock l
,      v$session s
where  1=1
and    v.object_id = d.object_id
and    v.object_id = l.id1
and    v.session_id = s.sid
order  by oracle_username
,	session_id;

Show which row is locked

select do.object_name
,      row_wait_obj#
,      row_wait_file#
,      row_wait_block#
,      row_wait_row#
,      dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#)
from   v$session   s
,      dba_objects do
where  1=1
and    s.ROW_WAIT_OBJ# = do.OBJECT_ID
and    sid             = &sid;

Check what is audited on a database

set pagesize 100
set linesize 256
select audit_option,success,failure from dba_stmt_audit_opts;

How old is the oldest audit record?

select extract (day from (systimestamp - min(ntimestamp#))) days_old from aud$;

Check what objects are being audited

tti col 60 'Object Audit Options (dba_obj_audit_opts)' skip col 50 'A=By Access, S=By Session. Left=on success, right=on failure' skip 2
set lines 1000 pages 100
col alt for a3 head "Alt|er"
col aud for a3 head "Aud|it"
col com for a3 head "Com|ent"
col del for a3 head "Del|ete"
col gra for a3 head "Gra|nt"
col ind for a3 head "Ind|ex"
col ins for a3 head "Ins|ert"
col loc for a3 head "Lo|ck"
col ren for a3 head "Ren|ame"
col sel for a3 head "Sel|ect"
col upd for a3 head "Upd|ate"
col ref for a3 head "Ref|rnc"
col exe for a3 head "Exe|cut"
col cre for a3 head "Cre|ate"
col rea for a3 head "Re|ad"
col wri for a3 head "Wri|te"
col fbk for a3 head "Fla|sh"
	
select *
from   dba_obj_audit_opts
/

Procedure to delete old audit records

select text from all_source where lower(name)='purge_audit_trail';

TEXT
------------------------------------------------------------------------------------------------
PROCEDURE purge_audit_trail IS
BEGIN
-- set the last archive timestamp
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
       audit_trail_type    => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
       last_archive_time   => systimestamp - interval '365' day,
       rac_instance_number => null
                                          );
-- purge audit trail till last archive timestamp
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD
, use_last_arch_timestamp => TRUE);

END purge_audit_trail;

Job setup to run the audit purge

BEGIN
sys.dbms_scheduler.create_job(
    job_name => '"SYS"."PURGE_AUDIT_TRAIL_JOB"',
    job_type => 'STORED_PROCEDURE',
    job_action => '"SYS"."PURGE_AUDIT_TRAIL"',
    repeat_interval => 'FREQ=DAILY;BYHOUR=20;BYMINUTE=0;BYSECOND=0',
    start_date => to_timestamp_tz('2012-02-22 Europe/Paris', 'YYYY-MM-DD TZR'),
    job_class => '"DEFAULT_JOB_CLASS"',
    comments => 'purge audit trails until last archive timestamp',
    auto_drop => FALSE,
    enabled => FALSE
);
sys.dbms_scheduler.set_attribute( name => '"SYS"."PURGE_AUDIT_TRAIL_JOB"', attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_OFF);
sys.dbms_scheduler.set_attribute( name => '"SYS"."PURGE_AUDIT_TRAIL_JOB"', attribute => 'job_weight', value => 1);
sys.dbms_scheduler.enable( '"SYS"."PURGE_AUDIT_TRAIL_JOB"' );
END;
/

A list of all SIDs in oratab that should restart after a server boot

Using awk 

awk -F: '$NF=="Y" && $1 !~/[#]/ {print $1}'  /etc/oratab

Using perl 

perl -F: -ne 'print "$1\n" if /(\w+):(.+):Y$/' /etc/oratab

Move the admin directories to a new location

./all_db_do "alter system set audit_file_dest='/oracle/&SID/admin/adump' scope=spfile;"

./all_db_do "shutdown immediate"

for db in `awk -F: 'NF && $1 !~/[#*]/ {print $1}'  /etc/oratab`
do
    echo $db
    mkdir -p /oracle/${db}/admin
    echo "/oracle/${db}/admin created"
    mv /oracle/admin/${db}/adump /oracle/${db}/admin/
    mv /oracle/admin/${db}/dpdump /oracle/${db}/admin/
    echo "Moved /oracle/admin/${db}/adump to /oracle/${db}/admin/"
done

./all_db_do startup

./all_db_do "create pfile from spfile;"

Check if the Standby database is up-to-date with the primary database

select arch.thread# "Thread"
,      arch.sequence# "Last Sequence Received"
,      appl.sequence# "Last Sequence Applied"
,      (arch.sequence# - appl.sequence#) "Difference"
from   (
       select thread#
       ,      sequence#
       from   v$archived_log
       where  1=1
       and    ( thread#,first_time ) in (
                                        select thread#, max(first_time)
                                        from   v$archived_log
                                        group  by thread#
                                        )
       ) arch
       ,
       (
       select thread#
       ,      sequence#
       from   v$log_history
       where  1=1
       and    ( thread#,first_time ) in ( select thread# ,max(first_time)
                                          from   v$log_history
                                          group  by thread#
                                         )
       ) appl
where  1=1
and    arch.thread# = appl.thread#
order  by 1;

Check for gaps on the Standby database

select thread#, low_sequence#, high_sequence# from v$archive_gap;

Ignore case-sensitive passwords in 11g

alter system set SEC_CASE_SENSITIVE_LOGON = false scope=both

External table for the Alert log (pre 11g)

CREATE OR REPLACE DIRECTORY bdump AS 'c:\oracle\product\diag dbms\orabase\orabase race\';

CREATE TABLE system.log_table (TEXT VARCHAR2(400))
ORGANIZATION EXTERNAL (
TYPE oracle_loader
DEFAULT DIRECTORY bdump
ACCESS PARAMETERS (
 RECORDS DELIMITED BY NEWLINE
 NOBADFILE NODISCARDFILE NOLOGFILE
 FIELDS TERMINATED BY '0x0A'
 MISSING FIELD VALUES ARE NULL)
LOCATION ('alert_orabase.log'))
REJECT LIMIT unlimited;

SELECT * FROM system.log_table;

CREATE OR REPLACE VIEW last_200_alerts AS
WITH alert_log AS (
 SELECT rownum as lineno, text FROM system.log_table)
SELECT text
FROM alert_log
WHERE lineno > (SELECT count(*)-200 FROM alert_log)
ORDER BY lineno;

SELECT * FROM last_200_alerts;

Alert log (11g+)

This reads the log.xml file (which contains the same information as alert.log)
Show the messages put in the alert log in the last 24 hours 

select substr(MESSAGE_TEXT, 1, 300) message_text
,      count(*) cnt
from   X$DBGALERTEXT
where  1=1
and    (MESSAGE_TEXT like '%ORA-%' or upper(MESSAGE_TEXT) like '%ERROR%')
and    cast(ORIGINATING_TIMESTAMP as DATE) > sysdate - &days_ago/1440
group  by substr(MESSAGE_TEXT, 1, 300)

There is also a fixed table X$DBGDIREXT which returns all file and directory names under [diagnostic_dest]/diag directory: 

select lpad(' ',lvl,' ')||logical_file file_name
from   x$dbgdirext
where  rownum <=20;

Write to a TCP/IP socket from PL/SQL

declare
 bt_conn               utl_tcp.connection;
 retval                binary_integer;
 l_sequence            varchar2(30) := 'Sequence Number 101021';
begin
 bt_conn := utl_tcp.open_connection(remote_host => '10.10.1.1'
                                   ,remote_port => 5000
                                   ,tx_timeout => 1
                                   )
 ;
 retval := utl_tcp.write_line(bt_conn,l_sequence)
 ;
 utl_tcp.flush(bt_conn)
 ;
 utl_tcp.close_connection(bt_conn)
 ;
exception
 when others then
  raise_application_error(-20101, sqlerrm)
  ; 
  utl_tcp.close_connection(bt_conn)
  ;
end
;
/

and read from it using netcat in another session 

nc -lk 5000

produces 

Sequence Number 101021

Handling single quote marks inside SQL statements

SQL> SELECT 'D''COSTA' name FROM DUAL;

NAME
-------
D'COSTA

Alternatively, use the 10g+ quoting method:

SQL> SELECT q'$D'COSTA$' NAME FROM DUAL;

NAME
-------
D'COSTA

Run a script on all databases in /etc/oratab

export ORAENV_ASK=NO
for db in `awk -F: 'NF && $1 !~/[#*]/ {print $1}'  /etc/oratab`
do
    echo $db
    ORACLE_SID=$db
    . oraenv
    sqlplus -V
done

Schedule a cron job to run on the last day of each month

0 23 28-31 * * [ $(date -d +1day +%d) -eq 1 ] && su - oracle -c "dsmc  arch -server=SO_U_`hostname`_ORX_M_SOL '/oracle/export/PV6/expdp_PV6_D_FULL_`date +\%Y\%m\%d`*'"

How to tell if a year is a leap year or not - using cal instead of reinventing the wheel

Test: Why is Julian day 0, 17th November 1858?
Pretty comprehensive answer: here and here
Also attempts to answer the question why typing 'cal 09 1752' on Unix systems looks so odd! 

function leap {
#  This function tells you if the year passed in as a parameter is a leap year or not...
if [[ $(cal 2 ${1:-$(date '+%Y')} | egrep '\<[[:digit:]]{1,2}\>' | wc -w) -eq 29 ]]; then
    leap='Y'
else
    leap='N'
fi
echo ${leap}
}

Reset an expired (and/or locked) user password to the same value

Simple, quick and easy version. Run this then pick and choose the statements you want to run. 

set lines 200 pages 200
col user_list for a200 head "Expired User List"

select 'alter user ' || su.name || ' identified by values' || ' ''' || spare4 || ';' || su.password || ''' account unlock;' user_list
from   sys.user$ su
,      dba_users du
where   1=1
and    su.name        = du.username
and    (account_status like 'EXPIRED%' or account_status like '%LOCKED')
and    du.username    not in
(
  'ORACLE_OCM'
, 'SCOTT'
, 'DIP'
, 'FLOWS_FILES'
, 'TSMSYS'
, 'MDDATA'
, 'ANONYMOUS'
, 'CTXSYS'
, 'MDSYS'
, 'ORDDATA'
, 'ORDPLUGINS'
, 'SI_INFORMTN_SCHEMA'
, 'OLAPSYS'
, 'XDB'
, 'ORDSYS'
, 'WMSYS'
, 'DMSYS'
, 'DSSYS'
, 'EXFSYS'
, 'SYS'
, 'SYSTEM'
, 'MGMT_VIEW'
, 'PERFSTAT'
, 'TRACESVR'
, 'LBACSYS'
, 'OWBSYS'
, 'OWBSYS_AUDIT'
, 'APEX_PUBLIC_USER'
, 'XS$NULL'
, 'APEX_030200'
, 'SPATIAL_CSW_ADMIN_USR'
, 'SPATIAL_WFS_ADMIN_USR'
, 'APPQOSSYS'
, 'OUTLN'
);

More industrialised version. Create a package. 

create or replace package dba_utils
as
    procedure reset_password ( p_userlist in varchar2 );
end dba_utils;
/


create or replace package body dba_utils
as

procedure reset_password ( p_userlist in varchar2 )
is
    cursor c1 ( p_userlist in varchar2 ) is
    select username
    ,      regexp_substr ( dbms_metadata.get_ddl('USER', username), '''[^'']+''' ) pwd
    from   dba_users
    where  1=1
    and    username in upper( p_userlist )
    ;
begin
    dbms_output.enable(null);
    for r1 in c1 ( p_userlist )
    loop
        begin
            execute immediate 'alter user ' || r1.username || ' account unlock';
            execute immediate 'alter user ' || r1.username || ' identified by values ' || r1.pwd;
        exception
        when others then
            dbms_output.put_line('Unable to modify user ' || r1.username);
        end;
    end loop;
end reset_password;

end dba_utils;
/

Call it 

set serveroutput on
exec dba_utils.reset_password ('STUART');

shell

Alert Log scraping with ADRCI

Count the number of background processes for a particular SID

SID=RAVJDE1
ps -ef|cut -c54-100|awk -v SID=$SID '$0 ~ /'$SID'$/ {print}'|wc -l

or ...this will work with any OS (so long as the process is at the end of the ps -ef or ps -aux listing 

SID=RAVJDE1
ps -ef|awk -v SID=$SID -F_ '{ $NF ~ /'$SID'$/ {print} }'|wc -l

Pick the columns you want in the ps listing 

ps -efo user,pid,ppid=MOM -o args

Find Oracle errors in the most recent DataPump log files

00 09 * * * /home/tools/scripts/oracle/dosh 'find /oracle/export -name "expdp*log" -mtime -1 -exec grep ORA- {} \; -ls' | mailx -s 'Datapump errors for <customer>last night' [email protected]

Find the 10 largest directories on a particular filesystem

du -ag /oracle | sort -nr | head -n 10

Find the 10 largest files on a particular mount point

find /home -size +1G -ls | sort -nr +6 | head -10

or 

find /home -xdev -ls | sort -nr +6 | head -10

Split a file in pieces using sed

Using fixed line numbers 

sed -ne '1,12p' file > text.part1
sed -ne '13,$p' file > text.part2

Using line with a /pattern/ on it 

sed -e '/hello/q' tt.dat | grep -v hello > text.part1
sed -n `sed -n '/hello/=' tt.dat`',$p' tt.dat | grep -v hello > text.part2

Split a file in pieces using Perl

perl -ne 'print if 1 .. /marker line/' text.dat >text.part1
perl -ne 'print if /marker line/ .. eof()' text.dat >text.part2

Find and display RMAN errors in an RMAN log file

sed -ne '/^RMAN-/,/^$/p'  RMAN_<SID>_<DATE>.log

cmd

Perl

For all files in a directory, replace a keyword (SSIIDD) in a file with another (a parameter)

for i in `ls`
do
    perl -pi -e 's/SSIIDD/'$SID'/g' $i
done

Parse tnsnames.ora to get just the service names

[from here] 

perl -ne 'print if m/^([^#()\W ][a-zA-Z.]*(?:[.][a-zA-Z]*\s?=)?)/' $TNS_ADMIN/tnsnames.ora

and use that to check the listener status

for svc in `perl -ne 'print if m/^([^#()\W ][a-zA-Z.]*(?:[.][a-zA-Z]*\s?=)?)/' $TNS_ADMIN/tnsnames.ora | sed -e 's/=//'` ;do
    tnsping $svc
done
Retrieved from "https://mailnest.com/dbawiki/index.php?title=Handy_scripts&oldid=2134"