DBA

DBUTILS PACKAGE TO CLEAN THE SCHEMA OBJECTS

CREATE OR REPLACE PACKAGE dbutils AS
/* Version: 1.0
|| Purpose: Hold Database Utilities that will be helpful to many other
||          scripts and PL/sql objects
|| Modification History:
|| 11/17/00 PWH Created
|| 02/08/04 LAB Added code to drop materialized views
||
|| pb = parameter boolean
|| ps = parameter string
|| pn = parameter number
|| cs = constant string
|| ls = local string
|| gs = global string
||
*/
  PROCEDURE screen_output
    (ps_text VARCHAR2
    ,pb_debug BOOLEAN
    );
  --
  PROCEDURE dyn_sql
    (ps_sql VARCHAR2
    ,pb_debug BOOLEAN
    );
  --
  PROCEDURE drop_cons
    (ps_schema VARCHAR2
    ,pb_debug BOOLEAN DEFAULT FALSE
    );
  --
  PROCEDURE drop_mvs
    (ps_schema VARCHAR2
    ,pb_debug BOOLEAN DEFAULT FALSE
    );
  --
  PROCEDURE drop_objects
    (ps_schema VARCHAR2
    ,ps_object_type VARCHAR2
    ,pb_debug BOOLEAN DEFAULT FALSE
    ,pb_cascade BOOLEAN DEFAULT TRUE
    );
  --
  PROCEDURE drop_plsql
    (ps_schema VARCHAR2
    ,pb_debug BOOLEAN DEFAULT FALSE
    );
  --
  PROCEDURE drop_schema
    (ps_schema VARCHAR2
    ,pb_retain_owner BOOLEAN DEFAULT TRUE
    ,pb_debug BOOLEAN DEFAULT FALSE
    );
END dbutils;
/

show err

CREATE OR REPLACE PACKAGE BODY dbutils AS
/* Version: 1.0
|| Purpose: Hold Database Utilities that will be helpful to many other
||          scripts and PL/sql objects
|| Modification History:
|| 11/17/00 PWH Created
||
*/
  PROCEDURE screen_output
    (ps_text IN VARCHAR2
    ,pb_debug IN BOOLEAN
    ) IS
  /* Version 1.0
  || Purpose: If debugging, present the output to the screen
  || Modification History:
  || 11/17/00 PWH Created
  ||
  */
    -- Constants
    cs_pkg_prc VARCHAR2(255) := 'dbutils.screen_output';
  BEGIN
    IF pb_debug
    THEN
      DBMS_OUTPUT.ENABLE(1000000);
      DBMS_OUTPUT.PUT_LINE(ps_text);
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
  END screen_output;
  --
  PROCEDURE dyn_sql
    (ps_sql IN VARCHAR2
    ,pb_debug IN BOOLEAN
    ) IS
  /* Version 1.0
  || Purpose: Run the SQL text passed in through dynamic SQL
  || Modification History:
  || 11/17/00 PWH Created
  ||
  */
    -- Constants
    cs_pkg_prc VARCHAR2(61) := 'dbutils.dyn_sql';
    -- local variables
    ln_dynamic_cursor INTEGER;
    ln_dynamic_data BINARY_INTEGER;
  BEGIN -- Dynamic Block
    -- If debugging, show the SQL statment
    screen_output(ps_sql, pb_debug);
    --
    ln_dynamic_cursor := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(ln_dynamic_cursor, ps_sql, DBMS_SQL.V7);
    ln_dynamic_data := DBMS_SQL.EXECUTE(ln_dynamic_cursor);
    DBMS_SQL.CLOSE_CURSOR(ln_dynamic_cursor);
  EXCEPTION
  WHEN OTHERS THEN
    screen_output(SQLERRM, pb_debug);
    IF DBMS_SQL.IS_OPEN(ln_dynamic_cursor)
    THEN
      DBMS_SQL.CLOSE_CURSOR(ln_dynamic_cursor);
    END IF;
  END dyn_sql;
  --
  PROCEDURE drop_cons
    (ps_schema VARCHAR2
    ,pb_debug BOOLEAN DEFAULT FALSE
    ) IS
  /* Version 1.0
  || Purpose: Drop the schema owner constraints
  || Modification History:
  || 11/27/00 PWH Created
  ||
  */
    -- Constants
    cs_pkg_prc VARCHAR2(61) := 'dbutils.drop_cons';
    --
    -- Local Variables
    CURSOR lcur_constraint IS
    SELECT constraint_name
          ,constraint_type
          ,table_name
      FROM dba_constraints
     WHERE owner = UPPER(ps_schema)
     ORDER BY constraint_type;
    --
    lrec_constraint lcur_constraint%ROWTYPE;
    --
    ls_sql VARCHAR2(255);
    ln_counter NUMBER := 0;
    ln_nbr_tries NUMBER := 10;
  BEGIN
    -- since constraint dropping sometimes has issues with other constraints
    -- we try several times...
    <<main_loop>>
    LOOP
      ln_counter := ln_counter + 1;
    EXIT main_loop WHEN ln_counter > ln_nbr_tries;
      FOR lrec_constraint IN lcur_constraint
      LOOP
        ls_sql := 'ALTER TABLE '||ps_schema||'.'||lrec_constraint.table_name
                  ||' DROP CONSTRAINT '||lrec_constraint.constraint_name
                  ||' CASCADE';
        dyn_sql(ls_sql,pb_debug);
      END LOOP;
    END LOOP main_loop;
  EXCEPTION
    WHEN OTHERS THEN
      IF lcur_constraint%ISOPEN
      THEN
        CLOSE lcur_constraint;
      END IF;
      screen_output('The following error occured while in '
                    ||cs_pkg_prc, pb_debug);
      screen_output(SQLERRM, pb_debug);
  END drop_cons;
  --
  PROCEDURE drop_mvs
    (ps_schema VARCHAR2
    ,pb_debug BOOLEAN DEFAULT FALSE
    ) IS
  /* Version 1.0
  || Purpose: Drop the schema owner materialized views
  || Modification History:
  || 11/27/00 PWH Created
  ||
  */
    -- Constants
    cs_pkg_prc VARCHAR2(61) := 'dbutils.drop_mvs';
    --
    -- Local Variables
    CURSOR lcur_object IS
    SELECT DISTINCT name AS object_name
          ,'SNAPSHOT' AS object_type
      FROM dba_snapshots
     WHERE owner = UPPER(ps_schema)
     UNION
    SELECT DISTINCT master AS object_name
          ,'SNAPSHOT LOG' AS object_type
      FROM dba_snapshot_logs
     WHERE log_owner = UPPER(ps_schema);
    --
    lrec_object lcur_object%ROWTYPE;
    --
    ls_sql VARCHAR2(255);
  BEGIN
    FOR lrec_object IN lcur_object
    LOOP
      IF lrec_object.object_type = 'SNAPSHOT LOG'
      THEN
        ls_sql := 'DROP '||lrec_object.object_type||' ON '
                  ||ps_schema||'.'||lrec_object.object_name;
        dyn_sql(ls_sql,pb_debug);
      ELSE
        ls_sql := 'DROP '||lrec_object.object_type||' '
                  ||ps_schema||'.'||lrec_object.object_name;
        dyn_sql(ls_sql,pb_debug);
      END IF;
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      IF lcur_object%ISOPEN
      THEN
        CLOSE lcur_object;
      END IF;
      screen_output('The following error occured while in '
                    ||cs_pkg_prc, pb_debug);
      screen_output(SQLERRM, pb_debug);
  END drop_mvs;
  --
  PROCEDURE drop_objects
    (ps_schema VARCHAR2
    ,ps_object_type VARCHAR2
    ,pb_debug BOOLEAN DEFAULT FALSE
    ,pb_cascade BOOLEAN DEFAULT TRUE
    ) IS
  /* Version 1.0
  || Purpose: Drop either the schema owner objects of the given type
  || Modification History:
  || 11/27/00 PWH Created
  ||
  */
    -- Constants
    cs_pkg_prc VARCHAR2(61) := 'dbutils.drop_objects';
    --
    -- Local Variables
    CURSOR lcur_object IS
    SELECT object_name
          ,object_type
      FROM dba_objects
     WHERE owner = UPPER(ps_schema)
       AND object_type = ps_object_type
     ORDER BY object_type,object_name;
    --
    lrec_object lcur_object%ROWTYPE;
    --
    ls_sql VARCHAR2(255);
  BEGIN
    -- since constraint dropping sometimes has issues with other constraints
    -- we try several times...
    IF ps_object_type = 'CONSTRAINT'
    THEN
      drop_cons(ps_schema,pb_debug);
    ELSE
      FOR lrec_object IN lcur_object
      LOOP
        ls_sql := 'DROP '||lrec_object.object_type||' '
                  ||ps_schema||'.'||lrec_object.object_name;
        dyn_sql(ls_sql,pb_debug);
      END LOOP;
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      IF lcur_object%ISOPEN
      THEN
        CLOSE lcur_object;
      END IF;
      screen_output('The following error occured while in '
                    ||cs_pkg_prc, pb_debug);
      screen_output(SQLERRM, pb_debug);
  END drop_objects;
  --
  PROCEDURE drop_plsql
    (ps_schema VARCHAR2
    ,pb_debug BOOLEAN DEFAULT FALSE
    ) IS
  /* Version 1.0
  || Purpose: Drop the schema owner plsql objects
  ||          Currently defined as TRIGGER, PACKAGE (and body by default)
  ||          ,PROCEDURE, FUNCTION
  || Modification History:
  || 11/27/00 PWH Created
  ||
  */
    -- Constants
    cs_pkg_prc VARCHAR2(61) := 'dbutils.drop_plsql';
    --
    -- Local Variables
    ls_sql VARCHAR2(255);
  BEGIN
    drop_objects(ps_schema,'TRIGGER',pb_debug);
    drop_objects(ps_schema,'PACKAGE',pb_debug);
    drop_objects(ps_schema,'PROCEDURE',pb_debug);
    drop_objects(ps_schema,'FUNCTION',pb_debug);
  EXCEPTION
    WHEN OTHERS THEN
      screen_output('The following error occured while in '
                    ||cs_pkg_prc, pb_debug);
      screen_output(SQLERRM, pb_debug);
  END drop_plsql;
  --
  PROCEDURE drop_schema
    (ps_schema VARCHAR2
    ,pb_retain_owner BOOLEAN DEFAULT TRUE
    ,pb_debug BOOLEAN DEFAULT FALSE
    ) IS
  /* Version 1.0
  || Purpose: Drop either the schema owner (not default)
  ||          or drop all objects under a schema (default)
  || Modification History:
  || 11/27/00 PWH Created
  ||
  */
    -- Constants
    cs_pkg_prc VARCHAR2(61) := 'dbutils.drop_schema';
    --
    -- Local Variables
    ls_sql VARCHAR2(255);
  BEGIN
    IF pb_retain_owner
    THEN
      IF UPPER(ps_schema) = 'SYS'
      THEN
        screen_output('You cannot drop the objects from SYS...',pb_debug);
      ELSE
        drop_objects(ps_schema,'SYNONYM',pb_debug);
        -- saved a few lines by bundling PLSQL into its own PROCEDURE
        drop_plsql(ps_schema,pb_debug);
        drop_objects(ps_schema,'VIEW',pb_debug);
        drop_objects(ps_schema,'SEQUENCE',pb_debug);
        -- dropping constraints prior to tables simplifies the dropping tables
        drop_cons(ps_schema,pb_debug);
        -- no sense in dropping indexes since CASCADE is going to wipe them out
        drop_mvs(ps_schema,pb_debug);
        drop_objects(ps_schema,'TABLE',pb_debug,TRUE);
      END IF;
    ELSE -- DROP the user and be done with it...
      IF UPPER(ps_schema) = 'SYS'
      THEN
        screen_output('You cannot drop the user: SYS',pb_debug);
      ELSE
        ls_sql := 'DROP USER '||ps_schema||' CASCADE';
      END IF;
      dyn_sql(ls_sql,pb_debug);
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      screen_output('The following error occured while in '
                    ||cs_pkg_prc, pb_debug);
      screen_output(SQLERRM, pb_debug);
  END drop_schema;
  --
END dbutils;
/

--Once this package is created, One can use this package to clean the schema by dropping all its objects, keeping schema intact. Sample of the usage is given blow:

exec sys.dbutils.drop_schema(ps_schema=>'SCOTT');

SPACE CONSUMED IN EACH DATAFILE

--This script will show you tablespace datafile, freespace , availiable and autoextend or not.

set pagesize 100
set linesize 200
column file_name format a60
column tablespace_name format a20
column status format a3 trunc
column t format 999,999.000 heading "Total MB"
column a format a4 heading "Aext"
column p format 990.00 heading "% Free"

SELECT df.file_name,
df.tablespace_name,
df. status,
(df.bytes/1024000) t,
(fs.s/df.bytes*100) p,
decode (ae.y,1,'YES','NO') a
FROM dba_data_files df,
(SELECT file_id,SUM(bytes) s
FROM dba_free_space
GROUP BY file_id) fs,
(SELECT file#, 1 y
FROM sys.filext$
GROUP BY file#) ae
WHERE df.file_id = fs.file_id
AND ae.file#(+) = df.file_id
ORDER BY df.tablespace_name, df.file_i
/

ROLES AND PRIVILEGES ASSIGNED TO A SCHEMA

set lines 110 pages 1000 ver off
col role for a16
col pv for a75 hea 'PRIVILEGE OR ROLE'
bre on role on type skip 1


define usercheck = '<SCHEMA_NAME>'


select grantee, 'ROL' type, granted_role pv
from dba_role_privs where grantee = '&usercheck' union
select grantee, 'PRV' type, privilege pv
from dba_sys_privs where grantee = '&usercheck' union
select grantee, 'OBJ' type,
max(decode(privilege,'WRITE','WRITE,'))||max(decode(privilege,'READ','READ'))||
max(decode(privilege,'EXECUTE','EXECUTE'))||max(decode(privilege,'SELECT','SELECT'))||
max(decode(privilege,'DELETE',',DELETE'))||max(decode(privilege,'UPDATE',',UPDATE'))||
max(decode(privilege,'INSERT',',INSERT'))||' ON '||object_type||' "'||a.owner||'.'||table_name||'"' pv
from dba_tab_privs a, dba_objects b
where a.owner=b.owner and a.table_name = b.object_name and a.grantee='&usercheck'
group by a.owner,table_name,object_type,grantee union
select username grantee, '---' type, 'empty user ---' pv from dba_users
where not username in (select distinct grantee from dba_role_privs) and
not username in (select distinct grantee from dba_sys_privs) and
not username in (select distinct grantee from dba_tab_privs) and username like '%&usercheck%'
group by username
order by grantee, type, pv;