How to find which column has which LOB attached in Oracle12c XMLTYPE column?

Question

I have below table with two XMLTYPE Columns (ERROR_FLAG , COL_XML ) and One LOB Column (LOG_TEXT )

CREATE TABLE APPLICATION_LOG
(
  ID                    NUMBER                  NOT NULL,
  LOG_TIME              TIMESTAMP(6)            NOT NULL,
  ERROR_FLAG            XMLTYPE,
  LOG_TEXT              CLOB,
  COL_XML               XMLTYPE,
  LOG_MESSAGETYPE       VARCHAR2(30 BYTE)       NOT NULL
)
XMLTYPE COL_XML STORE AS SECUREFILE BINARY XML (
  TABLESPACE users
  ENABLE      STORAGE IN ROW
  CHUNK       8192
  NOCACHE
  NOLOGGING)
ALLOW NONSCHEMA
DISALLOW ANYSCHEMA
LOB (LOG_TEXT) STORE AS BASICFILE (
  TABLESPACE  users
  ENABLE      STORAGE IN ROW
  CHUNK       8192
  PCTVERSION  10
  NOCACHE
  NOLOGGING)
NOCOMPRESS 
TABLESPACE users
;

If I execute below query I get three rows

SELECT * 
  FROM dba_lobs l 
 WHERE l.table_name  = 'APPLICATION_LOG'
   AND l.owner = 'SYSTEM'

How to find out that first LOB is for which XMLTYPE Column (ERROR_FLAG OR COL_XML ) Because their names are now system generated ? How to find SYS_NC00004$ ----> corresponds to what ?

Answer 1

You can use such a self-join through use of dba_tab_cols(or user_tab_cols researching only for the current user) dictionary view such as

SELECT c2.data_type, c2.column_name, c1.data_type, c1.column_name
  FROM dba_tab_cols c1
  JOIN dba_tab_cols c2  
    ON c1.segment_column_id = c2.column_id  
   AND c1.table_name = c2.table_name 
   AND c2.data_type = 'XMLTYPE'
 WHERE c1.table_name = 'APPLICATION_LOG'

Demo