Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

Oracle ACFS: “du” vs. “df” and “acfsutil info”

By Franck Pachot

.
This is a demo about Oracle ACFS snapshots, and how to understand the used and free space, as displayed by “df”, when there are modifications in the base parent or the snapshot children. The important concept to understand is that, when you take a snapshot, any modification to the child or parent will


[grid@cloud ~]$ asmcmd lsdg DATAC1

State    Type  Rebal  Sector  Logical_Sector  Block       AU   Total_MB    Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  HIGH  N         512             512   4096  4194304  214991104  191881152         11943936        59979016              0             Y  DATAC1/

On a database machine in the Oracle Cloud I have a diskgroup with lot of free space. I’ll use this DATAC1 diskgroup to store my ACFS filesystem. the size in MegaByte is not easy to read.
I can have a friendly overview from acfsutil with human readable sizes (in TeraByte there).


[grid@cloud ~]$ acfsutil info storage -u TB -l DATAC1

Diskgroup: DATAC1 (83% free)
  total disk space:         205.03
  ASM file space:            22.04
  total free space:         182.99
  net free with mirroring:   60.99
  usable after reservation:  57.20
  redundancy type:          HIGH

    Total space used by ASM non-volume files:
      used:                     19.03
      mirror used:               6.34

    volume: /dev/asm/dump-19
      total:                     1.00
      free:                      0.22
      redundancy type:         high
      file system:             /u03/dump
----
unit of measurement: TB

There’s already a volume here (/dev/asm/dump-19) which is named ‘DUMP’ and mounted as an ACFS filesystem (/u03/dump) but I’ll create a now one for this demo and will remove.

Logical volume: asmcmd volcreate

I have a diskgroup which exposes disk space to ASM. The first thing to do, in order to build a filesystem on it, is to create a logical volume, which is called ADVM for “ASM Dynamic Volume Manager”.


[grid@cloud ~]$ asmcmd volcreate -G DATAC1 -s 100G MYADVM

I’m creating a 100GB new volume, identified by its name (MYADVM) within the diskgroup (DATAC1). The output is not verbose at all but I can check it with volinfo.


[grid@cloud ~]$ asmcmd volinfo -G DATAC1 -a

Diskgroup Name: DATAC1

         Volume Name: DUMP
         Volume Device: /dev/asm/dump-19
         State: ENABLED
         Size (MB): 1048576
         Resize Unit (MB): 512
         Redundancy: HIGH
         Stripe Columns: 8
         Stripe Width (K): 1024
         Usage: ACFS
         Mountpath: /u03/dump

         Volume Name: MYADVM
         Volume Device: /dev/asm/myadvm-19
         State: ENABLED
         Size (MB): 102400
         Resize Unit (MB): 512
         Redundancy: HIGH
         Stripe Columns: 8
         Stripe Width (K): 1024
         Usage:
         Mountpath:

With “-a” I show all volumes on the diskgroup, but I could have mentioned the volume name. This is what we will do later.

“Usage” is empty because there’s no filesystem created yet, and “Mountpath” is empty because it is not mounted. The information I need is the Volume Device (/dev/asm/myadvm-19) as I’ll have access to it from the OS.


[grid@cloud ~]$ lsblk -a /dev/asm/myadvm-19

NAME          MAJ:MIN  RM  SIZE RO TYPE MOUNTPOINT
asm!myadvm-19 248:9731  0  100G  0 disk

[grid@cloud ~]$ lsblk -t /dev/asm/myadvm-19

NAME          ALIGNMENT MIN-IO OPT-IO PHY-SEC LOG-SEC ROTA SCHED    RQ-SIZE   RA
asm!myadvm-19         0    512      0     512     512    1 deadline     128  128

[grid@cloud ~]$ fdisk -l /dev/asm/myadvm-19

Disk /dev/asm/myadvm-19: 107.4 GB, 107374182400 bytes
255 heads, 63 sectors/track, 13054 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000

Visible from the OS, I have a 100GB volume with nothing there: I need to format it.

Filesystem: Linux mkfs


[grid@cloud ~]$ grep -v nodev /proc/filesystems

        iso9660
        ext4
        fuseblk
        acfs

ACFS is a filesystem supported by the kernel. I can use “mkfs” to format the volume to ACFS.


[grid@cloud ~]$ mkfs.acfs -f /dev/asm/myadvm-19

mkfs.acfs: version                   = 18.0.0.0.0
mkfs.acfs: on-disk version           = 46.0
mkfs.acfs: volume                    = /dev/asm/myadvm-19
mkfs.acfs: volume size               = 107374182400  ( 100.00 GB )
mkfs.acfs: Format complete.

I used “-f” because my volume was already formatted from a previous test. I have now a 100GB filesystem on this volume.


[grid@cloud ~]$ asmcmd volinfo -G DATAC1 MYADVM

Diskgroup Name: DATAC1

         Volume Name: MYADVM
         Volume Device: /dev/asm/myadvm-19
         State: ENABLED
         Size (MB): 102400
         Resize Unit (MB): 512
         Redundancy: HIGH
         Stripe Columns: 8
         Stripe Width (K): 1024
         Usage: ACFS
         Mountpath:

The “Usage” shows that ASM knows that the ADVM volume holds an ACFS filesystem.


[opc@cloud ~]$ sudo mkdir -p /myacfs

I’ll mount this filesystem to /myacfs


[opc@cloud ~]$ sudo mount -t acfs /dev/asm/myadvm-19 /myacfs

[opc@cloud ~]$ mount | grep /myacfs

/dev/asm/myadvm-19 on /myacfs type acfs (rw)

[opc@cloud ~]$ df -Th /myacfs

Filesystem         Type  Size  Used Avail Use% Mounted on
/dev/asm/myadvm-19 acfs  100G  448M  100G   1% /myacfs

[opc@cloud ~]$ ls -alrt /myacfs

total 100
drwxr-xr-x 30 root root  4096 Jun 23 10:40 ..
drwxr-xr-x  4 root root 32768 Jun 23 15:48 .
drwx------  2 root root 65536 Jun 23 15:48 lost+found

[opc@cloud ~]$ sudo umount /myacfs

I wanted to show that you can mount it from the OS but, as we have Grid Infrastructure, we usually want to manage it as a cluster resource.


[opc@cloud ~]$ sudo acfsutil registry -a /dev/asm/myadvm-19 /myacfs -u grid

acfsutil registry: mount point /myacfs successfully added to Oracle Registry

[opc@cloud ~]$ ls -alrt /myacfs

total 100
drwxr-xr-x 30 root root      4096 Jun 23 10:40 ..
drwxr-xr-x  4 grid oinstall 32768 Jun 23 15:48 .
drwx------  2 root root     65536 Jun 23 15:48 lost+found

The difference here is that I’ve set the owner of the filesystem to “grid” as that’s the user I’ll use for the demo.

Used and free space


[grid@cloud ~]$ asmcmd volinfo -G DATAC1 MYADVM

Diskgroup Name: DATAC1

         Volume Name: MYADVM
         Volume Device: /dev/asm/myadvm-19
         State: ENABLED
         Size (MB): 102400
         Resize Unit (MB): 512
         Redundancy: HIGH
         Stripe Columns: 8
         Stripe Width (K): 1024
         Usage: ACFS
         Mountpath: /myacfs

ADVM has the information about the mount path but now to interact with it I’ll use “acfsutil” for ACFS features or the standard Linux commands on filesystems.


[grid@cloud ~]$ du -ah /myacfs

64K     /myacfs/lost+found
96K     /myacfs

I have no files there: only 96 KB used.


[grid@cloud ~]$ df -Th /myacfs

Filesystem         Type  Size  Used Avail Use% Mounted on
/dev/asm/myadvm-19 acfs  100G  688M  100G   1% /myacfs

the whole size of 100GB is available.


[grid@cloud ~]$ acfsutil info storage -u GB -l DATAC1

Diskgroup: DATAC1 (83% free)
  total disk space:         209952.25
  ASM file space:           22864.75
  total free space:         187083.87
  net free with mirroring:  62361.29
  usable after reservation: 58473.23
  redundancy type:          HIGH

    Total space used by ASM non-volume files:
      used:                    19488.04
      mirror used:             6496.01

    volume: /dev/asm/myadvm-19
      total:                   100.00
      free:                     99.33
      redundancy type:         high
      file system:             /myacfs
...
----
unit of measurement: GB

“acfs info storage” shows all volumes in the diskgroup (I removed the output for the ‘DUMP’ one here)


[grid@cloud ~]$ acfsutil info fs /myacfs
/myacfs
    ACFS Version: 18.0.0.0.0
    on-disk version:       47.0
    compatible.advm:       18.0.0.0.0
    ACFS compatibility:    18.0.0.0.0
    flags:        MountPoint,Available
    mount time:   Tue Jun 23 15:52:35 2020
    mount sequence number: 8
    allocation unit:       4096
    metadata block size:   4096
    volumes:      1
    total size:   107374182400  ( 100.00 GB )
    total free:   106652823552  (  99.33 GB )
    file entry table allocation: 393216
    primary volume: /dev/asm/myadvm-19
        label:
        state:                 Available
        major, minor:          248, 9731
        logical sector size:   512
        size:                  107374182400  ( 100.00 GB )
        free:                  106652823552  (  99.33 GB )
        metadata read I/O count:         1203
        metadata write I/O count:        10
        total metadata bytes read:       4927488  (   4.70 MB )
        total metadata bytes written:    40960  (  40.00 KB )
        ADVM diskgroup:        DATAC1
        ADVM resize increment: 536870912
        ADVM redundancy:       high
        ADVM stripe columns:   8
        ADVM stripe width:     1048576
    number of snapshots:  0
    snapshot space usage: 0  ( 0.00 )
    replication status: DISABLED
    compression status: DISABLED

“acfsutil info fs” is the best way to have all information and here it shows the same as what we see from the OS: size=100GB and free=99.33GB

Add a file


[grid@cloud ~]$ dd of=/myacfs/file.tmp if=/dev/zero bs=1G count=42

42+0 records in
42+0 records out
45097156608 bytes (45 GB) copied, 157.281 s, 287 MB/s

I have created a 42 GB file in my filesystem. And now will compare the size info.


[grid@cloud ~]$ du -ah /myacfs

64K     /myacfs/lost+found
43G     /myacfs/file.tmp
43G     /myacfs

I see one additional file with 43GB


[grid@cloud ~]$ df -Th /myacfs

Filesystem         Type  Size  Used Avail Use% Mounted on
/dev/asm/myadvm-19 acfs  100G   43G   58G  43% /myacfs

The used space that was 688 MB is now 43GB, the free space which was 100 GB is now 58 GB and the 1% usage is now 43% – this is the exact math (rounded to next GB) as my file had to allocate extents for all its data.


[grid@cloud ~]$ acfsutil info storage -u GB -l DATAC1

Diskgroup: DATAC1 (83% free)
  total disk space:         209952.25
  ASM file space:           22864.75
  total free space:         187083.87
  net free with mirroring:  62361.29
  usable after reservation: 58473.23
  redundancy type:          HIGH

    Total space used by ASM non-volume files:
      used:                    19488.04
      mirror used:             6496.01

    volume: /dev/asm/myadvm-19
      total:                   100.00
      free:                     57.29
      redundancy type:         high
      file system:             /myacfs

Here the “free:” that was 99.33 GB before is now 57.29 GB which matches what we see from df.
total free: 106652823552 ( 99.33 GB )


[grid@cloud ~]$ acfsutil info fs /myacfs

/myacfs
    ACFS Version: 18.0.0.0.0
    on-disk version:       47.0
    compatible.advm:       18.0.0.0.0
    ACFS compatibility:    18.0.0.0.0
    flags:        MountPoint,Available
    mount time:   Tue Jun 23 15:52:35 2020
    mount sequence number: 8
    allocation unit:       4096
    metadata block size:   4096
    volumes:      1
    total size:   107374182400  ( 100.00 GB )
    total free:   61513723904  (  57.29 GB )
    file entry table allocation: 393216
    primary volume: /dev/asm/myadvm-19
        label:
        state:                 Available
        major, minor:          248, 9731
        logical sector size:   512
        size:                  107374182400  ( 100.00 GB )
        free:                  61513723904  (  57.29 GB )
        metadata read I/O count:         1686
        metadata write I/O count:        130
        total metadata bytes read:       8687616  (   8.29 MB )
        total metadata bytes written:    3555328  (   3.39 MB )
        ADVM diskgroup:        DATAC1
        ADVM resize increment: 536870912
        ADVM redundancy:       high
        ADVM stripe columns:   8
        ADVM stripe width:     1048576
    number of snapshots:  0
    snapshot space usage: 0  ( 0.00 )
    replication status: DISABLED
    compression status: DISABLED

What has changed here is only the “total free:” from 99.33 GB to 57.29 GB

Create a snapshot

For the moment, with no snapshot and no compression, the size allocated in the filesystem (as the “df” used) is the same as the size of the files (as the “du” file size).


[grid@cloud ~]$ acfsutil snap info /myacfs

    number of snapshots:  0
    snapshot space usage: 0  ( 0.00 )

The “acfsutil info fs” shows no snapshots, and “acfsutil snap info” shows the same but will give more details where I’ll have created snapshots.


[grid@cloud ~]$ acfsutil snap create -w S1 /myacfs

acfsutil snap create: Snapshot operation is complete.

This has created a read-write snapshot of the filesystem.


[grid@cloud ~]$ du -ah /myacfs

64K     /myacfs/lost+found
43G     /myacfs/file.tmp
43G     /myacfs

Nothing different here as this shows only the base. The snapshots are in a hidden .ACFS that is not listed but can be accessed when mentioning the path.


[grid@cloud ~]$ du -ah /myacfs/.ACFS

32K     /myacfs/.ACFS/.fileid
32K     /myacfs/.ACFS/repl
43G     /myacfs/.ACFS/snaps/S1/file.tmp
43G     /myacfs/.ACFS/snaps/S1
43G     /myacfs/.ACFS/snaps
43G     /myacfs/.ACFS

Here I see my 42 GB file in the snapshot. But, as I did no modifications there, it shares the same extents on disk. This means that even if I see two files of 42 GB there is only 42 GB allocated for those two virtual copies.


[grid@cloud ~]$ df -Th /myacfs

Filesystem         Type  Size  Used Avail Use% Mounted on

/dev/asm/myadvm-19 acfs  100G   43G   58G  43% /myacfs

As there are no additional extents allocated for this snapshot, “df” shows the same as before. Here we start to see a difference between the physical “df” size and the virtual “du” size.


[grid@cloud ~]$ acfsutil info storage -u GB -l DATAC1

Diskgroup: DATAC1 (83% free)
  total disk space:         209952.25
  ASM file space:           22864.75
  total free space:         187083.87
  net free with mirroring:  62361.29
  usable after reservation: 58473.23
  redundancy type:          HIGH

    Total space used by ASM non-volume files:
      used:                    19488.04
      mirror used:             6496.01

    volume: /dev/asm/myadvm-19
      total:                   100.00
      free:                     57.29
      redundancy type:         high
      file system:             /myacfs
        snapshot: S1 (/myacfs/.ACFS/snaps/S1)
          used:          0.00
          quota limit:   none
...
----
unit of measurement: GB

Here I have additional information for my snapshot: “snapshot: S1 (/myacfs/.ACFS/snaps/S1)” with 0 GB used because I did not modify anything in the snapshot yet. The volume still has the same free space.


[grid@cloud ~]$ acfsutil snap info /myacfs
    number of snapshots:  1
    snapshot space usage: 393216  ( 384.00 KB )

The new thing here is “number of snapshots: 1” with nearly no space usage (384 KB)


[grid@cloud ~]$ acfsutil info fs /myacfs
/myacfs
    ACFS Version: 18.0.0.0.0
    on-disk version:       47.0
    compatible.advm:       18.0.0.0.0
    ACFS compatibility:    18.0.0.0.0
    flags:        MountPoint,Available
    mount time:   Tue Jun 23 15:52:35 2020
    mount sequence number: 8
    allocation unit:       4096
    metadata block size:   4096
    volumes:      1
    total size:   107374182400  ( 100.00 GB )
    total free:   61513723904  (  57.29 GB )
    file entry table allocation: 393216
    primary volume: /dev/asm/myadvm-19
        label:
        state:                 Available
        major, minor:          248, 9731
        logical sector size:   512
        size:                  107374182400  ( 100.00 GB )
        free:                  61513723904  (  57.29 GB )
        metadata read I/O count:         4492
        metadata write I/O count:        236
        total metadata bytes read:       24363008  (  23.23 MB )
        total metadata bytes written:    12165120  (  11.60 MB )
        ADVM diskgroup:        DATAC1
        ADVM resize increment: 536870912
        ADVM redundancy:       high
        ADVM stripe columns:   8
        ADVM stripe width:     1048576
    number of snapshots:  1
    snapshot space usage: 393216  ( 384.00 KB )
    replication status: DISABLED
    compression status: DISABLED

Here I have more detail: those 384 KB are for the file allocation table only.

Write on snapshot

My snapshot shows no additional space but that will not stay as the goal is to do some modifications, within the same file, like a database would do on its datafiles as soon as it is opened read-write.


[grid@cloud ~]$ dd of=/myacfs/.ACFS/snaps/S1/file.tmp if=/dev/zero bs=1G count=10 conv=notrunc

10+0 records in
10+0 records out
10737418240 bytes (11 GB) copied, 58.2101 s, 184 MB/s

I’ve overwritten 10GB within the 42 GB file but the remaining 32 are still the same (that’s what the conv=notrunc is doing – not truncating the end of file).


[grid@cloud ~]$ du -ah /myacfs

64K     /myacfs/lost+found
43G     /myacfs/file.tmp
43G     /myacfs

Nothing has changed at the base level because I modified only the file in the snapshot.



[grid@cloud ~]$ du -ah /myacfs/.ACFS

32K     /myacfs/.ACFS/.fileid
32K     /myacfs/.ACFS/repl
43G     /myacfs/.ACFS/snaps/S1/file.tmp
43G     /myacfs/.ACFS/snaps/S1
43G     /myacfs/.ACFS/snaps
43G     /myacfs/.ACFS

Nothing has changed either in the snapshot because the file is still the same size.


[grid@cloud ~]$ df -Th /myacfs

Filesystem         Type  Size  Used Avail Use% Mounted on
/dev/asm/myadvm-19 acfs  100G   53G   48G  53% /myacfs

The filesystem, has increased by the amount I modified in the snapshot: I had 58GB available and now only 48GB. Because the 10GB of extents that were shared between the shanpshot child and the base (the snapshot parent) are not the same anymore and new extents had to be allocated for the snapshot. This is similar to copy-on-write.

That’s the main point of this blog post: you don’t see this new allocation with “ls” or “du” but only with “df” or the filesystem specific tools.


[grid@cloud ~]$ acfsutil info storage -u GB -l DATAC1

Diskgroup: DATAC1 (83% free)
  total disk space:         209952.25
  ASM file space:           22864.75
  total free space:         187083.87
  net free with mirroring:  62361.29
  usable after reservation: 58473.23
  redundancy type:          HIGH

    Total space used by ASM non-volume files:
      used:                    19488.04
      mirror used:             6496.01

    volume: /dev/asm/myadvm-19
      total:                   100.00
      free:                     47.09
      redundancy type:         high
      file system:             /myacfs
        snapshot: S1 (/myacfs/.ACFS/snaps/S1)
          used:         10.10
          quota limit:   none

----
unit of measurement: GB

the volume free space has decreased fro 57.29 to 47.09 GB


[grid@cloud ~]$ acfsutil snap info /myacfs
snapshot name:               S1
snapshot location:           /myacfs/.ACFS/snaps/S1
RO snapshot or RW snapshot:  RW
parent name:                 /myacfs
snapshot creation time:      Fri Jul  3 08:01:52 2020
file entry table allocation: 393216   ( 384.00 KB )
storage added to snapshot:   10846875648   (  10.10 GB )

    number of snapshots:  1
    snapshot space usage: 10846875648  (  10.10 GB )

10 GB has been added to the snapshot for the extents that are different than the parent.


[grid@cloud ~]$ acfsutil info fs /myacfs

/myacfs
    ACFS Version: 18.0.0.0.0
    on-disk version:       47.0
    compatible.advm:       18.0.0.0.0
    ACFS compatibility:    18.0.0.0.0
    flags:        MountPoint,Available
    mount time:   Fri Jul  3 07:33:19 2020
    mount sequence number: 6
    allocation unit:       4096
    metadata block size:   4096
    volumes:      1
    total size:   107374182400  ( 100.00 GB )
    total free:   50566590464  (  47.09 GB )
    file entry table allocation: 393216
    primary volume: /dev/asm/myadvm-19
        label:
        state:                 Available
        major, minor:          248, 9731
        logical sector size:   512
        size:                  107374182400  ( 100.00 GB )
        free:                  50566590464  (  47.09 GB )
        metadata read I/O count:         9447
        metadata write I/O count:        1030
        total metadata bytes read:       82587648  (  78.76 MB )
        total metadata bytes written:    89440256  (  85.30 MB )
        ADVM diskgroup:        DATAC1
        ADVM resize increment: 536870912
        ADVM redundancy:       high
        ADVM stripe columns:   8
        ADVM stripe width:     1048576
    number of snapshots:  1
    snapshot space usage: 10846875648  (  10.10 GB )
    replication status: DISABLED

The file entry table allocation has not changed. It was pointing to the parent for all extents before. Now, 10GB of extents are pointing to the snapshot child ones.

Write on parent

If I overwrite a different part on the parent, it will need to allocate new extents as those extents are share with the snapshot.


[grid@cloud ~]$ dd of=/myacfs/file.tmp if=/dev/zero bs=1G count=10 conv=notrunc seek=20

10+0 records in
10+0 records out
10737418240 bytes (11 GB) copied, 35.938 s, 299 MB/s

this has written 10GB again, but at the 20GB offset, not overlapping the range of extents modified in the snapshot.


[grid@cloud ~]$ df -Th /myacfs
Filesystem         Type  Size  Used Avail Use% Mounted on
/dev/asm/myadvm-19 acfs  100G   63G   38G  63% /myacfs

Obviously 10GB had to be allocated for those new extents.


[grid@cloud ~]$ acfsutil snap info /myacfs
snapshot name:               S1
snapshot location:           /myacfs/.ACFS/snaps/S1
RO snapshot or RW snapshot:  RW
parent name:                 /myacfs
snapshot creation time:      Fri Jul  3 09:17:10 2020
file entry table allocation: 393216   ( 384.00 KB )
storage added to snapshot:   10846875648   (  10.10 GB )


    number of snapshots:  1
    snapshot space usage: 21718523904  (  20.23 GB )

Those 10GB modified on the base are allocated for the snapshot, because what was a pointer to the parent is now a copy of extents in the state they were before the write.

I’m not going into details of copy-on-write or redirect-on-write. You can see that at file level with “acfsutil info file” and Ludovico Caldara made a great demo of it a few years ago:

Now what happens if I modify, in the parent, the same extents that have been modified in the snapshot? I don’t need a copy of the previous version of them, right?


[grid@cloud ~]$ dd of=/myacfs/file.tmp if=/dev/zero bs=1G count=10 conv=notrunc

10+0 records in
10+0 records out
10737418240 bytes (11 GB) copied, 34.9208 s, 307 MB/s

The first 10GB of this file, since the snapshot was taken, have now been modified in the base and in all snapshots (only one here).


[grid@cloud ~]$ df -Th /myacfs
Filesystem         Type  Size  Used Avail Use% Mounted on
/dev/asm/myadvm-19 acfs  100G   74G   27G  74% /myacfs

I had 10GB allocated again for this.


[grid@cloud ~]$ acfsutil snap info /myacfs
snapshot name:               S1
snapshot location:           /myacfs/.ACFS/snaps/S1
RO snapshot or RW snapshot:  RW
parent name:                 /myacfs
snapshot creation time:      Fri Jul  3 09:17:10 2020
file entry table allocation: 393216   ( 384.00 KB )
storage added to snapshot:   10846875648   (  10.10 GB )

    number of snapshots:  1
    snapshot space usage: 32564994048  (  30.33 GB )

[grid@cloud ~]$ acfsutil info file /myacfs/.ACFS/snaps/S1/file.tmp -u |
                awk '/(Yes|No)$/{ if ( $7!=l ) o=$1 ; s[o]=s[o]+$2 ; i[o]=$7 ; l=$7} END{ for (o in s) printf "offset: %4dGB size: %4dGB Inherited: %3s\n",o/1024/1024/1024,s[o]/1024/1024/1024,i[o]}' | sort
offset:    0GB size:   10GB Inherited:  No
offset:   10GB size:   31GB Inherited: Yes

I have aggregated, with awk, the range of snapshot extents that are inherited from the parent. The first 10GB are those that I modified in the snapshot. The others, above 10GB are from the S1 snapshot.


[grid@cloud ~]$ acfsutil info file /myacfs/file.tmp -u | 
                awk '/(Yes|No)$/{ if ( $7!=l ) o=$1 ; s[o]=s[o]+$2 ; i[o]=$7 ; l=$7} END{ for (o in s) printf "offset: %4dGB size: %4dGB Inherited: %3s %s\n",o/1024/1024/1024,s[o]/1024/1024/1024,i[o],x[o]}' | sort
offset:    0GB size:   10GB Inherited:  No
offset:   10GB size:    9GB Inherited: Yes
offset:   19GB size:   10GB Inherited:  No
offset:   30GB size:   11GB Inherited: Yes

Here in the base snapshot I see as inherited, between parent and child, the two ranges that I modified: 10GB from offset 0GB and 10 GB from offset 20GB

That’s the important point: when you take a snapshot, the modifications on the parent, as well as the modifications in the snapshot, will allocate new extents to add to the snapshot.

Why is this important?

This explains why, on ODA, all non-CDB databases are created on a snapshot that has been taken when the filesystem was empty.

Read-write snapshots are really cool, especially with multitenant as they are automated with the CRATE PLUGGABLE DATABASE … SNAPSHOT COPY. But you must keep in mind that the snapshot is made at filesystem level (this may change in 20c with File-Based Snapshots). Any change to the master will allocate space, whether used or not in the snapshot copy.

I blogged about that in the past: https://blog.dbi-services.com/multitenant-thin-provisioning-pdb-snapshots-on-acfs/

Here I just wanted to clarify what you see with “ls” and “du” vs. “df” or “acfsutil info”. “ls” and “du” show the virtual size of the files. “df” shows the extents allocated in the filesystem as base extents or snapshot copies. “acfsutil info” shows those extents allocated as “storage added to snapshot” whether they were allocated for modifications on the parent or child.


[grid@cloud ~]$ acfsutil info file /myacfs/.ACFS/snaps/S1/file.tmp -u
/myacfs/.ACFS/snaps/S1/file.tmp

    flags:        File
    inode:        18014398509482026
    owner:        grid
    group:        oinstall
    size:         45097156608  (  42.00 GB )
    allocated:    45105545216  (  42.01 GB )
    hardlinks:    1
    device index: 1
    major, minor: 248,9731
    create time:  Fri Jul  3 07:36:45 2020
    access time:  Fri Jul  3 07:36:45 2020
    modify time:  Fri Jul  3 09:18:25 2020
    change time:  Fri Jul  3 09:18:25 2020
    extents:
        -----offset ----length | -dev --------offset | inherited
                  0  109051904 |    1    67511517184 | No
          109051904  134217728 |    1    67620569088 | No
          243269632  134217728 |    1    67754786816 | No
          377487360  134217728 |    1    67889004544 | No
          511705088  134217728 |    1    68023222272 | No
          645922816  134217728 |    1    68157440000 | No
          780140544  134217728 |    1    68291657728 | No
          914358272  134217728 |    1    68425875456 | No
         1048576000  134217728 |    1    68560093184 | No
         1182793728  134217728 |    1    68694310912 | No
...

The difference between “du” and “df”, which is also the “storage added to snapshot” displayed by “acfsutil snap info”, is what you see as “inherited”=No in “acfsutil info file -u” on all files, parent and child. Note that I didn’t use compression here, which is another reason for the difference between “du” and “df”.

Cet article Oracle ACFS: “du” vs. “df” and “acfsutil info” est apparu en premier sur Blog dbi services.

20 Years of ORACLE-BASE.com

It was twenty years ago today that the first incarnation of my website was born.

It started life as a few scripts and notes put on the internet so I didn’t have to carry them around on floppy disks or CDs when I was moving around between contracts. I had been working with Oracle technologies for five years before the website was born, but most of the early content was Oracle 8i stuff.

I added new articles now and then, but I guess it really started to take off when I was preparing for the Oracle 9i OCP upgrade exam. For the 7.3, 8 and 8i OCP exams I used books to prepare, but for 9i OCP I decided to do the beta exam. This meant there were no books available yet, so I had to do it the hard way. I wrote my own revision notes for the whole of the syllabus and put them on the website. They got pretty popular, as free things often do. </p />
</p></div>

    	  	<div class=

Installing Virtualbox Guest Additions for Oracle Linux 8.2

Since I can never remember how to install Virtualbox Guest Additions I thought I’d write it down. Maybe it’ll save you a few minutes; I know it will save me a lot of time ;)

For this post I used the latest versions at the time of writing:

  • Virtualbox 6.1.10 for Linux (my host is running Ubuntu 20.04 LTS)
  • Oracle Linux 8.2 (V996906-01.iso)

The VM was installed using the “minimal-environment” group and booted into UEK 6. I believe this change came with Oracle Linux (OL) 8.2 and I seem to remember OL 8.1 used the Red Hat Compatible Kernel (RHCK) by default. There’s nothing wrong with that of course, it’s just an observation. I am going to stick with UEK 6 in my lab, instructions are different from using RHCK.

As usual I run a “dnf update -y” (in a tmux session of course) to upgrade the software release to the latest and greatest software. After this completed, it’s time for a reboot and the installation of the Guest Additions. I hope you forgive me for not sharing screen output from the system upgrade, I thought it would have been boring …

Right after the system comes back online after the reboot, you might notice a new kernel: 5.4.17-2011.3.2.1.el8uek.x86_64 is now the default, previously it was 5.4.17-2011.1.2.el8.

Before you can install the Guest Additions a few additional packages are required.

[root@ol8base ~]# dnf install perl make bzip2 gzip unzip kernel-uek-devel-$(uname -r) tar
Last metadata expiration check: 0:22:15 ago on Fri 29 May 2020 14:11:17 BST.
Package gzip-1.9-9.el8.x86_64 is already installed.
Dependencies resolved.
================================================================================
 Package                      Arch   Version            Repository         Size
================================================================================
Installing:
 bzip2                        x86_64 1.0.6-26.el8       ol8_baseos_latest  60 k
 kernel-uek-devel             x86_64 5.4.17-2011.3.2.1.el8uek
                                                        ol8_UEKR6          17 M
 make                         x86_64 1:4.2.1-10.el8     ol8_baseos_latest 498 k
 perl                         x86_64 4:5.26.3-416.el8   ol8_appstream      72 k
 tar                          x86_64 2:1.30-4.el8       ol8_baseos_latest 838 k
 unzip                        x86_64 6.0-43.el8         ol8_baseos_latest 196 k
Installing dependencies:
 annobin                      x86_64 8.90-1.el8         ol8_appstream     202 k
 binutils                     x86_64 2.30-73.0.1.el8    ol8_baseos_latest 5.8 M
 binutils-devel               x86_64 2.30-73.0.1.el8    ol8_appstream     1.1 M
 cpp                          x86_64 8.3.1-5.0.3.el8    ol8_appstream      10 M
 dtrace                       x86_64 2.0.0-1.0.el8      ol8_UEKR6         1.3 M

[...]

  qt5-srpm-macros-5.12.5-3.el8.noarch                                           
  redhat-rpm-config-122-1.0.1.el8.noarch                                        
  rust-srpm-macros-5-2.el8.noarch                                               
  tar-2:1.30-4.el8.x86_64                                                       
  unzip-6.0-43.el8.x86_64                                                       
  zip-3.0-23.el8.x86_64                                                         
  zlib-devel-1.2.11-13.el8.x86_64                                               

Complete! 

The next step is to mount the Guest Additions (virtual) CD and run the installer:

[root@ol8base ~]# mount /dev/sr0 /mnt
mount: /mnt: WARNING: device write-protected, mounted read-only.
[root@ol8base ~]# /mnt/VBoxLinuxAdditions.run 
Verifying archive integrity... All good.
Uncompressing VirtualBox 6.1.10 Guest Additions for Linux................
VirtualBox Guest Additions installer
Copying additional installer modules ...
Installing additional modules ...
VirtualBox Guest Additions: Starting.
VirtualBox Guest Additions: Building the VirtualBox Guest Additions kernel 
modules.  This may take a while.
VirtualBox Guest Additions: To build modules for other installed kernels, run
VirtualBox Guest Additions:   /sbin/rcvboxadd quicksetup 
VirtualBox Guest Additions: or
VirtualBox Guest Additions:   /sbin/rcvboxadd quicksetup all
VirtualBox Guest Additions: Building the modules for kernel 
5.4.17-2011.3.2.1.el8uek.x86_64. 

This seems to have gone well, let’s check if the necessary kernel modules are present:

[root@ol8base ~]# lsmod  | egrep -i 'Module|vbox'
Module                  Size  Used by
vboxsf                 81920  0
vboxguest             339968  2 vboxsf
vboxvideo              36864  0
drm_kms_helper        184320  2 vmwgfx,vboxvideo
ttm                   106496  2 vmwgfx,vboxvideo
drm                   516096  5 vmwgfx,drm_kms_helper,vboxvideo,ttm
[root@ol8base ~]#  

This is it! Guest additions ready and working with Oracle Linux 8.2/ UEK 6

Oracle Autonomous Linux: cron’d ksplice and yum updates

By Franck Pachot

.
Oracle Enterprise Linux (OEL) is a Linux distribution which is binary compatible with Red Hat Enterprise Linux (RHEL). However, unlike RHEL, OEL is open source, free to download, free to use, free to distribute, free to update and gets free bug fixes. And there are more frequent updates in OEL than in CentOS, the free base of RHEL. You can pay a subscription for additional support and features (like Ksplice or Dtrace) in OEL. It can run the same kernel as RHEL but also provides, still for free, the ‘unbreakable kernel’ (UEK) which is still compatible with RHEL but enhanced with optimizations, recommended especially when running Oracle products.

This is not new and I didn’t resist to illustrate the previous paragraph with the animated gif from the years of this UEK arrival. What is new is that OEL is also the base for the new Autonomous Linux which can run in the Oracle Cloud, automates Ksplice for updating the system online, without restart, and sending notifications about these updates. You can use it in the Oracle Cloud Free Tier.

When creating an Always Free compute instance you select the Oracle Autonomous Linux image. I’ve summarized all steps there:

Autonomous Linux image

https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/05/Annotat... 300w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/05/Annotat... 768w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/05/Annotat... 1536w" sizes="(max-width: 1024px) 100vw, 1024px" />

Generate an API private key


[opc@al ~]$ mkdir ~/.oci
[opc@al ~]$ openssl genrsa -out ~/.oci/oci_api_key.pem 2048 # no passphrase
[opc@al ~]$ chmod go-rwx ~/.oci/oci_api_key.pem
[opc@al ~]$ openssl rsa -pubout -in ~/.oci/oci_api_key.pem -out ~/.oci/oci_api_key_public.pem
writing RSA key

This generates an API key temporarily.

Configure OCI CLI profile


[opc@al ~]$ sudo al-config -u ocid1.user.oc1..aaaaaaaafo2liscovfefeubflxm2rswrzpnnmosue4lczmgaaodwtqdljj3q -t ocid1.tenancy.oc1..aaaaaaaazlv5wxkdhldyvxkkta7rjn25ocovfefexhidte5zgiyauut2i2gq -k ~/.oci/oci_api_key.pem
Configured OCI CLI profile.
Please delete /home/opc/.oci/oci_api_key.pem

This configures the OCI CLI profile for my user (ocid1.user.oc1.. is my user OCID which I find in Oracle Cloud » Governance and Administration » Identity » Users » Users Detail » OCID copy) in my tenancy (ocid1.tenancy.oc1.. is my tenancy OCI I find in Oracle Cloud » Governance and Administration » Administration » Tenancy Details » OCID copy).

Notifications

When something happens autonomously you want to be notified for it. This uses the OCI notification service with a topic you subscribe to. This is also available in the Free Tier. The topic is created with Oracle Cloud » Application Integration » Notifications » Create Topic, where you just put a name and a description and get the OCID for it (ocid1.onstopic.oc1.eu-frankfurt-1… for me).

While in the console, on this topic I’ve created a subscription where I have put my e-mail address. I’ll receive by e-mail all notifications sent to this topic.

Configure OCI notification service topic OCID


[opc@al ~]$ sudo al-config -T ocid1.onstopic.oc1.eu-frankfurt-1.aaaaaaaaamo7khj3xab6oec5xtcovfefeokqszapwsafeje6g6ltlnhd363a
Configured OCI notification service topic OCID.
Publishing message 'AL: Notification enabled on instance AL'
Published message 'AL: Notification enabled on instance AL'

In the Autonomous Linux instance I’ve setup the OCI notification service topic OCID. And that’s all.

Check your e-mails, you have to acknowledge the reception of notifications of course.

Kernel version


[opc@al ~]$ uname -a
Linux al 4.14.35-1902.301.1.el7uek.x86_64 #2 SMP Tue Mar 31 16:50:32 PDT 2020 x86_64 x86_64 x86_64 GNU/Linux

Here is the kernel version that has been installed


[opc@al ~]$ sudo uptrack-uname -a
Linux al 4.14.35-1902.302.2.el7uek.x86_64 #2 SMP Fri Apr 24 14:24:11 PDT 2020 x86_64 x86_64 x86_64 GNU/Linux

This is the effective kernel updated with Ksplice


[opc@al ~]$ sudo uptrack-show
Installed updates:
[cp1p7rl5] Known exploit detection.
[3kfqruxl] Known exploit detection for CVE-2017-7308.
[6vy9wlov] Known exploit detection for CVE-2018-14634.
[r8wncd28] KPTI enablement for Ksplice.
[3e9je971] Known exploit detection for CVE-2018-18445.
[20bmudk6] Out-of-bounds access when classifying network packets with traffic control index.
[oy5cke5u] NULL dereference while writing Hyper-V SINT14 MSR.
[5jsm8lzj] CVE-2020-9383: Information leak in floppy disk driver.
[5p7yd05e] NULL pointer dereference when initializing Differentiated Services marker driver.
[sajmv0xh] CVE-2018-19854: Information leak in cryptography socket NETLINK_CRYPTO call.
[1gefn4lp] CVE-2019-19965: Denial-of-service in SCSI device removal.
[6hu77eez] Invalid memory access when sending an excessively large packet using Segmentation Offloads.
[f0zxddhg] Livelock in loop device block resize operation.
[2lgm3hz9] CVE-2019-14814, CVE-2019-14815, CVE-2019-14816: Denial-of-service when parsing access point settings in Marvell WiFi-Ex driver.
[3yqxyw42] CVE-2019-20096: Memory leak while changing DCCP socket SP feature values.
[9g5kf79r] Improved fix for CVE-2020-2732: Privilege escalation in Intel KVM nested emulation.
[bq9hiiuj] Race condition in ipoib during high request load causes denial-of-service.
[3youemoz] CVE-2020-11494: Information leak in serial line CAN device communication.
[jpbi3wnm] Use-after-free when removing generic block device.
[if1ety6t] Memory corruption when reading EFI sysfs entries.
[iv8r17d8] CVE-2020-8648: Use-after-free in virtual terminal selection buffer.
[mojwd0zk] Various Spectre-V1 information leaks in KVM.
[nvi6r5wx] CVE-2019-19527: Denial-of-service in USB HID device open.
[o3df6mds] CVE-2020-8647, CVE-2020-8649: Use-after-free in the VGA text console driver.
[kjyqg48a] CVE-2019-19532: Denial-of-service when initializing HID devices.
[74j9dhee] Divide-by-zero when CPU capacity changes causes denial-of-service.
[lgsoxuy7] CVE-2019-19768: Use-after-free when reporting an IO trace.

Effective kernel version is 4.14.35-1902.302.2.el7uek

all details are there about the fixes applied by Ksplice, without any reboot.

One month later

I’ve created that on May 23th, 2020 and writing this one month later.

Here are the e-mails I’ve received from the topic subscription:
https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/06/Annotat... 300w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/06/Annotat... 768w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/06/Annotat... 1536w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/06/Annotat... 2048w" sizes="(max-width: 1024px) 100vw, 1024px" />
And my current machine state:


[opc@al ~]$ uptime
 19:26:39 up 38 days, 13:49,  2 users,  load average: 0.07, 0.02, 0.00
[opc@al ~]$ uname -a
Linux al 4.14.35-1902.301.1.el7uek.x86_64 #2 SMP Tue Mar 31 16:50:32 PDT 2020 x86_64 x86_64 x86_64 GNU/Linux
[opc@al ~]$ sudo uptrack-uname -a
Linux al 4.14.35-1902.303.4.1.el7uek.x86_64 #2 SMP Fri May 29 14:56:41 PDT 2020 x86_64 x86_64 x86_64 GNU/Linux
[opc@al ~]$

The VM has been running 24/7 without outage and the effective kernel is now higher than when installed.

Ksplice updates

This effective kernel has been updated on Tue Jun 16 08:04:33 GMT 2020 as reported by this e-mail I received:


noreply@notification.eu-frankfurt-1.oraclecloud.com
Jun 16, 2020, 10:04 AM
to AutonomousLinux

+------------------------------------------------------------------------+
|  Summary (Tue Jun 16 08:04:33 GMT 2020)                                |
+------------------------------------------------------------------------+
Ksplice updates installed: yes
Yum updates installed: no
Uptime: 08:04:33 up 24 days,  2:27,  0 users,  load average: 0.72, 0.20, 0.06
+------------------------------------------------------------------------+
|  Ksplice upgrade report                                                |
+------------------------------------------------------------------------+
Running 'ksplice -y all upgrade'.
Updating on-disk packages for new processes
Loaded plugins: langpacks
No packages marked for update
Nothing to do.
The following steps will be taken:
Install [i622mubr] Information leak in KVM_HC_CLOCK_PAIRING hypercall.
Install [35xnb9pi] CVE-2019-9500: Potential heap overflow in Broadcom FullMAC WLAN driver.
Install [ppqwl5uh] CVE-2019-15505: Out-of-bounds access in Technisat DVB-S/S2 USB2.0 driver.
Install [ctobm6wo] CVE-2019-19767: Use-after-free in with malformed ext4 filesystems.
Install [l5so0kqe] CVE-2019-19056, CVE-2019-19057: Denial-of-service in the Marvell mwifiex PCIe driver.
Install [b4iszmv7] CVE-2019-20636: Out-of-bounds write via crafted keycode table.
Install [5oec4s3n] Denial-of-service when mounting an ocfs2 filesystem.
Install [rafq9pe9] CVE-2019-9503: Denial-of-service when receiving firmware event frames over a Broadcom WLAN USB dongle.
Install [nlpu7kxi] Denial-of-service when initializing a serial CAN device.
Install [lnz9di5t] CVE-2020-11608: NULL pointer dereference when initializing USB GSPCA based webcams.
Install [2bodr9yk] CVE-2019-19537: Denial-of-service in USB character device registration.
Install [9iw2y1wn] CVE-2019-19524: Use-after-free when unregistering memoryless force-feedback driver.
Install [h5s7eh41] CVE-2020-11609: NULL pointer dereference when initializing STV06XX USB Camera device.
Install [behlqry8] Denial-of-service via invalid TSC values in KVM.
Install [onllaobw] CVE-2019-12819: Use-after-free during initialization of MDIO bus driver.
Install [fdn63bdc] CVE-2019-11599: Information leak in the coredump implementation.
Install [kb3b03z9] CVE-2019-19058: Denial-of-service in iwlwifi firmware interface.
Install [mgfi6p6r] Use-after-free when writing to SLIP serial line.
Install [hs2h9j8w] CVE-2019-14896, CVE-2019-14897: Denial-of-service when parsing BSS in Marvell 8xxx Libertas WLAN driver.
Install [bb9sd52m] CVE-2020-11668: NULL pointer dereference when initializing Xirlink C-It USB camera device.
Install [p4ygwgyj] Information leak in KVM's VMX operation path.
Install [1uxt1xo6] NFSv4 client fails to correctly renew lease when using fsinfo.
Install [hjoeh3zi] CVE-2020-0543: Side-channel information leak using SRBDS.
Installing [i622mubr] Information leak in KVM_HC_CLOCK_PAIRING hypercall.
Installing [35xnb9pi] CVE-2019-9500: Potential heap overflow in Broadcom FullMAC WLAN driver.
Installing [ppqwl5uh] CVE-2019-15505: Out-of-bounds access in Technisat DVB-S/S2 USB2.0 driver.
Installing [ctobm6wo] CVE-2019-19767: Use-after-free in with malformed ext4 filesystems.
Installing [l5so0kqe] CVE-2019-19056, CVE-2019-19057: Denial-of-service in the Marvell mwifiex PCIe driver.
Installing [b4iszmv7] CVE-2019-20636: Out-of-bounds write via crafted keycode table.
Installing [5oec4s3n] Denial-of-service when mounting an ocfs2 filesystem.
Installing [rafq9pe9] CVE-2019-9503: Denial-of-service when receiving firmware event frames over a Broadcom WLAN USB dongle.
Installing [nlpu7kxi] Denial-of-service when initializing a serial CAN device.
Installing [lnz9di5t] CVE-2020-11608: NULL pointer dereference when initializing USB GSPCA based webcams.
Installing [2bodr9yk] CVE-2019-19537: Denial-of-service in USB character device registration.
Installing [9iw2y1wn] CVE-2019-19524: Use-after-free when unregistering memoryless force-feedback driver.
Installing [h5s7eh41] CVE-2020-11609: NULL pointer dereference when initializing STV06XX USB Camera device.
Installing [behlqry8] Denial-of-service via invalid TSC values in KVM.
Installing [onllaobw] CVE-2019-12819: Use-after-free during initialization of MDIO bus driver.
Installing [fdn63bdc] CVE-2019-11599: Information leak in the coredump implementation.
Installing [kb3b03z9] CVE-2019-19058: Denial-of-service in iwlwifi firmware interface.
Installing [mgfi6p6r] Use-after-free when writing to SLIP serial line.
Installing [hs2h9j8w] CVE-2019-14896, CVE-2019-14897: Denial-of-service when parsing BSS in Marvell 8xxx Libertas WLAN driver.
Installing [bb9sd52m] CVE-2020-11668: NULL pointer dereference when initializing Xirlink C-It USB camera device.
Installing [p4ygwgyj] Information leak in KVM's VMX operation path.
Installing [1uxt1xo6] NFSv4 client fails to correctly renew lease when using fsinfo.
Installing [hjoeh3zi] CVE-2020-0543: Side-channel information leak using SRBDS.
Your kernel is fully up to date.
Effective kernel version is 4.14.35-1902.303.4.1.el7uek
+------------------------------------------------------------------------+
|  Yum upgrade report                                                    |
+------------------------------------------------------------------------+
Running 'yum-cron' with update cmd: default.
+------------------------------------------------------------------------+
|  Ksplice updates status                                                |
+------------------------------------------------------------------------+
Running 'ksplice all show'.
Ksplice user-space updates:
No Ksplice user-space updates installed

Ksplice kernel updates:
Installed updates:
[cp1p7rl5] Known exploit detection.
[3kfqruxl] Known exploit detection for CVE-2017-7308.
[6vy9wlov] Known exploit detection for CVE-2018-14634.
[r8wncd28] KPTI enablement for Ksplice.
[3e9je971] Known exploit detection for CVE-2018-18445.
[20bmudk6] Out-of-bounds access when classifying network packets with traffic control index.
[oy5cke5u] NULL dereference while writing Hyper-V SINT14 MSR.
[5jsm8lzj] CVE-2020-9383: Information leak in floppy disk driver.
[5p7yd05e] NULL pointer dereference when initializing Differentiated Services marker driver.
[sajmv0xh] CVE-2018-19854: Information leak in cryptography socket NETLINK_CRYPTO call.
[1gefn4lp] CVE-2019-19965: Denial-of-service in SCSI device removal.
[6hu77eez] Invalid memory access when sending an excessively large packet using Segmentation Offloads.
[f0zxddhg] Livelock in loop device block resize operation.
[2lgm3hz9] CVE-2019-14814, CVE-2019-14815, CVE-2019-14816: Denial-of-service when parsing access point settings in Marvell WiFi-Ex driver.
[3yqxyw42] CVE-2019-20096: Memory leak while changing DCCP socket SP feature values.
[9g5kf79r] Improved fix for CVE-2020-2732: Privilege escalation in Intel KVM nested emulation.
[bq9hiiuj] Race condition in ipoib during high request load causes denial-of-service.
[3youemoz] CVE-2020-11494: Information leak in serial line CAN device communication.
[jpbi3wnm] Use-after-free when removing generic block device.
[if1ety6t] Memory corruption when reading EFI sysfs entries.
[iv8r17d8] CVE-2020-8648: Use-after-free in virtual terminal selection buffer.
[mojwd0zk] Various Spectre-V1 information leaks in KVM.
[nvi6r5wx] CVE-2019-19527: Denial-of-service in USB HID device open.
[o3df6mds] CVE-2020-8647, CVE-2020-8649: Use-after-free in the VGA text console driver.
[kjyqg48a] CVE-2019-19532: Denial-of-service when initializing HID devices.
[74j9dhee] Divide-by-zero when CPU capacity changes causes denial-of-service.
[lgsoxuy7] CVE-2019-19768: Use-after-free when reporting an IO trace.
[i622mubr] Information leak in KVM_HC_CLOCK_PAIRING hypercall.
[35xnb9pi] CVE-2019-9500: Potential heap overflow in Broadcom FullMAC WLAN driver.
[ppqwl5uh] CVE-2019-15505: Out-of-bounds access in Technisat DVB-S/S2 USB2.0 driver.
[ctobm6wo] CVE-2019-19767: Use-after-free in with malformed ext4 filesystems.
[l5so0kqe] CVE-2019-19056, CVE-2019-19057: Denial-of-service in the Marvell mwifiex PCIe driver.
[b4iszmv7] CVE-2019-20636: Out-of-bounds write via crafted keycode table.
[5oec4s3n] Denial-of-service when mounting an ocfs2 filesystem.
[rafq9pe9] CVE-2019-9503: Denial-of-service when receiving firmware event frames over a Broadcom WLAN USB dongle.
[nlpu7kxi] Denial-of-service when initializing a serial CAN device.
[lnz9di5t] CVE-2020-11608: NULL pointer dereference when initializing USB GSPCA based webcams.
[2bodr9yk] CVE-2019-19537: Denial-of-service in USB character device registration.
[9iw2y1wn] CVE-2019-19524: Use-after-free when unregistering memoryless force-feedback driver.
[h5s7eh41] CVE-2020-11609: NULL pointer dereference when initializing STV06XX USB Camera device.
[behlqry8] Denial-of-service via invalid TSC values in KVM.
[onllaobw] CVE-2019-12819: Use-after-free during initialization of MDIO bus driver.
[fdn63bdc] CVE-2019-11599: Information leak in the coredump implementation.
[kb3b03z9] CVE-2019-19058: Denial-of-service in iwlwifi firmware interface.
[mgfi6p6r] Use-after-free when writing to SLIP serial line.
[hs2h9j8w] CVE-2019-14896, CVE-2019-14897: Denial-of-service when parsing BSS in Marvell 8xxx Libertas WLAN driver.
[bb9sd52m] CVE-2020-11668: NULL pointer dereference when initializing Xirlink C-It USB camera device.
[p4ygwgyj] Information leak in KVM's VMX operation path.
[1uxt1xo6] NFSv4 client fails to correctly renew lease when using fsinfo.
[hjoeh3zi] CVE-2020-0543: Side-channel information leak using SRBDS.

Effective kernel version is 4.14.35-1902.303.4.1.el7uek

--
You are receiving notifications as a subscriber to the topic: AL (Topic OCID: ocid1.onstopic.oc1.eu-frankfurt-1.aaaaaaaaamo7khj3xab6oec5xt5c7ia6eokqszapwsafeje6g6ltlnhd363a). To stop receiving notifications from this topic, unsubscribe.

Please do not reply directly to this email. If you have any questions or comments regarding this email, contact your account administrator.

Ksplice updates

I’ve also seen a notification about failed updates:


+------------------------------------------------------------------------+
|  Summary (Mon Jun 29 08:03:19 GMT 2020)                                |
+------------------------------------------------------------------------+
Ksplice updates installed: failed
Yum updates installed: no
Uptime: 08:03:19 up 37 days,  2:25,  0 users,  load average: 0.31, 0.08, 0.03
+------------------------------------------------------------------------+
|  Ksplice upgrade report                                                |
+------------------------------------------------------------------------+
Running 'ksplice -y all upgrade'.
Updating on-disk packages for new processes
Loaded plugins: langpacks
No packages marked for update
Nothing to do.
Unexpected error communicating with the Ksplice Uptrack server. Please
check your network connection and try again. If this error re-occurs,
e-mail ksplice-support_ww@oracle.com.

(Network error: TCP connection reset by peer)

Ok, network error at that time.
However, the next run was ok:


+------------------------------------------------------------------------+
|  Summary (Tue Jun 30 08:03:13 GMT 2020)                                |
+------------------------------------------------------------------------+
Ksplice updates installed: no
Yum updates installed: no
Uptime: 08:03:13 up 38 days,  2:25,  1 user,  load average: 0.00, 0.00, 0.00

and I can confirm by running manually:


[opc@al ~]$ ksplice -y all upgrade
Error: failed to configure the logger
[opc@al ~]$ sudo ksplice -y all upgrade
Updating on-disk packages for new processes
Loaded plugins: langpacks
ol7_x86_64_userspace_ksplice                                                                                                                     | 2.8 kB  00:00:00
No packages marked for update
100% |################################################################################################################################################################|
Nothing to do.
Nothing to be done.
Your kernel is fully up to date.
Effective kernel version is 4.14.35-1902.303.4.1.el7uek

Ksplice is about the kernel and some user space libraries such as glibc and openssl.
But Autonomous Linux also updates the packages.

Yum updates

In addition to kernel patches, the packages are also updated:


The following updates will be applied on al:
================================================================================
 Package                  Arch    Version                  Repository      Size
================================================================================
Installing:
 kernel                   x86_64  3.10.0-1127.13.1.el7     al7             50 M
Updating:
 bpftool                  x86_64  3.10.0-1127.13.1.el7     al7            8.4 M
 ca-certificates          noarch  2020.2.41-70.0.el7_8     al7            382 k
 kernel-tools             x86_64  3.10.0-1127.13.1.el7     al7            8.0 M
 kernel-tools-libs        x86_64  3.10.0-1127.13.1.el7     al7            8.0 M
 libgudev1                x86_64  219-73.0.1.el7_8.8       al7            107 k
 microcode_ctl            x86_64  2:2.1-61.10.0.1.el7_8    al7            2.7 M
 ntpdate                  x86_64  4.2.6p5-29.0.1.el7_8.2   al7             86 k
 python-perf              x86_64  3.10.0-1127.13.1.el7     al7            8.0 M
 python36-oci-cli         noarch  2.12.0-1.el7             al7            4.4 M
 python36-oci-sdk         x86_64  2.17.0-1.el7             al7             10 M
 rsyslog                  x86_64  8.24.0-52.el7_8.2        al7            620 k
 selinux-policy           noarch  3.13.1-266.0.3.el7_8.1   al7            497 k
 selinux-policy-targeted  noarch  3.13.1-266.0.3.el7_8.1   al7            7.2 M
 systemd                  x86_64  219-73.0.1.el7_8.8       al7            5.1 M
 systemd-libs             x86_64  219-73.0.1.el7_8.8       al7            416 k
 systemd-python           x86_64  219-73.0.1.el7_8.8       al7            143 k
 systemd-sysv             x86_64  219-73.0.1.el7_8.8       al7             95 k
Removing:
 kernel                   x86_64  3.10.0-1127.el7          @anaconda/7.8   64 M

Transaction Summary
================================================================================
Install   1 Package
Upgrade  17 Packages
Remove    1 Package
The updates were successfully applied

All packages are maintained up-to-date without human intervention and without downtime.

Package repository

The package repository is limited:


[opc@al ~]$ yum repolist
Loaded plugins: langpacks
ol7_x86_64_userspace_ksplice/primary_db                                                                                                          | 193 kB  00:00:00
repo id                                                       repo name                                                                                           status
!al7/x86_64                                                   Autonomous Linux 7Server (x86_64)                                                                   3,392
ol7_x86_64_userspace_ksplice                                  Ksplice aware userspace packages for Oracle Linux 7Server (x86_64)                                    438
repolist: 3,830
[opc@al ~]$ yum list all | wc -l
1462

1462 packages in one repo.
As a comparison, here is an Oracle Enterprise Linux image:


[opc@ol ~]$ yum repolist
Loaded plugins: langpacks, ulninfo
Repodata is over 2 weeks old. Install yum-cron? Or run: yum makecache fast
repo id                                                  repo name                                                                                                status
!ol7_UEKR5/x86_64                                        Latest Unbreakable Enterprise Kernel Release 5 for Oracle Linux 7Server (x86_64)                            200
!ol7_addons/x86_64                                       Oracle Linux 7Server Add ons (x86_64)                                                                       421
!ol7_developer/x86_64                                    Oracle Linux 7Server Development Packages (x86_64)                                                        1,319
!ol7_developer_EPEL/x86_64                               Oracle Linux 7Server Development Packages (x86_64)                                                       31,78$
!ol7_ksplice                                             Ksplice for Oracle Linux 7Server (x86_64)                                                                 6,41$
!ol7_latest/x86_64                                       Oracle Linux 7Server Latest (x86_64)                                                                     18,86$
!ol7_oci_included/x86_64                                 Oracle Software for OCI users on Oracle Linux 7Server (x86_64)                                              26$
!ol7_optional_latest/x86_64                              Oracle Linux 7Server Optional Latest (x86_64)                                                            13,91$
!ol7_software_collections/x86_64                         Software Collection Library release 3.0 packages for Oracle Linux 7 (x86_64)                             14,47$
repolist: 87,645
[opc@ol ~]$ yum list all | wc -l
Repodata is over 2 weeks old. Install yum-cron? Or run: yum makecache fast
36720
[opc@ol ~]$

There is a lot more here. Remember that OEL is compatible with RHEL.

If you need more packages you can open a SR and ask to have it added to the Autonomous Linux repository. For example, I use tmux everyday, especially in a free tier VM (see https://blog.dbi-services.com/always-free-always-up-tmux-in-the-oracle-cloud-with-ksplice-updates/).

If you don’t want to ask for it, there’s the possibility to add public-yum-ol7.repo there:


[opc@al ~]$ sudo yum-config-manager --add-repo http://yum.oracle.com/public-yum-ol7.repo
Loaded plugins: langpacks
adding repo from: http://yum.oracle.com/public-yum-ol7.repo
grabbing file http://yum.oracle.com/public-yum-ol7.repo to /etc/yum.repos.d/public-yum-ol7.repo
repo saved to /etc/yum.repos.d/public-yum-ol7.repo

This added the public Oracle Enterprise Linux repository. Is it correct to do that? It depends what you want: the minimum validated by Oracle to be autonomously updated without any problem, or a little additional customization.

And then install the package you want:


[opc@al ~]$ sudo yum install -y tmux

Loaded plugins: langpacks
Resolving Dependencies
--> Running transaction check
---> Package tmux.x86_64 0:1.8-4.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

========================================================================================================================================================================
 Package                              Arch                                   Version                                   Repository                                  Size
========================================================================================================================================================================
Installing:
 tmux                                 x86_64                                 1.8-4.el7                                 ol7_latest                                 241 k

Transaction Summary
========================================================================================================================================================================
Install  1 Package

Total download size: 241 k
Installed size: 554 k
Downloading packages:
tmux-1.8-4.el7.x86_64.rpm                                                                                                                        | 241 kB  00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : tmux-1.8-4.el7.x86_64                                                                                                                                1/1
  Verifying  : tmux-1.8-4.el7.x86_64                                                                                                                                1/1

Installed:
  tmux.x86_64 0:1.8-4.el7

Now the package is installed and will be updated

Autonomous cron

Those updates are scheduled by cron but you change the schedule through the al-config bash script provided:


[opc@al ~]$ sudo al-config -s
Current daily auto update time window(24-hour): 7-11
Current daily auto update time(24-hour): 08:03

This has set a random time during the 7am to 11 am window, which is here 08:03


[opc@al ~]$ cat /etc/cron.d/al-update
# Daily cron job for AL auto updates.
# Created by al-config, do not modify this file.
# If you want to change update time, use
# 'sudo al-config -w ' to set auto update time window
3 8 * * * root /usr/sbin/al-update >/dev/null

That’s the autonomous thing here: you don’t set the crontab job. You just call the al-config with a time window and it sets the crontab for you in a random time within this window.

Let’s play with this:


[opc@al ~]$ sudo al-config -w 0-2
Configured daily auto update time window(24-hour): 0-2
Configured daily auto update time(24-hour): 01:12
Created cron job file /etc/cron.d/al-update .
[opc@al ~]$ sudo al-config -w 0-2
Configured daily auto update time window(24-hour): 0-2
Configured daily auto update time(24-hour): 01:33
Created cron job file /etc/cron.d/al-update .
[opc@al ~]$ sudo al-config -w 0-2
Configured daily auto update time window(24-hour): 0-2
Configured daily auto update time(24-hour): 00:47
Created cron job file /etc/cron.d/al-update .
[opc@al ~]$ sudo al-config -w 0-2
Configured daily auto update time window(24-hour): 0-2
Configured daily auto update time(24-hour): 00:00
Created cron job file /etc/cron.d/al-update .
[opc@al ~]$ sudo al-config -w 0-2
Configured daily auto update time window(24-hour): 0-2
Configured daily auto update time(24-hour): 00:41
Created cron job file /etc/cron.d/al-update .

You see the idea. Very simple. But simple is awesome, right?

What is this scheduled job doing autonomously every day? You see it in the notification e-mail. Basically it runs:


ksplice -y all upgrade
yum-cron
ksplice all show

and sends the output to your e-mail

This is what keeps your Autonomous Linux up-to-date: ksplice, yum, and the output sent to your e-mail through:


Received: by omta-ad1-fd1-102-eu-frankfurt-1.omtaad1.vcndpfra.oraclevcn.com (Oracle Communications Messaging Server 8.1.0.1.20200619 64bit (built Jun 19 2020)) w

This is an excerpt from the notification e-mail headers. “Oracle Communications Messaging Server” is a heritage from Sun which, according to wikipedia, has its roots in Netscape Messaging Server. All those little bricks from years of enterprise IT are nicely wired together to bring this automation known as Autonomous.

Cet article Oracle Autonomous Linux: cron’d ksplice and yum updates est apparu en premier sur Blog dbi services.

Celebrating Pride Month: Perspectives on Identity, Diversity, Communication, and Change

Throughout June, we’ve published a series of Q&As at WordPress Discover featuring members of the Automattic team. These conversations explore personal journeys; reflections on identity; and diversity and inclusion in tech, design, and the workplace. Here are highlights from these interviews.


“In a World That Wants You to Apologize or Minimize Who You Are, Don’t.”

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

Gina Gowins is an HR operations magician on the Human League, our global human resources team. In this interview, Gina examines identity and language; communication and trust-building in a distributed, mostly text-based environment; and how her life experiences have informed her work.

I am particularly attached to the term queer as a repurposing of a word that was once used to isolate and disempower people — it was used to call people out as problematically different and other. From my perspective, there is no normal and no other; instead, we are all individual and unique. Identifying as queer allows me to take pride in my own individuality.

Language changes over time, and how we use language shapes our values and thinking. In a culture that is aggressively governed by heteronormative values and where it can still be dangerous and lonely to be LGBTQIA+ — such as the United States, where I live — defining myself as queer is also my small act of defiance. It is a reminder of the consistent fight for acceptance, inclusion, and justice that so many people face, and our inherent value and validity as humans.


“Reflect What Is Given, and In So Doing Change It a Little”

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

Echo Gregor is a software engineer on Jetpack’s Voyager team, working on new features that “expand Jetpack’s frontiers.” In this conversation, Echo talks about gender identity, pronouns, and names; and how xer identity and experiences have impacted xer approach to development and work in general.

Earlier in my transition, I called myself “E” sort of as a placeholder while I pondered name things. One late night, on the way home from a party, I had a friend ask if they could call me Echo, as it was the callsign equivalent for “E.” I immediately fell in love with the name, and gradually started using it more and more, until I made it my legal name.

I like that it’s simple and doesn’t have many gendered connotations in the modern world. I also appreciate it’s mythological origin! In the myth, Echo was a mountain nymph cursed by the goddess Hera — to be unable to speak, and only repeat the last words said to her.

I think there’s a lot of parallels in our world to that idea. We’re part of systems that are so much bigger than us that it’s rare any one of us can be loud enough to bring meaningful change, to speak new words. But echoes don’t perfectly repeat things. They reflect what is given, and in so doing change it a little. I like to try and live up to that by bringing a bit of change to the world, not by being the loudest, but by reflecting things back in my own way.


“Living My Life Freely and Authentically”

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

Mel Choyce-Dwan is a product designer on the theme team. In this Q&A, Mel tells us how she got involved with the WordPress community through a previous WordCamp, about her observations of tech events as a queer designer, and about the importance of inclusive design.

Show a lot of different kinds of people in your writing and your imagery, and don’t make assumptions. Talk to people from the communities you’re representing if you can, or read about their own experiences from their perspectives. Don’t assume you know better than someone else’s lived experience. When in doubt, talk to people.

And don’t just talk to people about how your product should work, talk about how it shouldn’t work. Talk about how people think others could hurt them using your product. People of marginalized identities often have stories of being harassed, stalked, or abused on the web. We need to think about how our products can be used for harm before — not after — the harassment.


“Every Person and Voice Has the Opportunity to Be Heard”

https://en-blog.files.wordpress.com/2020/06/niesha-1.jpeg?w=2048 2048w, https://en-blog.files.wordpress.com/2020/06/niesha-1.jpeg?w=150 150w, https://en-blog.files.wordpress.com/2020/06/niesha-1.jpeg?w=300 300w, https://en-blog.files.wordpress.com/2020/06/niesha-1.jpeg?w=768 768w" sizes="(max-width: 1024px) 100vw, 1024px" />

Niesha Sweet, a people experience wrangler on the Human League, says she feels like she was destined to work at Automattic. In this final interview, Niesha reflects on her Pride Month traditions and what she finds most rewarding about her HR work.

I would say that we all have to apply an additional level of empathy, understanding, and openness when working together. Just with communication alone — English is not the first language for some Automatticians, and some cultures’ communication style is direct. Assuming positive intent and having an additional level of empathy for one another allows us to effectively communicate with each other, while also appreciating our differences. 

The reward that comes with our diverse workforce is that every person and voice has the opportunity to be heard. Impostor syndrome is real, so some Automatticians may not feel as though they can share their ideas with anyone at the company, but we truly can. Our level of diversity is truly outside of what the typical company is aiming to achieve. That’s not to say we’re not looking to hire more diverse Automatticians, or increase our workforce with non-US hires, but we’re not limited by age, sexual orientation, race, and gender identity. Diversity has a different meaning in a lot of the countries where we have Automatticians, and that alone is rewarding. 


Learn more about diversity and inclusion at Automattic. We’re currently hiring — apply to work with us!

Oracle 19c Automatic Indexing: Common Index Creation Trap (Rat Trap)

When I go to a customer site to resolve performance issues, one of the most common issues I encounter is in relation to inefficient SQL. And one of the most common causes for inefficient SQL I encounter is because of deficiencies the default manner by which the index Clustering Factor is calculated. When it comes […]

Editing and Enhancing Images in the WordPress Apps

The WordPress app on your Android or iOS device is your companion wherever you go. Manage your site, write and publish, and even add images to your posts — from anywhere you are. Oftentimes, the most engaging posts include visuals, like the photos you take on the go: pictures from last week’s walk, snapshots of your afternoon picnic, or portraits of the family with your puppy.

Have you ever needed to edit your images on your phone? Maybe the lighting wasn’t quite right, or the framing and composition were off. You can now make small retouches right in the WordPress app, like cropping, rotating, and even adding a filter to change the mood of your photos.

Editing photos

You now have the option to edit an image. If your photo is already in the post, tap it, then tap the icon in the top right corner and select Edit. When you’re finished editing the image, tap Done and the previous image will be replaced with the new one.

https://en-blog.files.wordpress.com/2020/06/editing_already_published.pn... 150w, https://en-blog.files.wordpress.com/2020/06/editing_already_published.pn... 300w, https://en-blog.files.wordpress.com/2020/06/editing_already_published.pn... 768w, https://en-blog.files.wordpress.com/2020/06/editing_already_published.png 2019w" sizes="(max-width: 1024px) 100vw, 1024px" />

If you’re adding a new image, you can edit it before inserting it into the post. For example, add a Gallery Block, tap Add Media, and select Choose from your device. Select one or multiple photos, then in the bottom left corner, tap Edit. Edit your image, tap Insert, and that’s it!

https://en-blog.files.wordpress.com/2020/06/editing_multiple_android.png... 150w, https://en-blog.files.wordpress.com/2020/06/editing_multiple_android.png... 300w, https://en-blog.files.wordpress.com/2020/06/editing_multiple_android.png... 768w, https://en-blog.files.wordpress.com/2020/06/editing_multiple_android.png 2019w" sizes="(max-width: 1024px) 100vw, 1024px" />

If you’re offline, you can still add, edit, and insert new images to a post. 

Making small adjustments

Need to adjust or enhance an image? You can now rotate a photo or crop the borders:

https://en-blog.files.wordpress.com/2020/06/crop_android_and_ios.png?w=150 150w, https://en-blog.files.wordpress.com/2020/06/crop_android_and_ios.png?w=300 300w, https://en-blog.files.wordpress.com/2020/06/crop_android_and_ios.png?w=768 768w, https://en-blog.files.wordpress.com/2020/06/crop_android_and_ios.png 2019w" sizes="(max-width: 1024px) 100vw, 1024px" />

Adding a filter or drawing over an image

If you’re using the iOS app, you can apply a filter to your picture:

https://en-blog.files.wordpress.com/2020/06/filters.png?w=150 150w, https://en-blog.files.wordpress.com/2020/06/filters.png?w=300 300w, https://en-blog.files.wordpress.com/2020/06/filters.png?w=768 768w, https://en-blog.files.wordpress.com/2020/06/filters.png 2019w" sizes="(max-width: 1024px) 100vw, 1024px" />

And if you have iOS 13 or later, you can also draw over an image, either with your finger or with your Apple Pencil:

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


We’re thrilled about these new updates to the Media Editor! Let us know what you’d like to see in upcoming versions. We’d love to hear your feedback.

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.