This discussion is archived
6 Replies Latest reply: Jan 22, 2013 9:54 AM by amitavachatterjee1975 RSS

Dynamic alter tablespace statement to add datafiles

amitavachatterjee1975 Newbie
Currently Being Moderated
Hi Gurus,

I need a PL/SQL stored procedure which will accept a datafile name as parameter and dynamically create and execute "alter tablespace" command to add this passed datafile dynamically.

Thanks
Amitava.
  • 1. Re: Dynamic alter tablespace statement to add datafiles
    sb92075 Guru
    Currently Being Moderated
    amitavachatterjee1975 wrote:
    Hi Gurus,

    I need a PL/SQL stored procedure which will accept a datafile name as parameter and dynamically create and execute "alter tablespace" command to add this passed datafile dynamically.

    Thanks
    Amitava.
    we see that you have a keyboard & know how to type,
    so what prevents you from simply writing a procedure that meets your requirements?

    You will need to utilize EXECUTE IMMEDIATE
  • 2. Re: Dynamic alter tablespace statement to add datafiles
    Richard Harrison . Expert
    Currently Being Moderated
    Hi,
    Use oracle managed files then you don't have to worry about file names.

    Regards,
    Harry
  • 3. Re: Dynamic alter tablespace statement to add datafiles
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    amitavachatterjee1975 wrote:
    Hi Gurus,

    I need a PL/SQL stored procedure which will accept a datafile name as parameter and dynamically create and execute "alter tablespace" command to add this passed datafile dynamically.

    Thanks
    Amitava.
    Could You please explain why you need to use PL/SQL to add Datafiles ? if you need easy way to add them instead of command line you can use OEM
  • 4. Re: Dynamic alter tablespace statement to add datafiles
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    There was a recent forums discussion on running CREATE TABLESPACE commands from a Stored Procedure.


    Hemant K Chitale
  • 5. Re: Dynamic alter tablespace statement to add datafiles
    Girish Sharma Guru
    Currently Being Moderated
    See this demo : 11.2.0.1 Windows
    Before running this demo, I logged into SYS account and said :

    grant create tablespace to scott;
    grant select on dba_tablespaces to scott;
    grant select on dba_data_files to scott;
    grant alter tablespace to scott;

    even scott user is having DBA role. Then I login as scott user and :
    set serveroutput on;
    create or replace procedure create_tbs(tbs_name in varchar2, filename in varchar2) as
    rt dba_tablespaces%rowtype;
    str_tbs varchar2(500);
    v_tbs varchar2(100);
    begin
    str_tbs := 'create tablespace ' || tbs_name || ' datafile '''||filename|| ''' size 1m' ;
    dbms_output.put_line(str_tbs);
    select * into rt from dba_tablespaces where tablespace_name=upper(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;
    /
    
    select tablespace_name from dba_tablespaces;
    TABLESPACE_NAME
    ------------------------------
    SYSTEM
    SYSAUX
    UNDOTBS1
    TEMP
    USERS
    EXAMPLE
    PERFSTAT
    
    7 rows selected.
    
    SQL> exec create_tbs('TESTTBS','c:\data\test.dbf');
    create tablespace TESTTBS datafile 'c:\data\test.dbf' size 1m
    tablespace was created
    
    PL/SQL procedure successfully completed.
    
    SQL> exec create_tbs('SYSAUX','c:\data\test.dbf');
    create tablespace SYSAUX datafile 'c:\data\test.dbf' size 1m
    tablespace exists
    
    PL/SQL procedure successfully completed.
    
    SQL> drop tablespace testtbs including contents and datafiles;
    
    Tablespace dropped.
    
    SQL>Now I am going to create add datafile procedure, which is your question :
    
    column file_name for a50;
    column tablespace_name for a20;
    select file_name,tablespace_name from dba_data_files;
    FILE_NAME                                          TABLESPACE_NAME
    -------------------------------------------------- --------------------
    E:\APP\SERVERROOM\ORADATA\ORCL\USERS01.DBF         USERS
    E:\APP\SERVERROOM\ORADATA\ORCL\UNDOTBS01.DBF       UNDOTBS1
    E:\APP\SERVERROOM\ORADATA\ORCL\SYSAUX01.DBF        SYSAUX
    E:\APP\SERVERROOM\ORADATA\ORCL\SYSTEM01.DBF        SYSTEM
    E:\APP\SERVERROOM\ORADATA\ORCL\EXAMPLE01.DBF       EXAMPLE
    E:\APP\SERVERROOM\ORADATA\ORCL\PERFSTAT            PERFSTAT
    
    6 rows selected.
    
    create or replace procedure alter_tbs(tbs_name in varchar2, filename in varchar2) as
    rt dba_data_files%rowtype;
    str_tbs varchar2(500);
    v_tbs varchar2(100);
    begin
    str_tbs := 'alter tablespace ' || tbs_name || ' add datafile '''||filename|| ''' size 10m' ;
    dbms_output.put_line(str_tbs);
    select * into rt from dba_data_files where tablespace_name=upper(tbs_name) and file_name=upper(filename);
    dbms_output.put_line('Datafile already exists.');
    exception
    when no_data_found then
    execute immediate str_tbs;
    dbms_output.put_line('Datafile added.');
    end;
    /
    
    SQL> exec alter_tbs('users','E:\APP\SERVERROOM\ORADATA\ORCL\USERS01A.DBF');
    alter tablespace users add datafile 'E:\APP\SERVERROOM\ORADATA\ORCL\USERS01A.DBF' size 10m
    Datafile added.
    
    PL/SQL procedure successfully completed.
    
    SQL> column file_name for a50;
    SQL> column tablespace_name for a20;
    SQL> select file_name,tablespace_name from dba_data_files;
    
    FILE_NAME                                          TABLESPACE_NAME
    -------------------------------------------------- --------------------
    E:\APP\SERVERROOM\ORADATA\ORCL\USERS01.DBF         USERS
    E:\APP\SERVERROOM\ORADATA\ORCL\UNDOTBS01.DBF       UNDOTBS1
    E:\APP\SERVERROOM\ORADATA\ORCL\SYSAUX01.DBF        SYSAUX
    E:\APP\SERVERROOM\ORADATA\ORCL\SYSTEM01.DBF        SYSTEM
    E:\APP\SERVERROOM\ORADATA\ORCL\EXAMPLE01.DBF       EXAMPLE
    E:\APP\SERVERROOM\ORADATA\ORCL\PERFSTAT            PERFSTAT
    E:\APP\SERVERROOM\ORADATA\ORCL\USERS01A.DBF        USERS
    
    7 rows selected.
    
    SQL> alter tablespace users drop datafile 'E:\APP\SERVERROOM\ORADATA\ORCL\USERS01A.DBF';
    
    Tablespace altered.
    
    SQL> select file_name,tablespace_name from dba_data_files;
    
    FILE_NAME                                          TABLESPACE_NAME
    -------------------------------------------------- --------------------
    E:\APP\SERVERROOM\ORADATA\ORCL\USERS01.DBF         USERS
    E:\APP\SERVERROOM\ORADATA\ORCL\UNDOTBS01.DBF       UNDOTBS1
    E:\APP\SERVERROOM\ORADATA\ORCL\SYSAUX01.DBF        SYSAUX
    E:\APP\SERVERROOM\ORADATA\ORCL\SYSTEM01.DBF        SYSTEM
    E:\APP\SERVERROOM\ORADATA\ORCL\EXAMPLE01.DBF       EXAMPLE
    E:\APP\SERVERROOM\ORADATA\ORCL\PERFSTAT            PERFSTAT
    
    6 rows selected.
    
    SQL>
    Similar thread : Create a procedure to dynamically add a tablespace

    Regards
    Girish Sharma

Legend

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