Set SYS password

$ cd /u01/app/oracle/product/11.2.0/db_1/dbs/

$ orapwd file=orapwdbname password=some_password entries=5 force=y

New user

SQL> CREATE USER oracle IDENTIFIED BY PASSWORD;

Change password, unlock

SQL> alter user user_name identified by user_password account unlock;

Backup and Restore forgotten password using hash format

SQL> SELECT name,password FROM SYS.USER$ WHERE password is not null and name ='USER_NAME';

It will return something like F854844C34400B67

SQL> alter user USER_NAME identified by values 'F854844C34400B67';

Remove password expiration

SQL> select profile from DBA_USERS where username = 'USER_NAME';

SQL> alter profile PROFILE_NAME limit password_life_time UNLIMITED;

SQL> select resource_name,limit from dba_profiles where profile='PROFILE_NAME';

Show users status

SQL> SELECT username, account_status, lock_date FROM dba_users;

SQL>
col username format a21
col account_status format a20
col last_login format a41
select username, account_status, last_login from dba_users;                   -- syntax for version 12c

Connected Users

SQL> select username from v$session;

Who and when logged to the database

SQL>
col username   format a15
col last_login format a25
alter session set NLS_TIMESTAMP_TZ_FORMAT='DD.MM.YYYY HH24:MI:SS';

SQL> select username,logon_time from v$session where logon_time > '24/12/2015 07:00:00'; 

SQL> select username, last_login from dba_users order by username;                -- syntax for version 12c

SQL> select * from v$session where logon_time between TO_DATE('25/02/2016 00:00:00', 'dd/mm/yyyy HH24:MI:SS') and TO_DATE('26/02/2016 23:59:00', 'dd/mm/yyyy HH24:MI:SS');

Audit of the archivelogs, last actions in the database

SQL>
SELECT username, terminal, timestamp, owner, obj_name, action_name, sessionid, entryid, statementid
    FROM dba_audit_object
    WHERE username = 'COLVIR' and timestamp BETWEEN TO_DATE('19/09/2014 18:30:00', 'dd/mm/yyyy HH24:MI:SS') and TO_DATE('19/09/2014 20:30:00', 'dd/mm/yyyy HH24:MI:SS');

Who eating memory

SQL> 
SELECT USERNAME, round(VALUE/1024/1024,0) "Current UGA memory, Mb"
   FROM V$SESSION sess, V$SESSTAT stat, V$STATNAME name
WHERE sess.SID = stat.SID
   AND stat.STATISTIC# = name.STATISTIC#
   AND name.NAME = 'session uga memory'
ORDER BY "Current UGA memory, Mb" Desc;

Listing Object Privileges Granted to a User

SQL> SELECT TABLE_NAME, PRIVILEGE, GRANTABLE FROM DBA_TAB_PRIVS WHERE GRANTEE = 'USER_NAME';

TABLE_NAME    PRIVILEGE     GRANTABLE
------------------    ------------        ----------
EMP                   SELECT               NO
EMP                   DELETE              NO

SQL> SELECT GRANTEE, TABLE_NAME, COLUMN_NAME, PRIVILEGE FROM DBA_COL_PRIVS;

GRANTEE    TABLE_NAME    COLUMN_NAME   PRIVILEGE
---------------   ------------      -------------           --------------
SWILLIAMS    EMP              ENAME                INSERT
SWILLIAMS    EMP              JOB                     INSERT
JWARD           EMP              NAME                  INSERT
JWARD           EMP              JOB                     INSERT

Listing Direct System Privileges Granted to a User

SQL>
column PRIVILEGE format a25;
column ADMIN_OPTION format a5;
SELECT PRIVILEGE, ADMIN_OPTION FROM DBA_SYS_PRIVS WHERE GRANTEE = 'USER_NAME';

PRIVILEGE                                   ADMIN
---------------------------------------    -----------
SELECT ANY DICTIONARY               NO
DROP PROFILE                                NO
SELECT ANY SEQUENCE                  NO
CREATE VIEW                                  YES
DROP PUBLIC SYNONYM               YES
…

Listing Current Session’s System Privileges

SQL> SELECT * FROM SESSION_PRIVS;

PRIVILEGE
-------------------------
ALTER SYSTEM
AUDIT SYSTEM
CREATE SESSION
ALTER SESSION
RESTRICTED SESSION
CREATE TABLESPACE
…

Roles of the Database

SQL> SELECT * FROM DBA_ROLES;

ROLE                             PASSWORD
--------------------------      --------
CONNECT                       NO
RESOURCE                     NO
DBA                                NO
SECURITY_ADMIN          YES

Show user's security  profile

SQL> SELECT USERNAME, PROFILE, ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME='USER_NAME';

Show profiles and it settings

SQL> SELECT * FROM DBA_PROFILES ORDER BY PROFILE;

SQL> alter user USER_NAME profile some_profile;

SQL> create user USER_NAME identified by password profile some_profile;

CREATE PROFILE new_profile
  LIMIT PASSWORD_REUSE_MAX 10
        PASSWORD_REUSE_TIME 30;

CREATE PROFILE app_user LIMIT
   SESSIONS_PER_USER                   UNLIMITED 
   CPU_PER_SESSION                       UNLIMITED 
   CPU_PER_CALL                              3000 
   CONNECT_TIME                            45 
   LOGICAL_READS_PER_SESSION  DEFAULT 
   LOGICAL_READS_PER_CALL         1000 
   PRIVATE_SGA                                 15K
   COMPOSITE_LIMIT                         5000000; 

FAILED_LOGIN_ATTEMPTS  :Maximum times the user is allowed in fail login before locking the user account * 10
PASSWORD_LIFE_TIME  :Number of days the password is valid before expiry * 108 days
PASSWORD_REUSE_TIME  :Number of day after the user can use the already used password * UNLIMITED
PASSWORD_REUSE_MAX  :Number of times the user can use the already used password * UNLIMITED
PASSWORD_LOCK_TIME  :Number of days the user account remains locked after failed login * 1 day
PASSWORD_GRACE_TIME  :Number of grace days for user to change password * 7 days
PASSWORD_VERIFY_FUNCTION  :PL/SQL that can be used for password verification * NO DEFAULT SETTING
SEC_CASE_SENSITIVE_LOGON  :To control the case sensitivity in passwords * TRUE

SQL> ALTER PROFILE accountant LIMIT CPU_PER_CALL default LOGICAL_READS_PER_SESSION 20000 SESSIONS_PER_USER 1;

Drop user

SQL> DROP PROFILE accountant;

SQL> DROP PROFILE accountant CASCADE;             -- to deassign the profile from any users to whom it is assigned