Searching on long columns

December 16, 2015

Instr and other functions work great of clobs but not longs. This is relevant when attempting to query the data dictionary since for backward compatibility reasons they are still longs and not clobs. Here is how to fix by using xml functions to convert to clobs.

with parsed_xml as (
  select extractValue(xs.object_value, '/ROW/SEARCH_CONDITION') as search_condition
  from (
      select xmltype(dbms_xmlgen.getxml('
            select * from user_constraints 
            where table_name = ''SOME_TABLE_NAME'' 
            and constraint_name = ''SOME_CONSTRAINT_NAME''  ') ) as xmlf
      from dual
  , xmltable('/ROWSET/ROW' passing x.xmlf ) xs
select count(*) --into v_cnt
from parsed_xml
where search_condition like '%123%'

Sources :
working with longs
how to convert above link using better/newer xmltable function

traps in bash

November 3, 2015

Use mktemp to create a place for temporary files. Then use trap to make sure that even if you ctl-x to kill yourself, your temporary files get deleted, webservers remain in started state and temporary AWS resources get killed.

#put at beginning of script
SCRATCH=$(mktemp -d -t tmp.XXXXXXXXXX )
function cleanup {
    rm -rf "$SCRATCH"
    #write to log file maybe
    #read link below for good ideas
trap cleanup INT TERM EXIT
#in main part of script use scratch area for temp files
echo "something " > $SCRATCH/tmp

Sourced from (excellent read and if you are reading me then read this)

Can’t read the blue in Linux

August 28, 2015

It’s annoying to read the blue in vi and ls. So here is how to fix it.

For vi or vim, create .vimrc in your home directory and add the following line. FYI, it also works in vi.

:color desert

For ls, change LS_COLOR in your .bash_profile to something like


The important part is the di=00:34 (34=blue) being changed to di=00:32 (32=green). Then relogin or . .bash_profile.

More details here

Temp tablespace usage monitoring

March 25, 2015
/*Sort Space Usage by Session*/
SELECT   S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
         S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
         COUNT(*) sort_ops
FROM     gv$sort_usage T, gv$session S, dba_tablespaces TBS, gv$process P
WHERE    T.session_addr = S.saddr and T.INST_ID = S.INST_ID
AND      S.paddr = P.addr and S.INST_ID = P.INST_ID
AND      T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
         S.program, TBS.block_size, T.tablespace
ORDER BY mb_used desc, sid_serial;

/*Sort Space Usage by Statement*/
SELECT   S.sid || ',' || S.serial# sid_serial, S.username,
         T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
         T.sqladdr address, Q.hash_value, Q.sql_text
FROM     gv$sort_usage T, gv$session S, gv$sqlarea Q, dba_tablespaces TBS
WHERE    T.session_addr = S.saddr and T.INST_ID = S.INST_ID
AND      T.sqladdr = Q.address (+) and T.INST_ID = Q.INST_ID (+)
AND      T.tablespace = TBS.tablespace_name
ORDER BY mb_used desc, S.sid;

Add more to windows 7 preview pane

April 8, 2014

If you want to add the ability to see text file contents in the windows explorer preview pane for non standard file extensions, here is how to add them. In this case, pks and pkb. Put this into a dot reg file and double click or add using regedit.

Windows Registry Editor Version 5.00


Can’t resize tablespace due to stuff at end

March 31, 2014

When you try to resize a tablespace or data and get the error that there is stuff at the end of the file and you are not allowed to resize, this is how to rebuild the objects which puts the block on earlier empty spots.

This query indentifies them. (tablespace name is hard coded, need to replace or add in file id). Shamelessly stolen from .

set lines 132
col segment_type format a20
col segment_name format a25
col owner format a7

break on report
compute sum of kbytes on report

select * from
select owner, segment_type, segment_name, extent_id, file_id, block_id, bytes/1024 Kbytes
from dba_extents
where tablespace_name = 'EPM_MEDIUM'
group by owner, segment_type, segment_name, extent_id, file_id, block_id, bytes/1024
order by block_id desc
where rownum < 20

This query gives you the alter commands

	case when segment_type = 'LOBSEGMENT' then
		'alter table '||owner||'.'||table_name||' move lob ('||column_name||') store as (tablespace epm_medium);'
	when segment_type = 'TABLE' then
		'alter table '||owner||'.'||segment_name||' move;'
	when segment_type = 'INDEX' and iot_table_name is not null then
		'alter table '||owner||'.'||iot_table_name||' move;'
	when segment_type = 'INDEX' and iot_table_name is null then
		'alter index '||owner||'.'||segment_name||' rebuild;'
	end a 
	select d.owner, d.segment_type, d.segment_name, extent_id, file_id, block_id, bytes/1024 Kbytes, l.table_name, l.column_name, i.table_name iot_table_name
	from dba_extents d
	left outer join dba_lobs l on (d.owner=l.owner and d.segment_name=l.segment_name)
	left outer join dba_indexes i on (d.owner=i.owner and d.segment_name=i.index_name and i.index_type like 'IOT%')
	where d.tablespace_name = 'EPM_MEDIUM'
	group by d.owner, d.segment_type, d.segment_name, extent_id, file_id, block_id, bytes/1024, l.table_name, l.column_name, i.table_name
	order by block_id desc
where rownum < 20

Change tablespace name, rowcount and repeat until enough stuff is moved that you can resize.

Unfortunately, when you move a table you invalidate it’s indexes. So rebuild them.

select 'alter index '||owner||'.'||index_name||' rebuild;' 
from dba_indexes 
where status<> 'VALID' 
and table_owner not in ('SYS','SYSTEM');

Also, synonyms get invalidates so

    for rec in (
        select owner,object_name
        from dba_objects
        where status='INVALID' and owner like '%_RPT'
            execute immediate 'select * from '||rec.owner||'.'||rec.object_name;
        exception when others then null;
    end loop;

This query tells you what the hiwatermark for resizing a datafile is and how far you have been able to move stuff down the file (assumed 8k blocksize).

WHERE DBADF.FILE_ID = DBAFS.FILE_ID(+) AND CEIL(BLOCKS*8192/*BLKSIZE*//1024/1024)- CEIL((NVL(HWM,1)* 8192/*BLKSIZE*/)/1024/1024 ) > 0

Tables without segment

March 18, 2014

Apparently, it is a feature of that you can create tables without segments in EE. Unfortunately in SE1, this “feature” is not available. So if you are doing an export from EE that you want to import into SE1, you need to make sure that the segments get created first. here is how you do it.

    s varchar2(32000);
    x varchar2(4000);
    y varchar2(4000);
    for rec in (
        select 'insert into '||owner||'.'||table_name||' (XXXX) values (YYYY)' xxx, owner, table_name
        from dba_tables where segment_created='NO' 
        and owner not in ('SYSMAN')
        and owner = 'DYN_T02_EPM'
        x := null; y:=null;
        for rec2 in (select column_name from dba_tab_columns where owner = rec.owner and table_name = rec.table_name)
            x := x ||','||rec2.column_name;
            y := y ||','||'null';
        end loop;
        x := substr(x,2);
        y := substr(y,2);
        s := replace(replace(,'XXXX',x),'YYYY',y);
            execute immediate s;
        exception when others then
    end loop;

Note that the insert will never actually do anything but by trying to do an insert, oracle will create the segment which sticks around and then the insert fails. You end up with segments created for these table as you would have normally expected.

Datapump Monitoring Queries

August 31, 2013

To see running datapump jobs.

SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs
WHERE job_name NOT LIKE 'BIN$%'

If you want to clean up failed datapump jobs, do the following using owner_name, job_name from above.

SET serveroutput on
SET lines 100
  job1 NUMBER;

This query will show you two things. First, module=impdp.exe shows whether you impdp client has lost connection on really long import. Second, you can see which table it is currently working on.

select y.module, x.job_name,b.state,b.job_mode,
, x.owner_name,z.sql_text, p.message
, p.totalwork, p.sofar
, round((p.sofar/p.totalwork)*100,2) done
, p.time_remaining
from dba_datapump_jobs b 
left join dba_datapump_sessions x on (x.job_name = b.job_name)
left join v$session y on (y.saddr = x.saddr)
left join v$sql z on (y.sql_id = z.sql_id)
left join v$session_longops p ON (p.sql_id = y.sql_id)
WHERE 1=1 --y.module='Data Pump Worker'
--AND p.time_remaining > 0

Example below.
8-5-2013 3-00-03 PM

Lastly, the following queries are supposed to work, but don’t always because long-ops isn’t getting used.

select username,opname,target_desc,sofar,totalwork,message ,sysdate,t.*
where opname <> 'Gather Table''s Index Statistics'
and target_desc = 'IMPORT'
and opname = 'SYS_IMPORT_SCHEMA_01'

This one sometimes works. Generally, not very useful.

   substr(sql_text,instr(sql_text,'into "'),30) table_name, 
   rows_processed, round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
   trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_minute 
   sql_text like 'insert %into "%' and command_type = 2 and open_versions > 0;

This is a test article for Cate

February 8, 2013

cate is watching you mikey – cate p. 😉 By the way, happy birthday old duude.

Linux sending root mail externally

March 28, 2012

To get root to be able to send email to external account. su root and then

echo '' > ~/.forward
chmod 644 ~/.forward
echo 'hosts=local' > /etc/netsvc.conf

Assuming you don’t already have a /etc/netsvc.conf. I didn’t.

Edit your /etc/hosts file and add the name of the machine to end of the localhost line.

You do not need to modify last entry of /etc/aliases and it should be root: root. If you do modify that file, run newaliases.

Do not have a .mailrc. I had one that connected to google, but then it bypasses aliases and .forwards.