关于data dump二个小问题

saup007

1、怎么筛选出来job或scheduler job
我的方法，看有没有更有效的
impdp expdp/expdp dumpfile=saup.dmp directory=dir_expdp sqlfile=saup2.sql  TRANSFORM=segment_attributes:n EXCLUDE=table,index,SEQUENCE,PROCEDURE,USER


2、11g 通过 version 导出来后，报错，在medlink上有找到文章，但是没看明白。

MOS doc 780174.1  
BUG 4595736

In this Document [size=-1]  Symptoms   Cause   Solution   References Applies to: Oracle Server - Standard Edition - Version: 10.2.0.3 This problem can occur on any platform. Symptoms -- Problem Statement: Import receives the following errors: Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ ORA-39083: Object type PROCOBJ failed to create with error: ORA-06502: PL/SQL: numeric or value error: character to number conversion error Failing sql is: BEGIN dbms_scheduler.create_job('"AUTO_EDIT_NEWACCTS"', job_type=>'PLSQL_BLOCK', job_action=> 'begin new_acct_mgmt.autoedit_new_records; end;' , number_of_arguments=>0, start_date=>'09-DEC-08 08.30.00.000000 AM AMERICA/NEW_YORK', repeat_interval=> 'FREQ=MINUTELY;INTERVAL=15' , end_date=>NULL, job_class=>'"DEFAULT_JOB_CLASS"', enabled=>FALSE, auto_ ORA-39083: Object type PROCOBJ failed to create with error: ORA-06502: PL/SQL: numeric or value error: character to number conversion error Failing sql is: BEGIN dbms_scheduler.create_job('"QUICK_BATCH_DATA"', job_type=>'PLSQL_BLOCK', job_action=> 'begin batch_new_accounts.quick_batch_data; end;' , number_of_arguments=>0, start_date=>'09-DEC-08 08.10.00.000000 AM AMERICA/NEW_YORK', repeat_interval=> 'FREQ=MINUTELY;INTERVAL=15' , end_date=>NULL, job_class=>'"DEFAULT_JOB_CLASS"', enabled=>FALSE, auto_d Cause When importing job objects with logging level  set to FULL or OFF then the import for these job objects is bound to fail. Solution Prior to exporting these job objects, precreate these jobs with the logging level set to DBMS_SCHEDULER.LOGGING_RUNS and then export and then import the job. Then change the logging level as required. ReferencesBUG:4595736 - ORA-6502 DURING IMPDP DUE TO WRONG LOGGING_LEVEL PUT IN EXPORT DUMP

saup007

1. [oracle@localhost bak_full]$ expdp expdp/expdp directory=dir_expdp dumpfile=expdp.dmp logfile=expdp.log schemas=expdp  EXCLUDE=PROCOBJ
   
2. 
   
3. Export: Release 10.2.0.1.0 - Production on Wednesday, 23 May, 2012 21:37:03
   
4. 
   
5. Copyright (c) 2003, 2005, Oracle.  All rights reserved.
   
6. 
   
7. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
   
8. With the Partitioning, OLAP and Data Mining options
   
9. Starting "EXPDP"."SYS_EXPORT_SCHEMA_01":  expdp/******** directory=dir_expdp dumpfile=expdp.dmp logfile=expdp.log schemas=expdp EXCLUDE=PROCOBJ
   
10. Estimate in progress using BLOCKS method...
    
11. Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
    
12. Total estimation using BLOCKS method: 64 KB
    
13. Processing object type SCHEMA_EXPORT/USER
    
14. Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
    
15. Processing object type SCHEMA_EXPORT/ROLE_GRANT
    
16. Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
    
17. Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
    
18. ORA-39127: unexpected error from call to export_string := SYS.DBMS_RMGR_GROUP_EXPORT.grant_exp(4845,1,...)
    
19. ORA-06502: PL/SQL: numeric or value error: NULL index table key value
    
20. ORA-06512: at "SYS.DBMS_RMGR_GROUP_EXPORT", line 130
    
21. ORA-06512: at line 1
    
22. ORA-06512: at "SYS.DBMS_METADATA", line 5111
    
23. ORA-39127: unexpected error from call to export_string := SYS.DBMS_RMGR_GROUP_EXPORT.grant_exp(4844,1,...)
    
24. ORA-06502: PL/SQL: numeric or value error: NULL index table key value
    
25. ORA-06512: at "SYS.DBMS_RMGR_GROUP_EXPORT", line 130
    
26. ORA-06512: at line 1
    
27. ORA-06512: at "SYS.DBMS_METADATA", line 5111
    
28. ORA-39127: unexpected error from call to export_string := SYS.DBMS_RMGR_GROUP_EXPORT.grant_exp(4843,1,...)
    
29. ORA-06502: PL/SQL: numeric or value error: NULL index table key value
    
30. ORA-06512: at "SYS.DBMS_RMGR_GROUP_EXPORT", line 130
    
31. ORA-06512: at line 1
    
32. ORA-06512: at "SYS.DBMS_METADATA", line 5111
    
33. Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
    
34. Processing object type SCHEMA_EXPORT/TABLE/TABLE
    
35. Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
    
36. Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
    
37. Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
    
38. Processing object type SCHEMA_EXPORT/TABLE/COMMENT
    
39. Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
    
40. Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
    
41. ORA-39127: unexpected error from call to export_string := SYS.DBMS_RMGR_GROUP_EXPORT.grant_exp(4845,1,...)
    
42. ORA-06502: PL/SQL: numeric or value error: NULL index table key value
    
43. ORA-06512: at "SYS.DBMS_RMGR_GROUP_EXPORT", line 130
    
44. ORA-06512: at line 1
    
45. ORA-06512: at "SYS.DBMS_METADATA", line 5111
    
46. ORA-39127: unexpected error from call to export_string := SYS.DBMS_RMGR_GROUP_EXPORT.grant_exp(4844,1,...)
    
47. ORA-06502: PL/SQL: numeric or value error: NULL index table key value
    
48. ORA-06512: at "SYS.DBMS_RMGR_GROUP_EXPORT", line 130
    
49. ORA-06512: at line 1
    
50. ORA-06512: at "SYS.DBMS_METADATA", line 5111
    
51. ORA-39127: unexpected error from call to export_string := SYS.DBMS_RMGR_GROUP_EXPORT.grant_exp(4843,1,...)
    
52. ORA-06502: PL/SQL: numeric or value error: NULL index table key value
    
53. ORA-06512: at "SYS.DBMS_RMGR_GROUP_EXPORT", line 130
    
54. ORA-06512: at line 1
    
55. ORA-06512: at "SYS.DBMS_METADATA", line 5111
    
56. . . exported "EXPDP"."RMAN_TIME"                         5.320 KB       7 rows
    
57. Master table "EXPDP"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
    
58. ******************************************************************************
    
59. Dump file set for EXPDP.SYS_EXPORT_SCHEMA_01 is:
    
60.   /u01/backup/bak_full/expdp.dmp
    
61. Job "EXPDP"."SYS_EXPORT_SCHEMA_01" completed with 6 error(s) at 21:37:26
    
62. 
    
63. [oracle@localhost bak_full]$ impdp saup/saup dumpfile=expdp.dmp directory=dir_expdp REMAP_SCHEMA=expdp:test
    
64. 
    
65. Import: Release 10.2.0.1.0 - Production on Wednesday, 23 May, 2012 21:37:32
    
66. 
    
67. Copyright (c) 2003, 2005, Oracle.  All rights reserved.
    
68. 
    
69. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
    
70. With the Partitioning, OLAP and Data Mining options
    
71. Master table "SAUP"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
    
72. Starting "SAUP"."SYS_IMPORT_FULL_01":  saup/******** dumpfile=expdp.dmp directory=dir_expdp REMAP_SCHEMA=expdp:test
    
73. Processing object type SCHEMA_EXPORT/USER
    
74. Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
    
75. Processing object type SCHEMA_EXPORT/ROLE_GRANT
    
76. Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
    
77. Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
    
78. Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
    
79. Processing object type SCHEMA_EXPORT/TABLE/TABLE
    
80. Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
    
81. . . imported "TEST"."RMAN_TIME"                          5.320 KB       7 rows
    
82. Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
    
83. Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
    
84. Job "SAUP"."SYS_IMPORT_FULL_01" successfully completed at 21:37:36
    
85. 
    
86. 
    
87. 
    
88. 21:35:57 expdp@SAUP10G> select * from dba_objects where owner='TEST';
    
89. 
    
90. OWNER
    
91. ------------------------------
    
92. OBJECT_NAME
    
93. --------------------------------------------------------------------------------------------------------------------------------
    
94. SUBOBJECT_NAME                        OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE             CREATED                 LAST_DDL_TIME             TIMESTAMP                 STATUS  T G S
    
95. ------------------------------ ---------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - -
    
96. TEST
    
97. RMAN_SEQ
    
98.                                     52958                 SEQUENCE             2012-05-23 21:37:35 2012-05-23 21:37:35 2012-05-23:21:37:35 VALID         N N N
    
99. 
    
100. TEST
     
101. RMAN_TIME
     
102.                                     52959           52959 TABLE                     2012-05-23 21:37:35 2012-05-23 21:37:35 2012-05-23:21:37:35 VALID         N N N
     
103. 
     
104. TEST
     
105. PRO_RMAN_TIME
     
106.                                     52960                 PROCEDURE             2012-05-23 21:37:35 2012-05-23 21:37:35 2012-05-23:21:31:41 VALID         N N N
     
107. 
     
108. 。

复制代码

。MARK，在x86_64 11.2.0.2上是可以的expdp/******** directory=dir_expdp dumpfile=expdp.dmp logfile=expdp.log schemas=expdp EXCLUDE=PROCOBJ

[ 本帖最后由 saup007 于 2012-8-8 17:55 编辑 ]

saup007

怎么只有关于job或scheduler job的，schemas这个用户所有全导出来了。

看样子，只能导出元数据，再把 EXCLUDE其他对象类型， scheduler job生成SQL文本了，要不那么多scheduler jobs，也不能看dba_schedulers，一条一条的来写哈。。呵呵。

谢谢刘大

实验源数据：

create table EXPDP.rman_time(id number,rman_date date);

create sequence EXPDP.RMAN_SEQ
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;

create or replace procedure EXPDP.PRO_RMAN_TIME
as
begin

insert into rman_time values(rman_seq.nextval,sysdate);
commit;
end;
/

begin
  sys.dbms_scheduler.create_job(job_name            => 'EXPDP.RMAN_RECOVER_TIME',
                                job_type            => 'STORED_PROCEDURE',
                                job_action          => 'EXPDP.PRO_RMAN_TIME',
                                repeat_interval     => 'Freq=Minutely;Interval=1',
                                job_class           => 'DEFAULT_JOB_CLASS',
                                enabled             => true);
end;
/

[ 本帖最后由 saup007 于 2012-5-23 21:49 编辑 ]

Maclean Liu(刘相兵

ODM TEST:

SQL> variable v_jobno number;
SQL> exec dbms_job.submit(:v_JobNo, 'begin null; end;',TRUNC(SYSDATE)+1, 'TRUNC(SYSDATE)+1');

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL>  print :v_jobno;

   V_JOBNO
----------
         2

SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options






[oracle@vrh8 ~]$ expdp system/oracle schemas=maclean dumpfile=maclean_job  include=job

Export: Release 10.2.0.5.0 - 64bit Production on Wednesday, 23 May, 2012 6:49:23

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** schemas=maclean dumpfile=maclean_job include=job
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/JOB
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /s01/oracle/product/10.2.0.5/db_1/rdbms/log/maclean_job.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 06:49:26