oracle unlock\lock user account

oracle unlock\lock useraccount

解锁:
SQL>conn sys/password as sysdba;

SQL>alter user hr account unlock;

由于用户还处于过期状态,所以需要重设密码:

SQL> password hr;
Changing password for hr
New password: **
Retype new password: ** (hr)
Password changed
SQL>

SQL> conn hr/hr;
Connected.
SQL>

批量所用户

SQL> select username, DEFAULT_TABLESPACE from dba_users;

USERNAME                      DEFAULT_TABLESPACE
------------------------------ ------------------------------
SYS                           SYSTEM
SYSTEM                        SYSTEM
DBSNMP                        SYSAUX
SYSMAN                        SYSAUX
SCOTT                         USERS
MAX                           USERS
HR                            USERS
TEST                          TEST
OUTLN                         SYSTEM
FLOWS_FILES                   SYSAUX
MDSYS                         SYSAUX

USERNAME                      DEFAULT_TABLESPACE
------------------------------ ------------------------------
ORDSYS                        SYSAUX
EXFSYS                        SYSAUX
WMSYS                         SYSAUX
APPQOSSYS                     SYSAUX
APEX_030200                   SYSAUX
OWBSYS_AUDIT                  SYSAUX
ORDDATA                       SYSAUX
CTXSYS                        SYSAUX
ANONYMOUS                     SYSAUX
XDB                           SYSAUX
ORDPLUGINS                    SYSAUX

USERNAME                      DEFAULT_TABLESPACE
------------------------------ ------------------------------
OWBSYS                        SYSAUX
SI_INFORMTN_SCHEMA            SYSAUX
OLAPSYS                       SYSAUX
ORACLE_OCM                    USERS
XS$NULL                       USERS
BI                            USERS
PM                            USERS
MDDATA                        USERS
IX                            USERS
SH                            USERS
DIP                           USERS

USERNAME                      DEFAULT_TABLESPACE
------------------------------ ------------------------------
OE                            USERS
APEX_PUBLIC_USER              USERS
SPATIAL_CSW_ADMIN_USR         USERS
SPATIAL_WFS_ADMIN_USR         USERS

37 rows selected.

SQL> select 'alter user ' || username || ' account lock' || ';'from dba_users
  2  wheredefault_tablespace  ='USERS'
  3  and username like 'H%'
  4  order by username ;

'ALTERUSER'||USERNAME||'ACCOUNTLOCK'||';'
-------------------------------------------------------
alter user HR account lock;

SQL> select 'alter user ' || username || ' account lock' || ';'from dba_users
  2  where default_tablespace in('USERS','SYSTEM')
  3  and username like 'S%'
  4  order by username ;

'ALTERUSER'||USERNAME||'ACCOUNTLOCK'||';'
-------------------------------------------------------
alter user SCOTT account lock;
alter user SH account lock;
alter user SPATIAL_CSW_ADMIN_USR account lock;
alter user SPATIAL_WFS_ADMIN_USR account lock;
alter user SYS account lock;
alter user SYSTEM account lock;

6 rows selected.