RE: PeopleSoft DBA Forum Re: PeopleSoft database setup with different ownerid and accessid
- As mentioned earlier, Oracle Resource profiler allocates profiles to users,
so you would need multiple Access IDs.
You will need to grant SELECT, INSERT, UPDATE, DELETE on every table to the
additional Access IDs.
You will need to regrant the privileges when the object is dropped and
recreated by PS alter scripts.
Either, you will have to create synonyms for every table and view, or
alternatively, you might be able to use an AFTER LOGON trigger on the new
ACCESSID to alter the CURRENT_SCHEMA for those sessions to the OWNERID. I
haven't tested which profile you access. If know that you use the temporary
and default tablespaces for the CURRENT_SCHEMA, rather than the one as which
Before you do this make sure you really need to go down this path. Most of
the resource consumption for on-line users will be in the web and app
servers layers rather than the database.
Go-Faster Consultancy Ltd.
tel: +44 (0)7771 760660
fax: +44 (0)7092 348865
Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
DBA Blogs: PeopleSoft: http://blog.psftdba.com, Oracle:
PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
>From: psftdba@... [mailto:firstname.lastname@example.org]
>On Behalf Of shajivps
>Sent: Thursday, April 24, 2008 3:09 AM
>Subject: PeopleSoft DBA Forum Re: PeopleSoft database setup
>with different ownerid and accessid
> I have the same question and you sort of answered that
>partially. I have a requirement to separate connections
>coming from app servers using different accessid pointing to
>the same owner (SYSADM). This is to use oracle's resource
>manager to limit resources for different types of users
>(internal corporate, external
>vendor accounts coming thru portal etc.,). I need to guarantee a
>certain amount of cpu to each category of users. Any help on this
>will be much appreciated.
>--- In email@example.com, "David Kurtz" <david.kurtz@...> wrote:
>> The owner ID is always an access ID, and App Designer must use that
>> owner/access ID - otherwise you would be creating and dropping
>> the wrong schema. You don't have to call the Owner ID sysadm. But
>> owner ID must still be the access ID.
>> Theoretically, you can set up additional access IDs for use by
>> oprids, so that you could use database resource profiling or
>> techniques that are dependent on the database user name.
>> suggests this is a possible use, but gives you no help on how to
>> it. However, you are then responsible for creating all the
>> and synonyms. This is a considerable administrative overhead.
>> I have to ask why you would want to do this? I can only imagine
>> in the most extreme and contrived of circumstances, and I have
>never done it
>> other than the course of research.
>> David Kurtz
>> Go-Faster Consultancy Ltd.
>> tel: +44 (0)7771 760660
>> fax: +44 (0)7092 348865
>> web: www.go-faster.co.uk
>> Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
>> PeopleSoft: http://blog.psftdba.com, Oracle:
>> PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
>> >-----Original Message-----
>> >From: firstname.lastname@example.org [mailto:email@example.com] On
>> >Behalf Of Pattabhiram Marupudi
>> >Sent: Wednesday, April 23, 2008 4:57 PM
>> >To: firstname.lastname@example.org
>> >Subject: PeopleSoft DBA Forum PeopleSoft database setup with
>> >different ownerid and accessid
>> >Hello PSDBAs,
>> >Seeking assistance with the below question:
>> >Is it possible for a PS database to have a different ownerid and
>> >accessid. (Currently we have both of them set to sysadm) If
>> >you please let me know the procedure how to accomplish this.
>> >Thank you
>> >PeopleSoft for the Oracle DBA is published by Apress - see
>> >The PeopleSoft DBA Forum is managed by http://www.go-faster.co.uk.
>> >Yahoo! Groups Links
>PeopleSoft for the Oracle DBA is published by Apress - see
>The PeopleSoft DBA Forum is managed by http://www.go-faster.co.uk.
>Yahoo! Groups Links
The main business reason fo why we want to segragate the ownerid and
accessid is as below:
We have PeopleSoft AR/Billing application running on DB2 database.
As we have the below instances with ownerid (same as acccessid)
DEV -- devadm
TEST -- tstadm
PROD -- prdadm
When ever we request for Database refresh from PROD to test, it
takes up to 7 days as there is no easy way to change the schema of
an object during a refresh rather than drop and recreate all of the
tables and indexes and reload all the data.
Unlike in Oracle is restore from backup and change the password
in the database copy. If we can split the schema and login account,
we might be able to do that with UDB.
So, what I'm proposing is, if we can make the ownerid same for all
databases - something like "udbadm" and accessids different for each
database, Then it makes the refresh process seamless and efficient.
Can you please let me know your thoughts / suggestions on this...