Preventing Users from Connecting

[TStriker]

This is probably a deadly simple task but I'm an Oracle rookie so I'm not too embarrased.

I'm Running Oracle 8i on Windows 2000 Server. Every couple of months I do an export like this:

exp system/password@MYDB file=myfile.dmp log=mylog.log full=y

Then I drop all of the objects, import, and compile an invalid objects. I do this to keep the extents under control. I usually do this in the wee hours on the weekend so the chances of a user connecting (via the core ERP application) is remote but the possibility still exists. Is there any way to prevent users from connecting? I'm worried that someone will connect during the process and mess it up for me or I'll mess it up for them. Someone once suggested stopping the listener service but that also killed my ability to do the export.

Thanks!

-Striker

 

[Beilstwh]

Let me first say that what you are doing is generally NOT necessary. We have a database with over 100 gig of storage and has never needed to defragment. That being said, simply do the following

SHUTDOWN IMMEDIATE;

STARTUP RESTRICT;

This will allow only the dba accounts (sys,system) to reconnect. When you are done with your export and import issue the following command to reopen

ALTER DATABASE OPEN;



Bill
Oracle DBA/Developer
New York State, USA

 

[SantaMufasa]

I do this to keep the extents under control.

The fact that you are concerned about extents implies to me that you are not using "EXTENT MANAGEMENT LOCAL..." for your tablespaces. If you did use this option, then you would never again need to worry about extent-management issues.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[TStriker]

Bill,

Thank you very much! That is exactly what I'm looking for.

I figured I'd get some additional advice too with such a basic question. I don't doubt for a minute that you are correct but I'm guessing that keeping up with the extents as you suggest would not be the kind of thing that someone could do with very little training in Oracle administration.

-Striker

 

[SantaMufasa]

Also, Striker, there is a method of defragmenting tables and indexes that is much faster than exp/imp:

alter table <owner>.<tablename> move parallel nologging;
alter index <owner>.<indexname> rebuild parallel;

The only tables for which this technique is not appropriate is for tables with large objects (e.g., LONG, LONG RAW, CLOB, BLOB).

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]