JDBC execution on several tablespaces

[chenn]

hi.
i'd like to execute several statements on a variety of different tablespaces using only one user with "superprivileges" or whatever. (database is oracle 9i)
my dream-solution would be a "role"/user change issued from a jdbc call or a (temporary) change of the default-tablespace of my executing user.
the idea is to execute the same queries on different tablespaces and to prevent rewriting the queries for this scenario. the queries itself will create temporary tables and write their results in the tablespaces were they were executed.
this feature is desperatly needed to use only one connection (and one user) to the database.
a work-around might be alter session set current schema 'xyz' were 'xyz' represents my tablespaces.

any other hints, suggestions, comments welcome.



regards
chenn

 

[siberian]

You may be better off creating some oracle views that pull your data into a single virtual tablespace. Then you can just work with the views.

 

[dsanchez2]

In Oracle access to tables is not controlled by "tablespaces", it is controlled by "schemas" (i.e. users). To access a table from another schema/user you must prefix the schema name. For example: xxx.mytable

Alternative, you can create SYNONYM for these tables.

Also the owner of the tables must grant access to these tables.