How Many Profiles Can an Oracle Database Have?

When you create a database, Oracle gives you one profile, called DEFAULT. You can then create more profiles if you need them. I wondered if there might be a limit to the number of profiles you could create. I wrote some SQL to create 100,000 profiles and ran it in an Oracle 11.2.0.4 database.

SQL> conn system/manager
Connected.
SQL> select distinct profile from dba_profiles
  2  /

PROFILE
------------------------------
DEFAULT

SQL> declare
  2   sql_statement varchar2(100);
  3  begin
  4   for i in 1..100000 loop
  5    sql_statement := 'create profile profile'||i||
  6    ' limit idle_time 10';
  7    execute immediate sql_statement;
  8   end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> select count(distinct profile)
  2  from dba_profiles
  3  /

COUNT(DISTINCTPROFILE)
----------------------
                100001

SQL> 

This should be enough for most applications. If you have some strange database with over 100,000 users, each one needing a different profile, your applications probably need a redesign.