Re: Clean-up dba_datapump_jobs

On Oct 10, 2:06 pm, bdbafh <bdb..._at_gmail.com> wrote:
> JAW wrote:
> > I have two export jobs that don't have any sessions attached to them.
>
> > I would like to get these two jobs out of dba_datapump_jobs
>
> > I don't see anything at the moment.
>
> > ---------------------------- ------------------------------
> > STATE DEGREE ATTACHED_SESSIONS
> > DATAPUMP_SESSIONS
> > ------------------------------ ---------- -----------------
> > -----------------
> > OPS$ORACLE JAKE_RESTORE
> > IMPORT SCHEMA
> > EXECUTING 10
> > 1 12
>
> > OPS$ORACLE SYS_EXPORT_SCHEMA_01
> > EXPORT SCHEMA
> > NOT RUNNING 0
> > 0 0
>
> > OWNER_NAME JOB_NAME
> > ------------------------------ ------------------------------
> > OPERATION JOB_MODE
> > ------------------------------ ------------------------------
> > STATE DEGREE ATTACHED_SESSIONS
> > DATAPUMP_SESSIONS
> > ------------------------------ ---------- -----------------
> > -----------------
>
> > OPS$ORACLE SYS_EXPORT_SCHEMA_02
> > EXPORT SCHEMA
> > NOT RUNNING 0
> > 0 0
>
> 1 select 'DROP TABLE '||owner||'.'||table_name||';'
> 2 from dba_tables
> 3* where table_name like '%SYS%EXPORT%'
> SQL> /
> 'DROPTABLE'||OWNER||'.'||TABLE_NAME||';'
> -------------------------------------------------------------------------
> DROP TABLE SYSTEM.SYS_EXPORT_FULL_01;
>
> SQL> DROP TABLE SYSTEM.SYS_EXPORT_FULL_01;
>
> Table dropped.
>
> You might want to drop it with the purge option or purge the
> recyclebin manually.
>
> -bdbafh- Hide quoted text -
>
> - Show quoted text -

Thanks!

I was also able to come up with the below.

• locate DataPump master tables: SELECT o.status, o.object_id, o.object_type, o.owner||'.'||object_name "OWNER.OBJECT" FROM dba_objects o, dba_datapump_jobs j WHERE o.owner=j.owner_name AND o.object_name=j.job_name ;

DROP TABLE &USER..&TABLE; Received on Wed Oct 10 2007 - 13:09:36 CDT