Oracle 12.2 New Features: Scheduler, Data Pump, and More

Hi,

I’ve written a lot of articles on Oracle12 new features.  This article will conclude the series with a list of miscellaneous new features for the various other tools like data pump, the scheduler, and SQL*Loader.

Other Dan Hotka Oracle12.2 New Features blogs and articles include (but not limited to):

• Partitioning New Features
• Longer Object Names
• In_Memory
• Conditional Compilation (Click Here for part 1, Click Here for part 2)
• External Tables (Click Here to review)
• New Syntax (Click Here to review)

A simple feature, but I feel one of the most important new features in Oracle12, is an additional PL/SQL security feature that should help limit SQL Injection (Click Here to review my blog on SQL Injection).

Scheduler New Features

One of the new features is, you can list jobs in the scheduler definition that are not to run at the same time.

This feature is useful for groups of jobs that depend upon other jobs to be complete before they can start.

Using this feature, the original job will complete before the scheduler will start the dependent job or jobs.

You can already define JOB_LEVEL, GROUP_LEVEL, and PROGRAM_LEVEL within the scheduler; these are not Oracle12.2 new features.  The new feature here is the ability to list jobs that cannot be executed at the same time.

BEGIN
dbms_scheduler.create_incompatibility(
  incompatibility_name => 'icompat1', 
  object_name => 'job1,job2,job3',
  enabled => true );
END;
/

Syntax example is from the Oracle documentation. (Click here to review the entire example).

In this example, job1, job2, and job3 are deemed to be incompatible.  Job2 will not be started until job1 is done and job3 will not be started until job2 is complete.

In Oracle12.2, you can also define how many resources are required by a specific job.  A resource be either a name or a count.  At execution time, the scheduler will ensure that the job will not exceed the defined resources.

Basically, this feature allows you to define specific resources for specific jobs when the default resources are not adequate to run the associated job.

BEGIN
   DBMS_SCHEDULER.CREATE_RESOURCE(
      resource_name => 'my_resource',
      units         => 3,
      state         => 'ENFORCE_CONSTRAINTS',
      comments      => 'Resource1'
   )
END;
/

Syntax example is from the Oracle documentation. (Click here to review entire example).

This example syntax allows you to show dependencies between jobs so the scheduler will not run a job that is dependent upon a previous job until that previous job is done.

The final Scheduler new feature allows for ‘in-memory jobs’.  These jobs have a minimum or no data written to disk, working mostly or entirely with in-memory objects.  There are two types of these jobs: in-memory full jobs and in-memory runtime jobs.  The in-memory runtime jobs have some metadata that is recorded to disk, the in-memory full jobs write nothing to disk.  These jobs are intended to run just one time.

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name => 'my_repeat_job',
    program_name => 'repeat_prog',
    start_date => systimestamp,
    repeat_interval => 'freq=secondly;interval=10',
    job_style => '

IN_MEMORY_RUNTIME

',
    enabled => true);
END;
/

Syntax example is from the Oracle documentation. (Click here to review entire example).

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name => 'my_immediate_job',
    program_name => 'fast_op',
    job_style => '

',
    enabled => true);
END;
/

Syntax example is from the Oracle documentation. (Click here to review entire example).

Data Pump/SQL*Loader New Features

Data Pump now has a PARALLEL option for both export and import.

Data Pump now supports wild card variables for data file names.  This feature works in conjunction with the PARALLEL feature so that higher degrees of parallelism can be achieved without having to specify individual file names.

The substitution variables include:

The Oracle documentation has working examples of each of the above options.  Click Here to review the Oracle documentation on this topic.

There are two new parallel features for Data Pump.  ENABLE_PARALLEL_PARTITION_LOAD allows Data Pump to load data into partitions in parallel mode.  This feature is intended for data migration when the metadata is static, when the databases can be moved before they are taken offline.

GROUP_PARTITION_TABLE_DATA is part of the DATA_OPTIONS parameter for Data Pump’s export utility.  This feature specifies to unload all the partitioned table data in a single operation rather than one operation per partition.  For import, this feature allows for all the data to be loaded in one process and also in parallel mode.

Click Here to review these features in the Oracle documentation.

Data Pump and SQL*Loader now support the Hadoop file system.  This feature uses the HADOOP_TRAILERS access parameter.

Data Pump now supports moving of LONG data types.

SQL*Loader has a feature that now allows for a file specification to be added to the names of LOBFILES and secondary data files used in the loading process.  SDF_PREFIX will help when loading files when you don’t want to use the relative path but a pre-defined path for the files.

sqlldr control=picts.ctl log=picts.log sdf_prefix=lobdir/

This example is from the Oracle documentation (Click Here to review this feature in the Oracle documentation.

SQL*Loader Express Mode also supports hexadecimal and multi-byte strings without having to setup a control file.  Click Here to review the syntax of this feature in the Oracle Documentation.

Data Migration (DB2) New Features

DB2 can write its LOB information to a LLS (LOB Locator Specifier).  This file contains the LOB file name, useful information such as the length of the data, and other items.  SQL*Loader now supports LLS fields coming in from DB2 migrated table data.

LOAD DATA
INFILE *
TRUNCATE
INTO TABLE tklglls
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(col1 , col2 NULLIF col1 = '1' LLS)
BEGINDATA
1,"tklglls1.dat.1.11/"

Excerpt from Oracle Documentation (Click Here to review).

The column must be defined as a LOB type for this LLS feature to work.

Summary

I hope you find this information useful as you move your applications to Oracle12.2.

Click here to see Oracle Documentation on Oracle12.2 New Features.

Dan Hotka

Author/Instructor/Oracle Expert

www.DanHotka.com

Dan@DanHotka.com