My CEO wants to have two instances access one database. These are two research projects with identical table names but different project codes. The tables will be populated by the two separate projects. Reports will be generated for the projects based on the project codes. The users that populate the tables should not be able to access the data from the other project.
I have not done this before and I'm not quite sure how to start. I guess someone has done it so I need as much help as I can get please. Even if you've not really done it, I need some suggestions based on your Oracle experience.
You can't use 2 instance for a single database unless you're implementing Oracle Parallel Server.
You can setup two database in one server and establish a database link. Have a replication for necessary updates of two database.
But from my understanding to your problem you might just need to
grant INSERT privs of the table for the users who will input the data.
And grant SELECT privs of the table who will access the data of the table.
Looking at your requirements, why don't you just create a different schema as pando suggested, or create a set of roles with the access privileges and grant those roles based upon the user requirements.
Grant role_IUD-AB to project-A
Grant role_IUD-AB to project-B
Now go ahead and set up the table level triggers with AFTER insert,update, delete and fire them on the project-A.schema,project-B.schema to make the changes in the project-AB.schema
Now you can give full/select privileges to the person who ever want to use this AB schema to view objects.
But remember here you would be maintaining two sets of redundant information.
if I had to create one instance per project I would had to create 30 isnatnces in the first shop I was, just create one schema per project and you are set, standby database hmmm I wonder too! It's for HA nothing more!
Reply With Quote
Bookmarks