OCP 12C – SQL Enhancements

Cyrille Modiano

OCP Oracle DBA in Montreal at Desjardins

Latest posts by Cyrille Modiano (see all)

Extended Character Data Type Columns

  • In this release Oracle changed the maximum sixe of three data types
Data TypeOld Maximum sizeNew Maximum size
VARCHAR24000 bytes32.767 bytes
NVARCHAR24000 bytes32.767 bytes
RAW2000 bytes32.767 bytes
  •  In Oracle 12c if you set a VARCHAR2 to 4000 bytes or less it is stored inline, if you set it to more than 4000 bytes then it is transformed in extended character data type and stored out of line.
  • The new extended character data types are not enabled by default, you have to enable them explicitly using the following procedure:

  •  Be careful with the MAX_STRING_SIZE parameter, once changed from STANDARD to EXTENDED, you can’t go back to standard, it is irreversible.
  • Oracle recommends not to increase the size of existing varchar2 from their current size to 32,767 unless you have to, because it can cause row chaining. To modify a column it is recommended de recreate the table.
  • If you extend a column size, you’ll need to recreate the index too because it doesn’t support data type extensions.

Using the Database Migration Assistant for Unicode

  • DMU is a new GUI tool to perform CharacterSet change. You can now use this tool instead of the CCSSCAN and CSALTER in previous releases.
  • This tool provides error handling and recovery capabilities in case of failure.

SecureFiles as Default Storage For Large Objects

  • The SECUREFILES LOB becomes default in Oracle 12c, the parameter DB_SECUREFILE is set to PREFERRED if the COMPATIBLE parameter is at least
  • You can still create BASICFILES if you manually specify BASICFILE in the LOB storage clause.

SQL Row-Limiting Clause

  • In previous releases you had to use the ROWNUM pseudo column to filter the query results when you needed the TOP-N rows, oracle 12c has introduced the new ROW_LIMITING clause to the query:

  •  The OFFSET keyword let you skip the first number of rows indicated.

 Advanced Row Compression

  •  The Advances Row Compression feature is part of the Oracle Advanced Compression option.
  • In Oracle 12c you can choose between two types of compressions ROW STORE COMPRESS BASIC or ADVANCED

  • When you use basic table compression, warehouse compression, or archive compression, compression only occurs when data is bulk loaded into a table.
  • When you use advanced row compression, compression occurs while data is being inserted, updated, or bulk loaded into a table.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.