Oracle Tablespace(Drop and Create)
Today one of developer send me to verify, about some PL/SQL statement regarding Drop and Create tablespace in Oracle. well.. i'll show you that PL/SQL statement first and we will have a look in to this after.
SET SERVEROUTPUT ON;
DECLARE
v_exists NUMBER:=0;
v_if_exists_drop NUMBER:=1;
BEGIN
SELECT COUNT(*)
INTO v_exists
FROM dba_tablespaces
WHERE tablespace_name = 'TBS_DBUG';
IF v_exists > 0 THEN
dbms_output.put_line('TBS_DBUG tablespace exists...!');
IF v_if_exists_drop=1 THEN
BEGIN
EXECUTE IMMEDIATE 'DROP TABLESPACE TBS_DBUG INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS';
END;
dbms_output.put_line('TBS_DBUG tablespace Deleted...!');
END IF;
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLESPACE TBS_DBUG DATAFILE ''E:\Oracle\DBUG\tbs_dbug_01.dbf'' SIZE 5000K REUSE AUTOEXTEND ON';
END;
dbms_output.put_line('TBS_DBUG tablespace Created...!');
ELSE
dbms_output.put_line('TBS_DBUG tablespace does not exist...!');
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLESPACE TBS_DBUG DATAFILE ''E:\Oracle\DBUG\tbs_dbug_01.dbf'' SIZE 5000K REUSE AUTOEXTEND ON';
END;
dbms_output.put_line('TBS_DBUG tablespace Created...!');
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
/
Ok! first I'm going to discuss good things in above SQL statement.
SET SERVEROUTPUT ON;
DECLARE
v_exists NUMBER:=0;
v_if_exists_drop NUMBER:=1;
BEGIN
SELECT COUNT(*)
INTO v_exists
FROM dba_tablespaces
WHERE tablespace_name = 'TBS_DBUG';
IF v_exists > 0 THEN
dbms_output.put_line('TBS_DBUG tablespace exists...!');
IF v_if_exists_drop=1 THEN
BEGIN
EXECUTE IMMEDIATE 'DROP TABLESPACE TBS_DBUG INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS';
END;
dbms_output.put_line('TBS_DBUG tablespace Deleted...!');
END IF;
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLESPACE TBS_DBUG DATAFILE ''E:\Oracle\DBUG\tbs_dbug_01.dbf'' SIZE 5000K REUSE AUTOEXTEND ON';
END;
dbms_output.put_line('TBS_DBUG tablespace Created...!');
ELSE
dbms_output.put_line('TBS_DBUG tablespace does not exist...!');
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLESPACE TBS_DBUG DATAFILE ''E:\Oracle\DBUG\tbs_dbug_01.dbf'' SIZE 5000K REUSE AUTOEXTEND ON';
END;
dbms_output.put_line('TBS_DBUG tablespace Created...!');
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
/
Ok! first I'm going to discuss good things in above SQL statement.
- First I like the way written this, because mainly validate tablespace is exists.
- Then good output messages(DBMS_OUTPUT)
- And used Exception that is good too
- Ok! first Drop tablespace is very tricky thing and you have to do it very carefully,
- Best way, you have to check if tablespace contains any objects(Tables,Index, ect) And then you can go for delete.can find tablespace contain objects in "dba_segments" system view.
- But note some newly created tables(without any records) then it will not listed in "dba_segments" system view then, you have to find it in "dba_objects,dba_tables" like wise.
- If this is going to execute in Production database then you have to make-sure related backups of this database or schema.
- some times your tablespace will be very huge then, recommended for drop files one by one because this is very costly.And finally delete tablespace with last data file.
- ALTER TABLESPACE TBS_DBUG DROP DATAFILE 'E:\Oracle\DBUG\tbs_dbug_02.dbf';
- I personally prefer Create tablespace & Drop tablespace with two different PL/SQL scripts. But it was Ok with One script also.
- Well! Nothing much to say with Create tablespace,
- Best practices way is "AUTOEXTEND OFF" unless if you have any different situation such as development Database.
- If you have tablespace groups then use it.
- Tablespace size is very important before create tablespace, if you have decide max size of tablespace. or you have to create tablespace growth plan(daily,weekly,monthly and yearly)
In general if you can create a common procedure for create or drop tablespaces then is is much more clear. If anyone of you have good suggestion or idea please add some comments. most welcome your comments and ideas.
