Rem
Rem $Header: catdtyp.sql 10-jan-97.12:39:33 rshaikh Exp $
Rem
Rem catdtyp.sql
Rem
Rem  Copyright (c) Oracle Corporation 1996, 1997. All Rights Reserved.
Rem
Rem    NAME
Rem      catdtyp.sql - CATalog Drop TYPes for beta1 to beta2 migration
Rem
Rem    DESCRIPTION
Rem      drops all type and type related information.  drops the system
Rem	 types which will be recreated when the database is open again
Rem      as a beta2 database.
Rem
Rem    NOTES
Rem      Must be run on a beta1 database before migrating to a beta2
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    rshaikh     01/10/97 - change nchar value in props
Rem    rshaikh     11/15/96 - add property to dependency$
Rem    cxcheng     10/08/96 - Created
Rem

set echo off 
set serveroutput on 
 
rem ************************************************************************ 
rem The following PL/SQL code 
rem 1) selects all TABLEs, object VIEWs that are dependent on types, 
rem    and drops them 
rem 2) drops all TYPEs 
rem 3) drops all TABLEs that contain LOB columns 
rem 4) drops all LIBRARY objects 
rem ************************************************************************ 
 
DECLARE 
 
  -- select all dependent objects that are TABLES, VIEWS 
  CURSOR obj_cursor IS 
    SELECT u.name user_name, o.name obj_name, o.type# 
      FROM sys.user$ u, sys.obj$ o WHERE 
      o.obj# IN (SELECT d_obj# FROM sys.dependency$ WHERE 
                   p_obj# IN (SELECT obj# FROM sys.oid$ WHERE 
                                oid$ IN (SELECT toid FROM sys.type$))) 
      AND o.owner# = u.user# 
      AND o.type# IN (2, 4) 
      AND o.name NOT LIKE 'SYS_TN%'; 
 
  -- select all types with properties = 16 OR 64 OR 2048 => 
  --   system-generated or predefined type 
  -- all predefined types can only be dropped by dropping the extent tables 
  CURSOR type_cursor IS 
    SELECT u.name user_name, o.name obj_name FROM sys.user$ u, sys.obj$ o WHERE 
      o.obj# IN (SELECT obj# FROM sys.oid$ WHERE 
                   oid$ IN (SELECT toid FROM sys.type$ WHERE 
                              bitand(properties, 2128) = 0)) 
      AND o.owner# = u.user#; 
 
  -- select all tables containing user-defined LOB columns 
  CURSOR lob_cursor IS 
    SELECT u.name user_name, o.name obj_name FROM sys.user$ u, sys.obj$ o WHERE 
      o.obj# IN (SELECT obj# from sys.lob$ WHERE 
                   property IN (0, 2)) 
      AND o.owner# = u.user#; 
 
  -- select all library objects 
  CURSOR lib_cursor IS 
    SELECT u.name user_name, o.name obj_name FROM sys.user$ u, sys.obj$ o WHERE 
      o.obj# IN (SELECT obj# from sys.library$) 
      AND o.owner# = u.user#; 
 
  drop_cursor_id INTEGER;           -- cursor ID for dynamic SQL drop statement 
  dummy INTEGER; 
 
BEGIN 
 
  -- print heading 
  dbms_output.put_line('Dropping type, lob and library objects'); 
  dbms_output.put_line('--------------------------------------'); 
 
  -- get drop cursor 
  drop_cursor_id := dbms_sql.open_cursor; 
 
  -- remove each type-dependent object (table, view) 
  FOR c IN obj_cursor 
  LOOP 
 
    -- then use a different drop statement for each object 
    IF c.type# = 2 THEN     
      dbms_output.put_line('dropping table ' || 
                           c.user_name || '.' || c.obj_name); 
      dbms_sql.parse(drop_cursor_id, 'DROP TABLE ' || 
                     c.user_name ||'.'|| c.obj_name || ' CASCADE CONSTRAINTS', 
                     dbms_sql.NATIVE); 
    ELSIF c.type# = 4 THEN 
      dbms_output.put_line('dropping view ' || 
                           c.user_name || '.' || c.obj_name); 
      dbms_sql.parse(drop_cursor_id, 'DROP VIEW ' || 
                     c.user_name || '.' || c.obj_name, dbms_sql.NATIVE); 
    END IF; 
 
    -- execute statement parsed previously 
    dummy := dbms_sql.execute(drop_cursor_id); 
 
  END LOOP; 
 
  -- now we drop all the types 
  FOR c IN type_cursor 
  LOOP 
    dbms_output.put_line('dropping type ' || 
                         c.user_name || '.' || c.obj_name); 
    dbms_sql.parse(drop_cursor_id, 'DROP TYPE ' || 
                   c.user_name || '.' || c.obj_name || ' FORCE', 
                   dbms_sql.NATIVE); 
    -- execute statement 
    dummy := dbms_sql.execute(drop_cursor_id); 
  END LOOP; 
 
  -- next we drop all tables with user-defined lob columns 
  FOR c IN lob_cursor 
  LOOP 
    dbms_output.put_line('dropping table ' || 
                         c.user_name || '.' || c.obj_name || 
                         ' with user-defined lob columns'); 
    dbms_sql.parse(drop_cursor_id, 'DROP TABLE ' || 
                   c.user_name || '.' || c.obj_name || ' CASCADE CONSTRAINTS', 
                   dbms_sql.NATIVE); 
    -- execute statement 
    dummy := dbms_sql.execute(drop_cursor_id); 
  END LOOP; 
 
  -- finally we drop all the library objects 
  FOR c IN lib_cursor 
  LOOP 
    dbms_output.put_line('dropping library ' || 
                         c.user_name || '.' || c.obj_name); 
    dbms_sql.parse(drop_cursor_id, 'DROP LIBRARY ' || 
                   c.user_name || '.' || c.obj_name, dbms_sql.NATIVE); 
    -- execute statement 
    dummy := dbms_sql.execute(drop_cursor_id); 
  END LOOP; 
 
EXCEPTION 
 
  WHEN OTHERS THEN 
    dbms_sql.close_cursor(drop_cursor_id); 
    dbms_output.put_line(SQLERRM); 
 
END; 
/ 
 
rem ************************************************************************ 
rem Now that we have dropped all types and type-dependent and lob-dependent 
rem objects, we drop the system type tables 
rem ************************************************************************ 
 
rem delete all rows from type data dictionary tables 
DELETE FROM sys.type$; 
DELETE FROM sys.attribute$; 
DELETE FROM sys.collection$; 
DELETE FROM sys.method$; 
DELETE FROM sys.parameter$; 
DELETE FROM sys.result$; 
 
rem DROP TABLE  system type tables 
DROP TABLE sys.kottd$; 
DROP TABLE sys.kotad$; 
DROP TABLE sys.kotmd$; 
 
rem remove the server fdo row from kopm$ and clean out oid$
DELETE FROM sys.kopm$;
DELETE FROM sys.obj$ where oid$ in (select oid$ from sys.oid$);
DELETE FROM sys.oid$;

rem ************************************************************************ 
rem Create and update migrate$ to show that we are done with this stage 
rem ************************************************************************ 
 
CREATE TABLE migrate$ 
( version#   varchar2(30),                         /* version migrated from */ 
  migdate    date,             /* date the migration from v7->v8 took place */ 
  migrated   number not null    /* 0=not migrated, 1=migrated from v7 to v8 */ 
); 
 
INSERT INTO migrate$ VALUES ('801', sysdate, 1); 

update props$
  set value$= (select value$ from sys.props$ where name='NLS_CHARACTERSET')
  where name = 'NLS_NCHAR_CHARACTERSET';


rem ************************************************************************
rem Add the column to dependency$ here so ALTER DATABASE OPEN RESETLOGS passes
rem ************************************************************************
alter table dependency$ add (property number);
update dependency$ set property=1;
alter table dependency$ modify (property number not null);

commit;




