Re: impdp changes owner of jobs
From: Lothar Armbrüster <lothar.armbruester_at_t-online.de>
Date: Thu, 17 Jan 2008 18:24:33 +0100
Message-ID: <87ve5sl60e.fsf@t-online.de>
>> 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.
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.
Date: Thu, 17 Jan 2008 18:24:33 +0100
Message-ID: <87ve5sl60e.fsf@t-online.de>
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
>> owner.
>>
>> 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.
Regards,
Lothar
-- Lothar Armbrüster | lothar.armbruester_at_t-online.de Hauptstr. 26 | 65346 Eltville |Received on Thu Jan 17 2008 - 11:24:33 CST