7 Days of Oracle: Day #1 - dba_users

SQL Server has always been my passion. Throughout the years I have often needed to support other database technologies and Oracle is one that I have spent a lot of time with. I cannot claim to be an Oracle expert by any means, however I have experienced enough to know a couple of things that can make life easier when you're new to managing Oracle via SQL*Plus. I am dedicating 7 days of blogs to some of the basics that I utilized most.

DAY 1 - dba_users

User administration is one of the more common responsibilities of a DBA. The dba_users table contains information you need to collect information about an existing user. There is a lot more available in this table than what I show here, however, my goal is to keep things simple and stick to basics.  Here are some example statements that have proven useful.

-- Find the status of a user:
select username, account_status from dba_users where username = 'SYS';

USERNAME         ACCOUNT_STATUS

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

SYS                       OPEN

-- Find account expiration date
select username, expiry_date from dba_users where username='SYS';

USERNAME        EXPIRY_DA

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

SYS                       11-JUL-18

-- Find last time account logged in
select username, last_login from dba_users where username='SYS';

USERNAME         LAST_LOGIN

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

SYS                        05-OCT-18 09.54.53.000000000 AM -04:00

-- Find tablespaces user is configured to utilize
select username, default_tablespace, temporary_tablespace from dba_users where username='SYS';

USERNAME         DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE

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

SYS                       SYSTEM                                           TEMP

Here are a couple of commands you may need to execute once you have collected data from the dba_users table:

-- Change user password
alter user SYS identified by P#ssw0rd123;
User altered.

-- Unlock a locked account
alter user SYS account unlock;      
User altered.