Home » SQL & PL/SQL » SQL & PL/SQL » looking for sql script guru. (oracle 9i , solaris)
looking for sql script guru. [message #429899] Fri, 06 November 2009 03:40 Go to next message
koff10
Messages: 58
Registered: December 2006
Location: france
Member
Hi All,

I got two oracle database ( schema) data1 and data2 with tables.
when data1 is currently in use (production), data2 is stanby mode (without automatic update)
And when data2 is in use data1 is in standby mode.
Data1 to data2 is manually switch.
I' m just looking for a sql or pl/sql script to test which database is in use before manually update.

Any ideas welcome ?

thanks

koff10
Re: looking for sql script guru. [message #429910 is a reply to message #429899] Fri, 06 November 2009 04:32 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Have a look at the CONTROLFILE_TYPE and DATABASE_ROLE columns in v$database
Re: looking for sql script guru. [message #429929 is a reply to message #429910] Fri, 06 November 2009 06:07 Go to previous messageGo to next message
koff10
Messages: 58
Registered: December 2006
Location: france
Member
Thanks for answer.

But may be my posting is not very clear.
I'm saying that data1 and data2 are schema (user) not oracle instance.
koff10




JRowbottom wrote on Fri, 06 November 2009 11:32
Have a look at the CONTROLFILE_TYPE and DATABASE_ROLE columns in v$database

Re: looking for sql script guru. [message #429961 is a reply to message #429929] Fri, 06 November 2009 09:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think 'not very clear' is an understatement of the opacity of your post - You should patent it, and sell it to X-ray technicians to use as lightweight radiation shielding - it's that opaque.

Ok, so you've got two schema's and some sort of manual process to replicate data between them.

No-one who does not work with you knows how you determine which of the schemas is being used.

It's possible that each schema lives in its own tablespace, and you have one of the tablespaces set to read only.

It's possible that you have some sort of script that grants nd revokes privileges on the schema objects.

It's possible that there's a pair of flags in the IT department saying DATA1 and DATA2, and whichever one is flying from the flagpole is the current production system.

We don't know, and the reason for that is that you haven't told us.

As you write your reply to this, assume that we know NOTHING about your problem other than what you have told us. Then ask yourself 'If I knew only this information, would I understand the problem?'

Re: looking for sql script guru. [message #429964 is a reply to message #429961] Fri, 06 November 2009 09:35 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
JRowbottom wrote on Fri, 06 November 2009 15:24
I think 'not very clear' is an understatement of the opacity of your post - You should patent it, and sell it to X-ray technicians to use as lightweight radiation shielding - it's that opaque.

Almost spat my coffee onto my screen (I wonder if there's a smiley for that) One of the funniest things I've read here for a long time. I will have to use that within the next fornight or I will have failed
Re: looking for sql script guru. [message #429967 is a reply to message #429899] Fri, 06 November 2009 10:01 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I' m just looking for a sql or pl/sql script to test which database is in use before manually update.
The answer is strictly an application issue.
Oracle RDBMS does not know or care which schema is "active".
Both schemas simply exist at the instance level.

So ask the application architect what is the determining criteria to decide the active schema.
Previous Topic: updating table banktransactions with pl-sql
Next Topic: Date Format
Goto Forum:
  


Current Time: Fri Dec 09 15:11:24 CST 2016

Total time taken to generate the page: 0.13981 seconds