A simple 12c query with a cool result …

SQL> select table_name, column_name from dba_tab_cols
  2  where column_name like '%\_VC' escape '\'
  3  and owner = 'SYS'
  4  order by 1,2;

TABLE_NAME                     COLUMN_NAME
------------------------------ ----------------------
ALL_CONSTRAINTS                SEARCH_CONDITION_VC
ALL_VIEWS                      TEXT_VC
CDB_CONSTRAINTS                SEARCH_CONDITION_VC
CDB_VIEWS                      TEXT_VC
DBA_CONSTRAINTS                SEARCH_CONDITION_VC
DBA_VIEWS                      TEXT_VC
INT$DBA_CONSTRAINTS            SEARCH_CONDITION_VC
INT$DBA_VIEWS                  TEXT_VC
INT$INT$DBA_CONSTRAINTS        SEARCH_CONDITION_VC
USER_CONSTRAINTS               SEARCH_CONDITION_VC
USER_VIEWS                     TEXT_VC

6 comments

1. Has LONG gone?

   Reply

2. its still there….they’ve just added new columns 🙂

   Reply

3. Hmmm …

   a. why VARCHAR(4000) and not CLOB ?

   b. DBA_TAB_COLS still has a LONG column DATA_DEFAULT and no VC-column DATA_DEFAULT_VC, same holds for DBA_TAB_PARTITIONS.HIGH_VALUE …
   select c.table_name, c.column_name
   from dictionary d, dba_tab_cols c
   where c.table_name = d.table_name
   and c.data_type = ‘LONG’
   and not exists(
   select null
   from dba_tab_cols c_
   where c_.table_name = c.table_name
   and c_.column_name like c.column_name || ‘%’
   and c_.column_name != c.column_name
   and c_.data_type != ‘NUMBER’
   );
   See: even new 12c-Views for example DBA_ZONEMAP_MEASURES *ONLY HAVE A LONG COLUMN*

   Sorry, no, I don’t love it

   Reply

   1. http://en.wikipedia.org/wiki/Kaizen 🙂

      Reply

4. I sense a good pun in there somewhere

   Reply
5. Pingback: UNLONG my dictionary – those pesky LONG columns – Learning is not a spectator sport

This site uses Akismet to reduce spam. Learn how your comment data is processed.