A simple 12c query with a cool result …

Its not immediately obvious the significance of this query…but trust me…you’ll love it πŸ™‚

 

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

5 thoughts on “A simple 12c query with a cool result …

  1. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s