Re: impdp changes owner of jobs
Date: Thu, 17 Jan 2008 18:24:33 +0100
Mark D Powell <Mark.Powell_at_eds.com> writes:
> On Jan 16, 3:37 pm, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>> On Jan 16, 2:06 pm, joel garry <joel-ga..._at_home.com> wrote:
>> > On Jan 16, 9:32 am, lothar.armbrues..._at_t-online.de (Lothar Armbrüster)
>> > wrote:
>> > > Hello out there,
>> > > today I imported a full database export of an Oracle 10.2.0.3 database
>> > > running on Windows 2000. I precreated the tablespaces since I wanted
>> > > to put the datafiles on different locations.
>> > > After the impdp finished, I realized that every job created with
>> > > dbms_job now belongs to system which I used to run the import.
>> > > I granted imp_full_database to system prior to the import.
>> > > Now I wonder what I did wrong. Is there something to consider when I
>> > > want to import jobs?
>> > > This is a SE which was downgraded from an EE 9.1 via exp/imp a time
>> > > ago. Can that cause the problem?
>> > > Any crystal balls around? ;-)
>> > See bug 5620345 (closed, not feasible to fix) workaround:
>> > 1.Qualify the function name with owner of the function while
>> > specifying in interval parameter. . for ex:
>> > interval=>'usr001.next_min(60)' .
>> > 2.Run the impdp as the user who submitted the job and has access
>> > privileges to the function.
>> > Is your db SE or EE? See bug 5188282. There is at least one other
>> > which isn't public having to do with impdb and jobs, looks like they
>> > just didn't quite get it right. Perhaps you should submit this.
The database is SE but imported from an export made from an EE after removing everything not supported in SE. This was done with exp/imp. The result then was exported with expdp and imported into a freshly installed SE database using impdp. So the database may be some mix of SE and EE. The jobs were imported but belong to system. Maybe I should create some clean testcases.
>> > jg
>> > --
>> > @home.com is bogus.
>> > - Show quoted text -
>> I have not read the bug report yet but as a workaround I would think
>> you could use SQL to extract the job information and build dbms_job or
>> better dbms_scheduler scripts to recreate the jobs under the desired
>> HTH -- Mark D Powell --- Hide quoted text -
>> - Show quoted text -
> > PS - Since impdp is being used what about making a > content=metadata_only include=job export and then using the sqlfile= > option on the impdp to capture the DDL. You could then change it. > > For user jobs I would think this would work. > > HTH -- Mark D Powell -- >
This seems to be a way, but since I just wanted to create a simple copy of my database without having to remap users, datafiles etc., I will use exp/imp next time. There I know that it works. I thought impdp has better performance over imp but it has to work properly.
-- Lothar Armbrüster | lothar.armbruester_at_t-online.de Hauptstr. 26 | 65346 Eltville |Received on Thu Jan 17 2008 - 11:24:33 CST