Creating TS with datafile path from v$datafile
From: Pramod Ramachandran <pramod_at_rtimes.com>
Date: 21 Oct 2002 01:22:20 -0700
Message-ID: <6616e304.0210210022.12e781a1_at_posting.google.com>
Date: 21 Oct 2002 01:22:20 -0700
Message-ID: <6616e304.0210210022.12e781a1_at_posting.google.com>
Hi,
I need to create a tablespace with the datafile path read from a query as follows.
CREATE TABLESPACE TESTSPACE
DATAFILE ''''||(SELECT SUBSTR(NAME, 1,INSTR(NAME,'\',-1,1)) FROM V$DATAFILE WHERE
ROWNUM=1) ||'TESTFILE.DBF'||'''' SIZE 5M;
The statement
SELECT ''''||(SELECT SUBSTR(NAME, 1,INSTR(NAME,'\',-1,1)) FROM V$DATAFILE WHERE
ROWNUM=1) ||'TESTFILE.DBF'||'''' FROM DUAL;
returns the string 'E:\ORADATA\OW3P\TESTFILE.DBF', which is the path
to be given for the datafile.
But I cant use this statement in the CREATE TABLESPACE command. Why is this ?
Anyone please reply.
Thanks and regards
Pramod Received on Mon Oct 21 2002 - 10:22:20 CEST