Tablespaces and instances? 1

[hmckillop]

First off apologies, my Oracle knowlegde is shocking and limited so if I use the wrong terminology my only excuse will be is I work in SQL Server.

I have 1 physical server which is currently hosting a 200GB database but I have a requirement to have a further 4 databases of roughly the same structure and data (though only a small subset) to be used for different levels of testing of the same application.
Someone suggested to me that I have seperate instances and then which would allow me all the same tablenames, proc names and views etc.
Someone else suggested I have seperate tablespaces again allow me all the same tablenames, proc names and views etc.

Which option will definitely allow me to keep the same database schema and but allow the 4 new test application servers to point to four seperate databases but all residing on the one physical database server? (I cant buy a new database server as i dont have the money, but I can buy new disc space).

Thanks for your help in advance.




"I'm living so far beyond my income that we may almost be said to be living apart

 

[alexhu]

If you have 4 seperate instances, you will have to have 4 SGA's in memory.

How much memory do you have?

You will be able to shut down any one (or more) of the 4 at any time for whatever reason.

If you have each 'test system' in a seperate schema, then you will only have one SGA, one instance and one database to worry about.

However, should you need to shut it down, then it all comes down.

Depends on what you want to do really

Alex

 

[hmckillop]

Thanks for your reply, I think the 4 schema will be the choice as I dont want complaints about memory on the box.

One last question, how does the client application differentiate the schemas when connecting. With a different instance I think you can assign port numbers (or at least in SQL Server I can), how is this managed with Schemas

Thanks again.

"I'm living so far beyond my income that we may almost be said to be living apart

 

[alexhu]

Each schema is effectively a user, so there will be a different id/password for each one.

Alex

 

[Turkbear]

Hi,
Well, actually, not always ( a user can have access to many schemas).
It is a permissions issue -
One way is to create roles ( with whatever rights are needed ) for each schema and assign users to the appropriate role..When the app logs in as that user the schema assigned by the role will be the one it can access..





To Paraphrase:"The Help you get is proportional to the Help you give.."