Showing results for 
Search instead for 
Did you mean: 

Oracle 9.2.0 SYSDBA vs. SYSOPER schema required?

Go to solution
Ralph Grothe
Honored Contributor

Oracle 9.2.0 SYSDBA vs. SYSOPER schema required?

Hello Sysadmins with DBA background,

I'm currently about to replace a backup shell script by a Perl script but need some Oracle knowledge while I have no DBA in reach whom I could ask.
(the usual thing, they always expect us sysadmins to confess to their inquisitory grillings immediately but are never available when we need info from them ;-)

I need to set tablespaces into the state of backup mode (usually done by something like "alter tablespace BLABLA begin backup".
In the script this was done through a here file construct invoking the sqlplus binary.
Obviously with Perl and DBI I can avoid this mess.
But I got a bit confused when I had a look at the POD of DBD::Oracle again.
There it says that you can (amongst others) supply the connect() constructor with an attribute hash ref to distinguish between the schemas SYSDBA and SYSOPER.
Here's an excerpt from the POD:

Connect Attributes

The ora_session_mode attribute can be used to connect with SYSDBA
authorization and SYSOPER authorization.

$mode = 2; # SYSDBA
$mode = 4; # SYSOPER
DBI->connect($dsn, $user, $passwd, { ora_session_mode => $mode });

Now I wonder which schema I will have to use to acquire a database handle that allows me to set tablespaces into backup mode (and back afterwards), SYSDBA or SYSOPER?


Madness, thy name is system administration
Graham Cameron_1
Honored Contributor

Re: Oracle 9.2.0 SYSDBA vs. SYSOPER schema required?

Either will do what you want.

SYSOPER is the less powerful, so is probably a bit safer.

SYSDBA is a superset of SYSOPER, and allows anything, ilcluding CREATE (and therefore DROP) DATABASE...

As a hp-ux analogy, SYSDBA is like root, and sysoper is like restricted SAM for non-root access.


-- Graham
Computers make it easier to do a lot of things, but most of the things they make it easier to do don't need to be done.