Category Archives: Articles

This pages contains all the articles of dba-scripts.com

OCP 12C – Resource Manager and Performance Enhancements

Use Resource Manager for a CDB and a PDB

Managing Resources between PDBs

  • The Resource Manager uses Shares ans Utilization limit to manage resources allocated to PDBs.
  • The more “Shares” you allocate to a PDB, the more resource it will have.
  • Shares are allocated through DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE.
  • One directive can only concern one PDB and you can’t have multiple directive for the same PDB in the same plan.

Resource Manager

  • You can limit resource utilization of a specific PDB by using the UTILIZATION_LIMIT of the CREATE_CDB_PLAN_DIRECTIVE procedure. UTILIZATION_LIMIT is expressed in percentage of total system resources, if you set it to 50 it, the PDB will be able to use 50% of total system resources (CPU, I/O, parallel server).
  • The PARALLEL_SERVER_LIMIT parameter let you limit the parallel server utilization for a PDB.
  • If you don’t define a plan for a PDB, the default one applies, by default a PDB is being allocated :
    • 1 Share
    • No Utilization Limit
    • No Parallel Server Utilization limit

Continue reading OCP 12C – Resource Manager and Performance Enhancements

OCP 12C – SQL Enhancements

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

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 – DataPump, SQL*Loader, External Tables Enhancements