Script creating tablespaces and users using variables

[albitzt]

I created the following script which works almost perfectly. I wanted a section at the top to modify the physical and logical tablesapce names and teh usernames. It works fine, with the exception of when you run it, it prompts me to enter the 'DATFILE_PHYSICAL' value. Once that is done, the other XX_PHYSICAL variables work fine.

Can anyone explain to me please, why it is prompting for the value of 'DATFILE_PHYSICAL' variable?

Thanks.
________________________________

DEF DATFILE_LOGICAL=PMDB_DAT1
DEF INDEXFILE_LOGICAL=PMDB_NDX1
DEF LOBFILE_LOGICAL=PMDB_LOB1

DEF DATFILE_PHSYICAL=F:\app\Administrator\oradata\talbitz010311\PMDB_DAT1.dbf
DEF INDEXFILE_PHYSICAL=F:\app\Administrator\oradata\talbitz010311\PMDB_ndx1.dbf
DEF LOBFILE_PHYSICAL=F:\app\Administrator\oradata\talbitz010311\PMDB_lob1.dbf

DEF SCHOWNER="ADMIN"
DEF PRIVLOGIN="PRIV"
DEF PUBLOGIN="PUB"

prompt creating PMDB tablespaces

prompt creating PMDB data tablespace
create tablespace &DATFILE_LOGICAL
datafile '&DATFILE_PHYSICAL' size 700M reuse autoextend on next 200M
default storage (
initial 100K next 100K
minextents 1 maxextents 121
pctincrease 0)
online;

prompt creating PMDB index tablespace

create tablespace &INDEXFILE_LOGICAL
datafile '&INDEXFILE_PHYSICAL' size 700M reuse autoextend on next 200M
default storage (
initial 100K next 100K
minextents 1 maxextents 121
pctincrease 0)
online;


prompt creating PMDB LOB tablespace

create tablespace &LOBFILE_LOGICAL
datafile '&LOBFILE_PHYSICAL' size 700M reuse autoextend on next 200M
default storage (
initial 100K next 100K
minextents 1 maxextents 121
pctincrease 0)
online;

prompt completed the creation of PMDB tablespaces

prompt creating PMDB users

create user &SCHOWNER
identified by &SCHOWNER
temporary tablespace temp
default tablespace &DATFILE_LOGICAL;

grant dba to admuser with admin option;

create user &PRIVLOGIN
identified by &PRIVLOGIN
temporary tablespace temp
default tablespace &DATFILE_LOGICAL;

grant connect to &PRIVLOGIN;
grant create any procedure to &PRIVLOGIN;
grant execute any procedure to &PRIVLOGIN;

create user &PUBLOGIN
identified by &PUBLOGIN
temporary tablespace temp
default tablespace &DATFILE_LOGICAL;

grant connect to &PUBLOGIN


prompt completed creating PMDB users

 

[karluk]

You misspelled "DATFILE_PHYSICAL" in the def statement as "DATFILE_PHSYICAL". As a result, when you later reference the correctly spelled variable, it hasn't been assigned a value yet and prompts you for input.

 

[albitzt]

I looked at this for a good ten minutes and overlooked that every time.

-Thanks karluk.