How to fix “ORA-28002: the password will expire within X days” errors, by Zahar Hilkevich

In Oracle 11g (and probably all other subsequent releases) the DEFAULT profile comes with PASSWORD_LIFE_TIME attribute equal to 180 days. So in about 6 month users will start getting ORA-28002 errors if they have not changed their passwords since their accounts were created.

If it is critical not to expire passwords automatically (which may or may not be a good idea), a DBA should alter the default profile as follows:

ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME UNLIMITED
/

This change will not apply immediately to those users who already started getting ORA-28002 errors. All they need to do is to change their password even to the same value.
At the same time, a DBA can help the users and “alter” their passwords even without knowing them. This becomes really helpful when the number of such database users is high.

When I first faced this issue I came up with the following script that does the trick. The script should be executed by a sysdba user.

DECLARE
  CURSOR c IS
   SELECT u.name, u.spare4
   FROM user$ u JOIN dba_users d ON u.name=d.username
   WHERE d.account_status='EXPIRED(GRACE)';
BEGIN
   FOR v IN c LOOP
	EXECUTE IMMEDIATE 'ALTER USER ' || v.name || ' IDENTIFIED BY VALUES ''' || v.spare4 || '''';
   END LOOP;
END;
/

Here you go!

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Advertisements

Analyze all objects in current schema

Since Oracle version 10, ANALYZE command has been deprecated for statistics gathering. The command stays in Oracle and is intended to be used to list chained rows and validate structure.

The ANALYZE command was superseded by procedures in dbms_stats package.

Specifically, if you need to collect statistics for a schema, you need to use dbms_stats.gather_schema_stats procedure.

If you want to run it for the current schema with all default parameters use this:

exec dbms_stats.gather_schema_stats(USER)

The full signature of this procedure:

DBMS_STATS.GATHER_SCHEMA_STATS ( 
   ownname          VARCHAR2, 
   estimate_percent NUMBER   DEFAULT to_estimate_percent_type 
                                                (get_param('ESTIMATE_PERCENT')), 
   block_sample     BOOLEAN  DEFAULT FALSE, 
   method_opt       VARCHAR2 DEFAULT get_param('METHOD_OPT'),
   degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')), 
   granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'), 
   cascade          BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')), 
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL, 
   options          VARCHAR2 DEFAULT 'GATHER', 
   objlist          OUT      ObjectTab,
   statown          VARCHAR2 DEFAULT NULL, 
   no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')),
  force             BOOLEAN DEFAULT FALSE);

Nuances when dropping an Oracle user, by Zahar Hilkevich

When you need to drop a user who is not currently connected to the database, the task is trivial (please do not try it if you don’t really need to drop your user):

DROP USER SCOTT CASCADE;

With actively used database user schemas,  the above command may fail due to active Oracle sessions opened by that user (ORA-01940: cannot drop a user that is currently connected). So you may want to drop all those sessions first before dropping the user:

DECLARE
    CURSOR c IS
    SELECT sid, serial#
    FROM v$session 
    WHERE username='SCOTT';

    v_sql  VARCHAR2(200):='ALTER SYSTEM KILL SESSION ''{sid},{serial#}'' IMMEDIATE';
    v_kill VARCHAR2(200);
BEGIN
    FOR v IN c LOOP
        v_kill:=REPLACE(v_sql,'{sid}',v.SID);
        v_kill:=REPLACE(v_kill,'{serial#}',v.serial#);
        EXECUTE IMMEDIATE v_kill;
    END LOOP;
END;
/

Seems like we are ready now to drop the user? Not so quick! While we were dropping existing sessions, the user might have opened more new sessions, so dropping a user may still fail with the same error:

ORA-01940: cannot drop a user that is currently connected

To work around this issue, we need to apply a simple trick: revoke a privilege to create a session before killing existing sessions! Here is a complete script (to be executed as SYS or SYSTEM):

REVOKE CONNECT FROM scott -- so new sessions will be blocked
/
--If CREATE SESSION privilege was granted to scott, we may want to revoke it as well
--Kill existing sessions:
DECLARE
    CURSOR c IS
    SELECT sid, serial#
    FROM v$session 
    where username='SCOTT';

    v_sql  VARCHAR2(200):='ALTER SYSTEM KILL SESSION ''{sid},{serial#}'' IMMEDIATE';
    v_kill VARCHAR2(200);
BEGIN
    FOR v IN c LOOP
        v_kill:=REPLACE(v_sql,'{sid}',v.SID);
        v_kill:=REPLACE(v_kill,'{serial#}',v.serial#);
        EXECUTE IMMEDIATE v_kill;
    END LOOP;
END;
/
--Drop the user
DROP USER SCOTT CASCADE
/