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 !!!