Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

Most Recent – 2

A question arrived in my email a few days ago with the following observations on a statement that was supposed to query the data dictionary for some information about a specified composite partitioned table. The query was wrapped in a little PL/SQL, similar to the following:

declare
        v_src_part      varchar2(30) := null;
        v_tab           varchar2(30)  := 'PT_COMPOSITE_1';
begin

        select
                /*+ qb_name(main) */
                uts1.subpartition_name
        into    v_src_part
        from
                user_tab_subpartitions uts1
        where
                uts1.table_name = v_tab
        and     uts1.last_analyzed is not null
        and     uts1.num_rows = (
                        select
                                /*+ qb_name(max_subq) */
                                max (uts2.num_rows)
                        from
                                user_tab_subpartitions uts2
                        where
                                uts2.table_name = /* v_tab */ uts1.table_name
                )
        and     rownum = 1
        ;

The requirement is simple: identify the subpartitions of a specific table that have the largest number of rows of any subpartition of the table – but report only the first match.

You’ll notice that the where clause of the subquery has a commented “v_tab” in it. This is the PL/SQL variable used in the outer query block to identify the target table, and it shouldn’t really make any difference if I use the PL/SQL variable in the subquery rather than using a correlating column. However, the question that came with this block of code was was follows:

All the partitions and subpartitions had their stats when running the test. On a first run using the correlated subquery the block reported oracle error ORA-01403: no data found. Changing the code to use the PL/SQL variable the block reported a specific subpartition as expected. A few hours later (after changing the code back to use the correlated subquery) the block reported the same subpartition. Have you ever seen anything like this? The Oracle version is 12.1.0.2.

Rule 1, of course, is to be a little sceptical when someone says “Honest, Guv, the stats are all okay”. But I’m going to assume that the statistcs on this table really were complete and that there was no “data-related” reason for this query to behave in such a surprising way.

The email is an invitation to consider two points.

  1. This looks like a bug: the two versions of the query are logically equivalent, they should return the same results if the underlying data had not changed. (In fact, I think the only “legal” way that the query could return ORA-01403 is if there were no stats on any subpartitions of the table in question – any ordinary usage of the dbms_stats package other than delete_table_stats() would have ensured that the query had to find something.) So, the first run of the correlated subquery produced no data while the modified query did get a result. That suggests a problem with some transformation in the 12.1.0.2 code to handle correlated aggregate subqueries.
  2. How could the second execution of the version with the correlated subquery produce a result a few hours later. Here are a couple of possibilities:
    • Someone had gathered dictionary stats (i.e. on the tables used by the query, not on the subpartitioned table) in the “few hours” gap so the optimizer picked a different execution plan which bypassed the bug.
    • (minor variation on previous) Someone had gather dictionary stats when the first execution plan was already in memory but the “auto_invalidate” option for cursor invalidation meant that the query didn’t get re-optimised for a few hours.
    • Nothing changed, but the query had been flushed from the library cache and did need re-optimisation a few hours later. Since the version is 12.1.0.2 this means statistics feedback or automatic SQL directives could have had an impact – which means there may be dynamic sampling during optimisation – and a different set of random samples could have resulted in a different execution plan.
    • Other …

The interesting bit

There is a generic feature about this question that is more interesting than the “what went wrong, how could I get different results”, and it’s in the choice you can make between using a correlation column and repeating a pl/sql variable (or literal value ).

The switch to using a pl/sql variable turns the subquery into a single-row, “standalone”, subquery – one that could be run without any reference to the outer query – and this imposes a dramatic change on what the optimizer can doSometimes that change will make a huge difference to the optimisation time and the run time.

As a correlated subquery the notional “first strategy” for the optimizer is:

“for each row in the outer query execute the inner query as a filter subquery passing in the correlation value

If you take the “standalone” approach the optimizer will be looking for a plan that says (in effect):

“run the subquery once to generate a constant that you will need to execute the rest of the query”

Running the subquery once rather than once per row is likely to be a good idea – on the other hand Oracle can do “scalar subquery caching” so if the value of the correlation column is always the same the correlated subquery will actually run only once anyway.

More importantly, when the optimizer sees a correlated subquery it will consider unnesting it and then transforming it in various other ways; and it might take the optimizer a long time to work out what it can and can’t do, and the plan it finally does produce may be much slower than what it could have done if it had not unnested the subquery.

Some test results

So I ran 3 variations of the PL/SQL block on Oracle 19.3.0.0 with the CBO trace (10053) enabled and picked out a few highlights. The three tests in order were:

  1. Use the pl/sql variable so the subquery could run as a standalone query
  2. Use the correlating column to make the subquery a correlated subquery
  3. Use the correlating column, but add the hint /*+ no_unnest */ to the subquery.

The results were as follows – first the timing, then a critical measure that explains the timing:

  • Case 1 – standalone subquery – total time 0.82 seconds
  • Case 2 – correlated subquery – total time 5.76 seconds
  • Case 3 – correlated subquery with no_unnest hint – total time 0.84 seconds

Where did all that extra time go – a lot of it went in optimisation. How many “Join Orders” were examined for each query

  • Case 1 – standalone subquery – 90 join orders
  • Case 2 – correlated subquery – 863 join orders
  • Case 3 – correlated subquery with no_unnest hint – 90 join orders

If you’re wondering what the 773 extra join orders were about here’s a clue. I extracted all the lines from the case 2 trace file that started with “SU:” – those are the lines tagged for “Subquery Unnest” – using a call to grep -n “^SU:” {tracefile name} and this is the result:


  2945:SU: Unnesting query blocks in query block SEL$071BB01A (#1) that are valid to unnest.
  2947:SU: Considering subquery unnest on query block SEL$071BB01A (#1).
  2948:SU:   Checking validity of unnesting subquery SEL$4F5F2F29 (#2)
  2949:SU:   Passed validity checks, but requires costing.
  2950:SU: Using search type: exhaustive
  2951:SU: Starting iteration 1, state space = (2) : (1)
  2952:SU:   Unnesting subquery query block SEL$4F5F2F29 (#2)Subquery removal for query block SEL$4F5F2F29 (#2)
  3089:SU: Costing transformed query.
 66112:SU: Considering interleaved complex view merging
 66113:SU:   Unnesting subquery query block SEL$4F5F2F29 (#2)Subquery removal for query block SEL$4F5F2F29 (#2)
 66366:SU: Costing transformed query.
129372:SU: Finished interleaved complex view merging
129373:SU: Considering interleaved distinct placement
129374:SU: Finished interleaved distinct placement
129375:SU: Considering interleaved join pred push down
129376:SU:   Unnesting subquery query block SEL$4F5F2F29 (#2)Subquery removal for query block SEL$4F5F2F29 (#2)
251638:SU: Rejected interleaved query.
251640:SU: Finished interleaved join pred push down
251641:SU: Considering interleaved OR Expansion
251642:SU:   Unnesting subquery query block SEL$4F5F2F29 (#2)Subquery removal for query block SEL$4F5F2F29 (#2)
251651:SU: Finished interleaved OR Expansion
251653:SU: Updated best state, Cost = 19.085153
251654:SU: Starting iteration 2, state space = (2) : (0)
251665:SU: Costing transformed query.
310395:SU: Not update best state, Cost = 20.083998
310396:SU: Will unnest subquery SEL$4F5F2F29 (#2)

The optimizer checks the validity of unnesting (generated) query block SEL$4F5F2F29 at line 2948 of the trace and decides, 308,000 lines later after an exhaustive examination of the possibilities, that it will unnest the subquery. Since this is a recent version of Oracle we take one simple extra step by checking for “TIMER” information, again using a “grep -n” call –

251639:TIMER:  SU: Interleaved JPPD SEL$B73B51DC cpu: 1.263 sec elapsed: 1.263 sec
251652:TIMER: SU: iteration (#1) SEL$B73B51DC cpu: 2.607 sec elapsed: 2.607 sec
310577:TIMER: CBQT SU and CVM SEL$071BB01A cpu: 3.323 sec elapsed: 3.323 sec
433371:TIMER: Cost-Based Join Predicate Push-Down SEL$12B6FE6C cpu: 1.307 sec elapsed: 1.306 sec
433477:TIMER: Cost-Based Transformations (Overall) SEL$12B6FE6C cpu: 4.731 sec elapsed: 4.731 sec
496189:TIMER: SQL Optimization (Overall) SEL$12B6FE6C cpu: 5.306 sec elapsed: 5.306 sec

Of course most of the time spent in this particular example was a result of optimising (and writing the optimizer trace), but for my tiny example (table definition below) the final figures I’ll show are the buffer gets and CPU time reported by a basic 10046 trace file after optimisation with all the relevant data was cached:

  • Case 1 – standalone subquery – 89 buffer gets / 0.00 seconds
  • Case 2 – correlated subquery – 130 buffer gets / 0.53 seconds
  • Case 3 – correlated subquery with no_unnest hint – 121 buffer gets / 0.08 CPU seconds

The sub-centisecond time is a little suspect, of course, but the others seem fairly trustworthy.

Conclusion

The title of this piece is “Most Recent” because the commonest requirement for a query of this shape is find the most recent row matching the following predicates”, even though in this case the interpretation is “find me the row matching the largest value”.

The “standard” pattern for writing a “most recent” query is to use a correlated subquery – but it’s worth remembering that you may reduce optimisation time and run time by “copying down the constant” rather than using the correlation mechanism.

(There are alternative strategies to the subquery approach, of course, and the analytic max() – introduced in Oracle 8i – is gaining traction as one of the popular alternatives.)

Footnote 1

If you want to re-run my test on different platforms and versions of Oracle, here’s the code to generate the table.  (Don’t be surprised if you don’t get completely consistent results – much of the optimization will depend on the size of all the relevant tables (tab$, tabcompart$, etc.) in the data dictionary, rather than on the actual definition of this partitioned table.


em
rem     Script:         most_recent_3.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Jun 2020
rem
rem     Last tested 
rem             19.3.0.0
rem

create table pt_composite_1 (
        id,
        grp,
        small_vc,
        padding
)
nologging
partition by range(id) 
subpartition by hash (grp)
subpartitions 4
(
        partition p2 values less than (400),
        partition p3 values less than (800),
        partition p4 values less than (1600),
        partition p5 values less than (3200)
)
as
select
        rownum                          id,
        trunc(rownum/50)                grp,
        to_char(trunc(rownum/20))       small_vc,
        rpad('x',100)                   padding
from
        all_objects
where 
        rownum <= 3000 -- > comment to avoid wordpress format issue
;

execute dbms_stats.gather_table_stats(user,'pt_composite_1',granularity=>'ALL')


Footnote 2

For reference, here are the outputs I got from executing egrep -n -e”^SU:” -e”TIMER” against the other two CBO trace files.

First for the “standalone” form – note how line 3130 tells us that “there is no correlation”.


806:SU: Considering subquery unnesting in query block MISC$1 (#0)
2947:SU: Unnesting query blocks in query block SEL$071BB01A (#1) that are valid to unnest.
2952:SU: Considering subquery unnest on query block SEL$071BB01A (#1).
2953:SU:   Checking validity of unnesting subquery SEL$4F5F2F29 (#2)
2954:SU:     SU bypassed: No correlation to immediate outer subquery.
2955:SU:     SU bypassed: Failed basic validity checks.
2956:SU:   Validity checks failed.
3130:SU:     SU bypassed: No correlation to immediate outer subquery.

Then for the correlated subquery with /*+ no_unnest */ hint; and line 3122 tells us that SU was bypassed because of a hint/parameter:


809:SU: Considering subquery unnesting in query block MISC$1 (#0)
2945:SU: Unnesting query blocks in query block SEL$071BB01A (#1) that are valid to unnest.
2947:SU: Considering subquery unnest on query block SEL$071BB01A (#1).
2948:SU:   Checking validity of unnesting subquery SEL$4F5F2F29 (#2)
2949:SU:     SU bypassed: Not enabled by hint/parameter.
2950:SU:     SU bypassed: Failed basic validity checks.
2951:SU:   Validity checks failed.
3122:SU:     SU bypassed: Not enabled by hint/parameter.

Neither file showed any “TIMER” information since that appears, by default, only for steps that take longer than one second. (If you want to adjust the granularity, see Franck Pachot’s note on parse time that describes bug/fix_control 16923858.

Video : Hybrid Partitioned Tables in Oracle Database 19c

In today’s video we’ll give a quick demonstration of Hybrid Partitioned Tables, introduced in Oracle Database 19c.

The video is based on this 19c article.

The video only has a single example using external partitions pointing to CSV data. The article also includes and example using a Data Pump file.

The star of today’s video is Oren Nakdimon, who was taking a day off from being a God of Edition-Based Redefinition. </p />
</p></div>

    	  	<div class=

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=