Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Clean-up dba_datapump_jobs

Re: Clean-up dba_datapump_jobs

From: bdbafh <bdbafh_at_gmail.com>
Date: Wed, 10 Oct 2007 20:35:57 -0000
Message-ID: <1192048557.844060.321180@50g2000hsm.googlegroups.com>


On Oct 10, 2:09 pm, JAW <jwill..._at_aglresources.com> wrote:
> 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;
that's even better than what is in Note:294618.1 in Metalink.

-bdbafh Received on Wed Oct 10 2007 - 15:35:57 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US