job ownership during schema imp/exp changes

[NetComrade]

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

[DA Morgan]

Not sure I understand your question but my answer would be no.
If you provide the wrong schema name there is no reason for
Oracle not to use it.
--
Daniel A. Morgan
http://www.psoug.org
damo...@x.washington.edu
(replace x with u to respond)

[yon...@gmail.com]

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)?

Yong Huang

[NetComrade]

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

[Joel Garry]

Interestingly enough, the metalink document about how to use exp/imp to
copy PORTAL schemas has this little nugget:

---- Jobs
--
-- Reassign the JOBS imported to PORTAL. After the import, they belong
-- incorrectly to the user SYS.
update dba_jobs set LOG_USER='PORTAL', PRIV_USER='PORTAL' where
schema_user='PORTAL';
commit;

jg
--
@home.com is bogus.
Wanna buy a watch?
http://www.signonsandiego.com/uniontrib/20060126/news_1b26auction.html

[NetComrade]

On 26 Jan 2006 11:39:28 -0800, "Joel Garry" <joel-...@home.com>
wrote:

>Interestingly enough, the metalink document about how to use exp/imp to
>copy PORTAL schemas has this little nugget:
>
>---- Jobs
>--
>-- Reassign the JOBS imported to PORTAL. After the import, they belong
>-- incorrectly to the user SYS.
>update dba_jobs set LOG_USER='PORTAL', PRIV_USER='PORTAL' where
>schema_user='PORTAL';
>commit;

I didn't know updating dba_* was allowed.

Thanks for the tip.

[hpuxrac]

Updating dba_* ... ???

Heavily discouraged in general except for specific extenuating
circumstances.

Such as ( sometimes ) notes include in oracle support information.

[Joel Garry]

Netcomrade wrote:

>I didn't know updating dba_* was allowed.

Allowed, yes, a good idea, wellllllll... it depends. :-)

I haven't tried it, just thought it was interesting, and also
interesting that it was posted (in the portal context) to solve your
exact problem. There have been various tricks posted in the past on
updating internal tables, generally it is a really bad idea. This one
looks mostly harmless.

jg
--
@home.com is bogus.

Pew study author says "We still have needs, strong needs, to see, touch
and smell people."
http://www.signonsandiego.com/uniontrib/20060126/news_1n26internet.html