Check if Tablespace Exists?

[transio]

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:

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]

Also, I will need to know how to find if a USER exists, too. How do I do that?

Thanks !!!

[slimdave]

I wouldn't worry about it. Just issue the drop tablespace command -- you'll just get an error if it doesn't exist.

[transio]

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?

[stecal]

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;

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.

[transio]

Thanks !!!