4 Replies Latest reply: Jan 7, 2011 6:28 PM by William Robertson RSS

    Create a procedure to dynamically add a tablespace

    01sar01
      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
          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
            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
              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
                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?