RE: DBMS_Schedule

From: Christian Antognini <Christian.Antognini_at_trivadis.com>
Date: Mon, 14 Nov 2005 23:53:57 +0100
Message-ID: <F2C9CCA71510B442AF71446CAE8AEBAF205119@MSXVS04.trivadis.com>

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

Received on Mon Nov 14 2005 - 16:56:01 CST