Re: Preventing jobs from being imported

From: Paul Drake <bdbafh_at_gmail.com>
Date: 9 Aug 2004 19:49:57 -0700
Message-ID: <910046b4.0408091849.eda85ea@posting.google.com>

hboswel1_at_bellsouth.net wrote in message news:<cuifh0duak1uhls5m00d2pbjbrbi6erkc3_at_4ax.com>...
> On Mon, 09 Aug 2004 11:09:19 -0500, Turkbear <john.g_at_dot.spamfree.com>
> wrote:
>
> >hboswel1_at_bellsouth.net wrote:
> >
> >>I have a nightly export/import job that runs, to place some data on a
> >>database server that sits outside our firewall. The export parfile
> >>has these parameters:
> >>
> >>COMPRESS=Y
> >>GRANTS=Y
> >>INDEXES=Y
> >>ROWS=Y
> >>CONSTRAINTS=Y
> >>TRIGGERS=Y
> >>
> >>
> >>The import parfile has these commands:
> >>
> >>INDEXES=Y
> >>GRANTS=Y
> >>CONSTRAINTS=Y
> >>ROWS=Y
> >>IGNORE=Y
> >>ANALYZE=N
> >>
> >>
> >>The schema being exported/imported has some jobs associated with it,
> >>that I don't want exported or imported, but that's happening. How do
> >>I prevent this? I haven't found mention on jobs in the exp/imp
> >>documentation, and a Metalink search didn't turn anything up.
> >>
> >>Thanks,
> >>Harry Boswell
> >If all that is needed is the data, use the TABLE=(<table list>) method instead of a FULL export..
>
> We also need the procedures and packages. It appears that to get
> those, you have to get the jobs also.
>
>
> >

this should tidy it up - might have a typo, didn't eat dinner yet and brain is shutting down ...

connect imported_user/pword

begin

   for rec in (select job from user_jobs)

      loop
         execute immediate (exec dbms_job.remove(rec.job));
      end loop;

   end for;
end;

commit;

that won't work if you're importing as a different user. e.g. running imp as system, the system account would own the jobs. you'd have to connect as the owner of the dbms_jobs.

-bdbafh Received on Mon Aug 09 2004 - 21:49:57 CDT