How to disconnect a user from Oracle

Source: Internet
Author: User
During Oracle data restoration today, we found that the restored data is incorrect and there are repeated records. Query the relevant information and know that the user corresponding to the database should be deleted before restoration. However

During Oracle data restoration today, we found that the restored data is incorrect and there are repeated records. Query the relevant information and know that the user corresponding to the database should be deleted before restoration. However

During Oracle data restoration today, we found that the restored data is incorrect and there are repeated records. Query the relevant information and learn that the user corresponding to the database should be deleted before restoration, and then create a user with the same name, specify the corresponding tablespace, temporary space, and so on, and authorize. However, when deleting a user, the user must be disconnected. The following describes how to disconnect the user (tb_test) during the test.

Each user's connection information can be found in v $ session, for example, tb_test user connection information:

Select sid, serial # from v $ session where username = 'tb _ test ';

Obtain a process list and use alter system kill session 'sid, serial '; to terminate the session.

Eg. alter system kill session '2017, 22 ';

It can be seen that there may be multiple rows of records returned, but the statement alter system kill session 'sid, serial 'needs to be executed multiple times. The cursor may also be used to end all sessions at a time.

Eg. declare
V_sid v $ session. sid % type;
V_serial v $ session. serial # % type;

CURSOR killUser is
Select sid, serial #, username from v $ session where UPPER (username) = UPPER ('tb _ test ');
Begin
If killUser % isopen = false then
Open killUser;
End if;
Fetch killUser into v_sid, v_serial, v_userName;
While killUser % found loop
Execute immediate 'alter system kill session ''' | v_sid | ',' | v_serial | '''';

Fetch killUser into v_sid, v_serial, v_userName;
End loop;
Close killUser;
End;
/

After testing, the above method can achieve the goal. I just learned oracle and hope you can give me some advice.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.