Export automático.
Hola, quisiera conocer cómo automatizar un export. Por ejemplo, que el export se genere automáticamente a las 8 de la mañana, a las 13:00pm y a las 18:00pm.
Saludos.
Roberto Antonio.
This entry was posted on 24. May 2009 at 04:10 and is filed under Consultas. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response or trackback from your own site.
24. May 2009 at 08:49
Estimado Roberto,
Tu solucion va a depender del OS y version de Oracle. Por ejemplo:
Linux:
Puedes dispararlo desde el Crontab.
Windows:
Puedes dispararlo desde el Windows Scheduler.
Desde Oracle:
Usando el Oracle Scheduler, si usas version 10g o 11g, puedes crear el backup por export en forma de job desde el DB Console (OEM).
Si no sabes como hacerlo, avisame y te mandare algunos links para ayudarte, pero necesitare que me digas tu OS y version de Oracle.
Saludos,
Francisco Munoz Alvarez
28. May 2009 at 01:50
Hola, gracias Francisco por su respuesta, mi SO es Windows XP Professional y mi SGBD es Oracle XE.
Saludos.
31. May 2009 at 18:57
Estimado Francisco, he creado un Job con éxito, pero falla al ser ejecutado, por ejemplo, si consulto las vistas:
SELECT * FROM dba_scheduler_jobs WHERE job_name = ‘FULLEXPJOBXE’;
SELECT * FROM dba_scheduler_job_log WHERE job_name = ‘FULLEXPJOBXE’;
los resultados son:
3554 10/05/09 10:47:28,609000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RUN FAILED
3595 11/05/09 17:25:43,218000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3719 28/05/09 10:58:42,187000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3760 29/05/09 19:36:24,296000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3369 05/05/09 14:34:58,468000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RUN FAILED
3573 10/05/09 15:51:58,312000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3613 11/05/09 19:18:59,812000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3289 03/05/09 11:00:18,671000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3418 06/05/09 21:23:44,156000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3421 07/05/09 00:10:24,156000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3329 04/05/09 11:22:58,218000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RUN FAILED
3348 04/05/09 14:28:09,390000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3635 11/05/09 22:05:40,046000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3674 13/05/09 00:31:09,484000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3694 15/05/09 13:31:17,609000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RUN FAILED
3697 15/05/09 13:33:08,296000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3803 31/05/09 09:01:01,562000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3414 06/05/09 21:05:13,687000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RUN FAILED
3515 09/05/09 08:35:48,562000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RUN FAILED
3594 11/05/09 17:25:42,531000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RUN FAILED
3759 29/05/09 19:36:23,281000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RUN FAILED
3779 30/05/09 08:28:47,562000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3799 31/05/09 08:42:30,015000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RUN FAILED
3654 12/05/09 14:41:15,718000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RUN FAILED
3699 28/05/09 00:19:36,718000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3555 10/05/09 10:47:29,234000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3761 29/05/09 19:36:34,218000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3762 29/05/09 19:38:14,296000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3763 29/05/09 19:55:28,453000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3802 31/05/09 08:44:21,281000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3597 11/05/09 17:27:33,171000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3800 31/05/09 08:42:31,312000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3801 31/05/09 08:42:41,250000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3248 01/05/09 21:40:22,484000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3271 03/05/09 01:55:09,500000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3272 03/05/09 01:55:19,156000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3274 03/05/09 01:56:59,156000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3275 03/05/09 02:13:39,156000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3330 04/05/09 11:22:59,140000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3331 04/05/09 11:23:09,140000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3332 04/05/09 11:24:49,156000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3333 04/05/09 11:41:29,140000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3370 05/05/09 14:34:59,140000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3371 05/05/09 14:35:09,140000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3372 05/05/09 14:36:49,156000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3373 05/05/09 15:18:17,000000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3394 05/05/09 22:00:44,093000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3517 09/05/09 08:35:59,078000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3519 09/05/09 08:37:39,078000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3520 09/05/09 08:54:19,078000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3533 09/05/09 19:53:32,578000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3556 10/05/09 10:47:39,140000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3557 10/05/09 10:49:19,187000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3558 10/05/09 11:05:59,156000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3596 11/05/09 17:25:53,187000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3655 12/05/09 14:41:16,187000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3656 12/05/09 14:41:26,125000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3657 12/05/09 14:43:06,156000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3658 12/05/09 14:59:46,140000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3696 15/05/09 13:31:28,125000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3435 07/05/09 08:31:14,171000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3436 07/05/09 08:31:24,125000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3437 07/05/09 08:33:04,156000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3438 07/05/09 08:49:44,140000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3439 07/05/09 11:36:24,140000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3454 08/05/09 07:35:17,671000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RUN FAILED
3455 08/05/09 07:35:18,140000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3456 08/05/09 07:35:28,140000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3457 08/05/09 07:37:08,171000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3473 08/05/09 07:53:49,546000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3493 08/05/09 21:41:20,250000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3516 09/05/09 08:35:49,171000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3229 01/05/09 14:08:48,468000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3416 06/05/09 21:05:24,140000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3270 03/05/09 01:55:08,234000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RUN FAILED
3415 06/05/09 21:05:14,125000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3417 06/05/09 21:07:04,156000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
3434 07/05/09 08:31:13,234000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RUN FAILED
3695 15/05/09 13:31:18,140000000 -05:00 SYS FULLEXPJOBXE DEFAULT_JOB_CLASS RETRY_RUN FAILED
Mi código PL/SQL es el siguiente:
create or replace
procedure run_export
as
hand NUMBER;
BEGIN
hand := Dbms_DataPump.Open(operation => ‘EXPORT’,
job_mode => ‘FULL’,
job_name => ‘FULLEXPJOBXE’,
version => ‘COMPATIBLE’);
Dbms_DataPump.Add_File(handle => hand,
filename => ‘expdp_plsql.log’,
directory => ‘DMPDIR’,
filetype => 3);
Dbms_DataPump.Add_File(handle => hand,
filename => ‘expdp_plsql.dmp’,
directory => ‘DMPDIR’,
filetype => 1);
— Dbms_DataPump.Set_Parameter(handle => hand,
— name => ‘ESTIMATE’,
— value => ‘STATISTICS’);
Dbms_DataPump.DATA_FILTER( HANDLE =>HAND,
name => ‘TABLE_EXPR’,
VALUE =>’WHERE ”1=2”’, –’= ”SCOTT”’
TABLE_NAME => ‘TABLA’,
SCHEMA_NAME =>’SCHEMA’);
Dbms_DataPump.Start_Job(hand);
END;
BEGIN
sys.DBMS_SCHEDULER.create_job (
job_name => ’sys.FULLEXPJOBEX’,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘ BEGIN
sys.run_export;
END;’,
repeat_interval => ‘FREQ=DAILY;BYHOUR=7;BYMINUTE=0;BYSECOND=0′,
start_date => SYSTIMESTAMP AT TIME ZONE ‘US/Eastern’,
job_class => ‘DEFAULT_JOB_CLASS’,
comments => ‘Full Export Job’,
auto_drop => FALSE,
enabled => FALSE
);
sys.DBMS_SCHEDULER.set_attribute (name => ’sys.FULLEXPJOBEX’,
attribute => ‘restartable’,
VALUE => TRUE);
sys.DBMS_SCHEDULER.enable (’sys.FULLEXPJOBEX’);
END;
Gracias por su ayuda.
Roberto