Check for Tablespace Exist
505977Nov 6 2006 — edited Nov 6 2006How can I check to see if a tablespace exists?
I tried this and works by itself but it doesn't work in a script.
select Count(*) into v_cnt from SYS.TS$ Where NAME='mytestspace';
Here is the script. v_cnt ends up being 0 when the script is run and the tablespace exists. I am running the script under SYSTEM login.
spool createuser.log
set serveroutput on
declare
v_cnt NUMBER;
v_sql VARCHAR2(1000);
begin
select Count(*) into v_cnt from SYS.TS$ Where NAME='perform51';
dbms_output.put_line (v_cnt);
if v_cnt = 0 then
v_sql := 'CREATE TABLESPACE PERFORM51 DATAFILE ''/oracle/oradata/devsrvr/perform51.dbf'' SIZE 25M EXTENT MANAGEMENT LOCAL AUTOALLOCATE';
execute immediate (v_sql);
end if;
select count(*) into v_cnt from SYS.USER$ Where NAME='perform51_user';
if v_cnt = 0 then
v_sql := 'CREATE user perform51_user identified by perform51_user default tablespace perform51 temporary tablespace temp';
execute immediate (v_sql);
execute immediate ('GRANT connect, resource, dba, exp_full_database, imp_full_database to perform51_user');
execute immediate ('ALTER user perform51_user quota unlimited on perform51');
end if;
end;
/
spool off