oracle - Sqlplus parameters and variables with default values -

problem

i have sql scripts may use different tablespaces different database users.

in order remain flexible table creation i'd keep 1 script , apply various users. purpose have this:

tablespaces:

create tablespace my_tablespace datafile 'my_tablespace.dat' size 40m online;  create tablespace my_indexspace datafile 'my_indexspace.dat' size 40m online;  

and table creation script:

define default_tablespace = 'my_tablespace'; define default_indexspace = 'my_indexspace';  drop table test_table;  create table test_table ( id number )  tablespace &default_tablespace; create index my_index on test_table( id) tablespace &default_indexspace; 

i. e. can't set default tablespace user, because index uses different tablespace.

question

is possible override definition of default_tablespace , default_indexspace depending on e. g. environment variable?

something like:

define default_tablespace = isenviromentvariableset( 'other_tablespace') ? getenvironmentvariable( other_tablespace) : 'my_tablespace'; 

that way use different tablespaces whenever invoke script externally utility , @ same time keep default tablespace.

thank help!

in ddl operation (create, drop, etc.) u can't use variables.

easy way use pl/sql anonymous block this.

    declare       my_tabable_space varchar2(100) default 'my_some_tablespace';       other_tablespace varchar2(100);     begin       dbms_system.get_env('other_tablespace',other_tablespace);       if other_tablespace not null      my_tabable_space := other_tablespace ;       end if;        execute immediate 'create table test_table ( id number )  tablespace' || my_tabable_space;     end; / 

and select env variable u can use dbms_system.get_env ('name of variable', my_variable) package need dba right (i think.. :-) )