Re: Creating TS with datafile path from v$datafile
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