Re: Creating TS with datafile path from v$datafile

pramod_at_rtimes.com (Pramod Ramachandran) wrote in message news:<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

You could try

select 'CREATE TABLESPACE TESTSPACE '||chr(10)|| 'DATAFILE' ||f.fname||' SIZE 5m '||';'
from

     ( select '''' || substr(name,1,25)||'TESTFILE.dbf''' fname
       from v$datafile where rownum=1 ) f, dual;

... and spool it to text file. Received on Mon Oct 21 2002 - 18:17:30 CEST