Can you use oracle table spaces like variable

I am new to Oracle, I have two tablespaces one for dev and one for live.

Is t开发者_开发技巧here a way that I can have a variable on a Package header that contains a tablespace name, which can then be used from within the procedures defined in the package header?

I need to do this as I have one tableSpace (TableSpaceA) that needs to query tables in TableSpaceB via procedures. The Dev TableSpaceB has a different table space name to live therefore I was hoping I could declare in the Package header a variable like

Tablespace temp = "TableSpaceName"

Then

Select * from temp.TableName

And finally change "TableSpaceName" when i roll my changes out to the live environment?

Tablespaces are an administrative thing: they are a way of logically grouping storage without the need to worry about filepaths or other administrivia.

From your revised question it is obvious that Tony is right and what you mean is schema.

A schema is the set of objects owned by a user. When a procedure owned by User A needs to reference an object owned by Schema B there are two ways of doing this. The first way is to hardcode the schema name.

select * 
from user_b.emp;

This is usually fine but will not work in the scenario you describe, where the other schema has a different name different name in other environments (C rather than B).

The way around this problem is to use synonyms.

select * 
from not_my_emp;

In DEV the synonym would be:

create synonym not_my_emp for user_b.emp
/

whereas in production it would be

create synonym not_my_emp for user_c.emp
/

The table could even have a different table in production, it doesn't matter. The synonym acts as an interface to shield our objects from the untidy details of other schemas.

Note that a synonym does not grant privileges on the underlying object. This means that USER_B has to grant privileges on EMP in Development and USER_C has to grant privileges in Production.