Saturday, October 20, 2018

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';

---------------------- --------------------------------
SYS                       OPEN

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

---------------------- ---------
SYS                       11-JUL-18

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

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

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

---------------------- ------------------------------                   ------------------------------
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.

No comments:

Post a Comment

PASS Summit - Day 5

Wakey Wakey My very last morning waking up in Seattle and I finally sleep until my alarm goes off.  It was a bitter sweet morning.  On one...