oracle使用sql语句删除某用户下所有的表

超、凢脫俗 2023-06-05 08:18 18阅读 0赞

1.  在navicat premium中执行如下语句

select 'drop table "'||table_name||'";'
    from cat 
    where table_type='TABLE'

其执行结果如下：

drop table "ALARM";
    drop table "ANSWER";
    drop table "APPLY";
    drop table "APPLYCONTENT";
    drop table "APPLYSYSTEM";
    drop table "APPLYTYPE";
    drop table "APP_INFO";
    drop table "CARD_INFO";
    drop table "CARD_SERVERKEY";
    drop table "CARD_TYPEINFO";
    drop table "CPU_TOTAL";
    drop table "DEF$_AQCALL";
    drop table "DEF$_AQERROR";
    drop table "DEPARTMENT";
    drop table "DEVICEINFO";
    drop table "DEVICE_TOTAL";
    drop table "INS_BUSINESS";
    drop table "ITSCACERT";
    drop table "ITSCAUSER";
    drop table "J_ADMININFO";
    drop table "J_APPEXPINFO";
    drop table "J_DEVICESAFEINFO";
    drop table "J_DEVICESETINFO";
    drop table "J_DYNAMICDEVICEINFO";
    ...

这种写法有一个缺陷，不能删除带有外键的表,然后会堵塞sql的执行，需要去掉带有外键的sql才能继续执行，很麻烦。  
2. 优化后的sql语言

select 'drop table "'||table_name||'" cascade constraints;'
    from cat 
    where table_type='TABLE'

执行结果：

drop table "ALARM" cascade constraints;
    drop table "ANSWER" cascade constraints;
    drop table "APPLY" cascade constraints;
    drop table "APPLYCONTENT" cascade constraints;
    drop table "APPLYSYSTEM" cascade constraints;
    drop table "CARD_INFO" cascade constraints;
    drop table "CARD_SERVERKEY" cascade constraints;
    drop table "CARD_TYPEINFO" cascade constraints;
    drop table "CPU_TOTAL" cascade constraints;
    drop table "DEF$_AQCALL" cascade constraints;
    drop table "DEF$_AQERROR" cascade constraints;
    drop table "DEPARTMENT" cascade constraints;
    drop table "DEVICEINFO" cascade constraints;
    drop table "DEVICE_TOTAL" cascade constraints;
    drop table "INS_BUSINESS" cascade constraints;
    drop table "ITSCACERT" cascade constraints;
    drop table "ITSCAUSER" cascade constraints;
    drop table "J_ADMININFO" cascade constraints;
    drop table "J_APPEXPINFO" cascade constraints;
    ...

1.  将执行结果复制出来，在查询语句中执行，就可以删除所有的表格。