Oracle删除数据表和视图存储过程

1、查询当前表空间：

select username,default_tablespace from user_users；

 或查询所有表空间：

select tablespace_name from dba_tablespaces；

2、确定当前表空的名称【当前是TEST,根据步骤一确定】后执行存储过程：

declare

    v_name all_tables.table_name%type;

    cursor mycur is select table_name from all_tables where owner='TEST';

begin

    open mycur;

    loop

        fetch mycur into v_name;

        exit when mycur%NOTFOUND OR mycur%NOTFOUND IS NULL;

        execute immediate 'drop table '|| v_name;

    end loop;

    close mycur;

end;

3、删除所有视图：

declare

    v_name all_views.view_name%type;

    cursor mycur is select view_name from all_views where owner='TEST';

begin

    open mycur;

    loop

        fetch mycur into v_name;

        exit when mycur%NOTFOUND OR mycur%NOTFOUND IS NULL;

        execute immediate 'drop view '|| v_name ;

    end loop;

    close mycur;

end;