Question:

Hi all,

There are some things that I don't understand anymore as a simple developer.

In the init.ora file is a parameter called max_enabled_roles.
Our DBA says: maximum is 48 at Oracle 7.3.3.6.3.

When I look in sys.dba_roles I see 52 roles.
When I look in session_roles I see 21 roles (active roles ?) ?
When I look in User_role_privs I see 5 roles.

when I create, asy about 45 new roles it is accepted, but when I start
SQL*PLUS again, I'm not allowed to, because the maximum_enabled _roles is
exceeded.  As another user I can still start SQL*PLUS.

Who can give me some explanation

Tia

Hein van Vroonhoven

Question 1:

Answers embedded

Hth,

Sybrand Bakker, Oracle DBA

Hein van Vroonhoven <[email protected]> wrote in message
news:B1D5796F39B9D311908C00805F59305C022B0BFD@news.ahm-nl.akzonobel.nl...
> Hi all,
>
> There are some things that I don't understand anymore as a simple
developer.
>
> In the init.ora file is a parameter called max_enabled_roles.
> Our DBA says: maximum is 48 at Oracle 7.3.3.6.3.
>
No, untrue. Obviously he doesn't know how the change max_enabled_roles. Max
is 128.
> When I look in sys.dba_roles I see 52 roles.
Are the roles which have been created in the database.
> When I look in session_roles I see 21 roles (active roles ?) ?

These are the roles you have currently enabled in your session

> When I look in User_role_privs I see 5 roles.
You probably look at 5 privileges granted to one single role.
>
> when I create, asy about 45 new roles it is accepted, but when I start
> SQL*PLUS again, I'm not allowed to, because the maximum_enabled _roles is
> exceeded.  As another user I can still start SQL*PLUS.
All roles are automatically enabled by SYS by default. Roles are not enabled
immediately when you login

Answer 2:

Max_enabled_roles can be set to 148 (not 48).
When you created the roles -- they are granted to you implicity, you
have them. Hence by creating 45 more after already having 5, you
exceeded your limit of 48 (note: SYS has all roles by default so you'll
find SYS can no long log in either.
You'll want to set max_enabled_roles to some larger number and restart
the database or drop some of those roles.
SVRMGR> show parameter max_enabled
NAME TYPE VALUE
----------------------------------- ------- ----------------------------
--
max_enabled_roles integer
148
SVRMGR> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle7 Server Release 7.3.4.0.1 - Production
PL/SQL Release 2.3.4.0.0 - Production
CORE Version 3.5.4.0.0 - Production
TNS for Solaris: Version 2.3.4.0.0 - Production
NLSRTL Version 3.2.4.0.0 - Production
--
Thomas Kyte [email protected]
Oracle Service Industries http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp