How to delete all tables under a user in Oracle?

2 years ago
Liam
2 minutes

To delete all tables under an Oracle user, you can follow these steps:

  1. Firstly, log in to the Oracle database with a user account that has administrative privileges.
  2. To ensure that the user to be deleted does not have any active sessions, you can use the following command to check and kill the relevant sessions:
SELECT sid, serial# 
FROM v$session 
WHERE username = '要删除的用户名';

ALTER SYSTEM KILL SESSION 'sid, serial#' IMMEDIATE;

Replace ‘username to be deleted’ with the actual user to be deleted.

  1. Grant administrator privileges to the current session.
GRANT DBA TO 当前会话用户名;

Replace ‘current session username’ with the username of the current session.

  1. Connect to the user you want to remove with the following statement:
CONNECT 要删除的用户名/密码;
  1. Execute the following statement to delete all tables under the user.
BEGIN
   FOR cur_rec IN (SELECT object_name, object_type
                   FROM user_objects
                   WHERE object_type IN ('TABLE', 'VIEW'))
   LOOP
      BEGIN
         EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '"';
      EXCEPTION
         WHEN OTHERS THEN
            NULL;
      END;
   END LOOP;
END;
  1. Finally, disconnect from the database and reconnect as the administrator user.

Please be sure to back up your database before proceeding with these steps to prevent data loss. Additionally, use caution to avoid accidentally deleting unintended objects.

bannerAds