Setting up Oracle Database for development

To set up an Oracle Database for development I install the database in a docker container and then modify password policies in the default user profile as follows:

ALTER PROFILE DEFAULT LIMIT COMPOSITE_LIMIT UNLIMITED
  PASSWORD_LIFE_TIME UNLIMITED
  PASSWORD_REUSE_TIME UNLIMITED
  PASSWORD_REUSE_MAX UNLIMITED
  PASSWORD_VERIFY_FUNCTION NULL
  PASSWORD_LOCK_TIME UNLIMITED
  PASSWORD_GRACE_TIME UNLIMITED
  FAILED_LOGIN_ATTEMPTS UNLIMITED;

this prevents passwords from expiring and users from being locked.

If nevertheless a user’s password is expired and the user is locked, I unlock it with the following query:

ALTER USER <user> IDENTIFIED BY <password>;

For a common user, for example SYSTEM:

ALTER SESSION SET CONTAINER=CDB$ROOT;
ALTER USER SYSTEM IDENTIFIED BY <password> CONTAINER=ALL;

Determining what profile a user belongs to:

select profile from DBA_USERS where username = '<USER>';

Changing user’s profile to DEFAULT:

ALTER USER <user> PROFILE DEFAULT;

Leave a Reply

Your email address will not be published.