如何删除所有用户表？

小开

最佳答案

BEGIN
FOR cur_rec IN (SELECT object_name, object_type
FROM user_objects
WHERE object_type IN
('TABLE',
'VIEW',
'MATERIALIZED VIEW',
'PACKAGE',
'PROCEDURE',
'FUNCTION',
'SEQUENCE',
'SYNONYM',
'PACKAGE BODY'
))
LOOP
BEGIN
IF cur_rec.object_type = 'TABLE'
THEN
EXECUTE IMMEDIATE 'DROP '
|| cur_rec.object_type
|| ' "'
|| cur_rec.object_name
|| '" CASCADE CONSTRAINTS';
ELSE
EXECUTE IMMEDIATE 'DROP '
|| cur_rec.object_type
|| ' "'
|| cur_rec.object_name
|| '"';
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('FAILED: DROP '
|| cur_rec.object_type
|| ' "'
|| cur_rec.object_name
|| '"'
);
END;
END LOOP;
FOR cur_rec IN (SELECT *
FROM all_synonyms
WHERE table_owner IN (SELECT USER FROM dual))
LOOP
BEGIN
EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM ' || cur_rec.synonym_name;
END;
END LOOP;
END;
/

小开

最简单的方法是通过级联命令删除拥有对象的用户。

DROP USER username CASCADE

小开

最简单的方法是删除表空间，然后重新构建表空间。但我不想这么做。这与 Henry 的方法类似，只不过我只是在 gui 中对结果集执行复制/粘贴操作。

SELECT
'DROP'
,object_type
,object_name
,CASE(object_type)
WHEN 'TABLE' THEN 'CASCADE CONSTRAINTS;'
ELSE ';'
END
FROM user_objects
WHERE
object_type IN ('TABLE','VIEW','PACKAGE','PROCEDURE','FUNCTION','SEQUENCE')

小开

另一个对我有用的答案是(归功于 http://snipt.net/Fotinakis/drop-all-tables-and-constraints-within-an-oracle-schema/)

BEGIN


FOR c IN (SELECT table_name FROM user_tables) LOOP
EXECUTE IMMEDIATE ('DROP TABLE "' || c.table_name || '" CASCADE CONSTRAINTS');
END LOOP;


FOR s IN (SELECT sequence_name FROM user_sequences) LOOP
EXECUTE IMMEDIATE ('DROP SEQUENCE ' || s.sequence_name);
END LOOP;


END;

请注意，在运行马上之后，这个命令可以正常工作。它确实生成了一个需要粘贴到某个地方的脚本(就像这里的其他答案一样)。它直接在数据库上运行。

小开

如果你只是想要一个真正简单的方法来做到这一点. . 这里有一个脚本，我在过去使用

select 'drop table '||table_name||' cascade constraints;' from user_tables;

这将为模式中的所有表打印一系列拖放命令。假脱机查询的结果并执行它。

资料来源: https://forums.oracle.com/forums/thread.jspa?threadID=614090

同样，如果希望清除的表不止一个，则可以编辑以下内容以满足您的需要

select 'drop '||object_type||' '|| object_name || ';' from user_objects where object_type in ('VIEW','PACKAGE','SEQUENCE', 'PROCEDURE', 'FUNCTION', 'INDEX')

小开

begin
for i in (select 'drop table '||table_name||' cascade constraints' tbl from user_tables)
loop
execute immediate i.tbl;
end loop;
end;

小开

SELECT 'DROP TABLE "' || TABLE_NAME || '" CASCADE CONSTRAINTS;'
FROM user_tables;

user_tables是一个系统表，它包含用户的所有表 SELECT 子句将为每个表生成一个 DROP 语句你可以运行脚本

小开

移除甲骨文中的所有物体:

1)动态

DECLARE
CURSOR IX IS
SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE ='TABLE'
AND OWNER='SCHEMA_NAME';
CURSOR IY IS
SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE
IN ('SEQUENCE',
'PROCEDURE',
'PACKAGE',
'FUNCTION',
'VIEW') AND  OWNER='SCHEMA_NAME';
CURSOR IZ IS
SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('TYPE') AND  OWNER='SCHEMA_NAME';
BEGIN
FOR X IN IX LOOP
EXECUTE IMMEDIATE('DROP '||X.OBJECT_TYPE||' SCHEMA_NAME.'||X.OBJECT_NAME|| ' CASCADE CONSTRAINT');
END LOOP;
FOR Y IN IY LOOP
EXECUTE IMMEDIATE('DROP '||Y.OBJECT_TYPE||' SCHEMA_NAME.'||Y.OBJECT_NAME);
END LOOP;
FOR Z IN IZ LOOP
EXECUTE IMMEDIATE('DROP '||Z.OBJECT_TYPE||' SCHEMA_NAME.'||Z.OBJECT_NAME||' FORCE ');
END LOOP;
END;
/

2)静电

    SELECT 'DROP TABLE "' || TABLE_NAME || '" CASCADE CONSTRAINTS;' FROM user_tables
union ALL
select 'drop '||object_type||' '|| object_name || ';' from user_objects
where object_type in ('VIEW','PACKAGE','SEQUENCE', 'PROCEDURE', 'FUNCTION')
union ALL
SELECT 'drop '
||object_type
||' '
|| object_name
|| ' force;'
FROM user_objects
WHERE object_type IN ('TYPE');

小开

请按照以下步骤操作。

begin
for i in (select 'drop table '||table_name||' cascade constraints' tb from user_tables)
loop
execute immediate i.tb;
end loop;
commit;
end;
purge RECYCLEBIN;