Re: job ownership during schema imp/exp changes

On 18 Jan 2006 20:54:45 -0800, yong321_at_gmail.com wrote:

>NetComrade wrote:
>> Is there are any way to prevent jobs from being imported under wrong
>> username (SYS) when schema exp/imp is being done (e.g. schema
>> movement?)
>>
>> I suspect this has to do with user_id's not matching..
>>
>> Thanks.
>> .......
>> We run Oracle 9.2.0.6 on RH4 AMD
>> remove NSPAM to email
>
>If the intent is to avoid importing a job when you import other things
>owned by the original user, you can either drop the job after import,
>or pre-create a dummy job in the target database using
>dbms_jobs.isubmit with the same job number as the job in the source.
>This is because the imported job would take the same job number and
>fail to be imported if the number is already taken.
>
>Whoever imports the schema owns the job. If your intent is to create a
>job in the target database under the original user's ownership (as
>shown under dba_jobs.log_user and priv_user but not schema_user), you
>still do the same. Then recreate the job under the correct schema.
>Dbms_job.user_export makes this a little easier. See Metalink
>322797.999.
>
>Why not temporarily allow the user instead of SYS to import? Is there
>anything only SYS can do (like row level security stuff)?

The situation is schema migration from server to server. The problem is exp/imp with the sys(or system) user, and importing on the other server, however, the jobs get imported under sys (or system) user, instead of the original owner.

Yes, the workaround seemed to be to give exp_full_database on one end and imp_full_database on the other end, and just use the 'ownership' user to imp and exp.. However, I don't understand why jobs won't be owned by the original owner in the first place.

Thanks,
-A

.......
We run Oracle 9.2.0.6 on RH4 AMD
remove NSPAM to email Received on Thu Jan 26 2006 - 12:49:24 CST