Difference between revisions of "Handy scripts"

Revision as of 18:21, 26 February 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'
/

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
/

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

Increase the existing size of a datafile

alter database datafile '&full_path_of_datafile' &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

ALTER TABLESPACE &tablespace_name
      ADD DATAFILE '&datafile_name'
      SIZE 100M
      AUTOEXTEND ON
      NEXT 100M
      MAXSIZE 20G

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

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;

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 message_text from x$dbgalertext
where  1=1
and    originating_timestamp > systimestamp-1

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;

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

find /oracle/export -name "expdp*log" -mtime -1 -exec grep 'ORA-' {} \; -ls 2>/dev/null

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

@@ Line 161: / Line 161: @@
 </pre>
 ===Increase the existing size of a datafile===
+<pre>
 alter database datafile '&full_path_of_datafile' &new_meg_size.m;
+</pre>
 ===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!