Monday, June 10, 2019

Tip Example to Create Tablespace in the Same Location


 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.


sample sql we use is 
declare v_datafile VARCHAR2(100);
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';


No comments: