cancel
Showing results for 
Search instead for 
Did you mean: 

oracle question

SOLVED
Go to solution
Pieter_5
Advisor

oracle question

Hi,

Currently I have two users on the same hp-ux box who use the same database. What I would like to have is that user a connects to the same database as the other user but that user a is connected to another segment or tablespace than user b. By doing this user a will be able to make changes to his part of the database while the other user won't notice it. I have to use this kind of solution because the ORACLE_SID is hardcoded in a lot of programs and it is impossible to change it.
8 REPLIES
Michael Schulte zur Sur
Honored Contributor

Re: oracle question

Hi Andre,

user a and b are unix logins?
What about giving them different Oracle users
and each Oracle User a different default tablespace. What user are the programs using?

Michael
bob hollis
Frequent Advisor

Re: oracle question

I guess I don't quite understand your question.
do both these users have the same oracle id?
or are there different oracle IDs owning different tables?
If it's the second case - they will be independent of each other - you can separate their objects into different tablespaces - but even if you don't, they would each have different tables - i.e. a.tab1 b.tab1 would be different tables.

If they both connect as the same oracle user - or if they both update tables owned by the same user - they can't be separated.

you can give an oracle user its own default tablespace if that helps

create user billy identified by goat default tablespace BARN temporary tablespace TEMP
quota unlimited on BARN;

create user chicken identified by little default tablespace COOP temporary tablespace TEMP
quota unlimited on COOP;
Indira Aramandla
Honored Contributor
Solution

Re: oracle question

Hi,

From you question I understand that you have users A and user B who are two UNIX accounts. These two users A & B logon to the same database. And whne user A is modifying any tables / data user B should not be disturbed / unaware. If this is what you wanted then you can do the following.

Define user A and user B in the oracle database as two different users with different tablespaces as their default tablespaces. then give grants and quotas on the respective tablespaces.
Eg:.

Create user A identifed by
default tablespace temporary tablespace temp;

ALTER USER QUOTA [K/M] ON

Create user B identifed by
default tablespace temporary tablespace temp;

ALTER USER QUOTA [K/M] ON


Now when the user A logon to the database and creates any objects then they will be in tablespace-A and likewise with the user B they will be in tablepsace B.

I hope this helps.

IA
Never give up, Keep Trying
Steven E. Protter
Exalted Contributor

Re: oracle question

I really like the innovative solutions posted into this thread. They will work.

Over the long term however, if both users have dba rights, they can still step on each others data.

If you decide to examine your system to scope out the project of different instances, you can start as follows:



find / -exec ls -1 '$ORACLE_SID' {} \;

This will at least get you a list of the number of scripts that have the SID hard coded.

I'm sure some of the scripts will have to be replicated, and some will have to be modified. sed is a good utility for doing that.

I'm attacing a script that lets you read a filelist and change contents.

With the right tools, the job might not be as big as you think.

From a purely arrogant(sorry about that) IT view, oracle data that is unrelated, and not part of the same application doesn't belong in the same instance. There are lots reasons, including the need to someday tune the database for performance that factor into that decision.

regards,

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Hein van den Heuvel
Honored Contributor

Re: oracle question


SEP> From a purely arrogant(sorry about that) IT view, oracle data that is unrelated, and not part of the same application doesn't belong in the same instance.

yabutt... Oracle has a lot of 'stuff' like 200 MB system space, 5 - 10 background processes, REDO, UNDO, TEMP, that can all readily be shared and make for a MUCH more efficient total system usage.

I can not help but think that the targetted multiple users as described are going to be 'modest' users with say less than 30 tables and less than 1GB data. I have _nothing_ to base this on other then the tone of the question, so i may well be wrong. If I'm write though, then they'll happily live in the same database, even though that is not 'clean'.

fwiw,
Hein.
T G Manikandan
Honored Contributor

Re: oracle question

create different login for each user at the database level i.e. different schema.

By this way each user can work independently as the unix login.

Make sure that you do not give additional rights to the users except for creating/manipulating their own schema.

Thanks
T G Manikandan
Honored Contributor

Re: oracle question

create different login for each user at the database level i.e. different schema.

By this way each user can work independently as the unix login.

Make sure that you do not give additional rights to the users except for creating/manipulating their own schema.

Thanks
Yogeeraj_1
Honored Contributor

Re: oracle question

hi,

one way will be to create two users each one having "create table" priviledge.

You can then create the tables in each user's schema which will be completely independent of each other.

The tablespace can be the same or different and they will not affect anyone of them.

Are you using public synonyms?

if not, that would be easy to keep two version of the "data".

If you want table level restrictions, you can grant and revoke priviledges accordingly.

All these can be done irrespective of the current setting for ORACLE_SID.

hope this helps!

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)