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.

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

[ OFFSET offset { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ]
    { ROW | ROWS } { ONLY | WITH TIES } ]

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

-- FETCH 5 ROWS SKIPPING THE FIRST 10 ROWS

SELECT employee_id, last_name
FROM employees
ORDER BY employee_id
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

EMPLOYEE_ID LAST_NAME
----------- ----------
        110 Chen
        111 Sciarra
        112 Urman
        113 Popp
        114 Raphaely

-- FETCH FIRST 5 PERCENT ONLY

SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary
FETCH FIRST 5 PERCENT ROWS ONLY;

EMPLOYEE_ID LAST_NAME             SALARY
----------- --------------------- ------
        132 Olson                   2100
        128 Markle                  2200
        136 Philtanker              2200
        127 Landry                  2400
        135 Gee                     2400
        119 Colmenares              2500


/* FETCH 5 PERCENT WITH LOWEST SALARIES
   PLUS ALL ADDITIONAL EMPLOYESS WITH THE SAME 
   SALARY THAN LAST ROW FECTCHED */

SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary
FETCH FIRST 5 PERCENT ROWS WITH TIES;

EMPLOYEE_ID LAST_NAME             SALARY
----------- --------------------- ------
        132 Olson                   2100
        128 Markle                  2200
        136 Philtanker              2200
        127 Landry                  2400
        135 Gee                     2400
        119 Colmenares              2500
        131 Marlow                  2500
        140 Patel                   2500
        144 Vargas                  2500
        182 Sullivan                2500
        191 Perkins                 2500

 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

CREATE TABLE sales_history  ...  ROW STORE COMPRESS BASIC;
CREATE TABLE sales_history  ...  ROW STORE COMPRESS 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.