Category: Database

Posted on

OracleDB12c New Feature: Cascaded Truncate

Oracle Database 12c, now TRUNCATE statement is enhanced with  CASCADE option. This is pretty useful, that you need not worry about truncating all “child” tables before truncating the parent. With the CASCADE option, Oracle will truncate all tables with a foreign key constraint to the table being truncated. The foreign key must be ENABLED and defined with “ON DELETE CASCADE” clause. There is no set limit to the recursive level of cascaded truncate. All child, grand child, great grandchild, etc tables will be truncated.

Oracle12c also extended the same feature to truncating a partition. Now, ALTER TABLE … TRUNCATE PARTITION can include the CASCADE option as well. Specify CASCADE to truncate the corresponding partition(s) or subpartition(s) in all reference-partitioned child tables of table being truncated.

 

 

Posted on

OracleDB12c New Feature: Store More in VARCHAR2

Starting Oracle Database version 12.1, you can have your VARCHAR2 [and NVARCHAR2, RAW] column width up to 32767 bytes or characters.

To be able to use VARCHAR2 columns with more that 4000 bytes or characters [pre-12c maximum], you will need to set the initialization parameter MAX_STRING_SIZE = EXTENDED. By default the value of this parameter is STANDARD, which allows only 4000 bytes or characters for VARCHAR2.

MAX_STRING_SIZE controls the maximum size of VARCHAR2, NVARCHAR2, and RAW data types in SQL. STANDARD means that the length limits for Oracle Database releases prior to Oracle Database 12c apply (for example, 4000 bytes for VARCHAR2 and NVARCHAR2, and 2000 bytes for RAW).

EXTENDED means that the 32767 byte limit introduced in the Oracle Database 12c release applies.The COMPATIBLE initialization parameter must be set to 12.0.0.0 or higher to set MAX_STRING_SIZE = EXTENDED.

You can change the value of MAX_STRING_SIZE from STANDARD to EXTENDED. Once changed, you cannot change the value of MAX_STRING_SIZE from EXTENDED to STANDARD.

Changing the parameter value invalidates database objects such as views and materialized views. So, be sure to compile the objects using utlrp after the change.

To change the MAX_STRING_SIZE from STANDARD to EXTENDED, you must perform the following:

  • Shutdown database or PDB
  • Start the database or PDB in UPGRADE mode
  • Change the setting of MAX_STRING_SIZE to EXTENDED
  • Run the ?/rdbms/admin/utl32k.sql script as SYSDBA.
  • Restart the database or PDB in normal mode.

Note: for RAC databases all nodes must be restarted after the change.