Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

MAX_STRING_SIZE – stretching the dictionary

This post cycles back too some other historical posts of mine related to max_string_size and the fact that the data dictionary uses LONG data type columns because of historical reasons related to backward compatibility. Most of us probably have existing databases that have gone through the standard upgrade through various versions of the Oracle Database, and as such, all of our existing database have a max_string_size of STANDARD. Thus to convert to the larger string size, we start by setting max_string_size to EXTENDED, run the appropriate scripts after shutting out database down and re-opening it in upgrade mode, setting the appropriate initialization parameters for the instance in our SPFILE. But what if you are creating a new database? Do we follow the same steps – namely create the database and then apply the maximum string size changes in order to get all of that varchar2(32767) goodness?

I am going to suggest a different way. If you need to create a new database then there are benefits if you create that database from scratch with the maximum string size set to extended at the very start of the exercise.

That does imply a slightly slower creation time the first time you do this using the database creation assistant. To see why this is the case, let’s see what happens if you try to create a database with the standard preconfigured templates and also set the maximum string size to extended as part of the creation



C:\oracle\product\19\bin>dbca -silent -createDatabase  
       -templateName General_Purpose.dbc  -gdbname db19m -sid db19m 
       -responseFile NO_VALUE  -characterSet AL32UTF8  
       -sysPassword admin  -systemPassword admin  
       -createAsContainerDatabase false  -numberOfPDBs 0  
       -databaseType MULTIPURPOSE  -automaticMemoryManagement false  
       -storageType FS  -datafileDestination X:\oracle\oradata\  
       -ignorePreReqs -totalMemory 1536  -emConfiguration NONE  
       -initParams max_string_size=EXTENDED
       
Prepare for db operation
10% complete
Copying database files
40% complete
Creating and starting Oracle instance
42% complete
[WARNING] ORA-25153: Temporary Tablespace is Empty
ORA-06512: at line 3

43% complete
[WARNING] ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-14695: MAX_STRING_SIZE migration is incomplete

[FATAL] ORA-03114: not connected to ORACLE

60% complete
100% complete
[FATAL] ORA-03114: not connected to ORACLE

40% complete
10% complete
0% complete
Look at the log file "C:\oracle\cfgtoollogs\dbca\db19m\db19m1.log" for further details.

It looks to me like the creation assistant does not have the smarts out of the box to realise that the max_string_size of EXTENDED requires some post creation operations if you start with datafiles in the template that were built with the default setting of STANDARD. You can probably work around this using the post creation scripts option, but I did not pursue that any further. Using the “New_Database” template, which does not have any preconfigured data files, means the database dictionary is created from scripts and thus I can happily set max_string_size to extended and the database will create just fine.


C:\oracle\product\19\bin>dbca -silent -createDatabase  
       -templateName New_Database.dbt -gdbname db19m -sid db19m 
       -responseFile NO_VALUE  -characterSet AL32UTF8  
       -sysPassword admin  -systemPassword admin  
       -createAsContainerDatabase false  -numberOfPDBs 0  
       -databaseType MULTIPURPOSE  -automaticMemoryManagement false  
       -storageType FS  -datafileDestination X:\oracle\oradata\  
       -ignorePreReqs -totalMemory 1536  -emConfiguration NONE  
       -initParams max_string_size=EXTENDED

Prepare for db operation
4% complete
Creating and starting Oracle instance
6% complete
9% complete
Creating database files
13% complete
Creating data dictionary views
14% complete
17% complete
18% complete
21% complete
22% complete
24% complete
26% complete
Oracle JVM
33% complete
39% complete
46% complete
48% complete
Oracle Text
49% complete
51% complete
52% complete
Oracle Multimedia
65% complete
Oracle OLAP
66% complete
67% complete
68% complete
70% complete
Oracle Spatial
78% complete
Oracle Database Extensions for .NET
83% complete
Completing Database Creation
85% complete
87% complete
Executing Post Configuration Actions
100% complete
Database creation complete. For details check the logfiles at:
 C:\oracle\cfgtoollogs\dbca\db19m.
Database Information:
Global Database Name:db19m
System Identifier(SID):db19m
Look at the log file "C:\oracle\cfgtoollogs\dbca\db19m\db19m2.log" for further details.

But why did I go to all of this bother? Because if the database knows during the creation phase that you will be adopting a greater string size then that can also be reflected in the data dictionary. If you refer back to my post about those annoying LONG columns in the database, you can see from the description of the DBA_VIEWS dictionary view that we have tried to assist customers by including string based equivalents of those long columns. In my existing 19c database, where I did the usual “after the fact” conversion from max_string_size to EXTENDED, this is too late so to speak and as such the DBA_VIEWS columns are still capped at 4000 characters.


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)
 HAS_SENSITIVE_COLUMN                         VARCHAR2(3)
 ADMIT_NULL                                   VARCHAR2(3)
 PDB_LOCAL_ONLY                               VARCHAR2(3)

However with my freshly created database where I set max_string_size to EXTENDED at the very commencement of the creation, now take a look at my DBA_VIEWS dictionary view!


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(32767)
 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)
 HAS_SENSITIVE_COLUMN                         VARCHAR2(3)
 ADMIT_NULL                                   VARCHAR2(3)
 PDB_LOCAL_ONLY                               VARCHAR2(3)

Very cool indeed! It would be rare for many views to be more than 32,767 byte long.

But the benefits are far more wide ranging than just the DBA_VIEWS dictionary view . If I look at the definition of columns in the dictionary that now are at a larger size, we can see that many of the dictionary objects now contain a much larger capacity for holding those big strings


SQL> select table_name, column_name
  2  from   dba_tab_columns
  3  where  data_length = 32767
  4  order by 1,2;

TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
ALL_APPLY_ENQUEUE              DESTINATION_QUEUE_NAME
ALL_ATTRIBUTE_DIM_JOIN_PATHS   ON_CONDITION
ALL_AW_PROP                    PROPERTY_TYPE
ALL_AW_PROP                    PROPERTY_VALUE
ALL_COL_PENDING_STATS          HIGH_VALUE
ALL_COL_PENDING_STATS          LOW_VALUE
ALL_CONSTRAINTS                SEARCH_CONDITION_VC
ALL_GG_INBOUND_PROGRESS        APPLIED_HIGH_POSITION
ALL_GG_INBOUND_PROGRESS        APPLIED_LOW_POSITION
ALL_GG_INBOUND_PROGRESS        LOGBSN
ALL_GG_INBOUND_PROGRESS        OLDEST_POSITION
ALL_GG_INBOUND_PROGRESS        PROCESSED_LOW_POSITION
ALL_GG_INBOUND_PROGRESS        SPILL_POSITION
ALL_JAVA_COMPILER_OPTIONS      VALUE
ALL_NESTED_TABLE_COLS          HIGH_VALUE
ALL_NESTED_TABLE_COLS          LOW_VALUE
ALL_PART_COL_STATISTICS        HIGH_VALUE
ALL_PART_COL_STATISTICS        LOW_VALUE
ALL_PART_HISTOGRAMS            ENDPOINT_ACTUAL_VALUE
ALL_PART_HISTOGRAMS            ENDPOINT_ACTUAL_VALUE_RAW
ALL_SCHEDULER_CHAIN_RULES      ACTION
ALL_SCHEDULER_CHAIN_RULES      CONDITION
ALL_SCHEDULER_JOBS             RAISE_EVENTS
ALL_SCHEDULER_JOB_ARGS         VALUE
ALL_SCHEDULER_JOB_RUN_DETAILS  ERRORS
ALL_SCHEDULER_JOB_RUN_DETAILS  OUTPUT
ALL_SCHEDULER_PROGRAM_ARGS     DEFAULT_VALUE
ALL_STREAMS_TRANSFORMATIONS    COLUMN_TYPE
ALL_STREAMS_TRANSFORMATIONS    USER_FUNCTION_NAME
ALL_STREAMS_TRANSFORM_FUNCTION TRANSFORM_FUNCTION_NAME
ALL_STREAMS_TRANSFORM_FUNCTION VALUE_TYPE
ALL_SUBPART_COL_STATISTICS     HIGH_VALUE
ALL_SUBPART_COL_STATISTICS     LOW_VALUE
ALL_SUBPART_HISTOGRAMS         ENDPOINT_ACTUAL_VALUE
ALL_SUBPART_HISTOGRAMS         ENDPOINT_ACTUAL_VALUE_RAW
ALL_TAB_COLS                   HIGH_VALUE
ALL_TAB_COLS                   LOW_VALUE
ALL_TAB_COLS_V$                HIGH_VALUE
ALL_TAB_COLS_V$                LOW_VALUE
ALL_TAB_COLUMNS                HIGH_VALUE
ALL_TAB_COLUMNS                LOW_VALUE
ALL_TAB_COL_STATISTICS         HIGH_VALUE
ALL_TAB_COL_STATISTICS         LOW_VALUE
ALL_TAB_HISTGRM_PENDING_STATS  ENDPOINT_ACTUAL_VALUE
ALL_TAB_HISTGRM_PENDING_STATS  ENDPOINT_ACTUAL_VALUE_RAW
ALL_TAB_HISTOGRAMS             ENDPOINT_ACTUAL_VALUE
ALL_TAB_HISTOGRAMS             ENDPOINT_ACTUAL_VALUE_RAW
ALL_VIEWS                      TEXT_VC
ALL_VIEWS_AE                   TEXT_VC
ALL_WM_LOCKED_TABLES           LOCKING_STATE
ALL_WM_LOCKED_TABLES           LOCK_OWNER
ALL_WM_TAB_TRIGGERS            TRIGGER_TYPE
ALL_WM_VERSIONED_TABLES        CONFLICT
ALL_WM_VERSIONED_TABLES        DIFF
ALL_WM_VT_ERRORS               SQL_STR
ALL_XML_SCHEMAS                INT_OBJNAME
ALL_XML_SCHEMAS2               INT_OBJNAME
ALL_XML_TABLES                 TOKENSETS
ALL_XML_TAB_COLS               TOKENSETS
ALL_XSTREAM_TRANSFORMATIONS    COLUMN_TYPE
ALL_XSTREAM_TRANSFORMATIONS    USER_FUNCTION_NAME
AWR_CDB_CELL_DISKTYPE          CELL_NAME
AWR_CDB_CELL_DISKTYPE          FLASH_DISK_TYPE
AWR_CDB_CELL_DISKTYPE          HARD_DISK_TYPE
AWR_CDB_CELL_DISK_NAME         DISK
AWR_CDB_CELL_DISK_NAME         DISK_NAME
AWR_CDB_CELL_NAME              CELL_NAME
AWR_PDB_CELL_DISKTYPE          CELL_NAME
AWR_PDB_CELL_DISKTYPE          FLASH_DISK_TYPE
AWR_PDB_CELL_DISKTYPE          HARD_DISK_TYPE
AWR_PDB_CELL_DISK_NAME         DISK
AWR_PDB_CELL_DISK_NAME         DISK_NAME
AWR_PDB_CELL_NAME              CELL_NAME
AWR_ROOT_CELL_DISKTYPE         CELL_NAME
AWR_ROOT_CELL_DISKTYPE         FLASH_DISK_TYPE
AWR_ROOT_CELL_DISKTYPE         HARD_DISK_TYPE
AWR_ROOT_CELL_DISK_NAME        DISK
AWR_ROOT_CELL_DISK_NAME        DISK_NAME
AWR_ROOT_CELL_NAME             CELL_NAME
CDB_ADDM_FINDINGS              FINDING_NAME
CDB_ADDM_FINDINGS              IMPACT_TYPE
CDB_ADDM_FINDINGS              MESSAGE
CDB_ADDM_FINDINGS              MORE_INFO
CDB_ADDM_SYSTEM_DIRECTIVES     DESCRIPTION
CDB_ADDM_TASKS                 ERROR_MESSAGE
CDB_ADDM_TASKS                 STATUS_MESSAGE
CDB_ADDM_TASK_DIRECTIVES       DESCRIPTION
CDB_ADVISOR_ACTIONS            MESSAGE
CDB_ADVISOR_ACTIONS            RESULT_MESSAGE
CDB_ADVISOR_DEF_PARAMETERS     DESCRIPTION
CDB_ADVISOR_EXECUTIONS         ERROR_MESSAGE
CDB_ADVISOR_EXECUTIONS         STATUS_MESSAGE
CDB_ADVISOR_EXECUTION_TYPES    EXECUTION_DESCRIPTION
CDB_ADVISOR_EXEC_PARAMETERS    DESCRIPTION
CDB_ADVISOR_FINDINGS           FINDING_NAME
CDB_ADVISOR_FINDINGS           IMPACT_TYPE
CDB_ADVISOR_FINDINGS           MESSAGE
CDB_ADVISOR_FINDINGS           MORE_INFO
CDB_ADVISOR_FINDING_NAMES      FINDING_NAME
CDB_ADVISOR_JOURNAL            JOURNAL_ENTRY
CDB_ADVISOR_LOG                ERROR_MESSAGE
CDB_ADVISOR_LOG                STATUS_MESSAGE
CDB_ADVISOR_PARAMETERS         DESCRIPTION
CDB_ADVISOR_PARAMETERS_PROJ    DESCRIPTION
CDB_ADVISOR_RATIONALE          IMPACT_TYPE
CDB_ADVISOR_RATIONALE          MESSAGE
CDB_ADVISOR_RECOMMENDATIONS    BENEFIT_TYPE
CDB_ADVISOR_SQLW_JOURNAL       JOURNAL_ENTRY
CDB_ADVISOR_SQLW_PARAMETERS    DESCRIPTION
CDB_ADVISOR_TASKS              ERROR_MESSAGE
CDB_ADVISOR_TASKS              STATUS_MESSAGE
CDB_ALERT_HISTORY              REASON
CDB_ALERT_HISTORY              SUGGESTED_ACTION
CDB_ALERT_HISTORY_DETAIL       REASON
CDB_ALERT_HISTORY_DETAIL       SUGGESTED_ACTION
CDB_APPLY_ENQUEUE              DESTINATION_QUEUE_NAME
CDB_ATTRIBUTE_DIM_JOIN_PATHS   ON_CONDITION
CDB_AUTOTASK_CLIENT            ATTRIBUTES
CDB_AUTOTASK_OPERATION         ATTRIBUTES
CDB_AUTOTASK_TASK              ATTRIBUTES
CDB_AUTO_INDEX_CONFIG          PARAMETER_VALUE
CDB_AUTO_INDEX_EXECUTIONS      ERROR_MESSAGE
CDB_AW_PROP                    PROPERTY_TYPE
CDB_AW_PROP                    PROPERTY_VALUE
CDB_COL_PENDING_STATS          HIGH_VALUE
CDB_COL_PENDING_STATS          LOW_VALUE
CDB_CONNECT_ROLE_GRANTEES      PATH_OF_CONNECT_ROLE_GRANT
CDB_CONSTRAINTS                SEARCH_CONDITION_VC
CDB_GG_INBOUND_PROGRESS        APPLIED_HIGH_POSITION
CDB_GG_INBOUND_PROGRESS        APPLIED_LOW_POSITION
CDB_GG_INBOUND_PROGRESS        LOGBSN
CDB_GG_INBOUND_PROGRESS        OLDEST_POSITION
CDB_GG_INBOUND_PROGRESS        PROCESSED_LOW_POSITION
CDB_GG_INBOUND_PROGRESS        SPILL_POSITION
CDB_HIST_CELL_DISKTYPE         CELL_NAME
CDB_HIST_CELL_DISKTYPE         FLASH_DISK_TYPE
CDB_HIST_CELL_DISKTYPE         HARD_DISK_TYPE
CDB_HIST_CELL_DISK_NAME        DISK
CDB_HIST_CELL_DISK_NAME        DISK_NAME
CDB_HIST_CELL_NAME             CELL_NAME
CDB_JAVA_COMPILER_OPTIONS      VALUE
CDB_NESTED_TABLE_COLS          HIGH_VALUE
CDB_NESTED_TABLE_COLS          LOW_VALUE
CDB_OUTSTANDING_ALERTS         REASON
CDB_OUTSTANDING_ALERTS         SUGGESTED_ACTION
CDB_PART_COL_STATISTICS        HIGH_VALUE
CDB_PART_COL_STATISTICS        LOW_VALUE
CDB_PART_HISTOGRAMS            ENDPOINT_ACTUAL_VALUE
CDB_PART_HISTOGRAMS            ENDPOINT_ACTUAL_VALUE_RAW
CDB_REGISTRY                   OTHER_SCHEMAS
CDB_REGISTRY_HIERARCHY         COMP_ID
CDB_SCHEDULER_CHAIN_RULES      ACTION
CDB_SCHEDULER_CHAIN_RULES      CONDITION
CDB_SCHEDULER_JOBS             RAISE_EVENTS
CDB_SCHEDULER_JOB_ARGS         VALUE
CDB_SCHEDULER_JOB_RUN_DETAILS  ERRORS
CDB_SCHEDULER_JOB_RUN_DETAILS  OUTPUT
CDB_SCHEDULER_PROGRAM_ARGS     DEFAULT_VALUE
CDB_SERVER_REGISTRY            OTHER_SCHEMAS
CDB_SQL_MANAGEMENT_CONFIG      PARAMETER_VALUE
CDB_SQL_PLAN_DIR_OBJECTS       OBJECT_NAME
CDB_SQL_QUARANTINE             CPU_TIME
CDB_SQL_QUARANTINE             ELAPSED_TIME
CDB_SQL_QUARANTINE             IO_LOGICAL
CDB_SQL_QUARANTINE             IO_MEGABYTES
CDB_SQL_QUARANTINE             IO_REQUESTS
CDB_STREAMS_ADD_COLUMN         COLUMN_TYPE
CDB_STREAMS_TRANSFORMATIONS    COLUMN_TYPE
CDB_STREAMS_TRANSFORMATIONS    USER_FUNCTION_NAME
CDB_STREAMS_TRANSFORM_FUNCTION TRANSFORM_FUNCTION_NAME
CDB_STREAMS_TRANSFORM_FUNCTION VALUE_TYPE
CDB_SUBPART_COL_STATISTICS     HIGH_VALUE
CDB_SUBPART_COL_STATISTICS     LOW_VALUE
CDB_SUBPART_HISTOGRAMS         ENDPOINT_ACTUAL_VALUE
CDB_SUBPART_HISTOGRAMS         ENDPOINT_ACTUAL_VALUE_RAW
CDB_TAB_COLS                   HIGH_VALUE
CDB_TAB_COLS                   LOW_VALUE
CDB_TAB_COLS_V$                HIGH_VALUE
CDB_TAB_COLS_V$                LOW_VALUE
CDB_TAB_COLUMNS                HIGH_VALUE
CDB_TAB_COLUMNS                LOW_VALUE
CDB_TAB_COL_STATISTICS         HIGH_VALUE
CDB_TAB_COL_STATISTICS         LOW_VALUE
CDB_TAB_HISTGRM_PENDING_STATS  ENDPOINT_ACTUAL_VALUE
CDB_TAB_HISTGRM_PENDING_STATS  ENDPOINT_ACTUAL_VALUE_RAW
CDB_TAB_HISTOGRAMS             ENDPOINT_ACTUAL_VALUE
CDB_TAB_HISTOGRAMS             ENDPOINT_ACTUAL_VALUE_RAW
CDB_VIEWS                      TEXT_VC
CDB_VIEWS_AE                   TEXT_VC
CDB_WM_VERSIONED_TABLES        CONFLICT
CDB_WM_VERSIONED_TABLES        DIFF
CDB_WM_VT_ERRORS               SQL_STR
CDB_WORKLOAD_DIV_SUMMARY       EXPECTED_ERROR_MESSAGE
CDB_WORKLOAD_DIV_SUMMARY       OBSERVED_ERROR_MESSAGE
CDB_WORKLOAD_REPLAY_DIVERGENCE EXPECTED_ERROR_MESSAGE
CDB_WORKLOAD_REPLAY_DIVERGENCE OBSERVED_ERROR_MESSAGE
CDB_XML_SCHEMAS                INT_OBJNAME
CDB_XML_TABLES                 TOKENSETS
CDB_XML_TAB_COLS               TOKENSETS
CDB_XSTREAM_TRANSFORMATIONS    COLUMN_TYPE
CDB_XSTREAM_TRANSFORMATIONS    USER_FUNCTION_NAME
DBA_ADDM_FINDINGS              FINDING_NAME
DBA_ADDM_FINDINGS              IMPACT_TYPE
DBA_ADDM_FINDINGS              MESSAGE
DBA_ADDM_FINDINGS              MORE_INFO
DBA_ADDM_SYSTEM_DIRECTIVES     DESCRIPTION
DBA_ADDM_TASKS                 ERROR_MESSAGE
DBA_ADDM_TASKS                 STATUS_MESSAGE
DBA_ADDM_TASK_DIRECTIVES       DESCRIPTION
DBA_ADVISOR_ACTIONS            MESSAGE
DBA_ADVISOR_ACTIONS            RESULT_MESSAGE
DBA_ADVISOR_DEF_PARAMETERS     DESCRIPTION
DBA_ADVISOR_EXECUTIONS         ERROR_MESSAGE
DBA_ADVISOR_EXECUTIONS         STATUS_MESSAGE
DBA_ADVISOR_EXECUTION_TYPES    EXECUTION_DESCRIPTION
DBA_ADVISOR_EXEC_PARAMETERS    DESCRIPTION
DBA_ADVISOR_FINDINGS           FINDING_NAME
DBA_ADVISOR_FINDINGS           IMPACT_TYPE
DBA_ADVISOR_FINDINGS           MESSAGE
DBA_ADVISOR_FINDINGS           MORE_INFO
DBA_ADVISOR_FINDING_NAMES      FINDING_NAME
DBA_ADVISOR_JOURNAL            JOURNAL_ENTRY
DBA_ADVISOR_LOG                ERROR_MESSAGE
DBA_ADVISOR_LOG                STATUS_MESSAGE
DBA_ADVISOR_PARAMETERS         DESCRIPTION
DBA_ADVISOR_PARAMETERS_PROJ    DESCRIPTION
DBA_ADVISOR_RATIONALE          IMPACT_TYPE
DBA_ADVISOR_RATIONALE          MESSAGE
DBA_ADVISOR_RECOMMENDATIONS    BENEFIT_TYPE
DBA_ADVISOR_SQLW_JOURNAL       JOURNAL_ENTRY
DBA_ADVISOR_SQLW_PARAMETERS    DESCRIPTION
DBA_ADVISOR_TASKS              ERROR_MESSAGE
DBA_ADVISOR_TASKS              STATUS_MESSAGE
DBA_ALERT_HISTORY              REASON
DBA_ALERT_HISTORY              SUGGESTED_ACTION
DBA_ALERT_HISTORY_DETAIL       REASON
DBA_ALERT_HISTORY_DETAIL       SUGGESTED_ACTION
DBA_APPLY_ENQUEUE              DESTINATION_QUEUE_NAME
DBA_ATTRIBUTE_DIM_JOIN_PATHS   ON_CONDITION
DBA_AUTOTASK_CLIENT            ATTRIBUTES
DBA_AUTOTASK_OPERATION         ATTRIBUTES
DBA_AUTOTASK_TASK              ATTRIBUTES
DBA_AUTO_INDEX_CONFIG          PARAMETER_VALUE
DBA_AUTO_INDEX_EXECUTIONS      ERROR_MESSAGE
DBA_AW_PROP                    PROPERTY_TYPE
DBA_AW_PROP                    PROPERTY_VALUE
DBA_COL_PENDING_STATS          HIGH_VALUE
DBA_COL_PENDING_STATS          LOW_VALUE
DBA_CONNECT_ROLE_GRANTEES      PATH_OF_CONNECT_ROLE_GRANT
DBA_CONSTRAINTS                SEARCH_CONDITION_VC
DBA_GG_INBOUND_PROGRESS        APPLIED_HIGH_POSITION
DBA_GG_INBOUND_PROGRESS        APPLIED_LOW_POSITION
DBA_GG_INBOUND_PROGRESS        LOGBSN
DBA_GG_INBOUND_PROGRESS        OLDEST_POSITION
DBA_GG_INBOUND_PROGRESS        PROCESSED_LOW_POSITION
DBA_GG_INBOUND_PROGRESS        SPILL_POSITION
DBA_HIST_CELL_DISKTYPE         CELL_NAME
DBA_HIST_CELL_DISKTYPE         FLASH_DISK_TYPE
DBA_HIST_CELL_DISKTYPE         HARD_DISK_TYPE
DBA_HIST_CELL_DISK_NAME        DISK
DBA_HIST_CELL_DISK_NAME        DISK_NAME
DBA_HIST_CELL_NAME             CELL_NAME
DBA_JAVA_COMPILER_OPTIONS      VALUE
DBA_NESTED_TABLE_COLS          HIGH_VALUE
DBA_NESTED_TABLE_COLS          LOW_VALUE
DBA_OUTSTANDING_ALERTS         REASON
DBA_OUTSTANDING_ALERTS         SUGGESTED_ACTION
DBA_PART_COL_STATISTICS        HIGH_VALUE
DBA_PART_COL_STATISTICS        LOW_VALUE
DBA_PART_HISTOGRAMS            ENDPOINT_ACTUAL_VALUE
DBA_PART_HISTOGRAMS            ENDPOINT_ACTUAL_VALUE_RAW
DBA_REGISTRY                   OTHER_SCHEMAS
DBA_REGISTRY_HIERARCHY         COMP_ID
DBA_SCHEDULER_CHAIN_RULES      ACTION
DBA_SCHEDULER_CHAIN_RULES      CONDITION
DBA_SCHEDULER_JOBS             RAISE_EVENTS
DBA_SCHEDULER_JOB_ARGS         VALUE
DBA_SCHEDULER_JOB_RUN_DETAILS  ERRORS
DBA_SCHEDULER_JOB_RUN_DETAILS  OUTPUT
DBA_SCHEDULER_PROGRAM_ARGS     DEFAULT_VALUE
DBA_SERVER_REGISTRY            OTHER_SCHEMAS
DBA_SQL_MANAGEMENT_CONFIG      PARAMETER_VALUE
DBA_SQL_PLAN_DIR_OBJECTS       OBJECT_NAME
DBA_SQL_QUARANTINE             CPU_TIME
DBA_SQL_QUARANTINE             ELAPSED_TIME
DBA_SQL_QUARANTINE             IO_LOGICAL
DBA_SQL_QUARANTINE             IO_MEGABYTES
DBA_SQL_QUARANTINE             IO_REQUESTS
DBA_STREAMS_ADD_COLUMN         COLUMN_TYPE
DBA_STREAMS_TRANSFORMATIONS    COLUMN_TYPE
DBA_STREAMS_TRANSFORMATIONS    USER_FUNCTION_NAME
DBA_STREAMS_TRANSFORM_FUNCTION TRANSFORM_FUNCTION_NAME
DBA_STREAMS_TRANSFORM_FUNCTION VALUE_TYPE
DBA_SUBPART_COL_STATISTICS     HIGH_VALUE
DBA_SUBPART_COL_STATISTICS     LOW_VALUE
DBA_SUBPART_HISTOGRAMS         ENDPOINT_ACTUAL_VALUE
DBA_SUBPART_HISTOGRAMS         ENDPOINT_ACTUAL_VALUE_RAW
DBA_TAB_COLS                   HIGH_VALUE
DBA_TAB_COLS                   LOW_VALUE
DBA_TAB_COLS_V$                HIGH_VALUE
DBA_TAB_COLS_V$                LOW_VALUE
DBA_TAB_COLUMNS                HIGH_VALUE
DBA_TAB_COLUMNS                LOW_VALUE
DBA_TAB_COL_STATISTICS         HIGH_VALUE
DBA_TAB_COL_STATISTICS         LOW_VALUE
DBA_TAB_HISTGRM_PENDING_STATS  ENDPOINT_ACTUAL_VALUE
DBA_TAB_HISTGRM_PENDING_STATS  ENDPOINT_ACTUAL_VALUE_RAW
DBA_TAB_HISTOGRAMS             ENDPOINT_ACTUAL_VALUE
DBA_TAB_HISTOGRAMS             ENDPOINT_ACTUAL_VALUE_RAW
DBA_VIEWS                      TEXT_VC
DBA_VIEWS_AE                   TEXT_VC
DBA_WM_VERSIONED_TABLES        CONFLICT
DBA_WM_VERSIONED_TABLES        DIFF
DBA_WM_VT_ERRORS               SQL_STR
DBA_WORKLOAD_DIV_SUMMARY       EXPECTED_ERROR_MESSAGE
DBA_WORKLOAD_DIV_SUMMARY       OBSERVED_ERROR_MESSAGE
DBA_WORKLOAD_REPLAY_DIVERGENCE EXPECTED_ERROR_MESSAGE
DBA_WORKLOAD_REPLAY_DIVERGENCE OBSERVED_ERROR_MESSAGE
DBA_XML_SCHEMAS                INT_OBJNAME
DBA_XML_TABLES                 TOKENSETS
DBA_XML_TAB_COLS               TOKENSETS
DBA_XSTREAM_TRANSFORMATIONS    COLUMN_TYPE
DBA_XSTREAM_TRANSFORMATIONS    USER_FUNCTION_NAME
EXU10ASC                       HIVAL
EXU10ASC                       LOWVAL
EXU10ASCU                      HIVAL
EXU10ASCU                      LOWVAL
EXU8ASC                        HIVAL
EXU8ASC                        LOWVAL
EXU8ASCU                       HIVAL
EXU8ASCU                       LOWVAL
INT$DBA_APP_STATEMENTS         SQLSTMT
INT$DBA_ATTR_DIM_JOIN_PATHS    ON_CONDITION
INT$DBA_CONSTRAINTS            SEARCH_CONDITION_VC
INT$DBA_VIEWS                  TEXT_VC
INT$DBA_VIEWS_AE               TEXT_VC
INT$INT$DBA_CONSTRAINTS        SEARCH_CONDITION_VC
KU$_10_1_DBLINK_VIEW           AUTHPWDX
KU$_10_1_DBLINK_VIEW           PASSWORDX
KU$_10_1_FHTABLE_VIEW          TSTZ_COLS
KU$_10_1_FHTABLE_VIEW          XMLHIERARCHY
KU$_10_1_HTABLE_VIEW           TSTZ_COLS
KU$_10_1_IOTABLE_VIEW          TSTZ_COLS
KU$_10_1_IOTABLE_VIEW          XMLHIERARCHY
KU$_10_1_PFHTABLE_VIEW         TSTZ_COLS
KU$_10_1_PFHTABLE_VIEW         XMLHIERARCHY
KU$_10_1_PHTABLE_VIEW          TSTZ_COLS
KU$_10_1_PIOTABLE_VIEW         TSTZ_COLS
KU$_10_1_PIOTABLE_VIEW         XMLHIERARCHY
KU$_10_1_PTAB_COL_STATS_VIEW   HIVAL
KU$_10_1_PTAB_COL_STATS_VIEW   LOWVAL
KU$_10_1_TABLE_DATA_VIEW       TSTZ_COLS
KU$_10_1_TAB_COL_STATS_VIEW    HIVAL
KU$_10_1_TAB_COL_STATS_VIEW    LOWVAL
KU$_10_2_FHTABLE_VIEW          TSTZ_COLS
KU$_10_2_FHTABLE_VIEW          XMLHIERARCHY
KU$_10_2_STRMCOLTYPE_VIEW      HASHCODE
KU$_10_2_STRMCOL_VIEW          BASE_COL_NAME
KU$_10_2_STRMSUBCOLTYPE_VIEW   HASHCODE
KU$_10_2_STRMTABLE_VIEW        FDO
KU$_10_2_TABLE_DATA_VIEW       TSTZ_COLS
KU$_10_2_TAB_COL_VIEW          DEFAULT_VAL
KU$_11_2_VIEW_VIEW             TRANSTEXT
KU$_11_2_VIEW_VIEW             UNDERTEXT
KU$_ACPTABLE_VIEW              TSTZ_COLS
KU$_ACPTABLE_VIEW              XMLHIERARCHY
KU$_ADD_SNAP_VIEW              REF_ADD_DBA
KU$_ADD_SNAP_VIEW              REF_ADD_USER
KU$_ARGUMENT_VIEW              DEFAULT_VAL
KU$_ATTR_DIM_JOIN_PATH_VIEW    ON_CONDITION
KU$_COLTYPE_VIEW               HASHCODE
KU$_COLTYPE_VIEW               HAS_TSTZ
KU$_COLUMN_VIEW                ATTRNAME
KU$_COLUMN_VIEW                ATTRNAME2
KU$_COLUMN_VIEW                BASE_COL_NAME
KU$_COLUMN_VIEW                DEFAULT_VAL
KU$_COLUMN_VIEW                FULLATTRNAME
KU$_COL_STATS_VIEW             HIVAL
KU$_COL_STATS_VIEW             HIVAL_1000
KU$_COL_STATS_VIEW             LOWVAL
KU$_COL_STATS_VIEW             LOWVAL_1000
KU$_DBLINK_VIEW                AUTHPWDX
KU$_DBLINK_VIEW                PASSWORDX
KU$_EQNTABLE_DATA_VIEW         TSTZ_COLS
KU$_FHTABLE_VIEW               TSTZ_COLS
KU$_FHTABLE_VIEW               XMLHIERARCHY
KU$_HISTGRM_VIEW               EPVALUE
KU$_HISTGRM_VIEW               EPVALUE_RAW
KU$_HTABLE_DATA_VIEW           TSTZ_COLS
KU$_HTABLE_VIEW                TSTZ_COLS
KU$_HTPART_DATA_VIEW           TSTZ_COLS
KU$_HTSPART_DATA_VIEW          TSTZ_COLS
KU$_IND_COL_VIEW               DEFAULT_VAL
KU$_IND_COMPART_VIEW           HIBOUNDVAL
KU$_IND_PART_VIEW              HIBOUNDVAL
KU$_IOTABLE_DATA_VIEW          TSTZ_COLS
KU$_IOTABLE_VIEW               TSTZ_COLS
KU$_IOTABLE_VIEW               XMLHIERARCHY
KU$_IOTPART_DATA_VIEW          TSTZ_COLS
KU$_NIOTABLE_DATA_VIEW         TSTZ_COLS
KU$_NTABLE_DATA_VIEW           TSTZ_COLS
KU$_P2TCOLUMN_VIEW             ATTRNAME
KU$_P2TCOLUMN_VIEW             ATTRNAME2
KU$_P2TCOLUMN_VIEW             BASE_COL_NAME
KU$_P2TCOLUMN_VIEW             DEFAULT_VAL
KU$_P2TCOLUMN_VIEW             FULLATTRNAME
KU$_P2TPARTCOL_VIEW            ATTRNAME
KU$_P2TPARTCOL_VIEW            ATTRNAME2
KU$_P2TPARTCOL_VIEW            BASE_COL_NAME
KU$_P2TPARTCOL_VIEW            DEFAULT_VAL
KU$_P2TPARTCOL_VIEW            FULLATTRNAME
KU$_PARTITION_VIEW             TSTZ_COLS
KU$_PARTITION_VIEW             XMLHIERARCHY
KU$_PCOLUMN_VIEW               ATTRNAME
KU$_PCOLUMN_VIEW               ATTRNAME2
KU$_PCOLUMN_VIEW               BASE_COL_NAME
KU$_PCOLUMN_VIEW               DEFAULT_VAL
KU$_PCOLUMN_VIEW               FULLATTRNAME
KU$_PFHTABLE_VIEW              TSTZ_COLS
KU$_PFHTABLE_VIEW              XMLHIERARCHY
KU$_PHTABLE_VIEW               TSTZ_COLS
KU$_PIOTABLE_VIEW              TSTZ_COLS
KU$_PIOTABLE_VIEW              XMLHIERARCHY
KU$_PIOT_PART_VIEW             HIBOUNDVAL
KU$_PRIM_COLUMN_VIEW           ATTRNAME2
KU$_PRIM_COLUMN_VIEW           DEFAULT_VAL
KU$_REFGROUP_VIEW              REF_MAKE_DBA
KU$_REFGROUP_VIEW              REF_MAKE_USER
KU$_SIMPLE_COL_VIEW            ATTRNAME
KU$_SIMPLE_COL_VIEW            DEFAULT_VAL
KU$_SIMPLE_PKREF_COL_VIEW      DEFAULT_VAL
KU$_SIMPLE_SETID_COL_VIEW      DEFAULT_VAL
KU$_SP2TCOLUMN_VIEW            ATTRNAME
KU$_SP2TCOLUMN_VIEW            ATTRNAME2
KU$_SP2TCOLUMN_VIEW            BASE_COL_NAME
KU$_SP2TCOLUMN_VIEW            DEFAULT_VAL
KU$_SP2TCOLUMN_VIEW            FULLATTRNAME
KU$_SP2TPARTCOL_VIEW           ATTRNAME
KU$_SP2TPARTCOL_VIEW           ATTRNAME2
KU$_SP2TPARTCOL_VIEW           BASE_COL_NAME
KU$_SP2TPARTCOL_VIEW           DEFAULT_VAL
KU$_SP2TPARTCOL_VIEW           FULLATTRNAME
KU$_STRMCOLTYPE_VIEW           HASHCODE
KU$_STRMCOL_VIEW               ATTRNAME2
KU$_STRMCOL_VIEW               BASE_COL_NAME
KU$_STRMSUBCOLTYPE_VIEW        HASHCODE
KU$_STRMTABLE_VIEW             FDO
KU$_STRMTABLE_VIEW             VERS_MINOR
KU$_SUBCOLTYPE_VIEW            HASHCODE
KU$_SUBPARTITION_VIEW          TSTZ_COLS
KU$_SUBPARTITION_VIEW          XMLHIERARCHY
KU$_TABLE_DATA_VIEW            TSTZ_COLS
KU$_TAB_COL_VIEW               DEFAULT_VAL
KU$_TAB_COMPART_VIEW           HIBOUNDVAL
KU$_TAB_PART_VIEW              HIBOUNDVAL
KU$_TAB_SUBPART_VIEW           HIBOUNDVAL
KU$_TAB_TSUBPART_VIEW          HIBOUNDVAL
KU$_TYPE_VIEW                  HASHCODE
KU$_VIEW_VIEW                  TRANSTEXT
KU$_VIEW_VIEW                  UNDERTEXT
KU$_XMLSCHEMA_ELMT_VIEW        ELEMENT_NAME
LOCAL_CHUNK_TYPES              SHARDGROUP_NAME
MGMT_BSLN_BASELINES            TARGET_UID
MGMT_BSLN_DATASOURCES          DATASOURCE_GUID
MGMT_BSLN_DATASOURCES          METRIC_UID
MGMT_BSLN_DATASOURCES          TARGET_UID
MGMT_BSLN_METRICS              METRIC_UID
MGMT_BSLN_STATISTICS           DATASOURCE_GUID
MGMT_BSLN_THRESHOLD_PARMS      DATASOURCE_GUID
RESOURCE_VIEW                  ANY_PATH
SCHEDULER_JOB_ARGS             VALUE
SCHEDULER_JOB_ARGS_TBL         VALUE
SCHEDULER_PROGRAM_ARGS         DEFAULT_VALUE
SCHEDULER_PROGRAM_ARGS_TBL     DEFAULT_VALUE
SHA_DATABASES                  VERSION
SQT_TAB_COL_STATISTICS         HIGH_VALUE
SQT_TAB_COL_STATISTICS         LOW_VALUE
USER_ADDM_FINDINGS             FINDING_NAME
USER_ADDM_FINDINGS             IMPACT_TYPE
USER_ADDM_FINDINGS             MESSAGE
USER_ADDM_FINDINGS             MORE_INFO
USER_ADDM_TASKS                ERROR_MESSAGE
USER_ADDM_TASKS                STATUS_MESSAGE
USER_ADDM_TASK_DIRECTIVES      DESCRIPTION
USER_ADVISOR_ACTIONS           MESSAGE
USER_ADVISOR_ACTIONS           RESULT_MESSAGE
USER_ADVISOR_EXECUTIONS        ERROR_MESSAGE
USER_ADVISOR_EXECUTIONS        STATUS_MESSAGE
USER_ADVISOR_EXEC_PARAMETERS   DESCRIPTION
USER_ADVISOR_FINDINGS          FINDING_NAME
USER_ADVISOR_FINDINGS          IMPACT_TYPE
USER_ADVISOR_FINDINGS          MESSAGE
USER_ADVISOR_FINDINGS          MORE_INFO
USER_ADVISOR_JOURNAL           JOURNAL_ENTRY
USER_ADVISOR_LOG               ERROR_MESSAGE
USER_ADVISOR_LOG               STATUS_MESSAGE
USER_ADVISOR_PARAMETERS        DESCRIPTION
USER_ADVISOR_RATIONALE         IMPACT_TYPE
USER_ADVISOR_RATIONALE         MESSAGE
USER_ADVISOR_RECOMMENDATIONS   BENEFIT_TYPE
USER_ADVISOR_SQLW_JOURNAL      JOURNAL_ENTRY
USER_ADVISOR_SQLW_PARAMETERS   DESCRIPTION
USER_ADVISOR_TASKS             ERROR_MESSAGE
USER_ADVISOR_TASKS             STATUS_MESSAGE
USER_ATTRIBUTE_DIM_JOIN_PATHS  ON_CONDITION
USER_AW_PROP                   PROPERTY_TYPE
USER_AW_PROP                   PROPERTY_VALUE
USER_COL_PENDING_STATS         HIGH_VALUE
USER_COL_PENDING_STATS         LOW_VALUE
USER_CONSTRAINTS               SEARCH_CONDITION_VC
USER_JAVA_COMPILER_OPTIONS     VALUE
USER_NESTED_TABLE_COLS         HIGH_VALUE
USER_NESTED_TABLE_COLS         LOW_VALUE
USER_PART_COL_STATISTICS       HIGH_VALUE
USER_PART_COL_STATISTICS       LOW_VALUE
USER_PART_HISTOGRAMS           ENDPOINT_ACTUAL_VALUE
USER_PART_HISTOGRAMS           ENDPOINT_ACTUAL_VALUE_RAW
USER_REGISTRY                  OTHER_SCHEMAS
USER_SCHEDULER_CHAIN_RULES     ACTION
USER_SCHEDULER_CHAIN_RULES     CONDITION
USER_SCHEDULER_JOBS            RAISE_EVENTS
USER_SCHEDULER_JOB_ARGS        VALUE
USER_SCHEDULER_JOB_RUN_DETAILS ERRORS
USER_SCHEDULER_JOB_RUN_DETAILS OUTPUT
USER_SCHEDULER_PROGRAM_ARGS    DEFAULT_VALUE
USER_SUBPART_COL_STATISTICS    HIGH_VALUE
USER_SUBPART_COL_STATISTICS    LOW_VALUE
USER_SUBPART_HISTOGRAMS        ENDPOINT_ACTUAL_VALUE
USER_SUBPART_HISTOGRAMS        ENDPOINT_ACTUAL_VALUE_RAW
USER_TAB_COLS                  HIGH_VALUE
USER_TAB_COLS                  LOW_VALUE
USER_TAB_COLS_V$               HIGH_VALUE
USER_TAB_COLS_V$               LOW_VALUE
USER_TAB_COLUMNS               HIGH_VALUE
USER_TAB_COLUMNS               LOW_VALUE
USER_TAB_COL_STATISTICS        HIGH_VALUE
USER_TAB_COL_STATISTICS        LOW_VALUE
USER_TAB_HISTGRM_PENDING_STATS ENDPOINT_ACTUAL_VALUE
USER_TAB_HISTGRM_PENDING_STATS ENDPOINT_ACTUAL_VALUE_RAW
USER_TAB_HISTOGRAMS            ENDPOINT_ACTUAL_VALUE
USER_TAB_HISTOGRAMS            ENDPOINT_ACTUAL_VALUE_RAW
USER_VIEWS                     TEXT_VC
USER_VIEWS_AE                  TEXT_VC
USER_WM_LOCKED_TABLES          LOCKING_STATE
USER_WM_LOCKED_TABLES          LOCK_OWNER
USER_WM_TAB_TRIGGERS           TRIGGER_TYPE
USER_WM_VERSIONED_TABLES       CONFLICT
USER_WM_VERSIONED_TABLES       DIFF
USER_WM_VT_ERRORS              SQL_STR
USER_XML_SCHEMAS               INT_OBJNAME
USER_XML_TABLES                TOKENSETS
USER_XML_TAB_COLS              TOKENSETS
V_$BACKUP_ARCHIVELOG_DETAILS   FILESIZE_DISPLAY
V_$BACKUP_ARCHIVELOG_SUMMARY   INPUT_BYTES_DISPLAY
V_$BACKUP_ARCHIVELOG_SUMMARY   OUTPUT_BYTES_DISPLAY
V_$BACKUP_CONTROLFILE_DETAILS  FILESIZE_DISPLAY
V_$BACKUP_CONTROLFILE_SUMMARY  INPUT_BYTES_DISPLAY
V_$BACKUP_CONTROLFILE_SUMMARY  OUTPUT_BYTES_DISPLAY
V_$BACKUP_COPY_DETAILS         OUTPUT_BYTES_DISPLAY
V_$BACKUP_COPY_SUMMARY         OUTPUT_BYTES_DISPLAY
V_$BACKUP_DATAFILE_DETAILS     FILESIZE_DISPLAY
V_$BACKUP_DATAFILE_SUMMARY     INPUT_BYTES_DISPLAY
V_$BACKUP_DATAFILE_SUMMARY     OUTPUT_BYTES_DISPLAY
V_$BACKUP_PIECE_DETAILS        SIZE_BYTES_DISPLAY
V_$BACKUP_SET_DETAILS          ORIGINAL_INPRATE_BYTES_DISPLAY
V_$BACKUP_SET_DETAILS          ORIGINAL_INPUT_BYTES_DISPLAY
V_$BACKUP_SET_DETAILS          OUTPUT_BYTES_DISPLAY
V_$BACKUP_SET_DETAILS          OUTPUT_RATE_BYTES_DISPLAY
V_$BACKUP_SET_DETAILS          TIME_TAKEN_DISPLAY
V_$BACKUP_SET_SUMMARY          ORIGINAL_INPRATE_BYTES_DISPLAY
V_$BACKUP_SET_SUMMARY          ORIGINAL_INPUT_BYTES_DISPLAY
V_$BACKUP_SET_SUMMARY          OUTPUT_BYTES_DISPLAY
V_$BACKUP_SET_SUMMARY          OUTPUT_RATE_BYTES_DISPLAY
V_$BACKUP_SPFILE_DETAILS       FILESIZE_DISPLAY
V_$BACKUP_SPFILE_SUMMARY       INPUT_BYTES_DISPLAY
V_$PROXY_ARCHIVELOG_DETAILS    OUTPUT_BYTES_DISPLAY
V_$PROXY_ARCHIVELOG_SUMMARY    OUTPUT_BYTES_DISPLAY
V_$PROXY_COPY_DETAILS          OUTPUT_BYTES_DISPLAY
V_$PROXY_COPY_SUMMARY          OUTPUT_BYTES_DISPLAY
V_$RMAN_BACKUP_JOB_DETAILS     INPUT_BYTES_DISPLAY
V_$RMAN_BACKUP_JOB_DETAILS     INPUT_BYTES_PER_SEC_DISPLAY
V_$RMAN_BACKUP_JOB_DETAILS     OUTPUT_BYTES_DISPLAY
V_$RMAN_BACKUP_JOB_DETAILS     OUTPUT_BYTES_PER_SEC_DISPLAY
V_$RMAN_BACKUP_JOB_DETAILS     TIME_TAKEN_DISPLAY
V_$RMAN_BACKUP_SUBJOB_DETAILS  INPUT_BYTES_DISPLAY
V_$RMAN_BACKUP_SUBJOB_DETAILS  OUTPUT_BYTES_DISPLAY
WM$ALL_LOCKS_VIEW              LOCKING_STATE
WM$ALL_LOCKS_VIEW              LOCK_OWNER
WM$EXP_MAP                     VFIELD3
WM$METADATA_MAP                VFIELD3
XDS_ACE                        PRINCIPAL
XDS_ACL                        DESCRIPTION
XDS_ACL                        PARENT_ACL_PATH
_DBA_STREAMS_TRANSFM_FUNCTION  TRANSFORM_FUNCTION_NAME
_DBA_STREAMS_TRANSFM_FUNCTION  VALUE_TYPE
_DBA_SXGG_TRANSFORMATIONS      COLUMN_TYPE
_DBA_SXGG_TRANSFORMATIONS      USER_FUNCTION_NAME
_GV$SXGG_APPLY_COORDINATOR     HWM_POSITION
_GV$SXGG_APPLY_COORDINATOR     LWM_POSITION
_GV$SXGG_APPLY_READER          DEQUEUED_POSITION
_GV$SXGG_APPLY_READER          SPILL_LWM_POSITION
_GV$SXGG_APPLY_SERVER          COMMIT_POSITION
_GV$SXGG_APPLY_SERVER          DEP_COMMIT_POSITION
_GV$SXGG_APPLY_SERVER          LAST_APPLY_POSITION
_GV$SXGG_MESSAGE_TRACKING      MESSAGE_POSITION
_GV$SXGG_TRANSACTION           FIRST_MESSAGE_POSITION
_GV$SXGG_TRANSACTION           LAST_MESSAGE_POSITION
_HISTGRM_DEC                   EPVALUE_RAW
_HIST_HEAD_DEC                 HIVAL
_HIST_HEAD_DEC                 LOWVAL
_OPTSTAT_HISTGRM_HISTORY_DEC   EPVALUE_RAW
_OPTSTAT_HISTHEAD_HISTORY_DEC  HIVAL
_OPTSTAT_HISTHEAD_HISTORY_DEC  LOWVAL
_V$SXGG_APPLY_COORDINATOR      HWM_POSITION
_V$SXGG_APPLY_COORDINATOR      LWM_POSITION
_V$SXGG_APPLY_READER           DEQUEUED_POSITION
_V$SXGG_APPLY_READER           SPILL_LWM_POSITION
_V$SXGG_APPLY_SERVER           COMMIT_POSITION
_V$SXGG_APPLY_SERVER           DEP_COMMIT_POSITION
_V$SXGG_APPLY_SERVER           LAST_APPLY_POSITION
_V$SXGG_MESSAGE_TRACKING       MESSAGE_POSITION
_V$SXGG_TRANSACTION            FIRST_MESSAGE_POSITION
_V$SXGG_TRANSACTION            LAST_MESSAGE_POSITION
_user_stat                     R1
_user_stat                     R2
_user_stat                     R3
_user_stat_varray              R1
_user_stat_varray              R2
_user_stat_varray              R3

629 rows selected.

SQL>

Even that is still a subset of the true picture. If I alter the query to be anything that is larger than 4000 characters you can see that many dictionary objects have been spruced up to hold more information.


SQL> select table_name, column_name, data_length
  2  from   dba_tab_columns
  3  where  data_length > 4000 and data_length < 32767
  4  order by 1,2;

TABLE_NAME                     COLUMN_NAME                    DATA_LENGTH
------------------------------ ------------------------------ -----------
ALL_APPLY_ERROR_MESSAGES       MESSAGE                              32765
ALL_AW_PROP                    FULL_PROPERTY_VALUE                  32765
ALL_CAPTURE                    CLIENT_NAME                          16000
ALL_GOLDENGATE_INBOUND         REPLICAT_NAME                        16000
ALL_JSON_DATAGUIDES            DATAGUIDE                            32765
ALL_SCHEDULER_JOBS             PROGRAM_NAME                         16000
ALL_SCHEDULER_JOBS             PROGRAM_OWNER                        16000
ALL_SCHEDULER_JOBS             SCHEDULE_NAME                        16000
ALL_SCHEDULER_JOBS             SCHEDULE_OWNER                       16000
ALL_SCHEDULER_PROGRAM_ARGS     DEFAULT_ANYDATA_VALUE                 4408
ALL_SCHEDULER_WINDOWS          SCHEDULE_NAME                        16000
ALL_SCHEDULER_WINDOWS          SCHEDULE_OWNER                       16000
ALL_SDO_3DTXFMS                AFFINE_PARAMETERS                     4408
ALL_SDO_3DTXFMS                TXFM_SERIES_IDS                       4408
ALL_SDO_GEOR_SYSDATA           OTHER_TABLE_NAMES                     4408
ALL_STAT_EXTENSIONS            EXTENSION                            32765
ALL_WM_CONSTRAINT_VIOLATIONS   PREDICATE1                           32765
ALL_WM_CONSTRAINT_VIOLATIONS   PREDICATE2                           32765
AQ$_ALERT_QT_F                 USER_PROP                             4408
AQ$_AQ$_MEM_MC_F               USER_PROP                             4408
AQ$_AQ_PROP_TABLE_F            USER_PROP                             4408
AQ$_CHANGE_LOG_QUEUE_TABLE_F   USER_PROP                             4408
AQ$_ORA$PREPLUGIN_BACKUP_QTB_F USER_PROP                             4408
AQ$_PDB_MON_EVENT_QTABLE$_F    USER_PROP                             4408
AQ$_SCHEDULER$_EVENT_QTAB_F    USER_PROP                             4408
AQ$_SCHEDULER$_REMDB_JOBQTAB_F USER_PROP                             4408
AQ$_SCHEDULER_FILEWATCHER_QT_F USER_PROP                             4408
AQ$_SYS$SERVICE_METRICS_TAB_F  USER_PROP                             4408
AQ$_WM$EVENT_QUEUE_TABLE_F     USER_PROP                             4408
CDB_ADVISOR_OBJECTS            ATTR4                                32765
CDB_APPLY_ERROR_MESSAGES       MESSAGE                              32765
CDB_APP_ERRORS                 APP_STATEMENT                        32765
CDB_APP_STATEMENTS             APP_STATEMENT                        32765
CDB_AW_PROP                    FULL_PROPERTY_VALUE                  32765
CDB_CAPTURE                    CLIENT_NAME                          16000
CDB_COMMON_AUDIT_TRAIL         RLS_INFO                             32765
CDB_COMMON_AUDIT_TRAIL         SQL_BIND                             32766
CDB_COMMON_AUDIT_TRAIL         SQL_TEXT                             32766
CDB_CQ_NOTIFICATION_QUERIES    QUERYTEXT                            32765
CDB_GOLDENGATE_INBOUND         REPLICAT_NAME                        16000
CDB_HIST_REPORTS_DETAILS       REPORT                               32765
CDB_ILMDATAMOVEMENTPOLICIES    ACTION_CLAUSE                        32765
CDB_JSON_DATAGUIDES            DATAGUIDE                            32765
CDB_OUTLINE_HINTS              HINT                                 32765
CDB_SCHEDULER_JOBS             PROGRAM_NAME                         16000
CDB_SCHEDULER_JOBS             PROGRAM_OWNER                        16000
CDB_SCHEDULER_JOBS             SCHEDULE_NAME                        16000
CDB_SCHEDULER_JOBS             SCHEDULE_OWNER                       16000
CDB_SCHEDULER_JOB_ROLES        PROGRAM_NAME                         16000
CDB_SCHEDULER_JOB_ROLES        PROGRAM_OWNER                        16000
CDB_SCHEDULER_JOB_ROLES        SCHEDULE_NAME                        16000
CDB_SCHEDULER_JOB_ROLES        SCHEDULE_OWNER                       16000
CDB_SCHEDULER_WINDOWS          SCHEDULE_NAME                        16000
CDB_SCHEDULER_WINDOWS          SCHEDULE_OWNER                       16000
CDB_STAT_EXTENSIONS            EXTENSION                            32765
CDB_TUNE_MVIEW                 STATEMENT                            32765
CDB_UNIFIED_AUDIT_TRAIL        RLS_INFO                             32765
CDB_UNIFIED_AUDIT_TRAIL        SQL_BINDS                            32765
CDB_UNIFIED_AUDIT_TRAIL        SQL_TEXT                             32765
DBA_ADVISOR_OBJECTS            ATTR4                                32765
DBA_APPLY_ERROR_MESSAGES       MESSAGE                              32765
DBA_APP_ERRORS                 APP_STATEMENT                        32765
DBA_APP_STATEMENTS             APP_STATEMENT                        32765
DBA_AW_PROP                    FULL_PROPERTY_VALUE                  32765
DBA_CAPTURE                    CLIENT_NAME                          16000
DBA_COMMON_AUDIT_TRAIL         RLS_INFO                             32765
DBA_COMMON_AUDIT_TRAIL         SQL_BIND                             32766
DBA_COMMON_AUDIT_TRAIL         SQL_TEXT                             32766
DBA_CQ_NOTIFICATION_QUERIES    QUERYTEXT                            32765
DBA_GOLDENGATE_INBOUND         REPLICAT_NAME                        16000
DBA_HIST_REPORTS_DETAILS       REPORT                               32765
DBA_ILMDATAMOVEMENTPOLICIES    ACTION_CLAUSE                        32765
DBA_JSON_DATAGUIDES            DATAGUIDE                            32765
DBA_OUTLINE_HINTS              HINT                                 32765
DBA_SCHEDULER_JOBS             PROGRAM_NAME                         16000
DBA_SCHEDULER_JOBS             PROGRAM_OWNER                        16000
DBA_SCHEDULER_JOBS             SCHEDULE_NAME                        16000
DBA_SCHEDULER_JOBS             SCHEDULE_OWNER                       16000
DBA_SCHEDULER_JOB_ROLES        PROGRAM_NAME                         16000
DBA_SCHEDULER_JOB_ROLES        PROGRAM_OWNER                        16000
DBA_SCHEDULER_JOB_ROLES        SCHEDULE_NAME                        16000
DBA_SCHEDULER_JOB_ROLES        SCHEDULE_OWNER                       16000
DBA_SCHEDULER_PROGRAM_ARGS     DEFAULT_ANYDATA_VALUE                 4408
DBA_SCHEDULER_WINDOWS          SCHEDULE_NAME                        16000
DBA_SCHEDULER_WINDOWS          SCHEDULE_OWNER                       16000
DBA_SQLTUNE_BINDS              VALUE                                 4408
DBA_STAT_EXTENSIONS            EXTENSION                            32765
DBA_TUNE_MVIEW                 STATEMENT                            32765
DBA_UNUSED_OBJPRIVS_PATH       PATH                                  4408
DBA_UNUSED_PRIVS               PATH                                  4408
DBA_UNUSED_SYSPRIVS_PATH       PATH                                  4408
DBA_UNUSED_USERPRIVS_PATH      PATH                                  4408
EXU8JBQ                        NLSENV                                8000
EXU8JBQ                        WHAT                                  8000
EXU8JBQU                       NLSENV                                8000
EXU8JBQU                       WHAT                                  8000
EXU9FGA                        POLTXT                                8000
GV_$ADVISOR_CURRENT_SQLPLAN    OTHER_XML                            32765
GV_$ALL_SQL_MONITOR            BINDS_XML                            32765
GV_$ALL_SQL_MONITOR            OTHER_XML                            32765
GV_$ALL_SQL_PLAN               OTHER_XML                            32765
GV_$ALL_SQL_PLAN_MONITOR       OTHER_XML                            32765
GV_$CELL_CONFIG                CONFVAL                              32765
GV_$CELL_CONFIG_INFO           CONFVAL                              32765
GV_$CELL_STATE                 STATISTICS_VALUE                     32765
GV_$MAPPED_SQL                 MAPPED_SQL_FULLTEXT                  32765
GV_$MAPPED_SQL                 SQL_FULLTEXT                         32765
GV_$SQL                        SQL_FULLTEXT                         32765
GV_$SQLAREA                    SQL_FULLTEXT                         32765
GV_$SQLAREA_PLAN_HASH          SQL_FULLTEXT                         32765
GV_$SQLSTATS                   SQL_FULLTEXT                         32765
GV_$SQLSTATS_PLAN_HASH         SQL_FULLTEXT                         32765
GV_$SQL_MONITOR                BINDS_XML                            32765
GV_$SQL_MONITOR                OTHER_XML                            32765
GV_$SQL_PLAN                   OTHER_XML                            32765
GV_$SQL_PLAN_MONITOR           OTHER_XML                            32765
GV_$SQL_PLAN_STATISTICS_ALL    OTHER_XML                            32765
GV_$SQL_SHARED_CURSOR          REASON                               32765
GV_$SQL_SHARED_MEMORY          SQL_FULLTEXT                         32765
GV_$SQL_TESTCASES              SQL_TEXT_FULL                        32765
GV_$UNIFIED_AUDIT_TRAIL        RLS_INFO                             32765
GV_$UNIFIED_AUDIT_TRAIL        SQL_BINDS                            32765
GV_$UNIFIED_AUDIT_TRAIL        SQL_TEXT                             32765
HS_PARALLEL_PARTITION_DATA     HIGH_VALUE                            4408
HS_PARALLEL_PARTITION_DATA     LOW_VALUE                             4408
INT$DBA_APP_STATEMENTS         LONGSQLTXT                           32765
INT$DBA_JSON_DATAGUIDES        DATAGUIDE                            32765
KU$_10_1_COMMENT_VIEW          CMNT                                 32765
KU$_10_2_TRIGGER_VIEW          BODY                                 32765
KU$_11_2_TRIGGER_VIEW          BODY                                 32765
KU$_11_2_VIEW_VIEW             TEXT                                 32765
KU$_12_1_TRIGGER_VIEW          BODY                                 32765
KU$_CLUSTER_VIEW               FUNC_CLOB                            32765
KU$_COLUMN_VIEW                BINARYDEFVAL                         32765
KU$_COLUMN_VIEW                DEFAULT_VALC                         32765
KU$_COMMENT_VIEW               CMNT                                 32765
KU$_CONSTRAINT1_VIEW           CONDITION                            32765
KU$_DIMENSION_VIEW             DIMTEXT                              32765
KU$_FGA_POLICY_VIEW            PTXT                                 32765
KU$_IND_COMPART_VIEW           HIBOUNDVALC                          32765
KU$_IND_PART_VIEW              HIBOUNDVALC                          32765
KU$_JOB_VIEW                   INTERVAL_NUM                          8000
KU$_JOB_VIEW                   NLSENV                               32765
KU$_JOB_VIEW                   WHAT                                 32765
KU$_M_VIEW_VIEW                QUERY_TXT                            32765
KU$_M_VIEW_VIEW_BASE           QUERY_TXT                            32765
KU$_M_ZONEMAP_VIEW             QUERY_TXT                            32765
KU$_OUTLINE_VIEW               SQL_TEXT                             32765
KU$_P2TCOLUMN_VIEW             BINARYDEFVAL                         32765
KU$_P2TCOLUMN_VIEW             DEFAULT_VALC                         32765
KU$_P2TPARTCOL_VIEW            BINARYDEFVAL                         32765
KU$_P2TPARTCOL_VIEW            DEFAULT_VALC                         32765
KU$_P2T_CON1A_VIEW             CONDITION                            32765
KU$_P2T_CON1B_VIEW             CONDITION                            32765
KU$_P2T_CONSTRAINT1_VIEW       CONDITION                            32765
KU$_PCOLUMN_VIEW               BINARYDEFVAL                         32765
KU$_PCOLUMN_VIEW               DEFAULT_VALC                         32765
KU$_PIOT_PART_VIEW             HIBOUNDVALC                          32765
KU$_PRIM_COLUMN_VIEW           BINARYDEFVAL                         32765
KU$_PRIM_COLUMN_VIEW           DEFAULT_VALC                         32765
KU$_QTRANS_VIEW                SQL_EXPRESSION                       32765
KU$_SIMPLE_COL_VIEW            DEFAULT_VALC                         32765
KU$_SIMPLE_PKREF_COL_VIEW      DEFAULT_VALC                         32765
KU$_SIMPLE_SETID_COL_VIEW      DEFAULT_VALC                         32765
KU$_SP2TCOLUMN_VIEW            BINARYDEFVAL                         32765
KU$_SP2TCOLUMN_VIEW            DEFAULT_VALC                         32765
KU$_SP2TPARTCOL_VIEW           BINARYDEFVAL                         32765
KU$_SP2TPARTCOL_VIEW           DEFAULT_VALC                         32765
KU$_SP2T_CON1A_VIEW            CONDITION                            32765
KU$_SP2T_CONSTRAINT1_VIEW      CONDITION                            32765
KU$_TAB_COMPART_VIEW           HIBOUNDVALC                          32765
KU$_TAB_PART_VIEW              HIBOUNDVALC                          32765
KU$_TAB_SUBPART_VIEW           HIBOUNDVALC                          32765
KU$_TAB_TSUBPART_VIEW          HIBOUNDVALC                          32765
KU$_TRIGGER_VIEW               BODY                                 32765
KU$_VIEW_VIEW                  TEXT                                 32765
KU$_XMLSCHEMA_VIEW             SCHEMA_VAL                           32765
KU$_XMLSCHEMA_VIEW             STRIPPED_VAL                         32765
KU$_XSINST_RULE_VIEW           RULE                                  8000
KU$_ZM_VIEW_VIEW               QUERY_TXT                            32765
MDX_ODBO_CUBES                 CUBE_CAPTION                         32765
MDX_ODBO_CUBES                 DESCRIPTION                          32765
MDX_ODBO_DIMENSIONS            DESCRIPTION                          32765
MDX_ODBO_DIMENSIONS            DIMENSION_CAPTION                    32765
MDX_ODBO_DIMENSIONS            DIMENSION_NAME                       32765
MDX_ODBO_HIERARCHIES           DESCRIPTION                          32765
MDX_ODBO_HIERARCHIES           HIERARCHY_CAPTION                    32765
MDX_ODBO_LEVELS                DESCRIPTION                          32765
MDX_ODBO_LEVELS                LEVEL_CAPTION                        32765
MDX_ODBO_MEASURES              DESCRIPTION                          32765
MDX_ODBO_MEASURES              MEASURE_CAPTION                      32765
MDX_ODBO_PROPERTIES            DESCRIPTION                          32765
MDX_ODBO_PROPERTIES            PROPERTY_CAPTION                     32765
OPATCH_SQL_PATCHES             NODE_NAMES                           32000
ORDDCM_ANON_ATTRS              TAG_DESC                              7996
ORDDCM_ANON_ATTRS_TMP          TAG_DESC                              7996
ORDDCM_ANON_ATTRS_USR          TAG_DESC                              7996
ORDDCM_ANON_ATTRS_WRK          TAG_DESC                              7996
ORDDCM_CONFORMANCE_VLD_MSGS    MESSAGE                               7996
ORDDCM_CT_ACTION               DESCRIPTION                           7996
ORDDCM_CT_ACTION_TMP           DESCRIPTION                           7996
ORDDCM_CT_ACTION_USR           DESCRIPTION                           7996
ORDDCM_CT_ACTION_WRK           DESCRIPTION                           7996
ORDDCM_CT_LOCATORPATHS         LOCATOR_PATH                          7996
ORDDCM_CT_LOCATORPATHS_TMP     LOCATOR_PATH                          7996
ORDDCM_CT_LOCATORPATHS_USR     LOCATOR_PATH                          7996
ORDDCM_CT_LOCATORPATHS_WRK     LOCATOR_PATH                          7996
ORDDCM_CT_PRED                 DESCRIPTION                           7996
ORDDCM_CT_PRED_PAR             PARVAL                                7996
ORDDCM_CT_PRED_PAR_TMP         PARVAL                                7996
ORDDCM_CT_PRED_PAR_USR         PARVAL                                7996
ORDDCM_CT_PRED_PAR_WRK         PARVAL                                7996
ORDDCM_CT_PRED_SET             DESCRIPTION                           7996
ORDDCM_CT_PRED_SET_TMP         DESCRIPTION                           7996
ORDDCM_CT_PRED_SET_USR         DESCRIPTION                           7996
ORDDCM_CT_PRED_SET_WRK         DESCRIPTION                           7996
ORDDCM_CT_PRED_TMP             DESCRIPTION                           7996
ORDDCM_CT_PRED_USR             DESCRIPTION                           7996
ORDDCM_CT_PRED_WRK             DESCRIPTION                           7996
ORDDCM_CT_VLD_MSG              MESSAGE                               7996
ORDDCM_MAPPED_PATHS            REL_PATH                              7996
ORDDCM_MAPPED_PATHS_TMP        REL_PATH                              7996
ORDDCM_MAPPED_PATHS_USR        REL_PATH                              7996
ORDDCM_MAPPED_PATHS_WRK        REL_PATH                              7996
ORDDCM_RT_PREF_PARAMS          PARAM_DESC                            7996
ORDDCM_RT_PREF_PARAMS          VALUE                                 7996
ORDDCM_RT_PREF_PARAMS_TMP      PARAM_DESC                            7996
ORDDCM_RT_PREF_PARAMS_TMP      VALUE                                 7996
ORDDCM_RT_PREF_PARAMS_USR      PARAM_DESC                            7996
ORDDCM_RT_PREF_PARAMS_USR      VALUE                                 7996
ORDDCM_RT_PREF_PARAMS_WRK      PARAM_DESC                            7996
ORDDCM_RT_PREF_PARAMS_WRK      VALUE                                 7996
ORDDCM_UID_DEFS                UID_DESC                              7996
ORDDCM_UID_DEFS_TMP            UID_DESC                              7996
ORDDCM_UID_DEFS_USR            UID_DESC                              7996
ORDDCM_UID_DEFS_WRK            UID_DESC                              7996
PATH_VIEW                      PATH                                  4096
SCHEDULER_PROGRAM_ARGS         DEFAULT_ANYDATA_VALUE                 4408
SYSDBIMFS_METADATA$            VALUE                                 4096
UNIFIED_AUDIT_TRAIL            RLS_INFO                             32765
UNIFIED_AUDIT_TRAIL            SQL_BINDS                            32765
UNIFIED_AUDIT_TRAIL            SQL_TEXT                             32765
USER_ADVISOR_OBJECTS           ATTR4                                32765
USER_AW_PROP                   FULL_PROPERTY_VALUE                  32765
USER_CQ_NOTIFICATION_QUERIES   QUERYTEXT                            32765
USER_ILMDATAMOVEMENTPOLICIES   ACTION_CLAUSE                        32765
USER_JSON_DATAGUIDES           DATAGUIDE                            32765
USER_OUTLINE_HINTS             HINT                                 32765
USER_SCHEDULER_JOBS            PROGRAM_NAME                         16000
USER_SCHEDULER_JOBS            PROGRAM_OWNER                        16000
USER_SCHEDULER_JOBS            SCHEDULE_NAME                        16000
USER_SCHEDULER_JOBS            SCHEDULE_OWNER                       16000
USER_SCHEDULER_PROGRAM_ARGS    DEFAULT_ANYDATA_VALUE                 4408
USER_SDO_3DTXFMS               AFFINE_PARAMETERS                     4408
USER_SDO_3DTXFMS               TXFM_SERIES_IDS                       4408
USER_SDO_GEOR_SYSDATA          OTHER_TABLE_NAMES                     4408
USER_SQLTUNE_BINDS             VALUE                                 4408
USER_STAT_EXTENSIONS           EXTENSION                            32765
USER_SUBSCR_REGISTRATIONS      ANY_CONTEXT                           4408
USER_TUNE_MVIEW                STATEMENT                            32765
V_$ADVISOR_CURRENT_SQLPLAN     OTHER_XML                            32765
V_$ALL_SQL_MONITOR             BINDS_XML                            32765
V_$ALL_SQL_MONITOR             OTHER_XML                            32765
V_$ALL_SQL_PLAN                OTHER_XML                            32765
V_$ALL_SQL_PLAN_MONITOR        OTHER_XML                            32765
V_$CELL_CONFIG                 CONFVAL                              32765
V_$CELL_CONFIG_INFO            CONFVAL                              32765
V_$CELL_STATE                  STATISTICS_VALUE                     32765
V_$DIAG_LOG_EXT                SUPPLEMENTAL_DETAILS                  4003
V_$MAPPED_SQL                  MAPPED_SQL_FULLTEXT                  32765
V_$MAPPED_SQL                  SQL_FULLTEXT                         32765
V_$SQL                         SQL_FULLTEXT                         32765
V_$SQLAREA                     SQL_FULLTEXT                         32765
V_$SQLAREA_PLAN_HASH           SQL_FULLTEXT                         32765
V_$SQLSTATS                    SQL_FULLTEXT                         32765
V_$SQLSTATS_PLAN_HASH          SQL_FULLTEXT                         32765
V_$SQL_MONITOR                 BINDS_XML                            32765
V_$SQL_MONITOR                 OTHER_XML                            32765
V_$SQL_PLAN                    OTHER_XML                            32765
V_$SQL_PLAN_MONITOR            OTHER_XML                            32765
V_$SQL_PLAN_STATISTICS_ALL     OTHER_XML                            32765
V_$SQL_SHARED_CURSOR           REASON                               32765
V_$SQL_SHARED_MEMORY           SQL_FULLTEXT                         32765
V_$SQL_TESTCASES               SQL_TEXT_FULL                        32765
V_$UNIFIED_AUDIT_TRAIL         RLS_INFO                             32765
V_$UNIFIED_AUDIT_TRAIL         SQL_BINDS                            32765
V_$UNIFIED_AUDIT_TRAIL         SQL_TEXT                             32765
WM$MW_VERSIONS_VIEW_9I         SEEN_BY                              32765
_DBA_APPLY_ERROR_TXN           MESSAGE                               4408
_DBA_STREAMS_COMPONENT         COMPONENT_NAME                        4525
_DBA_STREAMS_COMPONENT_LINK    DEST_COMPONENT_NAME                   4525
_DBA_STREAMS_COMPONENT_LINK    SOURCE_COMPONENT_NAME                 4525
_DBA_STREAMS_MSG_NOTIFICATIONS ANY_CONTEXT                           4408
_DBA_STREAMS_TRANSFORMATIONS   COLUMN_VALUE                          4408
_user_stat                     CL1                                  32765

Now I could use the database configuration assistant to build a template based on this empty database, save the data files, and now I’ll get much quicker creation next time.

So if you are looking to use larger strings, perhaps take this opportunity to consider building your database from scratch and then using DataPump to import your existing data in order to get access to the full benefits of bigger strings.

The Oracle ACE program ♠ what it is not ♠

By Franck Pachot

.
I had a few questions about the Oracle ACE program recently and I thought about putting some answers there. Of course, that’s only my point of view, there’s an official web page: https://www.oracle.com/technetwork/community/oracle-ace/index.html

The program is flexible and open, with a large diversity of people, technologies, contributions, levels,… Then rather than explaining what it is, which would be limiting, I’ll rather tell you… what it is not.

It is not a graded evaluation

You may have heard about “ACE points”. When I entered the ACE program it was running for a long time with a subjective evaluation on the contributions in the Oracle community. Then it became more structured with a clear list of activities that are recognized, an application (APEX of course) to fill-in the contributions, and points to rate them. But the goal is not to get the highest score. The reason for this point system is to be sure that all contributions are accounted to determine your level of contribution.

Typically, you enter as an ACE Associate by listing a few blog posts, or presentations you did. Then you contribute more, maybe writing an article, giving more presentations, or being active on Oracle forums. You list all that and after a while, you may reach a number of points where they will evaluate an upgrade to the ACE level. Do not see this “more contributions” as a constraint. The goal of the program is to open new doors for contributing further. Being in the ACE program will help you to be selected for conferences, to meet Product Managers from Oracle, to know more people in the user community,… And you will realize that there are many more contributions that can count. You may realize that public-facing activities are not your preference. But at the same time, you will discuss with some product managers and realize that some code contribution, SR’s or Enhancement Requests are also recognized contributions. Some people do not want to talk at conferences but volunteer in their User Groups or organize meetups. That counts as well, and the idea raises when meeting people (physically or virtually). You may write a few chapters for a book on a technology you like with people you appreciate. You may meet people contributing to the Oracle Dev Gym. You may also realize that you like public-facing sharing and try to produce, in addition to presentations, some videos or podcasts. All that is accounted thanks to the points system.

Depending on your motivation, and the time you have, you may go further, to the ACE Director level. Or not, because you don’t have to, but I will come back on this later. I was not in the program for a long time when the “points” system was introduced, so I may be wrong in my opinion. But my feeling is that it was easier to enter the program when going physically to the main conferences and drinking a beer with influential people. Some contributions were highly visible (like speaking on mainstream technologies) and some were not. If you did not travel and do not drink beer, entering the program to high levels were probably harder. I think that the “points” system is fairer, bringing equality and diversity. And that the additional time to enter the contributions worths it.

It is not a technichal certification

The ACE program is not a technical validation of your knowledge like the Oracle Educations exams are. You don’t even get “points” for being Oracle Certified Master. Of course, valuable contributions are often based on technical skills. But all conferences miss good sessions on soft skills and sessions on basics for beginners. Of course, it is cool if you go deep into the internals of an Oracle Product, but you may have a lot to share even if you are a junior in this technology. Just share what you learned.

It is not a marketing campaign

You don’t need to be an expert on the product, but you cannot give a valuable contribution if you are not using and appreciating the product. This is still a tech community that has its roots in the Oracle Technology Network. You share in the spirit of this community and user groups: not marketing but tech exchanges. You are there to help the users and the product, and the communication around those. You are not there to sell any product, and you will realize the number of people contributing about free products. Oracle XE, SQL Developer, MySQL, Cloud Free Tier, Oracle Linux,… those are valuable contributions.

Of course, the ACE program has some budget that comes from marketing. And yes, you get more “points” when contributing to “cloud” products because that’s where all priorities are at Oracle Corp, and this includes the ACE program. But do not take it like “I must talk about the cloud”. Just take it as “cool, I got more point because I contributed to their flagship product”. If you contribute for points only, you are wrong. You will be tired of this quickly. Just contribute on what you like, and points will come to recognize what you did and encourage you to go further.

There is no compulsory content

I mentioned that you can contribute on any Oracle products, paid or free, and there are a lot. You don’t need to talk about the database. You don’t need to talk about the cloud. You don’t need to talk about expensive options. The ACE program is flexible and this is what allows diversity. Depending on your country, and depending on your job, or simply on you motivation, you may have nothing to share about some products that are common elsewhere. Some consultant have all their customers on Exadata, and have a lot to share about it. Others have all their databases in Standard Edition and their contributions are welcome as well.

I’ll be clear if you have some doubts: I have never been asked to talk or write about anything. All are my choices. And I have never been asked to change or remove some published content. And my subjects also cover problems and bugs, because I consider that it helps to share them as well. Actually, I’ve deleted a tweet only two times because of feedback from others. And the feedback was not to ask me to take it down but just to mention that one word may sound a little harsh, And I checked my tweet, and I agreed my wording was not ok, and then preferred not to leave something that could be interpreted this way. Two times, and it was my choice, and I’m at 20K tweets.

It is not a perpetual prize

The ACE levels I’ve mentioned (ACE Associate, ACE, ACE Director) are not Nobel prizes and are not granted for life. They show the level of current and recent contributions. If you do not contribute anymore, you will leave the program as an ACE Alumni. And that’s totally normal. The ACE program is there to recognize your contributions and helps you with those. You may change you job and work on different technology, lose your motivation, or simply don’t have time for this, and that’s ok. Or simply don’t want to be an ACE. Then it is simple: you don’t enter enough contributions in the “points” application and at next evaluation (in June usually) you will be ACE Alumni.

I have an anecdote about “don’t want to be an ACE”. The first nomination I entered, I did it for someone who contributed in his way (no public-facing but organizing awesome events). And I did it without telling him. I was excited to see his surprise, and he was accepted. But he told me that he didn’t want to be an ACE Associate. Sure I was surprised, but that’s perfectly ok. There’s no obligation about anything. Now, If I want to nominate someone I ask before </p />
</p></div>

    	  	<div class=

Some myths about PostgreSQL vs. Oracle

By Franck Pachot

.
I originally wrote this as a comment on the following post that you may find on internet:
https://www.2ndquadrant.com/en/blog/oracle-to-postgresql-reasons-to-migrate/
but my comment was not published (many links in it… I suppose it has been flagged as spam?) so I put it there.

You should never take any decision on what you read on the internet without verifying. It is totally valid to consider a move to Open Source databases, but doing it without good understanding is a risk for your migration project success.

In italics are the quotes from the article.

Kirk,
As you do a comparison and link to a list of PostgreSQL features, let me refine the name and description of the Oracle features you compare to, so that people can find them and do a fair comparison. I’m afraid they may not recognize the names and descriptions you provide, at least in current versions. As an example, nobody will get search hits for “Federation”, or “plSQL”, or “HTML DB”… in the Oracle documentation but they will find “Oracle Gateway”, “PL/SQL”, “APEX”…

Federation vs. Foreign Data Wrappers


There is no feature called “Federation”. 
The closest from your description is Database links and Heterogeneous Services through Database Gateway. They go further than FDW in many points. But anyway, I would never use that for ETL. ETL needs optimized bulk loads and there are other features for that (like External Tables to read files, and direct-path inserts to fast load). If your goal is to federate and distribute some small reference tables, then Materialized Views is the feature you may look for.
https://docs.oracle.com/en/database/oracle/oracle-database/20/heter/introduction.html#GUID-EC402025-0CC0-401F-AF93-888B8A3089FE

plSQL vs. everything else


“Oracle has a built-in programming language called plSQL.”
PL/SQL is more than that. It is compiled (to pcode or native), manages dependencies (tracks dependencies on schema objects), optimized for data access (UDF can even be compiled to run within the SQL engine), can be multithreaded (Parallel Execution). That’s different from PL/pgSQL which is interpreted at execution time. You mention languages as “as plug-ins” and for this, there are other ways to run different languages (external procedures, OJCM, External Table preprocessor,…) but when it comes to performance, transaction control, dependency tracking,… that’s PL/SQL.
https://docs.oracle.com/en/database/oracle/oracle-database/20/lnpls/overview.html#GUID-17166AA4-14DC-48A6-BE92-3FC758DAA940

Application programming


Providing an “API to communicate with the database” is not about open source as the main goal is: encapsulation and hide implementation details. In order to access internal structures, which is what you mention, Oracle provides relational views (known as V$ views) accessible with the most appropriate API for a relational database: SQL
https://docs.oracle.com/en/database/oracle/oracle-database/20/refrn/dynamic-performance-views.html#GUID-8C5690B0-DE10-4460-86DF-80111869CF4C

Internationalization and Localization


The “globalization toolkit” is only one part of the globalization features. You can also use any “any character encoding, collation and code page” but not relying on the OS implementation of it makes it cross-platform compatible and OS upgrade compatible (see https://wiki.postgresql.org/wiki/Locale_data_changes)
https://docs.oracle.com/en/database/oracle/oracle-database/20/nlspg/overview-of-globalization-support.html#GUID-6DD587EE-6686-4802-9C08-124B495978D5

Web Development


“Oracle acknowledges the existence of HTML through HTML DB. PostgreSQL natively supports JSON, XML and plugs in Javascript”. HTML DB can be found in paper books, but the name is “APEX” since 2006. And it is not (only) about HTML, JSON, or XML but is a low-code Rapid Application Development with no equivalent for other databases.
Support for the structures and languages you mention are all there. The latest trend being JSON: https://docs.oracle.com/en/database/oracle/oracle-database/20/adjsn/index.html

Authentication


“Oracle has a built-in authentication system.”
Yes, to be platform-independent, and has many other External Authentication: https://docs.oracle.com/en/database/oracle/oracle-database/20/dbseg/configuring-authentication.html#GUID-BF8E5E84-FE7E-449C-8081-755BAA4CF8DB

Extensibility


“Oracle has a plug-in system”. I don’t know what you are referring to. Oracle is multi-platform proprietary software. Commercial, which means with vendor supported. There are a lot of APIs for extensions, but the vendor must have to control what runs in the engine in order to provide support.

Read Scalability


“PostgreSQL can create a virtually unlimited read cluster”. Oracle has active/active cluster (called RAC) and read replicas (called Active Data Guard). For horizontal scalability, you use the same as for vertical (Parallel Execution) across multiple nodes (in sync, with instance affinity on partitions,…)
https://docs.oracle.com/en/database/oracle/oracle-database/20/vldbg/parallel-exec-intro.html#GUID-F9A83EDB-42AD-4638-9A2E-F66FE09F2B43

Cost


“they don’t mind charging you again for every single instance.” 
No, that’s wrong, license metrics are on processors (CPU) or users (NUP). You run as many instances as you want on your licensed servers for your licensed users: https://www.oracle.com/a/ocom/docs/corporate/oracle-software-licensing-basics.pdf
“jamming everything into a single instance just to reduce costs”
No, database consolidation is recommended to scale the management of multiple databases, but not for licensing costs. If you go there, there are a lot of features to allow isolation and data movement in consolidated databases: Multitenant, Resource Manager, Online Relocate, Lockdown Profiles,…

Performance


“differentiate the tuning parameters for your warehouse to OLTP to reporting to the data lake”: I already mentioned the point about read replicas and about multiple instances in a server. But with oracle, all the parameters I want to set different for OLTP or reporting do not require another instance. They can be set at session or PDB level. As Oracle does not need the filesystem buffer cache, there’s no need to separate on different servers to avoid noisy neighbours. 

I hope this helps to look further at the features. There are many reasons to migrate and the main one is the will to move from a commercial model (with license and support) to an open-source one (start with low cost, help from community). But decision must be made on facts and not rumours.

Franck.

Cet article Some myths about PostgreSQL vs. Oracle est apparu en premier sur Blog dbi services.

DataPump migration to SECUREFILE

These tweets somewhat befuddle me.

image

Because to be honest, if I no matter if I say “Yes” or “No” , and then for someone to take that response at face value…well…whilst I’m flattered in the level of trust, I’m also a little saddened because I think our community is best served by everyone always insisting on evidence for claims.

So naturally, it behoves me to conduct an experiment, which is the cause of my befuddlement. If the only way to answer this question with any confidence is via experiment, then perhaps these tweets should be “Hey, look at the results of my experiment!” or at least “Has anyone performed this experiment already?”. I’ll go with benefit of the doubt on this one and assume the latter.

Anyway…let us proceed. I’ll create a BASICFILE table, and load it up with ~4GB of data from ~300 files just taken at random from my PC.  They range from a few kilobytes up to 500MB.


SQL> create table t_basic ( id int generated as identity, data blob ) tablespace largets lob ( data ) store as basicfile;

Table created.

SQL> declare
  2    flist sys.odcivarchar2list :=
  3       sys.odcivarchar2list(
  4         '12c_DBFS_setup.pdf'
 ...
 ...
271        ,'zoom_dino.png'
272        ,'zoom_dino2.jpg'
273        ,'zoom_dino3.jpg'
274        ,'zoom_shark.jpg'
275        ,'zoom_shark.webp'
276        );
277
278    bf  bfile;
279    b   blob;
280    dest int;
281    src  int;
282
283  begin
284    for i in 1 .. flist.count
285    loop
286      --
287      begin
288        insert into t_basic (data)
289        values (empty_blob())
290        return data into b;
291        dest := 1;
292        src  := 1;
293        bf := bfilename('TMP', flist(i));
294        dbms_lob.fileopen(bf, dbms_lob.file_readonly);
295        dbms_lob.loadblobfromfile (b,bf,dbms_lob.lobmaxsize,dest,src);
296        dbms_lob.fileclose(bf);
297        commit;
298      exception
299        when others then
300          --
301          begin dbms_lob.fileclose(bf); exception when others then null; end;
302      end;
303    end loop;
304  end;
305  /

SQL> select count(*), sum(dbms_lob.getlength(data))
  2  from t_basic;

  COUNT(*) SUM(DBMS_LOB.GETLENGTH(DATA))
---------- -----------------------------
       272                    3508933562

SQL>

Now we’ll DataPump export that out, and look at a few options for reloading.


C:\>expdp userid=mcdonac/xxxxxxxx@db19_pdb1 directory=D_DRIVE tables=T_BASIC dumpfile=t_basic.dmp

Export: Release 19.0.0.0.0 - Production on Wed Jun 24 11:15:16 2020
Version 19.7.0.0.0

Copyright (c) 1982, 2020, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "MCDONAC"."SYS_EXPORT_TABLE_01":  userid=mcdonac/********@db19_pdb1 directory=D_DRIVE tables=T_BASIC dumpfile=t_basic.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN
. . exported "MCDONAC"."T_BASIC"                         3.268 GB     272 rows
Master table "MCDONAC"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MCDONAC.SYS_EXPORT_TABLE_01 is:
  D:\ORACLE\ORADATA\T_BASIC.DMP
Job "MCDONAC"."SYS_EXPORT_TABLE_01" successfully completed at Wed Jun 24 11:16:17 2020 elapsed 0 00:00:59

Test 1: Reload as BASICFILE


SQL> truncate table t_basic reuse storage;

Table truncated.

C:\>impdp userid=mcdonac/xxxxxxxx@db19_pdb1 directory=D_DRIVE tables=T_BASIC dumpfile=t_basic.dmp TABLE_EXISTS_ACTION=APPEND

Import: Release 19.0.0.0.0 - Production on Wed Jun 24 11:23:31 2020
Version 19.7.0.0.0

Copyright (c) 1982, 2020, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "MCDONAC"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "MCDONAC"."SYS_IMPORT_TABLE_01":  userid=mcdonac/********@db19_pdb1 directory=D_DRIVE tables=T_BASIC dumpfile=t_basic.dmp TABLE_EXISTS_ACTION=APPEN
D
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "MCDONAC"."T_BASIC" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "MCDONAC"."T_BASIC"                         3.268 GB     272 rows
Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "MCDONAC"."SYS_IMPORT_TABLE_01" successfully completed at Wed Jun 24 11:25:02 2020 elapsed 0 00:01:30

So 90 seconds is our baseline value to reload the table “as is” back into the database

Test 2: Alter to SECUREFILE in database, and reload


SQL> truncate table t_basic reuse storage;

Table truncated.

SQL> alter table t_basic move lob ( data ) store as securefile;

Table altered.

C:\>impdp userid=mcdonac/xxxxxxxx@db19_pdb1 directory=D_DRIVE tables=T_BASIC dumpfile=t_basic.dmp TABLE_EXISTS_ACTION=APPEND

Import: Release 19.0.0.0.0 - Production on Wed Jun 24 11:25:55 2020
Version 19.7.0.0.0

Copyright (c) 1982, 2020, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "MCDONAC"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "MCDONAC"."SYS_IMPORT_TABLE_01":  userid=mcdonac/********@db19_pdb1 directory=D_DRIVE tables=T_BASIC dumpfile=t_basic.dmp TABLE_EXISTS_ACTION=APPEN
D
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "MCDONAC"."T_BASIC" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "MCDONAC"."T_BASIC"                         3.268 GB     272 rows
Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "MCDONAC"."SYS_IMPORT_TABLE_01" successfully completed at Wed Jun 24 11:26:50 2020 elapsed 0 00:00:55

The SECUREFILE load is faster. Only 55 seconds this time.

Test 3: Transform via IMPDP and reload


SQL> drop table t_basic purge;

Table dropped.

C:\>impdp userid=mcdonac/xxxxxxxx@db19_pdb1 directory=D_DRIVE tables=T_BASIC dumpfile=t_basic.dmp transform=lob_storage:securefile

Import: Release 19.0.0.0.0 - Production on Wed Jun 24 11:28:00 2020
Version 19.7.0.0.0

Copyright (c) 1982, 2020, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "MCDONAC"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "MCDONAC"."SYS_IMPORT_TABLE_01":  userid=mcdonac/********@db19_pdb1 directory=D_DRIVE tables=T_BASIC dumpfile=t_basic.dmp transform=lob_storage:sec
urefile
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "MCDONAC"."T_BASIC"                         3.268 GB     272 rows
Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "MCDONAC"."SYS_IMPORT_TABLE_01" successfully completed at Wed Jun 24 11:29:01 2020 elapsed 0 00:01:00

And almost identical results from the transform parameter via DataPump. The little bit of extra time is probably attributable to the cost of creating a brand new table, and also extending the high water mark as is grows throughout the import.

Thus to answer the question: There is a good chance that migrating to SECUREFILE will not only give you the functionality benefits of the new storage type, but it will probably be faster to migrate to it as well….and lets hope that everyone in the community is motivated to always perform their own experiments to build our collective knowledge.

Video : Liquibase : Deploying Oracle Application Express (APEX) Applications

Today’s video is a quick demonstration of deploying an Oracle Application Express (APEX) application using Liquibase.

The video is based on a new article of the same name, which covers the deployment of both APEX workspaces and APEX applications using Liquibase.

Here’s some other content you might find useful.

The star of today’s video is Jorge Rimblas, making a welcome return to the channel, along with some serious reverb. </p />
</p></div>

    	  	<div class=

Covering Indexes in Postgres

Covering indexes are where all the columns needed are in the index and, in theory, only the index needs to be accessed. This seemingly straight forward strategy is much tricker on Postgres.

Let’s take TPC-H query 6:

select
        sum(l_extendedprice * l_discount) as revenue
from
        lineitem
where
            l_shipdate >= date '1996-01-01'
        and l_shipdate < cast(date '1996-01-01' + interval '1 year' as date)
        and l_discount between 0.06 - 0.01 and 0.06 + 0.01
        and l_quantity < 24;

we’ve got 3 predicate filter columns

  • l_shipdate
  • l_discount
  • l_quantity

None of them are that selective but all three are pretty good

select count(*) from
        lineitem
where
            l_shipdate >= date '1996-01-01'
        and l_shipdate < cast(date '1996-01-01' + interval '1 year' as date)

→ 1,823,373

select count(*) from
        lineitem
where l_discount between 0.06 - 0.01 and 0.06 + 0.01;

→ 3,272,056

select count(*) from
        lineitem
where l_quantity < 24;

→ 5,517,389

select count(*) from
        lineitem
where
            l_shipdate >= date '1996-01-01'
        and l_shipdate < cast(date '1996-01-01' + interval '1 year' as date)
        and l_discount between 0.06 - 0.01 and 0.06 + 0.01
        and l_quantity < 24;

→ 228,160

select count(*) from lineitem;

→ 11,997,996

228,160/11,997,996 = 1.9%

 

With the index added on all 3 fields, the optimizer doesn’t even use it!

https://explain.depesz.com/s/OG27

I forced the optimizer to use the index (set enable_seqscan=off;) , then yes, the improvement is small, from ~800+ ms to 600+ ms mainly because the index results have to go back to the table.

https://explain.depesz.com/s/bJMg

I made a covering index with all the fields in the query and it still goes back to the table!

https://explain.depesz.com/s/92mV

Now, if I do a vacuum and sure enough, the covering index works and get a 6x speedup!   ( 870ms to 140ms )

https://explain.depesz.com/s/fdy5

What does the vacuum have to do with the covering index?

From Jim Nasby:

The issue with covering indexes is that you still have to visit the heap page, unless that page happens to be marked as being all visible. Pages can be marked as all visible by autovacuum (by default runs once 10% of rows in a table have been updated or deleted) or a manual vacuum. They can only be marked visible if all live rows on the page are older than the oldest running transaction in the system.

In other words, effectiveness of this technique is highly dependent on the workload.

 

Reference

https://www.percona.com/blog/2018/08/06/basic-understanding-bloat-vacuum-postgresql-mvcc/

TL;DR postgres keeps undo in the data blocks. Vacuum clears out old unnecessary rows that are “dead”

https://bitnine.net/blog-useful-information/index-only-scan-and-visibility-map-12/

“Because there is no information for version in an index tuple, it is impossible to judge whether a tuple is readable in the current transaction or not”

https://blog.makandra.com/2018/11/investigating-slow-postgres-index-only-scans/

Replica + hot_standby_feedback can affect index only scan

 

Expert Advice: Manage Your Site on the Go Using the WordPress Mobile Apps

For many people, the go-to tool for updating a website is a laptop or desktop computer. Did you know, though, that the computer you carry around in your pocket has as much power as the one on your desk? The WordPress mobile apps are packed with features that make it possible to manage your site no matter where you are.

Want to become a WordPress app pro? Register for our next webinar, “WordPress Mobile: Your site. Your inspiration. Anywhere.” We’ll be sharing bite-sized tips that will transform the way you manage your site and connect with your audience. 

Some of the topics we’ll cover include:

  • How to create a site from your phone.
  • Using stats on the mobile app for a deep dive into your site’s performance. 
  • Leveraging the activity log to keep an eye on what’s going on around your site.
  • The recently introduced WordPress editor and the ways it has revolutionized mobile content creation. 
  • Starter page templates and how they can jump-start your page designs.
  • How to use the WordPress.com Reader to find new content and expand your site’s audience. 
  • Making the most of real-time notifications and alerts.

Date: Wednesday, June 24, 2020
Time: 10:00 a.m. PDT | 11:00 a.m. MDT | 12:00 p.m. CDT | 1:00 p.m. EDT | 17:00 UTC
Cost: Free
Registration link

Eli Budelli and I will be your hosts — we work on the WordPress mobile apps, so you’ll be learning and sharing with the people who are crafting your mobile experiences. No previous knowledge using our mobile apps is necessary, but we recommend a basic familiarity with WordPress.com and installing the WordPress app to ensure you can make the most from the webinar. The session will cover both iOS and Android, last about 40 minutes, and conclude with a Q&A session (15-20 minutes), so start writing down any questions you may have, and bring them with you to the webinar.

Attendee slots are limited, so be sure to register early to save your seat! But if you can’t make it, we’ve got your back. A recording of the webinar will be uploaded to our YouTube channel a few days after the event.

See you then!

Enjoy a Smoother Experience with the Updated Block Editor

Little details make a big difference. The latest block editor improvements incorporate some common feedback you’ve shared with us and make the editing experience even more intuitive than before.

We’ve also updated the categories we use to organize blocks, so you can find exactly what you need, fast. Read on to learn about recent changes you’ll notice next time you open the editor.

Move on quickly after citations and captions

Have you ever felt as if you were stuck inside a block after adding a citation? Now, when you hit Enter or Return at the end of the citation, you’ll be ready to start typing in a new text block.

Quotes were a bit sticky…
Much smoother now!

Quotes, images, embeds, and other blocks now offer this smoother experience. It’s a small change that will save you a little bit of time, but those seconds add up, and less frustration is priceless.

Streamlined heading selection

Another subtle-yet-helpful change we’ve introduced is simplified heading levels. Before, the block toolbar included a few limited options with additional ones in the sidebar. Now, you can find all available heading levels right in the block toolbar, and adjust the heading directly from the block you’re working on. (For even more simplicity, we’ve also removed the dropdown in the sidebar.)

Select a parent block with ease

Working with nested blocks to create advanced page layouts is now considerably smoother. Some users told us it was too difficult to select a parent block, se we’ve added an easier way to find it right from the toolbar. Now it’s a breeze to make picture-perfect layouts!

Filter your latest posts by author

Sites and blogs with multiple authors will love this update: you can now choose a specific author to feature in the Latest Posts block.

To highlight recent articles from a particular writer, just select their name in the block’s settings.

https://en-blog.files.wordpress.com/2020/06/latest-posts.jpg?w=150 150w, https://en-blog.files.wordpress.com/2020/06/latest-posts.jpg?w=300 300w, https://en-blog.files.wordpress.com/2020/06/latest-posts.jpg?w=768 768w" sizes="(max-width: 783px) 100vw, 783px" />

Renamed block categories

Finally, the next time you click the + symbol to add a new block, you’ll notice new, intuitive block categories that make it both easier and faster to find just the block you’re looking for.

What’s new:

  • Text
  • Media
  • Design

What’s gone:

  • Common
  • Formatting
  • Layout

You keep building, we’ll keep improving

Thank you for all your input on how the block editor can be better! We’re listening. If you have more ideas, leave a comment below.

</p />
</p></div>

    	  	<div class=

Read only partitions

The ability for part of a table to be read-only and other parts of the same table to allow full DML is a cool feature in the Oracle Partitioning option.  Perhaps the most common example you will typically see for this is range-based partitioning on a date/timestamp column.  As data “ages”, setting older partitions to read-only can yield benefits such as:

  • moving the older partitions to cheaper, or write-once storage
  • guaranteeing that older data cannot be tampered with
  • shrinking backup times because read-only data only needs to be backed up once (or twice to be sure)

But if you try this in 18c, you might get a surprise:


SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

SQL>
SQL>  create table t
  2      ( d date,
  3        id int,
  4        data varchar2(20)
  5      )
  6      partition by range (d )
  7      (
  8        partition p1 values less than ( date '2018-01-01' ),
  9        partition p2 values less than ( date '2019-01-01' ),
 10        partition p3 values less than ( date '2020-01-01' ),
 11        partition p4 values less than ( date '2021-01-01' )
 12     );

Table created.

SQL>
SQL> insert into t
  2      select date '2017-01-01' + rownum, rownum, 'data'||rownum
  3      from dual connect by level <= date '2021-01-01' - date '2017-01-01' - 1;

1460 rows created.

--
-- Make older data read only prevent tampering
--
SQL> alter table t modify partition p1 read only;

Table altered.

SQL>
SQL> alter table t modify partition p2 read only;

Table altered.

--
-- Expose a view for the current year
--

SQL> create or replace
  2      view VW as
  3      select d,id from t
  4      where d > date '2020-01-01';

View created.

--
-- And DML on this view should be fine...
--


SQL> delete from VW where id = 1100;

1 row deleted.

--
-- This *SHOULD* work but it does not in 18c
--

SQL> insert into VW values ( sysdate, 100);
insert into VW values ( sysdate, 100)
            *
ERROR at line 1:
ORA-14466: Data in a read-only partition or subpartition cannot be modified.

Luckily this is not an implementation restriction, its just a bug in 18c that has since been fixed. All is well in 19c, which is yet another reason this should be your “go to” release when upgrading.


SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL>
SQL>  create table t
  2      ( d date,
  3        id int,
  4        data varchar2(20)
  5      )
  6      partition by range (d )
  7      (
  8        partition p1 values less than ( date '2018-01-01' ),
  9        partition p2 values less than ( date '2019-01-01' ),
 10        partition p3 values less than ( date '2020-01-01' ),
 11        partition p4 values less than ( date '2021-01-01' )
 12     );

Table created.

SQL>
SQL> insert into t
  2      select date '2017-01-01' + rownum, rownum, 'data'||rownum
  3      from dual connect by level <= date '2021-01-01' - date '2017-01-01' - 1;

1460 rows created.

SQL> alter table t modify partition p1 read only;

Table altered.

SQL> alter table t modify partition p2 read only;

Table altered.

SQL> create or replace
  2      view VW as
  3      select d,id from t
  4      where d > date '2020-01-01';

View created.

SQL> delete from VW where id = 1100;

1 row deleted.

SQL> insert into VW values ( sysdate, 100);

1 row created.

Views and read-only partitions are a great way of controlling access to particular subsets of data to ensure your users can’t get themselves with either performance issues or auditor issues! Smile

Lower Cost Ignored

This is an update on a post I wrote nearly 10 years ago describing how the optimizer could choose to ignore a lower cost indexed access path and use a higher cost index if the lower cost were based on guesswork. The original article article used (select {constant} from dual) as a way of supplying an “unpeekable bind” in a predicate, but that mechanism stopped working 11gR2, hence the update.

The upate also goes into a little more detail about event 38036 which can be used to modify this behaviour by defining a “cut-off” percentage where Oracle will switch back to using the lower cost path.

We start with the code to generate the data – including, in this case – a table that I can query to supply “hidden constants” to the optimizer:

em
rem     Script:         index_range_2.sql
rem     Author:         Jonathan Lewis
rem     Dated:          June 2020
rem
rem     Last tested 
rem             19.3.0.0
rem             12.2.0.1
rem

create table t1
as
with generator as (
        select  --+ materialize
                rownum id 
        from dual 
        connect by 
                rownum <= 10000 -- > comment to avoid wordpress format issue
)
select
        rownum                  id,
        9 + mod(rownum-1,3)     scattered,
        trunc(rownum / 500)     clustered,
        lpad(mod(rownum,2),10)  ind_pad,
        lpad(rownum,10,'0')     small_vc,
        rpad('x',100)           padding
from
        generator       v1,
        generator       v2
where
        rownum <= 40000 -- > comment to avoid wordpress format issue
;

create index t1_equi  on t1(scattered, ind_pad, clustered);
create index t1_range on t1(clustered, scattered);

create table driver(name varchar2(20), value number(4,0));
create index drv_i1 on driver(name);

insert into driver values('Forty',40);
insert into driver values('FortyOne',41);
commit;

begin
        dbms_stats.gather_table_stats(
                ownname          => user,
                tabname          =>'T1',
                method_opt       => 'for all columns size 1'
        );

        dbms_stats.gather_table_stats(
                ownname          => user,
                tabname          =>'DRIVER',
                method_opt       => 'for all columns size 1'
        );
end;
/


The driver table models a pattern that I see fairly frequently of a table holding a set of “constants” that are subject to infrequent changes. (An alternative strategy is to have a PL/SQL package of constants and a function to return a value when supplied with a name.)

The key columns in the table are

  • scattered: 3 distinct values evenly but randomly scattered across the table – not a column you would index separately
  • clustered: 500 consecutive rows each for 80 distinct values, so very well clustered data

There are two indexes on the main data table

  • t1_range (clustered scattered) — roughly (80 * 3 =) 240 distinct keys
  • t1_equi (scattered, ind_pad, clustered) — roughly (3 * 2 * 80) = 480 distinct keys

If I execute the query: “select * from t1 where clustered between 40 and 41 and scattered = 10” Oracle uses the t1_range index to return an estimated 491 rows at a cost of 36. The estimates aren’t too far out since in my case the query returned 334 rows in 26 buffer visits (when my SQL*Plus arraysize was 500).

------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          |   496 |  8432 |    36   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1       |   496 |  8432 |    36   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | T1_RANGE |   496 |       |     5   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("CLUSTERED">=40 AND "SCATTERED"=10 AND "CLUSTERED".le.41)
       filter("SCATTERED"=10)

But what happens if we decide to keep the start and end values for clustered in the driver table:


set autotrace traceonly explain

select 
        /*  index(t1 t1_range) */
        small_vc
from
        t1
where
        scattered = 10
and     clustered between (select value from driver where name = 'Forty')
                  and     (select value from driver where name = 'FortyOne')
;

set autotrace off



You’ll notice the “hint that isn’t a hint” I’ve tested two versions of the query, one without a hint and one where I inserted the necessary “+” to have a hint instead of a comment. Here are the execution plans (with “<=” edited to “.le.” in the Predicate Information):


=======
Default
=======
-------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |         |    33 |   561 |    67   (3)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED  | T1      |    33 |   561 |    63   (4)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                    | T1_EQUI |    60 |       |    56   (4)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| DRIVER  |     1 |    11 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                  | DRV_I1  |     1 |       |     1   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID BATCHED| DRIVER  |     1 |    11 |     2   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN                  | DRV_I1  |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("SCATTERED"=10 AND "CLUSTERED">= (SELECT "VALUE" FROM "DRIVER" "DRIVER"
              WHERE "NAME"='Forty') AND "CLUSTERED".le. (SELECT "VALUE" FROM "DRIVER" "DRIVER" WHERE
              "NAME"='FortyOne'))
       filter("CLUSTERED">= (SELECT "VALUE" FROM "DRIVER" "DRIVER" WHERE "NAME"='Forty')
              AND "CLUSTERED".le. (SELECT "VALUE" FROM "DRIVER" "DRIVER" WHERE "NAME"='FortyOne'))
   4 - access("NAME"='Forty')
   6 - access("NAME"='FortyOne')

=============
Hint t1_range
=============
--------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |          |    33 |   561 |    10   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED  | T1       |    33 |   561 |     6   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                    | T1_RANGE |    60 |       |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| DRIVER   |     1 |    11 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                  | DRV_I1   |     1 |       |     1   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID BATCHED| DRIVER   |     1 |    11 |     2   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN                  | DRV_I1   |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("CLUSTERED">= (SELECT "VALUE" FROM "DRIVER" "DRIVER" WHERE "NAME"='Forty')
              AND "SCATTERED"=10 AND "CLUSTERED".le. (SELECT "VALUE" FROM "DRIVER" "DRIVER" WHERE
              "NAME"='FortyOne'))
       filter("SCATTERED"=10)
   4 - access("NAME"='Forty')
   6 - access("NAME"='FortyOne')

The big difference occurs at operation 2. You should notice two things – first, the estimate of rows is wrong by a factor of 10, but that’s not the point I want to chase. Secondly by default the optimizer has  selected the plan using the (far) more expensive index. This is a deliberately coded choice and if you have access to MOS then you should check Document ID 4112254.8: “CBO may not choose best index for single table access with unpeeked bind/function”.

Rerun the test (unhinted) after issuing.

alter session set events '38036 trace name context forever, level 10'

You will find that the optimizer automatically takes the path using the lower cost index. For values of 10 or higher Oracle will pick the lower cost index, for values of 9 or less the optimizer will stick with the higher cost index.

The question, of course, is what’s special about the value 10. Given the clue in the MOS document, and the costs from the plans above, and allowing for some fiddling with the arithmetic we can note the following:

  • Cost of using the expensive index for the table access is 63 (From the 10053 trace it’s actually 62.525996)
  • Cost of using the cheaper index for the table access if 6 (Again, from the 1003 trace, 6.037011)
  • Express 6.037011 / 62.525996 as a percentage and you get 9.655 which rounds to 10.

Probably not a coincidence! (Though it might take a few more test to decide whether it’s round() or ceiling())

So if the optimizer is picking the wrong index, and the arithmetic for the one it should be using is dictated by guessing, then calculate the ratio of the two costs for the table access, express as a percentage and round up (and add some if you want to) then set the event.

I’m not really sure how much use you could, or should, make of this event. Possibly it’s one of those things you use temporarily to allow you to generate an SQL Baseline (the event setting doesn’t get captured as an “opt_param()” hint unlike other fiddles of this type that you might use). Maybe there are a few special cases in batch jobs were the optimizer is a little inconsistent about which index to pick and the event is relevant and could be set through an ‘alter session’ command. It’s nice to know it’s there – but may be hard to use in practice.

Footnote

If you were wondering about the cardinality estimates of 60 rowids from the index and 33 rows from the table. This is the effect of the standard  “guesses for unpeeked binds” the optimizer uses. For bounded (between) range on an index the selectivity is 0.0045, for a table it’s 0.0025; in this case we then have to include a factor of 1/3 in the arithmetic to allow for the “scattered=10” predicate:

40,000 * 0.0045 * 1/3 = 60

40,000 * 0.0025 * 1/3 = 33.33….