Oracle 清除所有Objects

begin
  -- Drop Tables
  FOR REC IN (
      SELECT table_name 
      FROM user_tables 
      WHERE NOT EXISTS(SELECT 1 FROM USER_VIEWS WHERE VIEW_NAME = TABLE_NAME)
        and NOT EXISTS(SELECT 1 FROM USER_MVIEWS WHERE MVIEW_NAME = TABLE_NAME)
    )
  LOOP
    BEGIN 
      EXECUTE IMMEDIATE 'DROP TABLE ' || REC.table_name;
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE != -942 THEN
           RAISE;
        END IF;
  END;
  END LOOP;
  
  -- VIEW
  FOR REC IN (
      SELECT VIEW_NAME 
      FROM user_views
    )
  LOOP
    BEGIN 
      EXECUTE IMMEDIATE 'DROP VIEW ' || REC.VIEW_NAME;
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE != -942 THEN
           RAISE;
        END IF;
  END;
  END LOOP;
  
  -- MVIEW
  FOR REC IN (
      SELECT mview_name FROM USER_MVIEWS
    )
  LOOP
    BEGIN 
      EXECUTE IMMEDIATE 'DROP MATERIALIZED VIEW ' || REC.mview_name;
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE != -942 THEN
           RAISE;
        END IF;
  END;
  END LOOP;
  
  -- FUNCTION
  FOR REC IN (
      SELECT object_name FROM user_objects WHERE object_type = 'FUNCTION'
    )
  LOOP
    BEGIN 
      EXECUTE IMMEDIATE 'DROP FUNCTION ' || REC.object_name;
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE != -942 THEN
           RAISE;
        END IF;
  END;
  END LOOP;
  
  -- PROCEDURE
  FOR REC IN (
      SELECT object_name FROM user_objects WHERE object_type = 'PROCEDURE'
    )
  LOOP
    BEGIN 
      EXECUTE IMMEDIATE 'DROP PROCEDURE ' || REC.object_name;
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE != -942 THEN
           RAISE;
        END IF;
  END;
  END LOOP;
  
  -- DATABASE LINK
  FOR REC IN (
      SELECT object_name FROM user_objects WHERE object_type = 'DATABASE LINK'
    )
  LOOP
    BEGIN 
      EXECUTE IMMEDIATE 'DROP DATABASE LINK ' || REC.object_name;
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE != -942 THEN
           RAISE;
        END IF;
  END;
  END LOOP;
  
  -- TYPE
  FOR REC IN (
      SELECT object_name FROM user_objects WHERE object_type = 'TYPE'
    )
  LOOP
    BEGIN 
      EXECUTE IMMEDIATE 'DROP TYPE ' || REC.object_name || ' FORCE';
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE != -942 THEN
           RAISE;
        END IF;
  END;
  END LOOP;
  
  -- JOB
  FOR REC IN (
      SELECT object_name FROM user_objects WHERE object_type = 'JOB'
    )
  LOOP
    dbms_scheduler.drop_job(REC.OBJECT_NAME);
  END LOOP;

-- Drop SYNONYM
  FOR REC IN (
      select OBJECT_NAME from user_objects where object_type = 'SYNONYM'
    )
  LOOP
    BEGIN 
      EXECUTE IMMEDIATE 'DROP SYNONYM ' || REC.OBJECT_NAME;

    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE != -942 THEN
           RAISE;
        END IF;
  END;
  END LOOP;
end;