Posted by There was a time, many moons ago 
when CLOB, BLOB and BFILE did not exist as data types. So if you had anything longer than a few kilobytes of data to store, you had to use a LONG or a LONG RAW. But those data types came with all sorts of restrictions and frustrations, and we all embraced the improvements that the LOB data types brought in Oracle 8. But of course, we carry a lot of that historical “baggage” in the data dictionary.
SQL> select owner, table_name, column_name
2 from dba_tab_cols
3 where data_type = 'LONG'
4 and substr(table_name,1,4) in ('DBA_','ALL_','USER')
5 order by 1,2, 3;
OWNER TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------ -------------------------
SYS ALL_ARGUMENTS DEFAULT_VALUE
SYS ALL_CLUSTER_HASH_EXPRESSIONS HASH_EXPRESSION
SYS ALL_CONSTRAINTS SEARCH_CONDITION
SYS ALL_IND_EXPRESSIONS COLUMN_EXPRESSION
SYS ALL_IND_PARTITIONS HIGH_VALUE
SYS ALL_IND_SUBPARTITIONS HIGH_VALUE
SYS ALL_MVIEWS QUERY
SYS ALL_MVIEW_AGGREGATES MEASURE
SYS ALL_MVIEW_ANALYSIS QUERY
SYS ALL_NESTED_TABLE_COLS DATA_DEFAULT
SYS ALL_REGISTERED_MVIEWS QUERY_TXT
SYS ALL_REGISTERED_SNAPSHOTS QUERY_TXT
SYS ALL_SNAPSHOTS QUERY
SYS ALL_SQLSET_PLANS OTHER
SYS ALL_SUBPARTITION_TEMPLATES HIGH_BOUND
SYS ALL_SUMMARIES QUERY
SYS ALL_SUMMARY_AGGREGATES MEASURE
SYS ALL_TAB_COLS DATA_DEFAULT
SYS ALL_TAB_COLS_V$ DATA_DEFAULT
SYS ALL_TAB_COLUMNS DATA_DEFAULT
SYS ALL_TAB_PARTITIONS HIGH_VALUE
SYS ALL_TAB_SUBPARTITIONS HIGH_VALUE
SYS ALL_TRIGGERS TRIGGER_BODY
SYS ALL_VIEWS TEXT
SYS ALL_VIEWS_AE TEXT
SYS ALL_ZONEMAPS QUERY
SYS ALL_ZONEMAP_MEASURES MEASURE
SYS DBA_ADVISOR_SQLPLANS OTHER
SYS DBA_ARGUMENTS DEFAULT_VALUE
SYS DBA_CLUSTER_HASH_EXPRESSIONS HASH_EXPRESSION
SYS DBA_CONSTRAINTS SEARCH_CONDITION
SYS DBA_IM_EXPRESSIONS SQL_EXPRESSION
SYS DBA_IND_EXPRESSIONS COLUMN_EXPRESSION
SYS DBA_IND_PARTITIONS HIGH_VALUE
SYS DBA_IND_SUBPARTITIONS HIGH_VALUE
SYS DBA_MVIEWS QUERY
SYS DBA_MVIEW_AGGREGATES MEASURE
SYS DBA_MVIEW_ANALYSIS QUERY
SYS DBA_NESTED_TABLE_COLS DATA_DEFAULT
SYS DBA_OUTLINES SQL_TEXT
SYS DBA_REGISTERED_MVIEWS QUERY_TXT
SYS DBA_REGISTERED_SNAPSHOTS QUERY_TXT
SYS DBA_SNAPSHOTS QUERY
SYS DBA_SQLSET_PLANS OTHER
SYS DBA_SQLTUNE_PLANS OTHER
SYS DBA_SUBPARTITION_TEMPLATES HIGH_BOUND
SYS DBA_SUMMARIES QUERY
SYS DBA_SUMMARY_AGGREGATES MEASURE
SYS DBA_TAB_COLS DATA_DEFAULT
SYS DBA_TAB_COLS_V$ DATA_DEFAULT
SYS DBA_TAB_COLUMNS DATA_DEFAULT
SYS DBA_TAB_PARTITIONS HIGH_VALUE
SYS DBA_TAB_SUBPARTITIONS HIGH_VALUE
SYS DBA_TRIGGERS TRIGGER_BODY
SYS DBA_VIEWS TEXT
SYS DBA_VIEWS_AE TEXT
SYS DBA_ZONEMAPS QUERY
SYS DBA_ZONEMAP_MEASURES MEASURE
SYS USER_ADVISOR_SQLPLANS OTHER
SYS USER_ARGUMENTS DEFAULT_VALUE
SYS USER_CLUSTER_HASH_EXPRESSIONS HASH_EXPRESSION
SYS USER_CONSTRAINTS SEARCH_CONDITION
SYS USER_IM_EXPRESSIONS SQL_EXPRESSION
SYS USER_IND_EXPRESSIONS COLUMN_EXPRESSION
SYS USER_IND_PARTITIONS HIGH_VALUE
SYS USER_IND_SUBPARTITIONS HIGH_VALUE
SYS USER_MVIEWS QUERY
SYS USER_MVIEW_AGGREGATES MEASURE
SYS USER_MVIEW_ANALYSIS QUERY
SYS USER_NESTED_TABLE_COLS DATA_DEFAULT
SYS USER_OUTLINES SQL_TEXT
SYS USER_REGISTERED_MVIEWS QUERY_TXT
SYS USER_REGISTERED_SNAPSHOTS QUERY_TXT
SYS USER_SNAPSHOTS QUERY
SYS USER_SQLSET_PLANS OTHER
SYS USER_SQLTUNE_PLANS OTHER
SYS USER_SUBPARTITION_TEMPLATES HIGH_BOUND
SYS USER_SUMMARIES QUERY
SYS USER_SUMMARY_AGGREGATES MEASURE
SYS USER_TAB_COLS DATA_DEFAULT
SYS USER_TAB_COLS_V$ DATA_DEFAULT
SYS USER_TAB_COLUMNS DATA_DEFAULT
SYS USER_TAB_PARTITIONS HIGH_VALUE
SYS USER_TAB_SUBPARTITIONS HIGH_VALUE
SYS USER_TRIGGERS TRIGGER_BODY
SYS USER_VIEWS TEXT
SYS USER_VIEWS_AE TEXT
SYS USER_ZONEMAPS QUERY
SYS USER_ZONEMAP_MEASURES MEASURE
WMSYS ALL_WM_IND_EXPRESSIONS COLUMN_EXPRESSION
WMSYS USER_WM_IND_EXPRESSIONS COLUMN_EXPRESSION
So when we want to do some data mining against the dictionary, the following error is a common and frustrating one!
SQL> select *
2 from dba_views
3 where text like '%mystring%';
where text like '%mystring%'
*
ERROR at line 3:
ORA-00932: inconsistent datatypes: expected CHAR got LONG
I often hear the comment “Why not just in the next version, just change all those LONG’s to CLOB?”. I imagine it would be possible to do so, but just pause for a second and think of the amount of regression testing that would need to occur, not just inside the database group in the Oracle organization, but for every customer that has ever coded up a mechanism in PL/SQL, or C, or Java, or any other language that is potentially using a LONG column in one of their queries. I don’t know for sure, but I suspect that is the reason why we have added new columns to the dictionary rather than modify existing ones. For example, if you look at DBA_VIEWS, you can see the addition of a column TEXT_VC which is a VARCHAR2 equivalent to the pre-existing TEXT column which is a LONG.
SQL> desc DBA_VIEWS
Name Null? Type
----------------------------------- -------- ----------------
OWNER NOT NULL VARCHAR2(128)
VIEW_NAME NOT NULL VARCHAR2(128)
TEXT_LENGTH NUMBER
TEXT LONG
TEXT_VC VARCHAR2(4000)
TYPE_TEXT_LENGTH NUMBER
TYPE_TEXT VARCHAR2(4000)
OID_TEXT_LENGTH NUMBER
OID_TEXT VARCHAR2(4000)
VIEW_TYPE_OWNER VARCHAR2(128)
VIEW_TYPE VARCHAR2(128)
SUPERVIEW_NAME VARCHAR2(128)
EDITIONING_VIEW VARCHAR2(1)
READ_ONLY VARCHAR2(1)
CONTAINER_DATA VARCHAR2(1)
BEQUEATH VARCHAR2(12)
ORIGIN_CON_ID NUMBER
DEFAULT_COLLATION VARCHAR2(100)
CONTAINERS_DEFAULT VARCHAR2(3)
CONTAINER_MAP VARCHAR2(3)
EXTENDED_DATA_LINK VARCHAR2(3)
EXTENDED_DATA_LINK_MAP VARCHAR2(3)
TEXT_VC is a nice touch, but it would have been nice to have that as a CLOB column to get access to the entire DDL for the view should it exceed 4000 characters. I can’t solve that problem, but I thought perhaps I can give you a workable compromise. In 8i, the TO_LOB function was implemented to allow customers to migrate from LONG to CLOB with minimal fuss. I can take advantage of that to provide a CLOB-based copy of DBA_VIEWS:
SQL> create table system.t as
2 select
3 owner
4 ,view_name
5 ,text_length
6 ,to_lob(text) text
7 ,text_vc
8 ,type_text_length
9 ,type_text
10 ,oid_text_length
11 ,oid_text
12 ,view_type_owner
13 ,view_type
14 ,superview_name
15 ,editioning_view
16 ,read_only
17 ,container_data
18 ,bequeath
19 ,origin_con_id
20 ,default_collation
21 ,containers_default
22 ,container_map
23 ,extended_data_link
24 ,extended_data_link_map
25 from dba_views;
Table created.
SQL>
SQL> create index system.t_ix on system.t ( owner, view_name );
Index created.
That is all well and good, but of course, the moment I perform maintenance on any view in the system, that table will be “stale”. That still might be a perfectly reasonably solution for you – you just refresh the table contents (say) once per day, or when you do deployments of schema changes into your database. But perhaps we can do a little better than that. Using a DDL event trigger, we can capture changes on views and adjust our copy accordingly.
SQL> create or replace
2 trigger sys.view$clob$handler
3 after create or alter or drop or rename
4 on database
5 when ( ora_dict_obj_type = 'VIEW' )
6 declare
7 l_obj_name varchar2(128) := ora_dict_obj_name;
8 l_obj_owner varchar2(128) := ora_dict_obj_owner;
9
10 l_text varchar2(1000);
11 sql_text ora_name_list_t;
12 l_idx pls_integer;
13 begin
14 lock table system.t in exclusive mode;
15
16 --
17 -- remove the existing row for the view
18 --
19 delete from system.t where owner = l_obj_owner and view_name = l_obj_name;
20
21 --
22 -- if it is a rename event, we will try to derive the new name
23 -- from the sql statement by looking for a trailing "TO"
24 --
25 if ora_sysevent in ('RENAME') then
26 l_idx := ora_sql_txt(sql_text);
27 for i in 1 .. l_idx
28 loop
29 l_text := l_text || sql_text(i);
30 end loop;
31 l_idx := instr(lower(l_text),' to ');
32 if l_idx = 0 then
33 raise_application_error(-20000,'Could not find appropriate rename content');
34 end if;
35 --
36 -- The SQL has a trailing chr(0) which we need to remove
37 --
38 l_text := rtrim(ltrim(substr(l_text,l_idx+4)),' '||chr(0));
39 --
40 -- The object name in quotes mean we preserve the case specified, otherwise
41 -- we will normalize to upper
42 --
43 if l_text like '"%"' then
44 l_obj_name := rtrim(ltrim(l_text,'"'),'"');
45 else
46 l_obj_name := upper(l_text);
47 end if;
48 end if;
49
50 --
51 -- Now we insert the updated definition for the view, or
52 -- perhaps its new name
53 --
54 if ora_sysevent in ('CREATE','ALTER','RENAME') then
55 insert into system.t
56 select
57 owner
58 ,view_name
59 ,text_length
60 ,to_lob(text) text
61 ,text_vc
62 ,type_text_length
63 ,type_text
64 ,oid_text_length
65 ,oid_text
66 ,view_type_owner
67 ,view_type
68 ,superview_name
69 ,editioning_view
70 ,read_only
71 ,container_data
72 ,bequeath
73 ,origin_con_id
74 ,default_collation
75 ,containers_default
76 ,container_map
77 ,extended_data_link
78 ,extended_data_link_map
79 from dba_views
80 where owner = l_obj_owner
81 and view_name = l_obj_name;
82 end if;
83
84 end;
85 /
Trigger created.
Obviously, for more serious usage you’ll be choosing a better table name and not using SYS, but this is just a demo on my laptop. Unlike a DML trigger, where we have access to “new” and “old” images of the data, for a RENAME command, I had to probe the SQL text to try derive the new name. So lets now perform some view DDL and see how our tracking trigger accommodates the changes.
SQL> create view view1 as select * from ALL_objects;
View created.
SQL> create view view2 as select * from ALL_objects;
View created.
SQL> create view view3 as select * from view2;
View created.
SQL> drop view view1;
View dropped.
SQL> rename view2 to view2a;
Table renamed.
SQL>
SQL>
SQL> select count(*) from system.t;
COUNT(*)
----------
7347
SQL> select count(*) from dba_views;
COUNT(*)
----------
7347
SQL> select owner, view_name from dba_views
2 minus
3 select owner, view_name from system.t;
no rows selected
SQL>
SQL> select owner, view_name from system.t
2 minus
3 select owner, view_name from dba_views;
no rows selected
And there we have it – our own custom version of DBA_VIEWS where the TEXT is now exposed as a CLOB column. So now, mining that column for information is as easy as a simple predicate
SQL> select owner, view_name
2 from system.t
3 where text like 'selec%ALL_objects';
OWNER VIEW_NAME
------------------------------ -------------------
MCDONAC VIEW2A
Learning is not a spectator sport 
One comment