Channels ▼
RSS

Parallel

Databases and Refreshing Test Data


2. Clear Schema

The purpose of the procedure SCHEMA_REFRESH.CLEAR_SCHEMA is to prepare the schemas of the Test database for the import process. This procedure deletes/drops selected database objects from the R11 and WEB_USER schemas. Listing 2 shows a small sample of the database objects that will be drop/delete using dynamic SQL.

Note: This script works for the following Oracle database versions: 9i, 10g, and 11g. This script was sent to me by my friend Mike Haggerty.


procedure clear_schema
  as
           --
        v_oracle9 boolean := false;
        v_oracle10 boolean := false;
                v_oracle11 boolean := false;
        --
        --
        --  determine the Oracle version
        --
        PROCEDURE set_version AS
           v_version varchar2(20);
           v_comp varchar2(20);
        BEGIN
           DBMS_UTILITY.DB_VERSION ( v_version, v_comp);

           IF substr( v_version, 1, instr( v_version, '.') - 1 ) = '9' THEN
                    v_oracle9 := true;
           ELSIF substr( v_version, 1, instr( v_version, '.') - 1 ) = '10' THEN
                    v_oracle10 := true;
           ELSIF substr( v_version, 1, instr( v_version, '.') - 1 ) = '11' THEN
                    v_oracle11 := true;
           END IF;
        END;
        --
begin
    --
    --  just add a new entry for each schema
    --
    v_schema.delete;
    v_schema(1) := 'WEB_USER';
      v_schema(2) := 'R11';
        --
        set_version;
        --
    FOR i IN v_schema.FIRST..v_schema.LAST LOOP
        FOR t IN (SELECT owner, object_name 
                          from all_objects 
                        where object_type = 'VIEW' 
                        and owner = upper( v_schema(i) ) ) LOOP
            EXECUTE IMMEDIATE 'drop view ' || t.owner || '.' || t.object_name;          
        END LOOP;
        --
        FOR t IN (SELECT owner, object_name from all_objects where object_type = 'MATERIALIZED VIEW' and owner = upper( v_schema(i) ) ) LOOP
            EXECUTE IMMEDIATE 'drop MATERIALIZED VIEW ' || t.owner || '.' || t.object_name;  
     END LOOP;
         --       
        FOR t IN (SELECT owner, object_name from all_objects where object_type = 'PACKAGE' and owner = upper( v_schema(i) ) ) LOOP
            EXECUTE IMMEDIATE 'drop package ' || t.owner || '.' || t.object_name;
          END LOOP;
         --
        FOR t IN (SELECT owner, object_name from all_objects where object_type = 'PROCEDURE' and owner = upper( v_schema(i) ) ) LOOP
            EXECUTE IMMEDIATE 'drop procedure ' || t.owner || '.' || t.object_name;
        END LOOP;
         --
        FOR t IN (SELECT owner, object_name from all_objects where object_type = 'TYPE' and owner = upper( v_schema(i) ) ) LOOP
            EXECUTE IMMEDIATE 'drop type ' || t.owner || '.' || t.object_name;
            END LOOP;
--
        FOR t IN (SELECT owner, object_name from all_objects where object_type = 'TABLE' and owner = upper( v_schema(i) ) ) LOOP
               if v_oracle9 = true then
                EXECUTE IMMEDIATE 'drop table ' || t.owner || '.' || t.object_name || ' cascade constraints';
            elsif v_oracle10 = true or v_oracle11 = true then
               EXECUTE IMMEDIATE 'drop table ' || t.owner || '.' || t.object_name || ' cascade constraints purge';
              end if;
        END LOOP;
         --   
    END LOOP;
end clear_schema;

Listing 2: Clear Schema


Related Reading


More Insights






Currently we allow the following HTML tags in comments:

Single tags

These tags can be used alone and don't need an ending tag.

<br> Defines a single line break

<hr> Defines a horizontal line

Matching tags

These require an ending tag - e.g. <i>italic text</i>

<a> Defines an anchor

<b> Defines bold text

<big> Defines big text

<blockquote> Defines a long quotation

<caption> Defines a table caption

<cite> Defines a citation

<code> Defines computer code text

<em> Defines emphasized text

<fieldset> Defines a border around elements in a form

<h1> This is heading 1

<h2> This is heading 2

<h3> This is heading 3

<h4> This is heading 4

<h5> This is heading 5

<h6> This is heading 6

<i> Defines italic text

<p> Defines a paragraph

<pre> Defines preformatted text

<q> Defines a short quotation

<samp> Defines sample computer code text

<small> Defines small text

<span> Defines a section in a document

<s> Defines strikethrough text

<strike> Defines strikethrough text

<strong> Defines strong text

<sub> Defines subscripted text

<sup> Defines superscripted text

<u> Defines underlined text

Dr. Dobb's encourages readers to engage in spirited, healthy debate, including taking us to task. However, Dr. Dobb's moderates all comments posted to our site, and reserves the right to modify or remove any content that it determines to be derogatory, offensive, inflammatory, vulgar, irrelevant/off-topic, racist or obvious marketing or spam. Dr. Dobb's further reserves the right to disable the profile of any commenter participating in said activities.

 
Disqus Tips To upload an avatar photo, first complete your Disqus profile. | View the list of supported HTML tags you can use to style comments. | Please read our commenting policy.
 

Video