How to temporariliy disable access to Oracle database?

Question

I would like to be able to temporarily disable all access to an Oracle database, except my own, to be able to perform maintenance actions without interruptions. Simplest way?

score 4 · Answer 1 · edited Jun 11 '20 at 10:02

4

Check out this link:

How to switch Oracle in Single User-Multi Session Mode

During a migration it may be important, that nobody can connect to the database. You may have two possibilities to achieve this

On Table Level using LOCK TABLE

On Database Level using RESTRICTED SESSION

edited Jun 11 '20 at 10:02

Community

1

answered Nov 05 '10 at 17:34

score 3 · Answer 2 · answered Nov 06 '10 at 20:59

3

See Quiescing a Database

Short:

ALTER SYSTEM QUIESCE RESTRICTED;
puts the Database in quiesce mode
ALTER SYSTEM UNQUIESCE;
puts the database back in normal operation

answered Nov 06 '10 at 20:59

pacey

3,833
1
16
31

score 1 · Answer 3 · answered Nov 06 '10 at 20:54

1

This is known as quiescing the database, there are number of options depending on how you want to deal with users who are presently connected. My favourite technique when I'm in a tight change window is STARTUP FORCE RESTRICT.

answered Nov 06 '10 at 20:54

Gaius

1,471
1
12
19

score 1 · Answer 4 · answered Nov 12 '10 at 19:32

1

Additionally, a more basic solution would be to shutdown the listener.

answered Nov 12 '10 at 19:32

Sébastien Stoetzer

11
1

2

This will only work if you have no access from the database server itself. These connections bypass the listener. – Rob van Laarhoven Nov 23 '10 at 08:16

How to temporariliy disable access to Oracle database?

4 Answers4

Short: