oracle 清空某个分区,[转]利用Truncate清空Oracle各个分区的资料.

最新推荐文章于 2024-08-07 17:22:15 发布

转载

最新推荐文章于 2024-08-07 17:22:15 发布 · 586 阅读

文章标签：

#oracle 清空某个分区

本文介绍了一种安全、高效地在Oracle数据库中清空特定分区的方法，通过创建自定义存储过程来实现。这种方法避免了在生产环境中授予高权限角色，而是要求拥有者授权执行权限和删除权限给特定用户，确保操作的可控性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

In applications, users need truncate privileges on third party tables. The owner of the table can truncate his/her own tables. A user with DBA role granted can truncate a third party table. Any user with "drop any table" system privilege can truncate a third party table.

It is not recommended in Production environments to grant powerful roles like DBA role or privileges like "drop any table" privilege to accomplish this purpose.

This procedure need to be created in each schema. The schema owner need to grant execute permission to the user, and the delete permission on specified tables.

Then the user can execute the procedure to truncate tables User A owns the table temp_jp.

conn uwclass/uwclass

CREATE TABLE t1 AS

SELECT * FROM all_tables;

CREATE OR REPLACE PROCEDURE trunc_tab(p_tabname IN VARCHAR2) AUTHID DEFINER AS

/**********************************

AUTHOR JP Vijaykuma