Did You Know #36 – List All Active Roles

Uncategorized
| May 13, 2020

When granting roles to users we need to remember two things:

  • Any child role of the granted role is granted to the user as well (and this is hierarchical)
  • You can turn on and off roles in your session using the SET ROLE command

So now comes the question, how can I see ALL roles that are active in my session (only active ones and including all roles granted to me hierarchically)?

First, let’s create (more...)

Oracle 20c DBCA

Uncategorized
| May 7, 2020

As you probably know, Oracle 20c is available only as a preview on the cloud. If you’d like to create a new server on Oracle Cloud with 20c, here is a post with the instructions.

However, I thought of a way to still check the new dbca and it’s quite simple. Once you have a running server, it seems that the DB VM already contains tigervnc. So all you need to do is to start (more...)

Did You Know #35 – New Child Cursor Creation Reason

Uncategorized
| Apr 30, 2020

In many cases we see a new child cursor for existing SQL. You can see the children and their information in V$SQL (where CHILD_NUMBER identifies the child). To see the child execution plan, use the DBMS_XPLAN package:

SELECT * FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR('<SQL_ID>',<CHILD_NUMBER>));

However, sometimes we want to know why Oracle has created the new child. This information is available in the V$SQL_SHARED_CURSOR view. You will see there the SQL_ID, the CHILD_NUMBER (as well as other (more...)

Did You Know #34 – DST Patches with PDB

Uncategorized
| Apr 21, 2020

When I worked on Oracle Cloud 20c for my Data Dictionary Changes post, I ran into a problem with DST difference. Oracle 20c (20.2) is coming with DST V34 while Oracle 19c has V32 so I got ORA-39405 when I tried to import a table with data from 20c to 19c (see Mike’s post about this).

Luckily, sometime after Mike published his blog Oracle released a DST V34 patch for 19c, so I (more...)

Creating a Public Accessible 20c Database VM on Oracle Cloud

Uncategorized
| Apr 16, 2020

Lately I started playing with Oracle 20c and the cloud. I had a few issues with configuring the DB VM and accessing it from home, so here is a short guide.

The steps described in the post are:

  1. Create a Virtual Cloud Network (VCN)
  2. Create a subnet for your VCN
  3. Create a Gateway for the VCN
  4. Create a Route Table for the VCN
  5. Create the VM
  6. Access the VM from a public network

This guide (more...)

19.6 vs. 20.2 Data Dictionary Changes

Uncategorized
| Apr 14, 2020

In this post I’ll show what’s new in 20.2 Data Dictionary. To see comparison between other versions, check this post.

Interesting additions are:

  • Blockchain tables
  • SQL macros
  • Some details related to sharding

19.6 New DBA Views

DBA_ANALYTIC_VIEW_AGGR_DIMS
DBA_ANALYTIC_VIEW_AGGR_FNS
DBA_ANALYTIC_VIEW_FACT_COLS
DBA_AUTOSQLSET_ATTRNAME
DBA_AUTOSQLSET_OPTENV
DBA_AUTOSQLSET_SNAPSHOT
DBA_AUTOSQLSET_SNAPSHOT_ERROR
DBA_AUTOSQLSET_SQLPLAN
DBA_AUTOSQLSET_SQLSTAT
DBA_AUTOSQLSET_SQLTEXT
DBA_AUTOTASK_SETTINGS
DBA_AW_OBJ
DBA_AW_PROP
DBA_BLOCKCHAIN_TABLES
DBA_CERTIFICATES
DBA_CONSENSUS_CONFIG_CHANGES
DBA_CONSENSUS_LOGS
DBA_CONSENSUS_PEERS
DBA_DV_STATUS
DBA_JAVA_ARGUMENTS
DBA_JAVA_CLASSES
DBA_JAVA_COMPILER_OPTIONS
DBA_JAVA_DERIVATIONS
DBA_JAVA_FIELDS
DBA_JAVA_IMPLEMENTS
DBA_JAVA_INNERS
DBA_JAVA_LAYOUTS
DBA_JAVA_METHODS
DBA_JAVA_NCOMPS
DBA_JAVA_POLICY
DBA_JAVA_RESOLVERS
DBA_JAVA_THROWS
DBA_LOGSTDBY_SUPPORT_MODE
DBA_OGG_AUTO_CAPTURED_TABLES
DBA_QUEUE_SHARDS
DBA_ROLLING_SUPPORT_MODE
DBA_SUBSCR_DUR_REGISTRATIONS
DBA_TAB_COL_STAT_MODELS
DBA_WORKLOAD_REPLAY_IFSLA
DBA_ZONEMAP_AUTO_ACTIONS
DBA_ZONEMAP_AUTO_FINDINGS

19.6 New V$ Views

V_$AQ_DEQUEUE_SESSIONS
V_$AUTHPOOL_STATS
V_$COMPATIBILITY_REQUIREMENT
V_$CONSENSUS_CLIENTS
V_$CONSENSUS_LOGS
V_$CONTAINER_TOPOLOGY
V_$CON_WAITCLASSMETRIC
V_$CON_WAITCLASSMETRIC_HISTORY
V_$DG_BROKER_PROPERTY
V_$DG_BROKER_PROPERTY_INT
V_$DIAG_SESS_USER_TRACE_RECORDS
V_$DIAG_USER_TRACE_RECORDS
V_$FOREIGN_DATAFILE_COPY
V_$GCS_LOCK_STATE_RESOLUTION_HISTORY
V_$LCR_CACHE
V_$LREG_UPDSTAT
V_$MDX_CURSORS
V_$MDX_CURSOR_DEPENDENCY
V_$PMEM_FILESTORE
V_$QUERY_BLOCK_ORIGIN
V_$RESULT_SUBCACHE_STATISTICS
V_$SECUREFILE_SHRINK

19.6 New Dictionary Column

DBA_ADVISOR_OBJECTS.ATTR22
DBA_ADVISOR_OBJECTS.ATTR21
DBA_ANALYTIC_VIEWS.IS_REMOTE
DBA_ANALYTIC_VIEWS.DEFAULT_AGGR_GROUP_NAME
DBA_ANALYTIC_VIEWS.QUERY_TRANSFORM_RELY
DBA_ANALYTIC_VIEWS.QUERY_TRANSFORM_ENABLED
DBA_ANALYTIC_VIEW_BASE_MEAS.AGGR_GROUP_NAME
DBA_ANALYTIC_VIEW_LVLGRPS.MAT_TABLE_NAME
DBA_ANALYTIC_VIEW_LVLGRPS.MAT_TABLE_OWNER
DBA_APP_VERSIONS.APP_ROOT_CLONE_NAME
DBA_ATTRIBUTE_DIMENSIONS.CACHE_STAR
DBA_ATTRIBUTE_DIMENSIONS.MAT_TABLE_OWNER
DBA_ATTRIBUTE_DIMENSIONS.MAT_TABLE_NAME
DBA_ATTRIBUTE_DIM_TABLES.IS_REMOTE
DBA_AUTOTASK_SCHEDULE_CONTROL.INSTANCE_NUMBER
DBA_AUTOTASK_SCHEDULE_CONTROL.LAST_EXEC_INSTNUM
DBA_DB_LINKS.CREDENTIAL_NAME
DBA_DB_LINKS.CREDENTIAL_OWNER
DBA_GG_AUTO_CDR_TABLES.ROW_RESOLUTION_METHOD
DBA_GG_AUTO_CDR_TABLES.KEY_VERSION_COLUMN
DBA_GG_AUTO_CDR_TABLES.IGNORE_SITE_PRIORITY
DBA_GG_AUTO_CDR_TABLES.DELETE_ALWAYS_WINS
DBA_GOLDENGATE_SUPPORT_MODE.EXPLANATION
DBA_HIST_INST_CACHE_TRANSFER.CR_RDMA
DBA_HIST_INST_CACHE_TRANSFER.CURRENT_RDMA_TIME
DBA_HIST_INST_CACHE_TRANSFER.CURRENT_RDMA
DBA_HIST_INST_CACHE_TRANSFER.CR_RDMA_TIME
DBA_INVALID_OBJECTS.IMPORTED_OBJECT
DBA_JSON_COLSTORAGE_STATS.JSON
DBA_LOBS.VALUE_BASED
DBA_LOBS.MAX_INLINE
DBA_LOB_PARTITIONS.MAX_INLINE
DBA_LOB_SUBPARTITIONS.MAX_INLINE
DBA_LOGSTDBY_UNSUPPORTED_TABLE.EXPLANATION
DBA_OBJECTS.IMPORTED_OBJECT
DBA_OBJECTS_AE.IMPORTED_OBJECT
DBA_PDBS.LAST_REFRESH_TIME
DBA_PROCEDURES.SQL_MACRO
DBA_PROCEDURES.BLOCKCHAIN_MANDATORY_VOTES
DBA_PROCEDURES.BLOCKCHAIN
DBA_TABLES.EXTERNALLY_DUPLICATED
DBA_TABLES.EXTERNALLY_SHARDED
DBA_USERS.EXTERNAL_SHARD
DBA_WORKLOAD_CAPTURES.STORAGE_TYPE
DBA_WORKLOAD_CAPTURES.PATCH_NAME
DBA_WORKLOAD_CAPTURES.ENCRYPTION_VERIFIER
DBA_WORKLOAD_DIV_SUMMARY.INSTANCE_NUMBER
DBA_WORKLOAD_DIV_SUMMARY.WRC_ID
DBA_WORKLOAD_DIV_SUMMARY.IS_CLIENT_FAILURE
DBA_WORKLOAD_REPLAYS.PATCH_NAME
DBA_WORKLOAD_REPLAYS.PDB_LEVEL
DBA_WORKLOAD_REPLAYS.NUM_CLIENTS_ABORTED
DBA_WORKLOAD_REPLAY_DIVERGENCE.INSTANCE_NUMBER
DBA_WORKLOAD_REPLAY_DIVERGENCE.WRC_ID
DBA_WORKLOAD_REPLAY_DIVERGENCE.IS_CLIENT_FAILURE
DBA_WORKLOAD_SCHEDULE_CAPTURES.NUM_CLIENTS_ABORTED
DBA_XSTREAM_OUT_SUPPORT_MODE.EXPLANATION
DBA_XTERNAL_TAB_PARTITIONS.INMEMORY_COMPRESSION
DBA_XTERNAL_TAB_PARTITIONS.INMEMORY
DBA_XTERNAL_TAB_PARTITIONS.PROPERTY
DBA_ZONEMAPS.AUTOMATIC
DBA_ZONEMAPS.INCOMPLETE
DBA_ZONEMAPS.PARTLY_STALE
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_11_ID
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_21_TYPE
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_16_ID
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_18_VALUE
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_25_ID
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_20_ID
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_20_TYPE
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_23_TYPE
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_27_ID
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_18_TYPE
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_29_TYPE
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_21_VALUE
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_26_ID
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_29_ID
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_26_TYPE
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_17_VALUE
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_25_VALUE
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_24_ID
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_19_TYPE
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_22_ID
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_28_ID
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_12_VALUE
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_27_TYPE
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_13_TYPE
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_20_VALUE
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_13_ID
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_22_TYPE
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_11_VALUE
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_16_TYPE
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_28_VALUE
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_22_VALUE
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_11_TYPE
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_16_VALUE
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_17_TYPE
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_24_TYPE
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_17_ID
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_30_TYPE
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_23_ID
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_21_ID
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_19_ID
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_15_VALUE
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_25_TYPE
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_28_TYPE
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_14_VALUE
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_13_VALUE
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_24_VALUE
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_26_VALUE
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_15_TYPE
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_19_VALUE
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_30_VALUE
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_30_ID
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_14_ID
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_14_TYPE
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_18_ID
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_12_TYPE
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_23_VALUE
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_12_ID
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_15_ID
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_27_VALUE
V_$ALL_SQL_PLAN_MONITOR.OTHERSTAT_29_VALUE
V_$AQ_MESSAGE_CACHE_STAT.NUM_PRERELOADED
V_$AQ_MESSAGE_CACHE_STAT.AVG_RELOAD_TIME
V_$AQ_MESSAGE_CACHE_STAT.NUM_FG_RELOADED
V_$AQ_SHARDED_SUBSCRIBER_STAT.MSG_SN
V_$AQ_SHARDED_SUBSCRIBER_STAT.DEQUEUE_POSITION
V_$ASM_AUDIT_CLEANUP_JOBS.USE_TIMESTAMP
V_$BACKUP_REDOLOG.SECTION_SIZE
V_$CHANNEL_WAITS.ADDR
V_$CON_SYSTEM_EVENT.CPU
V_$CON_SYSTEM_EVENT.CPU_FG
V_$CON_SYSTEM_WAIT_CLASS.TIME_WAITED_MICRO_FG
V_$CON_SYSTEM_WAIT_CLASS.CPU_FG
V_$CON_SYSTEM_WAIT_CLASS.CPU
V_$CON_SYSTEM_WAIT_CLASS.TIME_WAITED_MICRO
V_$DATABASE.GOLDENGATE_BLOCKING_MODE
V_$FS_FAILOVER_OBSERVERS.LOG_FILE
V_$FS_FAILOVER_OBSERVERS.STATE_FILE
V_$HANG_INFO.VICTIM_PDB_ID
V_$HANG_INFO.HANG_STATUS
V_$HANG_SESSION_INFO.WAIT_TIME
V_$HANG_SESSION_INFO.PDB_ID
V_$IM_COL_CU.ULEVEL
V_$INSTANCE_CACHE_TRANSFER.CURRENT_RDMA
V_$INSTANCE_CACHE_TRANSFER.CR_RDMA_TIME
V_$INSTANCE_CACHE_TRANSFER.CURRENT_RDMA_TIME
V_$INSTANCE_CACHE_TRANSFER.CR_RDMA
V_$KEY_VECTOR.COMPRESSED_SIZE
V_$KEY_VECTOR.OFFLOADED
V_$KEY_VECTOR.EFILTER_SIZE
V_$PASSWORDFILE_INFO.IS_EXC
V_$PDBS.RECOVERY_TARGET_PDB_INCARNATION#
V_$PROCESS_MEMORY_DETAIL.SQL_ID
V_$PROCESS_MEMORY_DETAIL.MOST_RECENT
V_$PROCESS_MEMORY_DETAIL.TOTAL_MB
V_$PROCESS_MEMORY_DETAIL.TIMESTAMP
V_$PWFILE_USERS.PASSWORD_VERSIONS
V_$RESULT_CACHE_MEMORY.SUBCACHE_ID
V_$RESULT_CACHE_OBJECTS.GLOBAL
V_$RESULT_CACHE_OBJECTS.SUBCACHE_ID
V_$SERVICE_EVENT.CPU
V_$SERVICE_WAIT_CLASS.TIME_WAITED_MICRO
V_$SERVICE_WAIT_CLASS.CPU
V_$SESSION_EVENT.CPU
V_$SESSION_WAIT_CLASS.CPU
V_$SESSION_WAIT_CLASS.TIME_WAITED_MICRO
V_$SQL_PLAN_MONITOR.OTHERSTAT_26_TYPE
V_$SQL_PLAN_MONITOR.OTHERSTAT_12_VALUE
V_$SQL_PLAN_MONITOR.OTHERSTAT_22_TYPE
V_$SQL_PLAN_MONITOR.OTHERSTAT_25_TYPE
V_$SQL_PLAN_MONITOR.OTHERSTAT_18_VALUE
V_$SQL_PLAN_MONITOR.OTHERSTAT_13_VALUE
V_$SQL_PLAN_MONITOR.OTHERSTAT_14_ID
V_$SQL_PLAN_MONITOR.OTHERSTAT_24_VALUE
V_$SQL_PLAN_MONITOR.OTHERSTAT_11_ID
V_$SQL_PLAN_MONITOR.OTHERSTAT_29_TYPE
V_$SQL_PLAN_MONITOR.OTHERSTAT_30_VALUE
V_$SQL_PLAN_MONITOR.OTHERSTAT_21_VALUE
V_$SQL_PLAN_MONITOR.OTHERSTAT_27_TYPE
V_$SQL_PLAN_MONITOR.OTHERSTAT_26_VALUE
V_$SQL_PLAN_MONITOR.OTHERSTAT_11_TYPE
V_$SQL_PLAN_MONITOR.OTHERSTAT_17_VALUE
V_$SQL_PLAN_MONITOR.OTHERSTAT_28_VALUE
V_$SQL_PLAN_MONITOR.OTHERSTAT_20_ID
V_$SQL_PLAN_MONITOR.OTHERSTAT_23_TYPE
V_$SQL_PLAN_MONITOR.OTHERSTAT_14_VALUE
V_$SQL_PLAN_MONITOR.OTHERSTAT_16_TYPE
V_$SQL_PLAN_MONITOR.OTHERSTAT_21_ID
V_$SQL_PLAN_MONITOR.OTHERSTAT_22_ID
V_$SQL_PLAN_MONITOR.OTHERSTAT_28_ID
V_$SQL_PLAN_MONITOR.OTHERSTAT_18_ID
V_$SQL_PLAN_MONITOR.OTHERSTAT_20_VALUE
V_$SQL_PLAN_MONITOR.OTHERSTAT_12_ID
V_$SQL_PLAN_MONITOR.OTHERSTAT_20_TYPE
V_$SQL_PLAN_MONITOR.OTHERSTAT_14_TYPE
V_$SQL_PLAN_MONITOR.OTHERSTAT_16_ID
V_$SQL_PLAN_MONITOR.OTHERSTAT_28_TYPE
V_$SQL_PLAN_MONITOR.OTHERSTAT_25_VALUE
V_$SQL_PLAN_MONITOR.OTHERSTAT_11_VALUE
V_$SQL_PLAN_MONITOR.OTHERSTAT_21_TYPE
V_$SQL_PLAN_MONITOR.OTHERSTAT_15_ID
V_$SQL_PLAN_MONITOR.OTHERSTAT_29_VALUE
V_$SQL_PLAN_MONITOR.OTHERSTAT_24_TYPE
V_$SQL_PLAN_MONITOR.OTHERSTAT_12_TYPE
V_$SQL_PLAN_MONITOR.OTHERSTAT_27_VALUE
V_$SQL_PLAN_MONITOR.OTHERSTAT_13_ID
V_$SQL_PLAN_MONITOR.OTHERSTAT_17_TYPE
V_$SQL_PLAN_MONITOR.OTHERSTAT_19_VALUE
V_$SQL_PLAN_MONITOR.OTHERSTAT_19_ID
V_$SQL_PLAN_MONITOR.OTHERSTAT_27_ID
V_$SQL_PLAN_MONITOR.OTHERSTAT_15_TYPE
V_$SQL_PLAN_MONITOR.OTHERSTAT_23_ID
V_$SQL_PLAN_MONITOR.OTHERSTAT_26_ID
V_$SQL_PLAN_MONITOR.OTHERSTAT_25_ID
V_$SQL_PLAN_MONITOR.OTHERSTAT_23_VALUE
V_$SQL_PLAN_MONITOR.OTHERSTAT_24_ID
V_$SQL_PLAN_MONITOR.OTHERSTAT_30_TYPE
V_$SQL_PLAN_MONITOR.OTHERSTAT_17_ID
V_$SQL_PLAN_MONITOR.OTHERSTAT_30_ID
V_$SQL_PLAN_MONITOR.OTHERSTAT_15_VALUE
V_$SQL_PLAN_MONITOR.OTHERSTAT_16_VALUE
V_$SQL_PLAN_MONITOR.OTHERSTAT_13_TYPE
V_$SQL_PLAN_MONITOR.OTHERSTAT_18_TYPE
V_$SQL_PLAN_MONITOR.OTHERSTAT_22_VALUE
V_$SQL_PLAN_MONITOR.OTHERSTAT_19_TYPE
V_$SQL_PLAN_MONITOR.OTHERSTAT_29_ID
V_$SYSTEM_EVENT.CPU
V_$SYSTEM_EVENT.CPU_FG
V_$SYSTEM_WAIT_CLASS.TIME_WAITED_MICRO
V_$SYSTEM_WAIT_CLASS.CPU_FG
V_$SYSTEM_WAIT_CLASS.TIME_WAITED_MICRO_FG
V_$SYSTEM_WAIT_CLASS.CPU
V_$UNIFIED_AUDIT_TRAIL.OBJECT_TYPE
V_$UNIFIED_AUDIT_TRAIL.DB_UNIQUE_NAME
V_$UNIFIED_AUDIT_TRAIL.DP_WARNINGS1

Scratch Projects for Kids

Uncategorized
| Mar 30, 2020

As you probably know, I live in Vancouver, BC, Canada. BC for now is not under a complete lock down, but schools are closed (and it’s unclear what will happen with schools yet, we just “came back” from spring break), public areas are closed (libraries, museums, restaurants, etc.) and most workplaces sent people to work from home wherever possible. Like everyone around the globe, this is pretty new to us. Keeping the kids busy (more...)

Oracle Data Dictionary Changes

Uncategorized
| Mar 23, 2020

As Oracle releases new versions, there is a lot of information about new major features, but sometimes small changes are unnoticed. I decided to check the differences in the Oracle Data Dictionary between different versions. Sometimes Oracle add important things there, and sometimes these changes reflect a bigger change that was made to the database system.

I’ll try to start a series here about these changes, just showing what’s different and you can scan the (more...)

18.9 vs. 19.6 Data Dictionary Changes

Uncategorized
| Mar 23, 2020

In this post I’ll show what’s new in 19.6 Data Dictionary. To see comparison between other versions, check this post.

Interesting additions are:

  • Views/columns related to automatic indexing
  • Some V$ALL views (allow users to see stats and plan of their own SQLs without specific permissions)
  • V$DUAL

19.6 New DBA Views

DBA_AUTOTASK_SCHEDULE_CONTROL
DBA_AUTO_INDEX_CONFIG
DBA_AUTO_INDEX_EXECUTIONS
DBA_AUTO_INDEX_IND_ACTIONS
DBA_AUTO_INDEX_SQL_ACTIONS
DBA_AUTO_INDEX_STATISTICS
DBA_AUTO_INDEX_VERIFICATIONS
DBA_AUTO_STAT_EXECUTIONS
DBA_HIST_TABLE_SETTINGS
DBA_JSON_COLSTORAGE_STATS
DBA_JSON_TABLE_COLS_COUNT
DBA_JSON_VIEW_COLS_COUNT
DBA_LOGMNR_PROFILE_PLSQL_STATS
DBA_LOGMNR_PROFILE_TABLE_STATS
DBA_PDB_SNAPSHOTFILE
DBA_RAT_CAPTURE_SCHEMA_INFO
DBA_SQL_QUARANTINE
DBA_TRIGGERS_AE

19.6 New V$ Views

V_$ALL_ACTIVE_SESSION_HISTORY
V_$ALL_SQL_MONITOR
V_$ALL_SQL_PLAN
V_$ALL_SQL_PLAN_MONITOR
V_$AQ_PARTITION_STATS
V_$ASM_ACFSAUTORESIZE
V_$DIAG_VADR_CONTROL
V_$DUAL
V_$MY_NOLOGGING_STANDBY_TXN
V_$NOLOGGING_STANDBY_TXN
V_$SHARED_SERVER_STAT
V_$SQL_TESTCASES

19.6 New Dictionary Column

DBA_ADDM_TASKS.CDB_TYPE_ANALYZED
DBA_ADDM_TASKS.CDB_TYPE_DETECTED
DBA_ADDM_TASKS.DB_TYPE_ANALYZED
DBA_ADDM_TASKS.DB_TYPE_DETECTED
DBA_ADVISOR_ACTIONS.RESULT_LAST_MODIFIED
DBA_ADVISOR_ACTIONS.RESULT_MESSAGE
DBA_ADVISOR_ACTIONS.RESULT_STATUS
DBA_ADVISOR_OBJECTS.ADV_SQL_ID
DBA_ADVISOR_SQLPLANS.OUTROWS
DBA_ADVISOR_SQLPLANS.STARTS
DBA_ALL_TABLES.HYBRID
DBA_ALL_TABLES.LOGICAL_REPLICATION
DBA_APPLICATIONS.APP_CAPTURE_ERROR
DBA_APPLY_KEY_COLUMNS.APPLY_NAME
DBA_APP_ERRORS.SYSTEM_IGNORABLE
DBA_APP_ERRORS.USER_IGNORABLE
DBA_CONTEXT.TRACKING
DBA_DB_LINKS.INTRA_CDB
DBA_HIST_DYN_REMASTER_STATS.PERSISTENT_OBJECTS
DBA_HIST_RSRC_PDB_METRIC.MAX_AVG_RUNNING_SESSIONS
DBA_HIST_SEG_STAT.IM_MEMBYTES
DBA_IDENTIFIERS.DECLARED_OBJECT_NAME
DBA_IDENTIFIERS.DECLARED_OBJECT_TYPE
DBA_IDENTIFIERS.DECLARED_OWNER
DBA_IDENTIFIERS.IMPLICIT
DBA_INDEXES.AUTO
DBA_INDEXES.CONSTRAINT_INDEX
DBA_LOCKDOWN_PROFILES.EXCEPT_USERS
DBA_MVIEW_DETAIL_PARTITION.LAST_REFRESH_TIME
DBA_OBJECT_TABLES.HYBRID
DBA_OBJECT_TABLES.LOGICAL_REPLICATION
DBA_PDBS.CREDENTIAL_NAME
DBA_SEQUENCES.SHARDED_FLAG
DBA_SERVICES.NSPARE1
DBA_SERVICES.PLACEMENT_POLICY
DBA_SERVICES.RESET_STATE
DBA_SERVICES.TABLE_FAMILY_ID
DBA_SERVICES.VCSPARE1
DBA_SUPPLEMENTAL_LOGGING.SUBSET_REP
DBA_TABLES.ADMIT_NULL
DBA_TABLES.DATA_LINK_DML_ENABLED
DBA_TABLES.HYBRID
DBA_TABLES.LOGICAL_REPLICATION
DBA_TAB_COLS_V$.OWNER_ID
DBA_TAB_STATISTICS.NOTES
DBA_USERS.PASSWORD_CHANGE_DATE
DBA_VIEWS.ADMIT_NULL
DBA_VIEWS.PDB_LOCAL_ONLY
DBA_VIEWS_AE.ADMIT_NULL
DBA_VIEWS_AE.PDB_LOCAL_ONLY
V_$ACTIVE_SERVICES.TABLE_FAMILY_ID
V_$AQ_SUBSCRIBER_LOAD.DEQUEUE_SESSIONS
V_$ASM_ACFSSNAPSHOTS.REPL
V_$ASM_ACFSSNAPSHOTS.ADDITIONAL_STORAGE
V_$ASM_ACFSSNAPSHOTS.LINK
V_$ASM_ACFSSNAPSHOTS.QUOTA
V_$ASM_ACFSSNAPSHOTS.STATE
V_$ASM_AUDIT_CONFIG_PARAMS.PARAMETER_UNIT
V_$ASM_FILESYSTEM.METADATA_BLOCK_SIZE
V_$ASM_FILESYSTEM.FROZEN_STATE
V_$ASM_FILESYSTEM.COMPRESS_STATE
V_$ASM_FILESYSTEM.ACFS_COMPATIBILITY
V_$ASM_FILESYSTEM.RESIZE_STATE
V_$CIRCUIT.BOUND_REASON
V_$CIRCUIT.BOUND_TIME
V_$DATABASE.FS_FAILOVER_MODE
V_$DATABASE.SUPPLEMENTAL_LOG_DATA_SR
V_$DYNAMIC_REMASTER_STATS.PERSISTENT_OBJECTS
V_$GG_APPLY_RECEIVER.CURRENT_POSITION
V_$GG_APPLY_RECEIVER.OS_PROCESS_ID
V_$GG_APPLY_RECEIVER.TOTAL_COMMITS
V_$GG_APPLY_RECEIVER.TOTAL_ERRORS
V_$GG_APPLY_RECEIVER.TOTAL_TRANSACTIONS_RECEIVED
V_$IMHMSEG.N_ROWS_UPDATE
V_$IMHMSEG.N_ROWS_INSERT
V_$IMHMSEG.N_ROWS_DELETE
V_$IMHMSEG.N_SEGMENT_DELETE
V_$IMHMSEG.N_SEGMENT_UPDATE
V_$IMHMSEG.N_SEGMENT_INSERT
V_$IM_GLOBALDICT.NUM_SYMBOLS
V_$IM_GLOBALDICT.TOTAL_LENGTH
V_$IM_GLOBALDICT_PIECEMAP.HTNUM
V_$KCCDI.DIFMOD
V_$KCCFE.FERFNO
V_$RESTORE_POINT.REPLICATED
V_$RMAN_OUTPUT.GUID
V_$SERVICES.TABLE_FAMILY_ID
V_$SHARED_SERVER.BOUND_REASON
V_$SHARED_SERVER.BOUND_TIME
V_$SQL.AVOIDED_EXECUTIONS
V_$SQL.SQL_QUARANTINE
V_$SQLSTATS.DELTA_AVOIDED_EXECUTIONS
V_$SQLSTATS.AVOIDED_EXECUTIONS
V_$SQL_MONITOR.CURRENT_USERNAME
V_$SQL_MONITOR.CURRENT_USER#
V_$SUBSCR_REGISTRATION_STATS.SHARD_ID
V_$UNIFIED_AUDIT_TRAIL.PROTOCOL_RETURN_CODE
V_$UNIFIED_AUDIT_TRAIL.PROTOCOL_SESSION_ID
V_$UNIFIED_AUDIT_TRAIL.PROTOCOL_MESSAGE
V_$UNIFIED_AUDIT_TRAIL.PROTOCOL_ACTION_NAME
V_$UNIFIED_AUDIT_TRAIL.PROTOCOL_USERHOST
V_$UNIFIED_AUDIT_TRAIL.EVENT_TIMESTAMP_OLD

12.2.0.1 vs. 18.9 Data Dictionary Changes

Uncategorized
| Mar 23, 2020

In this post I’ll compare the Data Dictionary tables and columns between 12.2.0.1 and 18.9. To see comparison between other versions, check this post.

18.9 New DBA Views

DBA_CONNECTION_TESTS
DBA_GOLDENGATE_CONTAINER_RULES
DBA_HIST_ASM_DISK_STAT_SUMMARY
DBA_HIST_PROCESS_WAITTIME
DBA_INMEMORY_AIMTASKDETAILS
DBA_INMEMORY_AIMTASKS
DBA_JSON_DATAGUIDE_FIELDS
DBA_PDB_SNAPSHOTS
DBA_PRIVATE_TEMP_TABLES
DBA_REGISTRY_BACKPORTS
DBA_REGISTRY_SQLPATCH_RU_INFO
DBA_WORKLOAD_CAPTURE_SQLTEXT
DBA_WORKLOAD_DIV_SUMMARY
DBA_WORKLOAD_LONG_SQLTEXT

18.9 New V$ Views

V_$AQ_IPC_ACTIVE_MSGS
V_$AQ_IPC_MSG_STATS
V_$AQ_IPC_PENDING_MSGS
V_$ASM_AUDIT_LOAD_JOBS
V_$ASM_CACHE_EVENTS
V_$DATABASE_REPLAY_PROGRESS
V_$IMHMSEG
V_$IM_DELTA_HEADER
V_$IM_GLOBALDICT
V_$IM_GLOBALDICT_PIECEMAP
V_$IM_GLOBALDICT_SORTORDER
V_$IM_GLOBALDICT_VERSION
V_$IM_SMU_DELTA
V_$JAVA_PATCHING_STATUS
V_$JAVA_SERVICES
V_$LOCKDOWN_RULES
V_$MEMOPTIMIZE_WRITE_AREA
V_$QUARANTINE_SUMMARY
V_$SQL_SHARD
V_$WLM_PCSERVICE

18.9 New Dictionary Column

DBA_ADVISOR_EXECUTIONS.ACTUAL_DOP
DBA_ADVISOR_EXECUTIONS.CONCURRENT_EXECUTION
DBA_ADVISOR_EXECUTIONS.REQUESTED_DOP
DBA_ADVISOR_SQLSTATS.DIRECT_GETS
DBA_ADVISOR_SQLSTATS.CACHED_GETS
DBA_ADVISOR_SQLSTATS.ATTR3
DBA_ADVISOR_SQLSTATS.ATTR2
DBA_ALL_TABLES.MEMOPTIMIZE_WRITE
DBA_ALL_TABLES.HAS_SENSITIVE_COLUMN
DBA_ALL_TABLES.MEMOPTIMIZE_READ
DBA_ANALYTIC_VIEWS.DYN_ALL_CACHE
DBA_ANALYTIC_VIEW_DIMENSIONS.REFERENCES_DISTINCT
DBA_APP_PATCHES.PATCH_CHECKSUM
DBA_APP_STATEMENTS.VERSION_NUMBER
DBA_APP_STATEMENTS.OPCODE
DBA_APP_STATEMENTS.SESSION_ID
DBA_APP_VERSIONS.APP_VERSION_CHECKSUM
DBA_ARGUMENTS.TYPE_OBJECT_TYPE
DBA_CPOOL_INFO.CON_ID
DBA_DB_LINKS.VALID
DBA_DB_LINKS.SHARD_INTERNAL
DBA_EXTERNAL_TABLES.INMEMORY_COMPRESSION
DBA_EXTERNAL_TABLES.INMEMORY
DBA_HIST_ASM_DISKGROUP_STAT.NUM_FAILGROUP
DBA_HIST_ASM_DISKGROUP_STAT.STATE
DBA_HIST_CELL_DB.IS_CURRENT_SRC_DB
DBA_HIST_CELL_DISKTYPE.CONFVAL
DBA_HIST_DATABASE_INSTANCE.STARTUP_TIME_TZ
DBA_HIST_PDB_INSTANCE.SNAP_ID
DBA_HIST_PDB_INSTANCE.OPEN_TIME_TZ
DBA_HIST_PDB_INSTANCE.STARTUP_TIME_TZ
DBA_HIST_PDB_IN_SNAP.OPEN_TIME_TZ
DBA_HIST_SEG_STAT.REPOPULATE_CUS_TOTAL
DBA_HIST_SEG_STAT.REPOPULATE_CUS_DELTA
DBA_HIST_SEG_STAT.POPULATE_CUS_TOTAL
DBA_HIST_SEG_STAT.POPULATE_CUS_DELTA
DBA_HIST_SEG_STAT.IM_SCANS_DELTA
DBA_HIST_SEG_STAT.IM_DB_BLOCK_CHANGES_TOTAL
DBA_HIST_SEG_STAT.IM_DB_BLOCK_CHANGES_DELTA
DBA_HIST_SEG_STAT.GC_REMOTE_GRANTS_TOTAL
DBA_HIST_SEG_STAT.GC_REMOTE_GRANTS_DELTA
DBA_HIST_SEG_STAT.IM_SCANS_TOTAL
DBA_HIST_SNAPSHOT.END_INTERVAL_TIME_TZ
DBA_HIST_SNAPSHOT.BEGIN_INTERVAL_TIME_TZ
DBA_HIST_SNAP_ERROR.STEP_ID
DBA_HIST_SQLSTAT.OBSOLETE_COUNT
DBA_HIST_WR_CONTROL.SRC_DBNAME
DBA_HIST_WR_CONTROL.SRC_DBID
DBA_IDENTIFIERS.ATTRIBUTE
DBA_IDENTIFIERS.CHARACTER_SET
DBA_IDENTIFIERS.CHAR_USED
DBA_IDENTIFIERS.LENGTH
DBA_IDENTIFIERS.LOWER_RANGE
DBA_IDENTIFIERS.NULL_CONSTRAINT
DBA_IDENTIFIERS.PRECISION
DBA_IDENTIFIERS.PRECISION2
DBA_IDENTIFIERS.SCALE
DBA_IDENTIFIERS.SQL_BUILTIN
DBA_IDENTIFIERS.UPPER_RANGE
DBA_IND_SUBPARTITIONS.DOMIDX_OPSTATUS
DBA_JSON_COLUMNS.OBJECT_TYPE
DBA_LOCKDOWN_PROFILES.USERS
DBA_LOGMNR_SESSION.PURGE_SCN
DBA_MINING_MODELS.ALGORITHM_TYPE
DBA_OBJECT_TABLES.HAS_SENSITIVE_COLUMN
DBA_OBJECT_TABLES.MEMOPTIMIZE_WRITE
DBA_OBJECT_TABLES.MEMOPTIMIZE_READ
DBA_PDBS.LAST_REFRESH_SCN
DBA_PDBS.SNAPSHOT_INTERVAL
DBA_PDBS.SNAPSHOT_MODE
DBA_PDBS.TEMPLATE
DBA_PDBS.TENANT_ID
DBA_PLSQL_COLL_TYPES.CHAR_USED
DBA_PRIV_CAPTURES.RUN_NAME
DBA_PROCEDURES.POLYMORPHIC
DBA_QUEUE_TABLES.REPLICATION_MODE
DBA_REFRESH.JOB_NAME
DBA_REFRESH_CHILDREN.JOB_NAME
DBA_REGISTRY.VERSION_FULL
DBA_REGISTRY_HIERARCHY.VERSION_FULL
DBA_REGISTRY_HISTORY.BUNDLE_SERIES
DBA_REGISTRY_SQLPATCH.PATCH_TYPE
DBA_REGISTRY_SQLPATCH.RU_LOGFILE
DBA_REGISTRY_SQLPATCH.SOURCE_BUILD_DESCRIPTION
DBA_REGISTRY_SQLPATCH.SOURCE_BUILD_TIMESTAMP
DBA_REGISTRY_SQLPATCH.SOURCE_VERSION
DBA_REGISTRY_SQLPATCH.TARGET_BUILD_DESCRIPTION
DBA_REGISTRY_SQLPATCH.TARGET_BUILD_TIMESTAMP
DBA_REGISTRY_SQLPATCH.TARGET_VERSION
DBA_RGROUP.JOB_NAME
DBA_ROLES.EXTERNAL_NAME
DBA_RSRC_IO_CALIBRATE.ADDITIONAL_INFO
DBA_RSRC_PLAN_DIRECTIVES.PQ_TIMEOUT_ACTION
DBA_SERVER_REGISTRY.VERSION_FULL
DBA_TABLES.MEMOPTIMIZE_READ
DBA_TABLES.HAS_SENSITIVE_COLUMN
DBA_TABLES.MEMOPTIMIZE_WRITE
DBA_TAB_COLS_V$.COLUMN_INT_ID
DBA_TAB_COLS_V$.TABLE_ID
DBA_TAB_PARTITIONS.MEMOPTIMIZE_WRITE
DBA_TAB_PARTITIONS.MEMOPTIMIZE_READ
DBA_TAB_SUBPARTITIONS.MEMOPTIMIZE_WRITE
DBA_TAB_SUBPARTITIONS.MEMOPTIMIZE_READ
DBA_TYPES.PERSISTABLE
DBA_VIEWS.HAS_SENSITIVE_COLUMN
DBA_VIEWS_AE.HAS_SENSITIVE_COLUMN
DBA_WORKLOAD_CAPTURES.ENCRYPTION
DBA_WORKLOAD_REPLAYS.REPLAY_DEADLOCKS
DBA_WORKLOAD_REPLAY_THREAD.IS_SCALE_UP_SESS
V_$AQ_MESSAGE_CACHE_STAT.MIN_EVICT_PERCENT
V_$AQ_MESSAGE_CACHE_STAT.LAST_AVG_MEMORY_HORIZON
V_$AQ_MESSAGE_CACHE_STAT.LAST_AVG_CACHED_HORIZON
V_$AQ_MESSAGE_CACHE_STAT.AVG_OPT_TIME_ERROR
V_$AQ_MESSAGE_CACHE_STAT.AVG_OPTTIME_DRIFT
V_$AQ_MESSAGE_CACHE_STAT.LAST_LEEWAY_SHIFT
V_$AQ_MESSAGE_CACHE_STAT.MAX_OPT_TIME_DRIFT
V_$AQ_MESSAGE_CACHE_STAT.MAX_OPT_TIME_ERROR
V_$AQ_MESSAGE_CACHE_STAT.LAST_AVG_SUBSHARD_HORIZON
V_$AQ_MESSAGE_CACHE_STAT.NUM_THRESHOLD_DRIFT
V_$AQ_MESSAGE_CACHE_STAT.MIN_OPT_TIME_ERROR
V_$AQ_MESSAGE_CACHE_STAT.MIN_OPT_TIME_DRIFT
V_$AQ_MSGBM.ENQ_TIME
V_$AQ_MSGBM.PRIORITY
V_$AQ_MSGBM.CORRELATION
V_$AQ_MSGBM.DELIVERY_TIME
V_$AQ_MSGBM.STATE
V_$AQ_MSGBM.EXPIRATION
V_$AQ_MSGBM.LCK_BITMAP
V_$ASM_FILE.PARENT_FILNUMINC
V_$ASM_FILE.PARENT_FILNUM
V_$BGPROCESS.PRIORITY
V_$CONTAINERS.GUID_BASE64
V_$CONTAINERS.LAST_CHANGED_BY
V_$CONTAINERS.MEMBER_CDB
V_$CONTAINERS.TENANT_ID
V_$CONTAINERS.UPGRADE_LEVEL
V_$DATABASE_KEY_INFO.MASTERKEY_ACTIVATED
V_$ENCRYPTION_WALLET.KEYSTORE_MODE
V_$EVENT_OUTLIERS.IS_DEFINITE
V_$EVENT_OUTLIERS.OUTLIER_WAIT_TIME
V_$EVENT_OUTLIERS.P1
V_$EVENT_OUTLIERS.P2
V_$EVENT_OUTLIERS.WAIT_ID
V_$EVENT_OUTLIERS.PNAME
V_$EVENT_OUTLIERS.P3
V_$IM_COL_CU.DSB_WIDTH
V_$IM_COL_CU.DSB_EXPONENT
V_$IM_HEADER.TOTAL_DELTA
V_$IM_HEADER.NUM_DELTA
V_$IM_HEADER.IS_EXTERNAL
V_$IM_IMECOL_CU.DSB_EXPONENT
V_$IM_IMECOL_CU.DSB_WIDTH
V_$IM_IMECOL_CU.IMEFLAG
V_$IM_SEGMENTS.IS_EXTERNAL
V_$IM_SEGMENTS_DETAIL.XMEMMEMBYTES
V_$IM_SEGMENTS_DETAIL.IS_EXTERNAL
V_$IM_SEGMENTS_DETAIL.DRAMMEMBYTES
V_$IM_SMU_HEAD.NUMDELTA
V_$INSTANCE.VERSION_LEGACY
V_$INSTANCE.VERSION_FULL
V_$KEY_VECTOR.CREATION_DURATION
V_$KEY_VECTOR.PAYLOAD_COLUMN_COUNT
V_$PDBS.TENANT_ID
V_$PDBS.LAST_CHANGED_BY
V_$PDBS.GUID_BASE64
V_$PDBS.TEMPLATE
V_$PDBS.UPGRADE_LEVEL
V_$PROCESS.CPU_USED
V_$SQL.RESULT_CACHE
V_$VERSION.BANNER_LEGACY
V_$VERSION.BANNER_FULL