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

 
 Home
 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
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

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 \
TABLE_EXISTS_ACTION=replace \
logfile=dpump_dir:impusr.log

Example 2: Import with Data Option

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

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:

DBMS_SCHEDULER.CREATE_PROGRAM
(
 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:

BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM
(
 program_name   => `CHECK_USER`,
 program_action => `/dba/scripts/ckuser.sh`,
 program_type   => `EXECUTABLE`
);
END;
/

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:

BEGIN
DBMS_SCHEDULER.DROP_PROGRAM
(
 program_name => ?check_user?,
 force        =>  FALSE
);
END;
/

Create and Drop a Schedule

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

DBMS_SCHEDULER.CREATE_SCHEDULE
(
 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

* YEARLY

* MONTHLY

* WEEKLY

* DAILY

* HOURLY

* MINUTELY

* SECONDLY

The repeat interval range is from 1 to 99.

The other Frequency clause is made of the following elements:

* BYMONTH

* BYWEEKNO

* BYYEARDAY

* BYMONTHDAY

* BYDAY

* BYHOUR

* BYMINUTE

* BYSECOND

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

Every March and June of the year:

REPEAT_INTERVAL=> `FREQ=YEARLY; BYMONTH=3,6`

Every 20th day of the month:

REPEAT_INTERVAL=> `FREQ=MONTHLY; BYMONTHDAY=20`

Every Sunday of the week:

REPEAT_INTERVAL=> `FREQ=WEEKLY; BYDAY=SUN`

Every 60 days:

REPEAT_INTERVAL=> `FREQ=DAILY; INTERVAL=60`

Every 6 hours:

REPEAT_INTERVAL=> `FREQ=HOURLY; INTERVAL=6`

Every 10 minutes:

REPEAT_INTERVAL=> `FREQ=MINUTELY;INTERVAL=10`

Every 30 seconds:

REPEAT_INTERVAL=> `FREQ=SECONDLY;INTERVAL=30`

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

REPEAT_INTERVAL=> `SYSDATE ?1`
REPERT_INTERVAL=> `SYSDATE + 36/24`

The following steps are used to create a schedule:

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

A schedule can be dropped by performing the following steps:

BEGIN
DBMS_SCHEDULER.DROP_SCHEDULE
(
 schedule_name            => `HOURLY_SCHEDULE`,
 force               =>  FALSE
);
END;
/
 


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.

http://www.rampant-books.com/book_2004_1_10g_grid.htm


 

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