Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









Original Import Utility Parameter

Oracle RAC Cluster Tips by Burleson Consulting

This is an excerpt from the bestselling book Oracle Grid & Real Application Clusters.  To get immediate access to the code depot of working RAC scripts, buy it directly from the publisher and save more than 30%.

Some of the parameters in the original import utility are no longer needed because the new Data Pump import utility takes care of them automatically.  Those parameters are: buffer,  charset, commit, compile , filesize, recordlength, resumable , resumable_name,  resumable_timeout, statistics, toid_novalidate, tts_owners, userid, volsize.

The following examples illustrate how the import can be accomplished now:

Example 1: Import User into a Different Schema

impdp system/manager \
SCHEMAS=usr01 \
REMAP_SCHEMA=usr01:usr02 \
DUMPFILE=dpump_dir:usr01.dmp \
EXCLUDE=index, materialized_view \

Example 2: Import with Data Option

impdp system/manager \,hr.job_history \
CONTENT=data_only \
DUMPFILE= dpump_dir:table.dmp \

New Scheduler Utilities

Oracle 10g provides a new package, dbms_scheduler, which has a number of functions and procedures.  Collectively, these functions are called the Scheduler.  The Scheduler provides rich functionality to meet the needs of complex enterprise scheduling in a grid environment. It helps database administrators and application developers simplify their management tasks.

Scheduler Components

The Scheduler has the following components:

* Schedule: This specifies when and how many times a job is executed.

* Program: This is a collection of metadata about what is run by the schedule.  A program can be a PL/SQL procedure, an executable C program, a Shell script, or a java application, and so on.  A list of arguments can be specified for a program.

* Job: This specifies what program needs to execute and at what time or schedule.  A job class defines a category of jobs that share common resource usage requirements and other characteristics.

* Window: A window is represented by an interval of time with a well defined beginning and end.  It is used to activate different resource plans at different times.  A window group represents a list of windows.

Create, Enable, Disable, and Drop a Program

The dbms_scheduler.create_program procedure can be used to create a program.  As mentioned previously, a program is a collection of metadata about what is to be run by the Scheduler.  To create a program using dbms_scheduler is really just to register a program with the Scheduler.

The syntax for creating a program using the Scheduler is as follows:

 Program_name                        in varchar2,
 Program_type              in varchar2,
 Program_action                       in varchar2,
 Number_of_auguments            in pls_integer default 0,
 Enable                         in Boolean default false,
 Comments                               invarchar2 default null

The program_type parameter includes the following values:

* plsql_block

* stored_procedure

* executable 

To create a program in the user?s own schema, the CREATE JOB privilege must be assigned.  To create a program in another user?s schema, the CREATE ANY JOB privilege must be assigned.  When a program is created, it is created in a disabled state by default; a job cannot execute a program until the program is enabled.

The following is an example of creating a program to check database user sessions:

 program_name   => `CHECK_USER`,
 program_action => `/dba/scripts/`,
 program_type   => `EXECUTABLE`

This program can be enabled as follows:

execute DBMS_SCHEDULER.ENABLE (`check_user`);

This program can be disabled as follows:

execute DBMS_SCHEDULER.DISABLE (?check_user?);

In addition, this program can be dropped as follows.  If force is set to TRUE, all jobs referencing the program are disabled before dropping the program:

 program_name => ?check_user?,
 force        =>  FALSE

Create and Drop a Schedule

The create_schedule procedure can be used to create a schedule for the job by using the following syntax:

 schedule_name            in varchar2,
 start_date                    in timestamp with timezone default null,
 repeat_interval in varchar2,
 end_date                     in timestamp with timezone default null,
 comments                    in varchar2 default null

In this procedure, start_date specifies the date on which the schedule becomes active, and end_date specifies that the schedule becomes inactive after the specified date.  repeat_interval is an expression using either the calendar syntax or PL/SQL syntax, which tells how often a job should be repeated.

The repeat_interval calendaring expression has three parts.

The Frequency clause is made of the following elements








The repeat interval range is from 1 to 99.

The other Frequency clause is made of the following elements:









The following are some examples of the use of calendaring expressions:

Every March and June of the year:


Every 20th day of the month:


Every Sunday of the week:


Every 60 days:


Every 6 hours:


Every 10 minutes:


Every 30 seconds:


The following are some examples of using PL/SQL expressions:


The following steps are used to create a schedule:

 schedule_name            => `HOURLY_SCHEDULE`,
 start_date                    => `TRUNC(SYSDATE)+23/24`
 repeat_interval => `FREQ=HOURLY; INTERVAL=1`

A schedule can be dropped by performing the following steps:

 schedule_name            => `HOURLY_SCHEDULE`,
 force               =>  FALSE

This is an excerpt from the bestselling book Oracle Grid & Real Application Clusters, Rampant TechPress, by Mike Ault and Madhu Tumma.

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational