Re: Creating TS with datafile path from v$datafile

From: Darren Mallette <darren.cuthere_at_mallette.cut_cut_cut.com>
Date: Mon, 21 Oct 2002 15:43:40 GMT
Message-ID: <MgVs9.33082$%h2.2022_at_news02.bloor.is.net.cable.rogers.com>


Try this:

set heading off

set pagesize 0

spool cr_tabspace.sql

select 'CREATE TABLESPACE TESTSPACE DATAFILE ' || chr(39) || SUBSTR(NAME, 1,INSTR(NAME,'\',-1,1)) ||'TESTFILE.DBF' || chr(39) || ' SIZE 5M;'

FROM V$DATAFILE WHERE ROWNUM=1; spool off

_at_cr_tabspace

"Pramod Ramachandran" <pramod_at_rtimes.com> 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
Received on Mon Oct 21 2002 - 17:43:40 CEST

Original text of this message