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.