OCP 12C – SQL Enhancements

Extended Character Data Type Columns

  • In this release Oracle changed the maximum sixe of three data types
Data Type Old Maximum size New Maximum size
VARCHAR2 4000 bytes 32.767 bytes
NVARCHAR2 4000 bytes 32.767 bytes
RAW 2000 bytes 32.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.

Continue reading

email

OCP 12C – DataPump, SQL*Loader, External Tables Enhancements

Oracle DataPump Enhancements

Full Transportable Export and Import of Data

  • In Oracle 12c you now have the possibility to create full transportable exports and imports. A full transportable export contains all objects and data needed to create a copy of the database.
  • To create a fully transportable export of your database you need to specify these 2 parameters in your command line:
    • FULL=Y
    • TRANSPORTABLE=ALWAYS
  • The feature in not exactly new in Oracle 12c, it is possible to use it since 11.2.0.3.
  • What can you do with a full transportable export:
    • Convert a standard databaase to a PDB inside an existing CDB
    • Upgrade a 11.2.0.3/4 to Oracle 12c
    • Move the database to a different server
  • The Full Transportable Tablespace feature can’t be used directly to transfer a database to a platform with a different endian format, you must use DBMS_FILE_TRANSFER or RMAN to convert the files to the good endian format.
  • You must specify the VERSION=12.0 parameter if the database version is less that 12.1

Continue reading

OCP 12C – Partitioning Enhancements

Partitioning

Online Partition operations

  • Table Partitions and subpartitions can now be moved online.

  • Compression options can also be added during an online partition move.

Reference Partitioning Enhancements

Truncate or Exchange Partition with Cascade option

  • With Oracle 12c, it is now possible to use the CASCADE option to cascade operations to a child-referenced table when you TRUNCATE PARTITION or EXCHANGE PARTITION.
    • A parent table ORDERS
    • A child-referenced table INVOICE containing invoces for the corresponding orders in the first table.

Continue reading