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

SELECT 'CREATE TABLESPACE '||tablespace_name||' DATAFILE '||file_name||';' from dba_data_files;

However, if you have multiple datafiles you need to proceduralize it. In the scripts set on www.robonerd.com in the download discusison area in the free stuff area I have a script called tbsp_rct81.sql that should help.

Mike Ault
www.robonerd.com Received on Mon Oct 21 2002 - 17:54:39 CEST