DBA: DC City Stories: Make SQLPlus Results Pretty!

Here are some common commands for the users of SQLPlus to make our results readable:
set PAGESIZE 100
This sets number of rows that appears before another header is printed. You can exclude the header all together by setting the value to 0.
set WRAP off
This ensures the results don't wrap to the next line if it's too long.
col column_name for a25 
This specifies a specific column size. Substitute the name of the column in, and the desired size in the last parameter.

DBA: DC City Stories: Oracle 11g Password Expiry

Don't be caught out with 11g's new DEFAULT profile password policy. The 11G DEFAULT profile has the following:

• 6 month password expiry
• 10 failed login attempts before lockout

Both will cause havoc when they're service accounts.


To unlock an account:


ALTER USER SCOTT ACCOUNT UNLOCK;

To find the profile settings:

col profile for a20
col resource_name for a25
col limit for a15
select profile,resource_name,limit
from dba_profiles
where resource_type='PASSWORD'
order by profile;

PROFILE    RESOURCE_NAME             LIMIT

---------- ------------------------- ---------------

DEFAULT    PASSWORD_REUSE_TIME       UNLIMITED

DEFAULT    PASSWORD_LOCK_TIME        1

DEFAULT    FAILED_LOGIN_ATTEMPTS     UNLIMITED

DEFAULT    PASSWORD_VERIFY_FUNCTION  NULL

DEFAULT    PASSWORD_LIFE_TIME        UNLIMITED

DEFAULT    PASSWORD_REUSE_MAX        UNLIMITED

DEFAULT    PASSWORD_GRACE_TIME       7

To find out the status of the accounts:

set pagesize 100
set wrap off
col USERNAME for a25
col PROFILE for a15
col ACCOUNT_STATUS for a20
col EXPIRY_DATE for a15

select USERNAME, PROFILE, ACCOUNT_STATUS, EXPIRY_DATE
from DBA_USERS;

USERNAME                  PROFILE    ACCOUNT_STATUS       EXPIRY_DATE

------------------------- ---------- -------------------- ---------------

CORPDIR_USER              DEFAULT    OPEN

MMS_USER                  DEFAULT    OPEN

NIZAR_RO                  DEFAULT    OPEN

SS_USER                   DEFAULT    OPEN

SCOTT                     DEFAULT    EXPIRED & LOCKED     17-MAR-11

ORACLE_OCM                DEFAULT    EXPIRED & LOCKED     15-AUG-09

XS$NULL                   DEFAULT    EXPIRED & LOCKED     15-AUG-09

MDDATA                    DEFAULT    EXPIRED & LOCKED     15-AUG-09

DIP                       DEFAULT    EXPIRED & LOCKED     15-AUG-09

APEX_PUBLIC_USER          DEFAULT    EXPIRED & LOCKED     15-AUG-09

SPATIAL_CSW_ADMIN_USR     DEFAULT    EXPIRED & LOCKED     15-AUG-09

SPATIAL_WFS_ADMIN_USR     DEFAULT    EXPIRED & LOCKED     15-AUG-09

DBSNMP                    MONITORING EXPIRED              20-SEP-11

To change the DEFAULT profile password policy:

ALTER PROFILE DEFAULT LIMIT
FAILED_LOGIN_ATTEMPTS UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED;