DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Check if Tablespace Exists?

  1. #1
    Join Date
    May 2003
    Location
    Miami, FL
    Posts
    22

    Check if Tablespace Exists?

    Hi guys,

    I'm writing a schema for automation of a tablespace creation, and I want to (at the beginning) check if it exists, then drop it if it does prior to creating it.

    Something like:
    Code:
    IF EXISTS(
        SELECT id 
        FROM sys.tablespaces
        WHERE name = 'my_table_space_name')
    
            BEGIN
                DROP TABLESPACE my_table_space_name INCLUDING CONTENTS;
            END;
    
    CREATE TABLESPACE my_table_space_name
    ....
    My question is how do I determine if the tablespace exists? (top part). I've been researching this for an hour now and can't find documentation on the sys schema.

    Thanks in advance for your help !!

    Transio - Home | About | Design | Company

  2. #2
    Join Date
    May 2003
    Location
    Miami, FL
    Posts
    22
    Also, I will need to know how to find if a USER exists, too. How do I do that?

    Thanks !!!
    Transio - Home | About | Design | Company

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253

    Re: Check if Tablespace Exists?

    I wouldn't worry about it. Just issue the drop tablespace command -- you'll just get an error if it doesn't exist.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    May 2003
    Location
    Miami, FL
    Posts
    22
    The problem is that I can't accept an exception here.

    I'm running the script from the context of an application.

    Does anyone know how to check?
    Transio - Home | About | Design | Company

  5. #5
    Join Date
    May 2002
    Posts
    2,645
    DECLARE
    v_exists number;
    BEGIN
    select count(*) into v_exists
    from dba_tablespaces
    where tablespace_name = 'TOOLS';
    IF v_exists > 0 THEN
    dbms_output.put_line('tablespace exists');
    ELSE
    dbms_output.put_line('tablespace does not exist');
    END IF;
    END;

    Code:
    SQL> DECLARE
      2  v_exists number;
      3  BEGIN
      4  select count(*) into v_exists
      5  from dba_tablespaces
      6  where tablespace_name = 'TOOLS';
      7  IF v_exists > 0 THEN
      8  dbms_output.put_line('tablespace exists');
      9  ELSE
     10  dbms_output.put_line('tablespace does not exist');
     11  END IF;
     12  END;
     13  /
    tablespace exists
    
    PL/SQL procedure successfully completed.
    
    
      1  DECLARE
      2  v_exists number;
      3  BEGIN
      4  select count(*) into v_exists
      5  from dba_tablespaces
      6  where tablespace_name = 'TOOLSSS';
      7  IF v_exists > 0 THEN
      8  dbms_output.put_line('tablespace exists');
      9  ELSE
     10  dbms_output.put_line('tablespace does not exist');
     11  END IF;
     12* END;
    SQL> /
    tablespace does not exist
    
    PL/SQL procedure successfully completed.

  6. #6
    Join Date
    May 2003
    Location
    Miami, FL
    Posts
    22
    Thanks !!!
    Transio - Home | About | Design | Company

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width