Shell script wrapper

Here is an easy shell script wrapper to easily run an SQL script.

#!/bin/ksh

# Commands
typeset -u Command
for Name in mkdir
do
       Command=$Name

       case $Name in
               grep) PathNames=/usr/xpg4/bin:/usr/bin:/bin ;;
               *)    PathNames=/usr/bin:/bin ;;
       esac

       while [[ -n $PathNames ]]
       do
               PathName=${PathNames%%:*}
               PathNames=${PathNames#*:}

               if [[ $PathName = $PathNames ]]
               then
                       PathNames=""
               fi

               if [[ -x $PathName/$Name ]]
               then
                       eval "$Command=$PathName/$Name"
                       break
               fi
       done

       if [[ -z $(eval print `print \\$${Command}`) ]]
       then
               print -u2 "Unable to find a $Name executable"
               exit 1
       fi
done

# Directories

BaseDir=/home/oracle
SQLDir=${BaseDir}/sql
LogDir=${BaseDir}/logs
ENVFileDir=/usr/local/bin

# Misc
ProgName=${0##*/}

# Files

SQLFile=${SQLDir}/${ProgName%%.sh}.sql

# Parameters

ORACLE_SID=$1

if [[ -z $ORACLE_SID ]]
then
        print -u2 "ERROR - Oracle SID must be set or passed in"
        exit 1
fi

# Get correct environment

. ${ENVFileDir}/${ORACLE_SID}_env

# Set the output directory

OutFile=$LogDir/${ProgName%%.sh}_$ORACLE_SID.out

# Run the SQL

if [[ ! -d $BaseDir ]]
then
        print -u2 "ERROR - Unable to find directory $BaseDir"
        exit 1
fi

if [[ ! -d $LogDir ]]
then
        $MKDIR $LogDir
        if [[ $? -ne 0 ]]
        then
                print -u2 "ERROR - Unable to create directory $LogDir"
                exit 1
        fi
fi

if [[ ! -f $SQLFile ]]
then
        print -u2 "ERROR - Unable to find file $SQLFile"
        exit 1
fi

cd $LogDir
if [[ $PWD != $LogDir ]]
then
        print  -u2 "ERROR - Unable to change directory to $LogDir"
        exit 1
fi

$ORACLE_HOME/bin/sqlplus / as sysdba @$SQLFile $ORACLE_SID > $OutFile 2>&1
if [[ $? -ne 0 ]]
then
        print -u2 "ERROR - SQLPLus errored - please investigate"
        exit 1
fi

exit 0

You name the script with the same name as the SQL script you wish to run.

For example if you have a script called say

purge_stats.sql

then name the script

purge_stats.sh

Here's an example of a simple SQL script

set trims on
whenever sqlerror exit failure rollback

spool purge_stats_&1..log

set timing on
exec dbms_stats.purge_stats(sysdate-14)

spool off

exit 

Some usage notes

1. Environment file for the database must be of the format

<oracle_sid>_env

Bind Variable Capture

When capturing bind variable it is easy to get the character and number data but other types are obscured in the anydata type.

Here's a script to help to unmask those values.

col data_value for a50

accept SQLID prompt 'Enter SQL ID : '

select position
     , case datatype
            when 180 then to_char(anydata.accesstimestamp(value_anydata),'DD-MON-YYYY HH24:MI:SS')
            when  12 then to_char(anydata.accessdate(value_anydata),'DD-MON-YYYY HH24:MI:SS')
            else value_string
       end data_value
from  v$sql_bind_capture
where sql_id = '&SQLID';

Detect Unindexed Foreign Keys

Unindexed foreign keys continue to be a prime performance bottleneck.

Here is a script that will run through the foreign keys in a schema and display them all and the index that is associated with them (or not) and indicate whether the foreign key requires an index.

set lines 140
set pages 1000

col status for a6
col child_table for a24
col fk_columns for a27
col indexed_columns for a27
col parent_table for a24
col pk_columns for a27

select decode( b.table_name, NULL, '****', 'ok' ) Status
     , a.table_name                               Child_Table
     , c.table_name                               Parent_Table
     , c.columns                                  PK_Columns
     , a.columns                                  FK_Columns
     , b.columns                                  Indexed_Columns
from
( select a.table_name
       , a.constraint_name
       , b.r_constraint_name
       , max(decode(position, 1,'  '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 2,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 3,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 4,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 5,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 6,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 7,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 8,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 9,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,10,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,11,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,12,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,13,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,14,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,15,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,16,', '||rpad(substr(column_name,1,25),25),NULL)) columns
    from user_cons_columns a, user_constraints b
   where a.constraint_name = b.constraint_name
     and b.constraint_type = 'R'
   group by a.table_name
          , a.constraint_name
          , b.r_constraint_name ) a,
( select table_name
       , index_name
       , max(decode(column_position, 1,'  '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position, 2,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position, 3,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position, 4,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position, 5,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position, 6,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position, 7,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position, 8,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position, 9,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position,10,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position,11,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position,12,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position,13,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position,14,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position,15,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position,16,', '||rpad(substr(column_name,1,25),25),NULL)) columns
    from user_ind_columns
   group by table_name
          , index_name ) b,
( select a.table_name
       , a.constraint_name
       , max(decode(position, 1,'  '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 2,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 3,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 4,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 5,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 6,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 7,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 8,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 9,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,10,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,11,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,12,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,13,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,14,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,15,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,16,', '||rpad(substr(column_name,1,25),25),NULL)) columns
    from user_cons_columns a, user_constraints b
   where a.constraint_name = b.constraint_name
     and b.constraint_type in ( 'P' , 'U' )
   group by a.table_name
          , a.constraint_name ) c
where a.table_name = b.table_name (+)
  and b.columns (+) like a.columns || '%'
  and a.r_constraint_name = c.constraint_name
order by a.table_name
/

You will need quite a wide screen to incorporate all the data. I use 140 character width.

How to disable the DBMS_SCHEDULER

I discovered this after searching all over the web and am putting it here so that I do not have to do that again.

To turn the scheduler off use

exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'TRUE')

Note that the TRUE is not a BOOLEAN but text.

To check the status

select * from DBA_SCHEDULER_GLOBAL_ATTRIBUTE;

To turn the scheduler back on again

exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'FALSE')

Creating Object DDL

Here is some handy code to create object DDL using DBMS_METADATA.

There are 2 scripts, I shall call them the calling script and the running script.

The calling script (which I called get_obj.sql) is as follows

doc
 Possible objects to get DDL for are :-
  CONSTRAINT
  DB_LINK
  DIRECTORY
  FUNCTION
  INDEX
  MATERIALIZED_VIEW
  MATERIALIZED_VIEW_LOG
  PACKAGE_SPEC
  PACKAGE_BODY
  PROCEDURE
  SEQUENCE
  SYNONYM
  TABLE
  TRIGGER
  TYPE_SPEC
  TYPE_BODY
  VIEW
#

accept Object prompt  'Enter Object type -> '
accept OwnerIN prompt   "Enter Schema Owner (default %) -> "
accept ObjNameIN prompt "Enter Object Name (default %) -> "

set verify off 
set head off
set feed off
set pages 0
set trims on
set term off
set lines 200

col spool_on      for a200
col select_clause for a200
col from_clause   for a200
col where_clause  for a200
col and_clause    for a200
col object_clause for a200
col spool_off     for a200

col object_type_dir  new_value dir_and_type  noprint
col object_type_dirs new_value dir_and_types noprint

col OwnerOUT new_value Owner noprint
col ObjNameOUT new_value ObjName noprint

select nvl('&OwnerIN','%') OwnerOUT , nvl('&ObjNameIN','%') ObjNameOUT from dual;

SELECT 
   CASE upper('&Object')
  WHEN 'MATERIALIZED_VIEW'     THEN 'mview'
  WHEN 'MATERIALIZED_VIEW_LOG' THEN 'mview_log'
  WHEN 'PACKAGE_SPEC'          THEN 'package'
  WHEN 'TYPE_SPEC'             THEN 'type'
  ELSE                              lower('&Object')
   END        object_type_dir 
FROM      dual;

SELECT 
  CASE '&dir_and_type'
  WHEN 'index'                 THEN 'indexes'
  WHEN 'package_body'          THEN 'package_bodies'
  WHEN 'type_body'             THEN 'type_bodies'
  ELSE                              '&dir_and_type'||'s'
  END         object_type_dirs
FROM     dual;

spool get_sp_obj.sql

SELECT     'spool do_get_sp_obj.sql'                                                spool_on
 ,  q'[SELECT '@@get_ddl '||upper('&Object')||' '||]'
 || CASE '&Object'
  WHEN 'MATERIALIZED_VIEW_LOG' THEN q'[log_owner||' '||log_table||' '||log_owner||'/]'||'&dir_and_types'||q'[ '||master]'
  WHEN 'DB_LINK'               THEN q'[owner||' '||db_link||' '||owner||'/]'||'&dir_and_types'||q'[/'||db_link]'
  WHEN 'FUNCTION'              THEN q'[owner||' '||object_name||' '||owner||'/]'||'&dir_and_types'||q'[ '||object_name]'
  WHEN 'PROCEDURE'             THEN q'[owner||' '||object_name||' '||owner||'/]'||'&dir_and_types'||q'[ '||object_name]'
  WHEN 'PACKAGE_SPEC'          THEN q'[owner||' '||object_name||' '||owner||'/]'||'&dir_and_types'||q'[ '||object_name]'
  WHEN 'PACKAGE_BODY'          THEN q'[owner||' '||object_name||' '||owner||'/]'||'&dir_and_types'||q'[ '||object_name]'
  WHEN 'SEQUENCE'              THEN q'[sequence_owner||' '||sequence_name||' '||sequence_owner||'/]'||'&dir_and_types'||q'[ '||sequence_name]'
  WHEN 'TYPE_SPEC'             THEN q'[owner||' '||type_name||' '||owner||'/]'||'&dir_and_types'||q'[ '||type_name]'
  WHEN 'TYPE_BODY'             THEN q'[owner||' '||object_name||' '||owner||'/]'||'&dir_and_types'||q'[ '||object_name]'
  ELSE                              q'[owner||' '||]'||'&dir_and_type'||q'[_name||' '||owner||'/]'||'&dir_and_types'||q'[ '||]'||'&dir_and_type'||'_name'
    END                                                                      select_clause
 ,  'from '
 || CASE '&Object'
  WHEN 'FUNCTION'              THEN 'dba_objects'
  WHEN 'PROCEDURE'             THEN 'dba_objects'
  WHEN 'PACKAGE_SPEC'          THEN 'dba_objects'
  WHEN 'PACKAGE_BODY'          THEN 'dba_objects'
  WHEN 'TYPE_SPEC'             THEN 'dba_types'
  WHEN 'TYPE_BODY'             THEN 'dba_objects'
  ELSE                              'dba_'||'&dir_and_types'
    END                                                                      from_clause
 , 'where '
 || CASE '&Object'
  WHEN 'MATERIALIZED_VIEW_LOG' THEN 'log_owner'
  WHEN 'SEQUENCE'              THEN 'sequence_owner'
  ELSE                              'owner'
    END
 || q'[ like upper('&Owner')]'                                                    where_clause
 , 'and '
 || CASE '&Object'
  WHEN 'FUNCTION'              THEN q'[object_type = 'FUNCTION']'
  WHEN 'PROCEDURE'             THEN q'[object_type = 'PROCEDURE']'
  WHEN 'PACKAGE_SPEC'          THEN q'[object_type = 'PACKAGE']'
  WHEN 'PACKAGE_BODY'          THEN q'[object_type = 'PACKAGE BODY']'
  WHEN 'TYPE_BODY'             THEN q'[object_type = 'TYPE BODY']'
  ELSE                              '1 = 1'
    END                                                                     and_clause
 , 'and '
 || CASE '&Object'
  WHEN 'MATERIALIZED_VIEW_LOG' THEN q'[master like upper('&ObjName')]'
  WHEN 'DB_LINK'               THEN q'[db_link like upper('&ObjName')]'
  WHEN 'FUNCTION'              THEN q'[object_name like upper('&ObjName')]'
  WHEN 'PROCEDURE'             THEN q'[object_name like upper('&ObjName')]'
  WHEN 'PACKAGE_SPEC'          THEN q'[object_name like upper('&ObjName')]'
  WHEN 'PACKAGE_BODY'          THEN q'[object_name like upper('&ObjName')]'
  WHEN 'TYPE_SPEC'             THEN q'[type_name like upper('&ObjName')]'
  WHEN 'TYPE_BODY'             THEN q'[object_name like upper('&ObjName')]'
  ELSE                              '&dir_and_type'||q'[_name like upper('&ObjName')]'
    END
 || ';'                                                                     object_clause
 ,  'spool off'                                                             spool_off
FROM    dual
/
spool off

@@get_sp_obj.sql

@@do_get_sp_obj.sql

set verify on
set head on
set pages 60
set lines 140
set term on
set feed 5

This script as you can see on line 72 calls the running script called get_ddl.sql

Here is get_ddl.sql

set lines 32767
set pages 0
set head off
set feed off
set trims on
set verify off
set term off
set long 5000000

col text for a32767

!if [ ! -d &4 ]; then mkdir -p &4;fi

spool &4/&5..sql

select dbms_metadata.get_ddl('&1','&3','&2') text from dual
/

prompt /

spool off

Load both of these scripts into the same directory and call the calling script from SQL*Plus.

For example

@get_obj

You will be prompted to input some object_types and possibly names and each object creation script will be spooled to its individual file in a directory structure that matches the object type.
For example all the table definitions will be in a directory named 'tables'.

These scripts can be handy if you need to do a lot of reconfiguring or simply wish to have a record of schema objects.

Finding the Events

To find out which events you have set in your 10g oracle database use the following from SQL*Plus when connected as sysdba.

oradebug setmypid
oradebug dump events 4

Find the trace file in the user dump destination and it will contain something like this

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /home/oracle/product/10.2.0
System name:    Linux
Node name:      bob
Release:        2.6.9-78.0.8.ELxenU
Version:        #1 SMP Wed Nov 5 07:40:25 EST 2008
Machine:        x86_64
Instance name: fred
Redo thread mounted by this instance: 1
Oracle process number: 18
Unix process pid: 26048, image: oracle@bob (TNS V1-V3)

*** 2010-02-10 17:26:22.100
*** ACTION NAME:() 2010-02-10 17:26:22.099
*** MODULE NAME:(sqlplus@bob (TNS V1-V3)) 2010-02-10 17:26:22.099
*** SERVICE NAME:(SYS$USERS) 2010-02-10 17:26:22.099
*** SESSION ID:(197.7107) 2010-02-10 17:26:22.099
Dump event group for level SYSTEM
TC Addr  Evt#(b10)   Action   TR Addr    Arm    Life
81246A20 10298       1       81246ab0 0 0
       TR Name             TR level   TR address       TR arm     TR life    TR type
       CONTEXT                      32 0         -1          2          0 

Here we have set event 10298 at level 32 like so

alter system set events '10298 trace name context forever, level 32';

This event incidentally turns off the checking for the correct parameters used when using nfs mountpoints for the datafiles and rman backups.

To do the same in 11g use the following

alter session set events 'immediate eventdump(system)';
alter session set events 'immediate eventdump(session)';
alter session set events 'immediate eventdump(process)';

Another quick SQL way to get events set is to run the following code

SET SERVEROUTPUT ON

DECLARE
   l_level NUMBER;
BEGIN
   FOR l_event IN 10000..10999
   LOOP
       dbms_system.read_ev (l_event,l_level);
       IF l_level > 0
       THEN
           dbms_output.put_line ('Event '||TO_CHAR (l_event)||' is set at level '||TO_CHAR (l_level));
       END IF;
   END LOOP;
END;
/ 

This will catch most of the events commonly set.

Bad SnapShot Controlfile Configuration

Have you ever configured your snapshot controlfile incorrectly?

Can you no longer resync your catalog?

Are you getting the following messages

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of configure command at 02/04/2010 13:52:00
RMAN-03014: implicit resync of recovery catalog failed
RMAN-03009: failure of full resync command on default channel at 02/04/2010 13:52:00
ORA-01580: error creating control backup file /home/luke/rman_backup_area/test/scf/test_scf.f
ORA-27040: file create error, unable to create file
Linux Error: 2: No such file or directory 

You cannot now just change the entry whilst connected to the catalog like so

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/oracle/rman_backup_area/test/scf/test_scf.f';

As to do so it requires that the catalog be resync'ed but it can't do that because it cannot write a snapshot controlfile! Ahh!

To resolve the issue there are 2 solutions that I know of

1. Using RMAN connect to the target with no catalog

rman target / nocatalog
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/oracle/rman_backup_area/test/scf/test_scf.f';

2. Use the DBMS_BACKUP_RESTORE package from the database

EXECUTE SYS.DBMS_BACKUP_RESTORE.CFILESETSNAPSHOTNAME ('/home/oracle/rman_backup_area/test/scf/test_scf.f');

Become another user in PL/SQL

There are some things even SYS cannot achieve without becoming the user, two things spring to mind.

1. Create private database links
2. Submit and modify DBMS_JOBS and scheduler jobs.

For the last 2 you can use DBMS_IJOB and DBMS_ISCHED.

But for database links there is another way - DBMS_SYS_SQL

This hidden package has a function called PARSE_AS_USER which can parse a cursor after doing a 'become user'.

Here's a sample bit of code that creates a database link as another user.

First you have to find out the user's USER_ID (in my case it was 428) and then pass that number in to the function to tell it which user to become.

DECLARE
  l_cursors DBMS_SQL.NUMBER_TABLE;
  l_result  NUMBER;
BEGIN
  l_cursors(428):=DBMS_SYS_SQL.OPEN_CURSOR;

  --parsing statement as a different owner

  DBMS_SYS_SQL.PARSE_AS_USER(
      c             => l_cursors(428),
      statement     => 'create database link luke connect to scott identified by tiger using ''dev''',
      language_flag => dbms_sql.native,
      userid        => 428
  );

 l_result:=DBMS_SYS_SQL.EXECUTE(l_cursors(428));
END;
/

And there you have it the database link luke is now created with an owner user_id 428.

Which materialized views are being refreshed?

This is a handy little bit of SQL to see which materialized views are currently being refreshed.

select
      o.owner         "Owner"
     ,o.object_name   "Mat View"
     ,s.username
     ,s.sid           "Sid"
from
     v$lock           l
    ,dba_objects      o
    ,v$session        s
where o.object_id   = l.id1
and   l.type        = 'JI'
and   l.lmode       = 6
and   s.sid         = l.sid
and   o.object_type = 'TABLE';

The trick is in the lock type - JI.

Datafile Mapping

What I wanted to create was a simple listing of where the free space was in a datafile to see if it would be a good candidate for either shrinking or reorganising.

But it was not as simple as I had hoped.

I started with this initial piece of SQL.

select file_id
, block_id
,   owner
||'.'
||segment_name
||decode(partition_name,NULL,NULL,'.'||partition_name)
||' extent '
||extent_id             object_name     
, bytes/1024/1024         mbytes
from dba_extents
where file_id = &1
union
select file_id
, block_id     
, '************* FREE *************'
, bytes/1024/1024 mbytes
from dba_free_space
where file_id = &1
order by 1 , 2
/

The output was strange: in my locally managed tablespace there were 2 rows next to each other saying they were free. How could this be?

Well a little bit of thought made me realise the obvious; old dropped objects in the recyclebin were being displayed as free space.

Well I wanted my datafile mapper to be clever and show those recyclebin objects, so I began to dig deeper.

Firstly, dba_extents must have excluded the recyclebin objects. Here's how; this is the listing from the text from DBA_VIEWS for the view DBA_EXTENTS

select ds.owner
, ds.segment_name
, ds.partition_name
, ds.segment_type
, ds.tablespace_name
, e.ext#
, f.file#
, e.block#
, e.length * ds.blocksize
, e.length
, e.file#
from sys.uet$ e
, sys.sys_dba_segs ds
, sys.file$ f
where e.segfile# = ds.relative_fno
and e.segblock# = ds.header_block
and e.ts# = ds.tablespace_id
and e.ts# = f.ts#
and e.file# = f.relfile#
and bitand(NVL(ds.segment_flags,0), 1) = 0
and bitand(NVL(ds.segment_flags,0), 65536) = 0
union all
select ds.owner
, ds.segment_name
, ds.partition_name
, ds.segment_type
, ds.tablespace_name
, e.ktfbueextno
, f.file#
, e.ktfbuebno
, e.ktfbueblks * ds.blocksize
, e.ktfbueblks
, e.ktfbuefno
from sys.sys_dba_segs ds
, sys.x$ktfbue e
, sys.file$ f
where e.ktfbuesegfno = ds.relative_fno
and e.ktfbuesegbno = ds.header_block
and e.ktfbuesegtsn = ds.tablespace_id
and ds.tablespace_id = f.ts#
and e.ktfbuefno = f.relfile#
and bitand(NVL(ds.segment_flags, 0), 1) = 1
and bitand(NVL(ds.segment_flags,0), 65536) = 0

Notice the last bitand - it is this flag that is set when the object is a dropped object.

Secondly, the objects must have been included in the view DBA_FREE_SPACE.

select ts.name
, fi.file#
, f.block#
, f.length * ts.blocksize
, f.length
, f.file#
from sys.ts$ ts
, sys.fet$ f
, sys.file$ fi
where ts.ts# = f.ts#
and f.ts# = fi.ts#
and f.file# = fi.relfile#
and ts.bitmapped = 0
union all
select /*+ ordered use_nl(f) use_nl(fi) */
ts.name
, fi.file#
, f.ktfbfebno
, f.ktfbfeblks * ts.blocksize
, f.ktfbfeblks
, f.ktfbfefno
from sys.ts$ ts
, sys.x$ktfbfe f
, sys.file$ fi
where ts.ts# = f.ktfbfetsn
and f.ktfbfetsn = fi.ts#
and f.ktfbfefno = fi.relfile#
and ts.bitmapped <> 0
and ts.online$ in (1,4)
and ts.contents$ = 0
union all
select /*+ ordered use_nl(u) use_nl(fi) */
ts.name
, fi.file#
, u.ktfbuebno
, u.ktfbueblks * ts.blocksize
, u.ktfbueblks
, u.ktfbuefno
from sys.recyclebin$ rb
, sys.ts$ ts
, sys.x$ktfbue u
, sys.file$ fi
where ts.ts# = rb.ts#
and rb.ts# = fi.ts#
and u.ktfbuefno = fi.relfile#
and u.ktfbuesegtsn = rb.ts#
and u.ktfbuesegfno = rb.file#
and u.ktfbuesegbno = rb.block#
and ts.bitmapped <> 0 
and ts.online$ in (1,4) 
and ts.contents$ = 0
union all
select ts.name
, fi.file#
, u.block#
, u.length * ts.blocksize
, u.length
, u.file#
from sys.ts$ ts
, sys.uet$ u
, sys.file$ fi
, sys.recyclebin$ rb
where ts.ts# = u.ts#
and u.ts# = fi.ts#
and u.segfile# = fi.relfile#
and u.ts# = rb.ts#
and u.segfile# = rb.file#
and u.segblock# = rb.block#
and ts.bitmapped = 0

So I simply knocked out the last 2 bits of unioned SQL statements did an outer join to the SYS.RECYCLEBIN$ and hey presto I have my query.

select *
from (
select
f.file#                        file_id
, e.block#                       block_id
, case when bitand(NVL(ds.segment_flags,0), 65536) = 1
then   '***-> '
||ds.owner
||'.'
||r.original_name
||decode( ds.partition_name
,NULL
,NULL
,'.'||ds.partition_name)
||'(Dropped)'
else   ds.owner
||'.'
||ds.segment_name
||decode( ds.partition_name
,NULL
,NULL
,'.'||ds.partition_name)
end
||' extent '
||e.ext#                              object_name
, (e.length * ds.blocksize)/1024/1024   mbytes
from sys.uet$ e
, sys.sys_dba_segs ds
, sys.obj$ o left outer join sys.recyclebin$ r
on o.obj# = r.obj#
, sys.file$ f
where e.segfile# = ds.relative_fno
and e.segblock# = ds.header_block
and e.ts# = ds.tablespace_id
and e.ts# = f.ts#
and e.file# = f.relfile#
and ds.segment_objd = o.dataobj#
and bitand(NVL(ds.segment_flags,0), 1) = 0
union all
select
f.file#                             file_id
, e.ktfbuebno                         block_id
, case when bitand(NVL(ds.segment_flags,0), 65536) = 1
then   '***-> '
||ds.owner
||'.'
||r.original_name
||decode( ds.partition_name
,NULL
,NULL
,'.'||ds.partition_name)
||'(Dropped)'
else   ds.owner
||'.'
||ds.segment_name
||decode( ds.partition_name
,NULL
,NULL
,'.'||ds.partition_name)
end
||' extent '
||e.ktfbueextno                            object_name
, (e.ktfbueblks * ds.blocksize)/1024/1024    mbytes
from sys.sys_dba_segs ds
, sys.x$ktfbue e
, sys.file$ f
, sys.obj$ o left outer join sys.recyclebin$ r
on o.obj# = r.obj#
where e.ktfbuesegfno = ds.relative_fno
and e.ktfbuesegbno = ds.header_block
and e.ktfbuesegtsn = ds.tablespace_id
and ds.tablespace_id = f.ts#
and e.ktfbuefno = f.relfile#
and ds.segment_objd = o.dataobj#
and bitand(NVL(ds.segment_flags, 0), 1) = 1
) dba_extents
where dba_extents.file_id = &1
union all
select *
from (
select fi.file#                            file_id
, f.block#                            block_id
, '************* FREE *************'  object_name
, (f.length * ts.blocksize)/1024/1024 mbytes
from sys.ts$ ts
, sys.fet$ f
, sys.file$ fi
where ts.ts# = f.ts#
and f.ts# = fi.ts#
and f.file# = fi.relfile#
and ts.bitmapped = 0
union all
select /*+ ordered use_nl(f) use_nl(fi) */
fi.file#
, f.ktfbfebno
, '************* FREE *************'
, (f.ktfbfeblks * ts.blocksize)/1024/1024
from sys.ts$ ts
, sys.x$ktfbfe f
, sys.file$ fi
where ts.ts# = f.ktfbfetsn
and f.ktfbfetsn = fi.ts#
and f.ktfbfefno = fi.relfile#
and ts.bitmapped <> 0
and ts.online$ in (1,4)
and ts.contents$ = 0
) dba_free_space
where dba_free_space.file_id = &1
order by 1 , 2
/

And it seems to work for me!

Sometimes ls just doesn't cut the mustard

Occasionally I want more than ls can give me.

On this occasion I required the file modification date in a specific format for a specified file. So here's a perl one-liner to give it to me.

perl -e '$mtime=(stat(@ARGV))[9];@f=(localtime($mtime))[3..5];printf "%02d/%02d/%d\n",$f[0],$f[1]+1,$f[2]+1900;' filename

This produces the date in dd/mm/yyyy format.

Just what I wanted.

Here's another similar script to get the file size

perl -e '$size=(stat(@ARGV))[7];printf "%d\n",$size;' filename

This is the information you can get from stat

0  dev     device number of filesystem
1  ino     inode number
2  mode    file mode (type and permissions)
3  nlink   number of (hard) links to the file
4  uid     numeric user ID of file's owner
5  gid     numeric group ID of file's owner
6  rdev    the device identifier (special files only)
7  size    total size of file, in bytes
8  atime   last access time in seconds since the epoch
9  mtime   last modify time in seconds since the epoch
10 ctime   inode change time in seconds since the epoch
11 blksize preferred block size for file system I/O
12 blocks  actual number of blocks allocated

UNIX flavours and double evaluation

When I write shell scripts (ksh) I do not like to rely on a PATH so I tend to hardcode all UNIX executables. This means that they can generally run as is using cron.

Example

CAT=/usr/bin/cat
$CAT milk

However this is not very useful when mixing UNIX variants so I have come up with this way of coding so that we can catch as many UNIX flavours as possible

# Commands
typeset -u Command
for Name in cat date grep hostname mailx mv perl ps rm
do
       Command=$Name

       case $Name in
               grep) PathNames=/usr/xpg4/bin:/usr/bin:/bin ;;
               *)    PathNames=/usr/bin:/bin ;;
       esac

       while [[ -n $PathNames ]]
       do
               PathName=${PathNames%%:*}
               PathNames=${PathNames#*:}

               if [[ $PathName = $PathNames ]]
               then
                       PathNames=""
               fi

               if [[ -x $PathName/$Name ]]
               then
                       eval "$Command=$PathName/$Name"
                       break
               fi
       done

       if [[ -z $(eval print `print \\$${Command}`) ]]
       then
               print -u2 "Unable to find a $Name executable"
               exit 1
       fi
done

The bit I like the best is that last if statement which checks that the executable was found.


The eval command tries to print out, say, $CAT to check that the CAT variable has something in it, but as CAT is the value of the variable Command we have to do some jiggery pokery to get it to parse properly.