Oracle Tablespace(Drop and Create)

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.

1. First I like the way written this, because mainly validate tablespace is exists.
2. Then good output messages(DBMS_OUTPUT)
3. And used Exception that is good too

Ok! what about bad practices or whats wrong is that?

1. Ok! first Drop tablespace is very tricky thing and you have to do it very carefully,
1. 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.
2. 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.
3. If this is going to execute in Production database then you have to make-sure related backups of this database or schema.
4. 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';
5. I personally prefer Create tablespace & Drop tablespace with two different PL/SQL scripts. But it was Ok with One script also.       
2. Well! Nothing much to say with Create tablespace, 
1. Best practices way is "AUTOEXTEND OFF" unless if you have any different situation such as development Database.
2. If you have tablespace groups then use it.
3. 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.