EXPDP ORA-31626 ORA-31633 ORA-06512 ORA-01950

ORA-31626 ORA-31633 ORA-06512 ORA-01950
ORA-31626: job does not exist
ORA-31633: unable to create master table "SYSTEM.SYS_EXPORT_FULL_09"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1020
ORA-01950: no privileges on tablespace 'SYSTEM'



ALTER USER SYSTEM QUOTA unlimited ON SYSTEM;
ALTER USER SYSTEM QUOTA unlimited ON USR;
ALTER USER SYSTEM QUOTA unlimited ON INDX;
GRANT UNLIMITED TABLESPACE TO SYSTEM;

ALTER USER LDBO QUOTA unlimited ON SYSTEM;
ALTER USER LDBO QUOTA unlimited ON USR;
ALTER USER LDBO QUOTA unlimited ON INDX;
GRANT UNLIMITED TABLESPACE TO LDBO ;

expdp procedure in 11g

 1) create directory export_auto as 'd:\expdp1213';

 create user dba_export_user identified by test123;

grant connect, create database link, resource, create view to dba_export_user;
grant unlimited tablespace to dba_export_user;
grant exp_full_database to dba_export_user;
grant read,write on directory export_auto to dba_export_user;
grant execute on dbms_flashback to dba_export_user;
grant create table to dba_export_user;
grant FLASHBACK ANY TABLE to dba_export_user;


2)
CREATE OR REPLACE PROCEDURE dba_export_user.start_export
IS
   hdl_job        NUMBER;
   l_cur_scn      NUMBER;
   l_job_state    VARCHAR2 (20);
   l_status       SYS.ku$_status1010;
   l_job_status   SYS.ku$_jobstatus1010;
BEGIN

 begin
    execute immediate 'drop table  dba_export_user.AUTO_EXPORT';
    exception when others then null;
   end;

   hdl_job := DBMS_DATAPUMP.OPEN ( operation => 'EXPORT', job_mode => 'FULL', job_name => 'AUTO_EXPORT' );
 
   DBMS_DATAPUMP.add_file (handle => hdl_job,filename => 'EXPDP1213.dmp',directory => 'EXPORT_AUTO',filetype => DBMS_DATAPUMP.ku$_file_type_dump_file,reusefile => 1);
                         
   DBMS_DATAPUMP.add_file (handle => hdl_job,filename => 'export.log',DIRECTORY => 'EXPORT_AUTO',filetype => DBMS_DATAPUMP.ku$_file_type_log_file,reusefile => 1);
                     
   DBMS_DATAPUMP.start_job (handle => hdl_job);
 
   DBMS_DATAPUMP.wait_for_job (handle => hdl_job, job_state => l_job_state);
 
   DBMS_OUTPUT.put_line ('Job exited with status:' || l_job_state);

  DBMS_DATAPUMP.detach(handle => hdl_job);

END;
/


3) Change the time, Date

begin
 dbms_scheduler.create_job(
      job_name => 'EXPORT_JOB'
     ,job_type => 'STORED_PROCEDURE'
     ,job_action => 'dba_export_user.start_export'
     ,start_date => '08-FEB-12 06.02.00.00 PM ASIA/CALCUTTA'
       ,repeat_interval => 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN;'
     ,enabled => TRUE
     ,comments => 'EXPORT_DATABASE_JOB');
end;
/

EXEC  dbms_scheduler.run_job('EXPORT_JOB');

Drop All Database Objects

BEGIN
  FOR cur_rec IN (SELECT table_owner, table_name, constraint_name FROM dba_constraints
                  WHERE  constraint_type = 'R' and table_owner not in ('DBSNMP','ORACLE_OCM','OUTLN','SYS','SYSMAN','SYSTEM','TSMSYS','WMSYS','XDB')) LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE ' || cur_rec.table_owner|| '.'  || cur_rec.table_name || ' DROP CONSTRAINT ' || cur_rec.constraint_name;
  END LOOP;
  FOR cur_rec IN (SELECT owner, object_name, object_type
                  FROM   dba_objects where owner not in ('DBSNMP','ORACLE_OCM','OUTLN','SYS','SYSMAN','SYSTEM','TSMSYS','WMSYS','XDB')) LOOP
    BEGIN
      EXECUTE IMMEDIATE 'DROP ' || cur_rec.owner|| '.'  || cur_rec.object_type || ' ' || cur_rec.object_name;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
FOR cur_rec IN (SELECT owner,synonym_name,table_owner FROM  dba_synonyms where table_owner not in ('DBSNMP','ORACLE_OCM','OUTLN','SYS','SYSMAN','SYSTEM','TSMSYS','WMSYS','XDB')) LOOP
BEGIN
      EXECUTE IMMEDIATE 'DROP ' || cur_rec.owner || ' SYNONYM ' || cur_rec.table_owner || '.' || cur_rec.synonym_name || ' FORCE';
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
FOR cur_rec IN (SELECT owner, queue_table FROM dba_queue_tables where owner not in ('DBSNMP','ORACLE_OCM','OUTLN','SYS','SYSMAN','SYSTEM','TSMSYS','WMSYS','XDB')) LOOP
    BEGIN
    DBMS_AQADM.DROP_QUEUE_TABLE('' || cur_rec.owner || '' || '.' || '' || cur_rec.queue_table || '',force =>true);
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
END;
/

EXPDP Data Pump Job Scheduling with rename dump and remove old files

1) create directory export_auto as 'd:\expdp1213';

create user dba_export_user identified by test123;

grant connect, create database link, resource, create view to dba_export_user;

grant unlimited tablespace to dba_export_user;

grant exp_full_database to dba_export_user;

grant read,write on directory export_auto to dba_export_user;

grant execute on dbms_flashback to dba_export_user;

grant create table to dba_export_user;

grant FLASHBACK ANY TABLE to dba_export_user;

2)

CREATE OR REPLACE PROCEDURE dba_export_user.start_export

IS

hdl_job NUMBER;

l_cur_scn NUMBER;

l_job_state VARCHAR2 (20);

l_status SYS.ku$_status1010;

l_job_status SYS.ku$_jobstatus1010;

BEGIN

begin

execute immediate 'drop table dba_export_user.AUTO_EXPORT';

exception when others then null;

end;

hdl_job := DBMS_DATAPUMP.OPEN ( operation => 'EXPORT', job_mode => 'FULL', job_name => 'AUTO_EXPORT' );

DBMS_DATAPUMP.add_file (handle => hdl_job,filename => 'exp1213.dmp',directory => 'EXPORT_AUTO',filetype => DBMS_DATAPUMP.ku$_file_type_dump_file);

DBMS_DATAPUMP.add_file (handle => hdl_job,filename => 'export.log',DIRECTORY => 'EXPORT_AUTO',filetype => DBMS_DATAPUMP.ku$_file_type_log_file);

DBMS_DATAPUMP.start_job (handle => hdl_job);

DBMS_DATAPUMP.wait_for_job (handle => hdl_job, job_state => l_job_state);

DBMS_OUTPUT.put_line ('Job exited with status:' || l_job_state);

DBMS_DATAPUMP.detach(handle => hdl_job);

----------------------RENAME BACKUP WITH DATE

begin

UTL_FILE.FRENAME ('EXPORT_AUTO','exp1213.DMP','EXPORT_AUTO','exp1213'||'_'||TO_CHAR(SYSDATE,'DDMMYYYY')||'.DMP');

end;

begin

UTL_FILE.FRENAME ('EXPORT_AUTO','export.log','EXPORT_AUTO','export'||'_'||TO_CHAR(SYSDATE,'DDMMYYYY')||'.LOG');

end;

------------DELETE TWO DAYS BEFORE BACKUP

begin

UTL_FILE.FREMOVE ('EXPORT_AUTO','exp1213'||'_'||TO_CHAR(SYSDATE-2,'DDMMYYYY')||'.DMP');

end;

begin

UTL_FILE.FREMOVE ('EXPORT_AUTO','export'||'_'||TO_CHAR(SYSDATE-2,'DDMMYYYY')||'.log');

end;

END;

/

3) Change the time, Date

begin

dbms_scheduler.create_job(

job_name => 'EXPORT_JOB'

,job_type => 'STORED_PROCEDURE'

,job_action => 'dba_export_user.start_export'

,start_date => '08-FEB-12 06.02.00.00 PM ASIA/CALCUTTA'

,repeat_interval => 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN;'

,enabled => TRUE

,comments => 'EXPORT_DATABASE_JOB');

end;

/

Note: Rename the dmp file with sysdate on daily basis before next schedule time

manually execute backup job

EXEC dba_export_user.start_export;

check running job status

select * from DBA_datapump_jobs;

drop job

EXEC dbms_scheduler.drop_job('dba_export_user.start_export');

expdp

http://www.dba-oracle.com/t_export_import_different_releases_of_oracle.htm