This discussion is archived
4 Replies Latest reply: Jan 7, 2011 4:28 PM by William Robertson RSS

Create a procedure to dynamically add a tablespace

01sar01 Newbie
Currently Being Moderated
Hi,

I want to create a procedure that will accept two parameters. The name of a tablespace and the name of its datafile, with the path.
i don't know if this will work...currently it gives an error. Is it something that cannot be done using dynamic sql like this or is there another method.

Thanks for your help!

create or replace procedure create_tbs(tbs_name varchar2, filename varchar2)
is
str_tbs varchar2(500);
v_tbs varchar2(100);
begin
str_tbs := 'create tablespace ' || tbs_name || ' datafile '''||filename|| ''' size 1m' ;

select tablespace_name into v_tbs
from sys.dba_tablespaces
where tablespace_name=tbs_name;

dbms_output.put_line('tablespace exists');

exception

when no_data_found then
execute immediate str_tbs;
dbms_output.put_line('tablespace was created');
end;
/


SQL> execute create_tbs('mytab','/u01/app/oracle/oradata/orcl/mytab01.dbf')
ERROR....No such file or directory. (The directory does exist !!)
  • 1. Re: Create a procedure to dynamically add a tablespace
    828259 Newbie
    Currently Being Moderated
    01sar01 wrote:
    Hi,

    I want to create a procedure that will accept two parameters. The name of a tablespace and the name of its datafile, with the path.
    i don't know if this will work...currently it gives an error. Is it something that cannot be done using dynamic sql like this or is there another method.

    Thanks for your help!

    create or replace procedure create_tbs(tbs_name varchar2, filename varchar2)
    is
    str_tbs varchar2(500);
    v_tbs varchar2(100);
    begin
    str_tbs := 'create tablespace ' || tbs_name || ' datafile '''||filename|| ''' size 1m' ;
    You can't do select like this in pl/sql, as the tbs_name is passed as a parameter change to dynamic and make sure you have access to the dba_tablespaces table too.

    --> select tablespace_name into v_tbs --
    --> from sys.dba_tablespaces --
    --> where tablespace_name=tbs_name;--
    >
    dbms_output.put_line('tablespace exists');

    exception

    when no_data_found then
    execute immediate str_tbs;
    dbms_output.put_line('tablespace was created');
    end;
    /


    SQL> execute create_tbs('mytab','/u01/app/oracle/oradata/orcl/mytab01.dbf')
    ERROR....No such file or directory. (The directory does exist !!)
  • 2. Re: Create a procedure to dynamically add a tablespace
    HiteshNirkhey Journeyer
    Currently Being Moderated
    hi i think you have to create a directory for that
    create directory TST_DATA_DIR as '/u01/app/oracle/oradata/orcl/mytab01.dbf';
    and after that
    execute create_tbs('mytab',TST_DATA_DIR)
    Regards
    Hitesh Nirkhey
  • 3. Re: Create a procedure to dynamically add a tablespace
    01sar01 Newbie
    Currently Being Moderated
    The directory can reference only a directory on the OS, i cannot attach the filename to it. Maybe with three parameters. tablespace_name, directory_name and then filename.

    Thanks
  • 4. Re: Create a procedure to dynamically add a tablespace
    William Robertson Oracle ACE
    Currently Being Moderated
    The syntax looks OK. Did you try having the procedure display the generated CREATE TABLESPACE command and then copying & pasting it manually onto the command line to test it?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points