Error in using bind variables in tablespace creation script
PROMPT Start of Script - Tablespace creation
variable tsp varchar2(300);
PROMPT
ACCEPT dbcs PROMPT 'Enter TNS Name of database :'
PROMPT
ACCEPT syspwddb PROMPT 'Enter password for sys user of database :'
PROMPT
PROMPT Connecting as SYS user to database
CONN sys/&syspwddb@&dbcs AS SYSDBA;
/* CREATE DEMODATA TABLESPACE, SIZE 2 GB WITH EXTENT MANAGEMENT LOCAL*/
PROMPT
PROMPT Create a tablespace named DEMODATA with Datafile named DEMODATA01.DBF Size 2 GB
SELECT 'Ex. of Datafile Location - Name :' || SUBSTR(NAME,1,INSTR(NAME,'/',-1)) ||'DEMODATA01.DBF'
into :tsp
FROM V$DATAFILE
WHERE ROWNUM < 2;
PROMPT PL. WAIT...! TABLE SPACE CREATION IN PROGRESS...!
CREATE TABLESPACE DEMODATA DATAFILE :tsp SIZE 2048 M EXTENT MANAGEMENT LOCAL;
CREATE USER SEED IDENTIFIED BY SEED
DEFAULT TABLESPACE DEMODATA
TEMPORARY TABLESPACE TEMP;
CONN SEED/ENCOVER@&DBCS;
-- End of script
i got the following error.
CREATE TABLESPACE DEMODATA DATAFILE :tsp SIZE 2048 M EXTENT MANAGEMENT LOCAL
*
ERROR at line 1:
ORA-02236: invalid file name
I need to create a tablespace where the datafiles are stored by as shown below.
I am able to get the path and concatenated the new demo data file DEMODATA01.DBF.
But while using the same from tablespace creation i face the following error.
how to rectify or is there any other method.
thanks in advance.