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

2019-10-21

× 文章目录

在navicat premium中执行如下语句

1
2
3

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才能继续执行，很麻烦。

优化后的sql语言

1
2
3

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;
...

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

本文作者： Lujianlong
本文链接： http://yoursite.com/2019/10/21/oracle使用sql语句删除某用户下所有的表/
本文访问量：118 次
版权声明： 本作品采用知识共享署名-非商业性使用-相同方式共享 4.0 国际许可协议进行许可。转载请注明出处！