As a DBA you sometimes have to take time to look for tricks to accomplish tasks that seem simple at first glance. A few days ago, I was asked to identify tables that had sequences prefixed by the schema name as a default value in the columns. You may think: it’s an easy task, just filter on the “data_default” column of the “dba_tab_columns” view, but unfortunately it’s not that simple using SQLPlus. The “data_default” column is in “long” datatype, so you cannot include it directly in the “where” clause of a query.
In my examples, I will simulate the search for a default column with the value “systimestamp” in the tables of the “SYS” schema.
First tries
For my first try, I just tried to add the column “data_default” (“long” datatype) in the “where” clause of my query
SQL> select owner, table_name, column_name, data_default from dba_tab_columns where owner = 'SYS' and data_default ='systimestamp';
select owner, table_name, column_name, data_default from dba_tab_columns where owner = 'SYS' and data_default ='systimestamp'
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype
The message doesn’t say much, and neither does the Oracle documentation:
# oerr ora 00997
00997, 00000, "illegal use of LONG datatype"
// *Cause:
// *Action:
Using the “like” operator instead of “=” doesn’t work either, but returns a different error:
SQL> select owner, table_name, column_name, data_default from dba_tab_columns where owner = 'SYS' and data_default like '%systimestamp%';
select owner, table_name, column_name, data_default from dba_tab_columns where owner = 'SYS' and data_default like '%systimestamp%'
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected CHAR got LONG
Once again, the message doesn’t say much, and neither does the Oracle documentation:
# oerr ora 00932
00932, 00000, "inconsistent datatypes: expected %s got %s"
// *Cause:
// *Action:
At this point, I notice that it is not possible to filter on “long” columns, so I have to find a different approach.
Filter on “long” columns using PL/SQL
In order to achieve my goal, I decided to use a PL/SQL anonymous block.
It may not be the only available solution, but it works and is relatively simple to use.
I use a cursor that will fetch all the results of my query “select owner, table_name, column_name, data_default from dba_tab_columns where owner = ‘SYS’“, and then return only those containing the text I’m looking for using comparaison operator “record.data_default = ‘systimestamp’“. This also works when replacing “=” with “like”.
set serveroutput on
set long 999999999
BEGIN
FOR record IN (select owner, table_name, column_name, data_default from dba_tab_columns where owner = 'SYS') LOOP
IF record.data_default = 'systimestamp' THEN
dbms_output.put_line(record.owner||';'||record.table_name||';'||record.column_name||';'||record.data_default);
END IF;
END LOOP;
END;
/
Output:
SYS;SYSDBIMFS$;CREATED;systimestamp
SYS;SYSDBIMFS$;LASTREAD;systimestamp
PL/SQL procedure successfully completed.
For my needs, the output was in CSV format, but it can be formatted by modifying the PL/SQL block a bit. For example, if you want it to look like the output of a select on a table:
set serveroutput on
set long 999999999
set lines 180
BEGIN
dbms_output.Put_line(Rpad('OWNER', 30)||Rpad('TABLE_NAME', 30)||Rpad('COLUMN_NAME', 30)|| Rpad('DATA_DEFAULT', 30));
dbms_output.Put_line(Rpad(' ', 30,'-')||Rpad(' ', 30,'-')||Rpad(' ', 30,'-')|| Rpad(' ', 30,'-'));
FOR record IN (select owner, table_name, column_name, data_default from dba_tab_columns where owner = 'SYS') LOOP
IF record.data_default like '%systimestamp%' THEN
dbms_output.Put_line(Rpad(record.OWNER, 30)||Rpad(record.TABLE_NAME, 30)||Rpad(record.COLUMN_NAME, 30)|| Rpad(record.DATA_DEFAULT, 30));
END IF;
END LOOP;
END;
/
Output:
OWNER TABLE_NAME COLUMN_NAME DATA_DEFAULT
----------------------------- ----------------------------- ----------------------------- -----------------------------
SYS SYSDBIMFS$ CREATED systimestamp
SYS SYSDBIMFS$ LASTREAD systimestamp
This method works but has a few drawbacks:
- It is not optimized from a performance point of view: by filtering directly on the PL/SQL cursor, the Oracle optimizer is bypassed. The filter on the “long” column is not taken into account in the calculation of the query execution plan. This is why it is necessary to add as many filters as possible in the initial query, in order to reduce the number of lines fetched by the cursor.
- The “long” column on which you want to filter, must be part of your initial select clause.
- Formatting the result takes longer than a simple “select / from / where”.
I hope you find this post useful. Stay tuned for more DBA stuff!
