RE: DBMS_Schedule

From: Sanjay Mishra <smishra_97_at_yahoo.com>
Date: Tue, 15 Nov 2005 05:01:52 -0800 (PST)
Message-ID: <20051115130152.38356.qmail@web51313.mail.yahoo.com>

Chris

You are really great as well as your explanation and now I know how to copy the dbms_scheduler jobs from one db to another.

Thanks Again
Sanjay

Christian Antognini <Christian.Antognini_at_trivadis.com> wrote: Sanjay

>I can see the following but don't know as how to use it with Export dump
>JOB SCHEMA_EXPORT/JOB
>SCHEMA_EXPORT/JOB SCHEMA_EXPORT/JOB
>JOB DATABASE_EXPORT/SCHEMA/JOB
>SCHEMA/JOB DATABASE_EXPORT/SCHEMA/JOB
>DATABASE_EXPORT/SCHEMA/JOB DATABASE_EXPORT/SCHEMA/JOB
>
>
>Any help is appreciated

When you perform a plain export you see something like that:

oracle_at_trantor:/tmp/ [A1020] expdp system/manager schemas=scott

Export: Release 10.2.0.1.0 - Production on Tuesday, 15 November, 2005 0:40:13

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options Starting "SYSTEM"."SYS_EXPORT_SCHEMA_05": system/******** schemas=scott Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 384 KB

Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
. . exported "SCOTT"."SCHEDULER$_JOB_ARG" 6.914 KB 6 rows
. . exported "SCOTT"."DEPT" 5.656 KB 4 rows
. . exported "SCOTT"."DUMMY" 4.921 KB 1 rows
. . exported "SCOTT"."EMP" 7.859 KB 15 rows
. . exported "SCOTT"."SALGRADE" 5.585 KB 5 rows

. . exported "SCOTT"."BONUS" 0 KB 0 rows Master table "SYSTEM"."SYS_EXPORT_SCHEMA_05" successfully loaded/unloaded

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_05 is: /u00/app/oracle/admin/A1020/dpdump/expdat.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_05" successfully completed at 00:41:05

The idea of the parameter INCLUDE is that you can explicitly specify which objects should be exported. According to the export type different paths are available... (You see them in the output, see previous example.) The complete list is available from DATAPUMP_PATHS or DATABASE_EXPORT_OBJECTS, SCHEMA_EXPORT_OBJECTS and TABLE_EXPORT_OBJECTS.

For example to export only indexes (with statistics) and user information the following could be used:

oracle_at_trantor:/tmp/ [A1020] expdp system/manager schemas=scott include=SCHEMA_EXPORT/USER,SCHEMA_EXPORT/TABLE/INDEX

Export: Release 10.2.0.1.0 - Production on Tuesday, 15 November, 2005 0:47:34

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options Starting "SYSTEM"."SYS_EXPORT_SCHEMA_05": system/******** schemas=scott include=SCHEMA_EXPORT/USER,SCHEMA_EXPORT/TABLE/INDEX Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 0 KB

Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Master table "SYSTEM"."SYS_EXPORT_SCHEMA_05" successfully loaded/unloaded

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_05 is: /u00/app/oracle/admin/A1020/dpdump/expdat.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_05" successfully completed at 00:47:51

Now, you simply have to ask yourself which path should be used to export the object you want to have in the export file. IIRC 1) "JOB" is for DBMS_JOB jobs 2) Scheduler jobs are part of "PROCOBJ".

HTH
Chris

New Features Oracle Database 10g Release 2 seminars @ www.trivadis.com Italiano: Lugano (24-Nov)
Français: Genève (17-Nov)
Deutsch: Zürich (29-Nov), Stuttgart (13-Dec)

--
http://www.freelists.org/webpage/oracle-l


  


		
---------------------------------
 Yahoo! FareChase - Search multiple travel sites in one click.  
--
http://www.freelists.org/webpage/oracle-l

Received on Tue Nov 15 2005 - 07:04:13 CST