A simple 12c query with a cool result …

November 6, 2014

1–2 minutes

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

6 responses to “A simple 12c query with a cool result …”

Yuri

November 6, 2014 at 9:46 pm

Has LONG gone?

Reply
connormcdonald

November 6, 2014 at 9:51 pm

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

Reply
Matthias Rogel

November 6, 2014 at 10:42 pm

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. connormcdonald
  
  November 7, 2014 at 6:31 am
  
  http://en.wikipedia.org/wiki/Kaizen 🙂
  
  Reply
Scott

November 7, 2014 at 9:12 am

I sense a good pun in there somewhere

Reply
UNLONG my dictionary – those pesky LONG columns – Learning is not a spectator sport

April 6, 2021 at 12:05 pm

[…] are not on the improve. Oracle has been making progress over the years, and if you refer to this post, you’ll see that varchar2 columns have been added where possible to make things […]

Reply