甲骨文 |删除表

发布于 2024-10-22 02:53:38 字数 185 浏览 25 评论 0原文

我想删除表空间中的某些表，这些表的公共名称附加到每个表的末尾，例如：

TABLE1_NAME1_COMMON
TABLE2_NAME2_COMMON
TABLE3_NAME3_COMMON

我听说过 Oracle 函数，但我对这些函数不太熟悉，所以我期待一些帮助。

谢谢。

原文

I want to drop certain tables in a tablespace that has common name appended to end of each table for an example:

TABLE1_NAME1_COMMON
TABLE2_NAME2_COMMON
TABLE3_NAME3_COMMON

I heard about Oracle functions but I'm not familiar much with those so I'm expecting some helping hand.

Thanks.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

一绘本一梦想 2024-10-29 02:53:38

如果您完全确定自己在做什么，即，如果您确定没有意外删除不想删除的表，则可以执行以下操作：

set serveroutput on size 1000000

begin
for r in (

  select table_name 
    from user_tables 
   where table_name like '%\_COMMON' escape '\')

loop

  execute immediate 'drop table ' || r.table_name;

end loop;
exception when others then
   dbms_output.put_line(sqlerrm);
end;
/

编辑：

已更改，现在从 user_tables 而不是 dba_tables 选择，因为这样做似乎更安全。
添加了 set serveroutput on 以便打印 dbms_output.put_line
添加了 begin .. exception .. end 以便显示错误。

If you're completely sure what you're doing, ie, if you're sure that you don't accidentally drop a table that you don't want to drop, you can do a:

set serveroutput on size 1000000

begin
for r in (

  select table_name 
    from user_tables 
   where table_name like '%\_COMMON' escape '\')

loop

  execute immediate 'drop table ' || r.table_name;

end loop;
exception when others then
   dbms_output.put_line(sqlerrm);
end;
/

Edit:

Changed Now selecting from user_tables instead of dba_tables as it seems more safe to do.
Added set serveroutput on in order for dbms_output.put_line to be printed
Added begin .. exception .. end in order for errors to be shown.

回复收藏 0 原文

孤寂小茶 2024-10-29 02:53:38

您可以在过程中执行此操作，但最好只选择那些 DROP 语句，查看它们并手动执行它们：

SELECT 'DROP TABLE ' || table_name || ';'
FROM user_tables
WHERE table_name LIKE '%\_COMMON' ESCAPE '\';

将返回

DROP TABLE TABLE1_NAME1_COMMON;
DROP TABLE TABLE2_NAME2_COMMON;
DROP TABLE TABLE3_NAME3_COMMON;

You could do that in a procedure, but it might be better to just select those DROP-statements, review them and execute them manually:

SELECT 'DROP TABLE ' || table_name || ';'
FROM user_tables
WHERE table_name LIKE '%\_COMMON' ESCAPE '\';

would return

DROP TABLE TABLE1_NAME1_COMMON;
DROP TABLE TABLE2_NAME2_COMMON;
DROP TABLE TABLE3_NAME3_COMMON;

回复收藏 0 原文

三月梨花 2024-10-29 02:53:38

要识别它们，您可以使用：

SELECT * FROM user_tables WHERE tablespace_name='MySpace' AND table_name like '%COMMON';

然后您可以使用 SELECT 派生 DROP 语句。或者您可以编写一个 PL/SQL 函数来循环遍历“公用表”并使用 EXECUTE IMMEDIATE 删除它们。

不过，我首先要确保您对选择有 100% 的把握。

to identify them you can use:

SELECT * FROM user_tables WHERE tablespace_name='MySpace' AND table_name like '%COMMON';

You could then either, derive your DROP statements using a SELECT. Or you could write a PL/SQL function to loop through the "Common Tables" and DROP them using EXECUTE IMMEDIATE.

I would make sure you are 100% sure in the selections first however.

回复收藏 0 原文

~没有更多了~