Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Check for Tablespace Exist

505977Nov 6 2006 — edited Nov 6 2006
How 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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 4 2006
Added on Nov 6 2006
3 comments
639 views