Tip Example to Create Tablespace in the Same Location

Requirement:

 We need to create a new tablespace. Default datafile will be created on Oracle DB Home if  db_create_file_dest is not set . We don't wanna that happen, we would like to use the existing datafile location for the new tablespace.

Solution:

sample sql we use is 

declare v_datafile VARCHAR2(100);

begin

select  ((select  regexp_substr(name,'^.*/\')  from v$datafile where rownum = 1)||'livesqldata01.dbf')

into v_datafile   from dual;

execute immediate 'create tablespace LIVESQL datafile '''||v_datafile||''' size 50M reuse autoextend on';

end;

/