如何在 Oracle RDBMS 中按掩码删除用户

发布于 2024-10-08 05:27:37 字数 115 浏览 3 评论 0原文

我想删除名称开头为“WIN”的所有用户（例如“WIN$DOWS”）。是否可以写类似以下内容的内容？

drop user where name like 'WIN%'

原文

I want to drop all users who have 'WIN' at the start of their name (for example, 'WIN$DOWS'). Is it possible to write something like like the follownig?

drop user where name like 'WIN%'

分享到QQ

分享到微博

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

发布评论

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

掌心的温暖 2024-10-15 05:27:37

DROP USER 语句不支持 WHERE 子句，更不用说 LIKE 和通配符了。

您需要从 DBA_USERS 匹配，并迭代该列表：

--Bye Users!
FOR i IN (SELECT t.username
            FROM DBA_USERS t
           WHERE t.username LIKE 'WIN%') LOOP
  EXECUTE IMMEDIATE 'drop user '|| i.username ||'';
END LOOP;

The DROP USER statement doesn't support a WHERE clause, much less LIKE and wildcarding.

You need to fetch a list of users from DBA_USERS that match, and iterate over that list:

--Bye Users!
FOR i IN (SELECT t.username
            FROM DBA_USERS t
           WHERE t.username LIKE 'WIN%') LOOP
  EXECUTE IMMEDIATE 'drop user '|| i.username ||'';
END LOOP;

回复收藏 0 原文

じее 2024-10-15 05:27:37

万一级联删除

BEGIN
  FOR i IN (
    SELECT t.username
    FROM DBA_USERS t
    WHERE t.username LIKE 'WIN%') 
  LOOP
    EXECUTE IMMEDIATE 'DROP USER '|| i.username || ' CASCADE';
  END LOOP;
 EXCEPTION WHEN OTHERS THEN
   dbms_output.put_line(sqlerrm);
END;
/

In case cascade delete

BEGIN
  FOR i IN (
    SELECT t.username
    FROM DBA_USERS t
    WHERE t.username LIKE 'WIN%') 
  LOOP
    EXECUTE IMMEDIATE 'DROP USER '|| i.username || ' CASCADE';
  END LOOP;
 EXCEPTION WHEN OTHERS THEN
   dbms_output.put_line(sqlerrm);
END;
/

回复收藏 0 原文

我很OK 2024-10-15 05:27:37

在没有使用 BEGIN .. EXCEPTION .. END 语法的情况下，我在上述解决方案中遇到了错误。
这对我有用：

BEGIN
  FOR i IN (
    SELECT t.username
    FROM DBA_USERS t
    WHERE t.username LIKE 'WIN%') 
  LOOP
    EXECUTE IMMEDIATE 'DROP USER '|| i.username;
  END LOOP;
 EXCEPTION WHEN OTHERS THEN
   dbms_output.put_line(sqlerrm);
END;
/

对于级联删除添加 || i.username 之后的“CASCADE”。

I had an error with the solution above without the BEGIN .. EXCEPTION .. END syntax.
This works for me:

BEGIN
  FOR i IN (
    SELECT t.username
    FROM DBA_USERS t
    WHERE t.username LIKE 'WIN%') 
  LOOP
    EXECUTE IMMEDIATE 'DROP USER '|| i.username;
  END LOOP;
 EXCEPTION WHEN OTHERS THEN
   dbms_output.put_line(sqlerrm);
END;
/

For a cascading deletion add || ' CASCADE' after i.username.

回复收藏 0 原文

~没有更多了~