Switching schemas in Oracle makes no sense, because schemas are tied to the user. In fact you don't login to oracle with a username but with a schema name. Schema and username are the same things. It is not like MySQL where you have Databases that denote these object groups and are separate from users.
However, this is not something that is confined. If you track which schemas you want to access, and have the proper grants issued.. You can always access any object with the following notion: schema_name.object_name This is irregardless of which schema you are logged into. However, the proper grants need to be issued and can only be issued if you are logged into the schema that owns those objects. For example.. Table A is in Schema X and Table B is in schema Y.. X wants to access B and Y wants to access A.. So you have to login as Y and GRANT SELECT,UPDATE,INSERT,DELETE ON B TO X; Then login as X and GRANT SELECT,UPDATE,INSERT,DELETE ON A TO Y; Then if you are logged in as X you can do SELECT * FROM Y.B; And logged in as Y SELECT * FROM X.A; Hope that helps, Carl Furst On 11/12/12 3:32 PM, "John Scoles" <byter...@hotmail.com> wrote: > > >---------------------------------------- >> Date: Mon, 12 Nov 2012 19:04:56 +0000 >> From: martin.ev...@easysoft.com >> To: dbi-users@perl.org >> CC: kevin.k...@gmail.com >> Subject: Fwd: DBD::Oracle Schema different than User question >> >> Hi Kevin, >> >> I've forwarded your email on to the dbi-users list. See >> http://dbi.perl.org and look at the support page. Sorry for top posting >> but my email client is having some sort of fit with your email. I don't >> have any issue with any well formed patch to set the schema but I'll >> wait to see what others say as personally I've never had to change it. >> >> Martin >> >> >> -------- Original Message -------- >> Subject: DBD::Oracle Schema different than User question >> Date: Mon, 12 Nov 2012 13:04:05 -0500 >> From: Kevin L. Kane <kevin.k...@gmail.com> >> To: Tim Bunce <t...@cpan.org>, Yanick Champoux <yan...@cpan.org>, >> "Martin J. Evans" <mjev...@cpan.org> >> >> Hi all, >> I am running into a problem and was planning on modifying my local >> DBD::Oracle to add support for a "schema=<mySchema>" construct in the >> connect string. Specifically, I want to connect as user X but set >> current_schema to Y. > >Sounds like someone tried to creata a MySQL type DB schema on Oracle >again;) > >Anyway if you want to log in wiht 1 generic user then change the 'schema' > just issue >an sql something like this > >exec sql execute immediate ALTER SESSION SET CURRENT_SCHEM=sss > > > >> Another solution i've toyed with is having a >> trigger that switches my schema when I log in but I need to do this >> for a lot of different schemas and I will always be the same user. It >> seems weird to me that support for this isn't included in DBD::Oracle >> currently. >> >> If I do this in a sane way are you at all interested in the patch? > >IF if you come up with a patch we will have a look at it. > >> Am I just missing something and this functionality is already there? Or >> should this functionality not exist in the first place and why? >> > >I don't think it should be there because what oracle thinks a schema is >different thatn other DB?? >A schema to an Oracle DB is the set of all tables and other objects owned >by a user account if I am not mistaken >not the grouping of all the table under an app or alike > >Anyway off the top of head I do not think there is a way with OCI to >change the shcema without loging in again as there > is only OCISession begin OCIlogin2 neither take any shcema params??? > > >Cheers >John >> Thanks, >> Kevin >> -- >> Kevin L. Kane >> kevin.kane at gmail.com >> >> >> >> >> >> ********************************************************** MLB.com: Where Baseball is Always On
