Difference between revisions of "Handy scripts"

Revision as of 21:26, 17 November 2013

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

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

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

set lines 2000
select 'alter system kill session '''||s.sid||','||s.serial#||''' immediate;' killer
,      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'
/

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

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

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

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

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

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

@@ Line 285: / Line 285: @@
 </pre>
-===List all datafiles ith their size===
+===List all datafiles with their size===
   select filetype
   ,      name