Deprecated: Array and string offset access syntax with curly braces is deprecated in /home/u158179386/domains/dba-scripts.com/public_html/wp-content/plugins/easy-table/inc/Encoding.php on line 156

Deprecated: Array and string offset access syntax with curly braces is deprecated in /home/u158179386/domains/dba-scripts.com/public_html/wp-content/plugins/easy-table/inc/Encoding.php on line 158

Deprecated: Array and string offset access syntax with curly braces is deprecated in /home/u158179386/domains/dba-scripts.com/public_html/wp-content/plugins/easy-table/inc/Encoding.php on line 159

Deprecated: Array and string offset access syntax with curly braces is deprecated in /home/u158179386/domains/dba-scripts.com/public_html/wp-content/plugins/easy-table/inc/Encoding.php on line 160

Extended Character Data Type Columns

• In this release Oracle changed the maximum sixe of three data types

•  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:

SQL> conn / as sysdba
SQL> shutdown immediate
SQL> startup upgrade
SQL> alter system set max_string_size=extended;
SQL> @?/rdbms/admin/utl32k.sql
SQL> shutdown immediate
SQL> startup

•  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 OCP 12C – SQL 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 – DataPump, SQL*Loader, External Tables Enhancements →

Online Partition operations

• Table Partitions and subpartitions can now be moved online.

SQL> ALTER TABLE TEST MOVE PARTITION TEST_2012 ONLINE;

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

SQL> ALTER TABLE TEST MOVE PARTITION TEST_2012 COMPRESS FOR QUERY UPDATE INDEXES ONLINE;

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 OCP 12C – Partitioning Enhancements →