-
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 !!
-
Also, I will need to know how to find if a USER exists, too. How do I do that?
Thanks !!!
-
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.
-
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?
-
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.
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|