The paper below describes an automated system that generates, selects, verifies, and maintains materialized views in the Oracle RDBMS; it presents a novel technique, called the extended covering subexpression algorithm, for the automated generation of materialized views. An extensive set of experiments is described that demonstrates the feasibility and efficiency of this approach. This system has been fully implemented and will be deployed on the Oracle Autonomous Database on the Cloud.

For more information see https://dl.acm.org/doi/abs/10.14778/3415478.3415533

Would you please download and install the one-off patch for your database version and OS for bugs not included in RU yet? If you do not find a patch for your specific version and OS from MOS, please open a Service Request with details on the patch needed. In addition, please include a list of patches already applied (opatch lsinventory -detail) and any other patches you intend to apply.

Some of the Optimizer bug fixes (not all) are controlled by the “_FIX_CONTROL” setting and are DISABLED by default in 19c when installed either through one-off backport or through RUs. Please refer to the corresponding Patch Readme carefully for one-off patches and enable the fixed control to activate the fix.

Example:

SQL> alter system set “_FIX_CONTROL”=”29302565:1”;

V$TRANSACTION lists the active transactions in the system.

(i) The following columns together point to a transaction. (i.e.) The combination of the following should give a unique transaction id for that database.

XIDUSN – Undo segment number
XIDSLOT – NUMBER Slot number
XIDSQN – NUMBER Sequence number

(ii) The following columns explain the number of undo blocks / undo records used per transaction.
USED_UBLK – Number of undo blocks used
USED_UREC – Number of undo records used

In the case of transaction rollback, the above columns will estimate the number of undo blocks that need to be rolled back.

The number of undo records and undo blocks (USED_UREC and USED_UBLK) decreases while the transaction rolls back. When they reach 0, the transaction disappears from v$transaction.

The following query can be used to monitor the transaction rollback.
SELECT A.SID, A.USERNAME, B.XIDUSN, B.USED_UREC, B.USED_UBLK
FROM V$SESSION A, V$TRANSACTION B
WHERE A.SADDR=B.SES_ADDR;

(iii) The STATUS following column explains the status of a transaction.

ACTIVE – Explains the transaction is active.

Before performing a normal/transactional shutdown, YOU can check this view to understand any ACTIVE transactions.
SELECT XIDUSN, XIDSLT, XIDSEQ , SES_ADDR, STATUS FROM V$TRANSACTION;

 

You can import database objects that are enabled for the IM column store using the TRANSFORM=INMEMORY:y option of the ‘impdp’ command. With this option, Data Pump keeps the IM column store clause for all objects that have one. When the TRANSFORM=INMEMORY:n option is specified, Data Pump drops the IM column store clause from all objects that have one.

You can also use the TRANSFORM=INMEMORY_CLAUSE:string option to override the IM column store clause for a database object in the dump file during import. For example, you can use this option to change the IM column store compression for a database object being imported.

Example:

transform=inmemory_clause:\”INMEMORY MEMCOMPRESS FOR DML PRIORITY CRITICAL\”

Note the \ characters are necessary to “escape” the double quotes so they are not stripped by the OS.

 

Reference:

Oracle Database In-Memory Option (DBIM) Basics and Interaction with Data Warehousing Features (Doc ID 1903683.1)

After applying DB RU 19.12.0.0.210720 or DB RUR 19.11.1.0.210720, you may notice that the Block Change Tracking (BCT) file is getting created of a size that is not in line with the database size.
Oracle recommends that you apply interim one-off Patch 33185773 to correct this problem(s) in the RU/RURs indicated above.
Note the fix for this issue has been included in the Oct2021 quarterly RU/RURs.

Oracle CBO performs transitivity as the first step in optimization. Transitivity means generating additional predicates based on existing predicates. The main advantage is the inclusion of extra access methods. Transitivity is not done for join predicates.

Assume A, B, and C are columns. If A=5 and A=B, you can conclude that B=5. This can be applied to queries as shown on the following page. Note that A=5 contains a constant.
However, in terms of joins, if A=B and B=C, it is not concluded that A=C; in other words, the Oracle server does not generate transitive closures for join predicates.
For more details about transitivity, see Doc ID 68979.1

 

For all offerings using Oracle Database 19c or later, if you are not licensed for Oracle Multitenant, then you may have up to 3 user-created PDBs in a given container database at any time. For all offerings using Oracle Database 12.1 through 18c, if you are not licensed for Oracle Multitenant, then the container database architecture is available in single-tenant mode, that is, with one user-created PDB, one user-created application root, and one user-created proxy PDB.

EE: Extra cost option; if you are licensed for Oracle Multitenant, then you can create up to 252 PDBs.

ODA and Exa: Extra cost option; if you are licensed for Oracle Multitenant, then you can create up to 4096 PDBs.

ExaCS/CC, DBCS EE-HP, and DBCS EE-EP: Included option; you can create up to 4096 PDBs.

Starting with the Oracle Database 21c release, an application can change its database passwords without an administrator having to schedule downtime. To accomplish this, a database administrator can associate a profile having a non-zero limit for the PASSWORD_ROLLOVER_TIME password profile parameter, new with this release, with an application schema. This allows the database password of the application used to be altered while allowing the older password to remain valid for the time specified by the PASSWORD_ROLLOVER_TIME limit. During the rollover period of time, the application instance can use either the old password or the new password to connect to the database server. When the rollover time expires, only the new password is allowed. It is an Oracle 21c feature backported to Oracle 19.12 RU.

Check out:

https://docs.oracle.com/en/database/oracle/oracle-database/21/dbseg/configuring-authentication.html#GUID-ACBA8DAE-C5B4-4811-A31D-53B97C50249B

DBMS_LOB.APPEND RAISES ORA-22275 WHEN CLOB RETURNED BY XMLSERIALIZE() GETS APPENDED TO ANOTHER CLOB
REDISCOVERY INFORMATION:

Error signaling Function: koklnflushint

The fix for 32008819 is first included in 19.11.0.0.DBRU:210420 (APR 2021) DB Release Update(DB RU)