Handy scripts

SQL

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

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'
/

Pretty longops listing

col opname for a30 head "Job"
col target for a20
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
,      target
,      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    time_remaining > 0
/

Kill an Oracle session

SELECT s.inst_id,
       s.sid,
       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     = '&session_id'

SYS@PIBPROD> /

  INST_ID        SID    SERIAL# SPID       USERNAME   PROGRAM
---------- ---------- ---------- ---------- ---------- ---------------------------------------------
        1        235       5609 4718756    OPS$PIBCTM sqlplus@pibprod (TNS V1-V3)

SYS@PIBPROD> alter system kill session '235,5609';

or better still (by killing also the operating system process)...

SYS@PIBPROD> alter system disconnect session '235,5609' immediate;

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')

This query will 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'

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
/

Return epoch seconds in Perl

perl -e 'print time();'

Return epoch seconds in PL/SQL

You cannot just use sysdate as this is timezone sensitive...

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;

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';

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';

Increase the existing size of a datafile

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

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;

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 ith their size

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

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

select s.sid
,      username
,      program
,      module
,      action
,      logon_time
,      l.*
from   v$session s
,      v$enqueue_lock l
where  1=1
and    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 || ' ) ' AS 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;

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

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

Split a file in pieces using sed

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

Find and display RMAN errors in an RMAN log file

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

cmd

Database Export - from DBA Oracle (Burleson)

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