Oracle DBA Cheat Sheet

1. View the version of Oracle that is installed.

   SELECT * FROM PRODUCT_COMPONENT_VERSION;
    - or -
   SELECT * FROM V$VERSION;

2. View database name.

   SELECT NAME FROM V$DATABASE;
    - or -
   SELECT * FROM GLOBAL_NAME;

3. View NLS (National Language Support) Parameters

   SELECT * FROM NLS_DATABASE_PARAMETERS;
    - or -
   SELECT * FROM V$NLS_PARAMETERS;

4. View Sessions

   SELECT SCHEMANAME, OSUSER, MACHINE, PROGRAM, STATE FROM V$SESSION;

5. View Services

   SELECT SERVICE_ID, NAME, NETWORK_NAME FROM DBA_SERVICES;

6. View current database instance details

   SELECT INSTANCE_NAME, HOST_NAME, VERSION, STARTUP_TIME, STATUS FROM V$INSTANCE;

1. List tablespaces, status and type

   SELECT TABLESPACE_NAME, STATUS, CONTENTS FROM DBA_TABLESPACES;

2. Create tablespace

   CREATE TABLESPACE myspace 
       DATAFILE 'datafile_directory_path\data_file_name.dbf' 
       SIZE 20M 
       AUTOEXTEND ON
       NEXT 512K
    MAXSIZE UNLIMITED;

3. List Datafiles, tablespaces and status

   SELECT FILE_NAME, TABLESPACE_NAME, STATUS FROM DBA_DATA_FILES;

4. To check the current size of a tablespace

   SELECT SUM(bytes/1024/1024/1024) "Size in GB" from DBA_DATA_FILES WHERE TABLESPACE_NAME='MYSPACE';

3. List Datafiles, tablespaces and status

   SELECT FILE_NAME, TABLESPACE_NAME, STATUS FROM DBA_DATA_FILES;

5. Check the size of a database

Size of an Oracle database is the sum of the size of its Data files, Temporary files, Redo logs and Control files.

   SELECT ROUND(
       SUM(Q1."Data Files" + 
           Q2."Temp Files" + 
           Q3."Redo Logs" + 
           Q4."Control Files"
           )/1024/1024/1024,  2) 
       AS "Total Size (GB)"
   FROM
    (SELECT SUM(bytes) "Data Files" from DBA_DATA_FILES) Q1,
    (SELECT SUM(bytes) "Temp Files" from DBA_TEMP_FILES) Q2,
    (SELECT SUM(bytes) "Redo Logs" from V_$LOG) Q3,
    (SELECT SUM(BLOCK_SIZE * FILE_SIZE_BLKS)"Control Files" FROM V$CONTROLFILE) Q4;

1. List all users, account status and profile

   SELECT USERNAME, ACCOUNT_STATUS, PROFILE FROM DBA_USERS;

2. List all roles

   SELECT * FROM DBA_ROLES;

3. Create User

   CREATE USER charlie IDENTIFIED BY password123;

4. Change user password

   ALTER USER charlie IDENTIFIED BY newpassword;
    - or -
   PASSWORD

5. Create user profile (with all default limits)

   CREATE PROFILE MY_PROFILE LIMIT;

6. View all user profiles and limits

   SELECT * FROM DBA_PROFILES;
   SELECT * FROM DBA_PROFILES WHERE PROFILE='MY_PROFILE';

7. Change password lifetime, reuse time, failed login attempts

   SELECT * FROM DBA_PROFILES WHERE PROFILE='MY_PROFILE' AND RESOURCE_NAME = 'PASSWORD_LIFE_TIME';

8. Set password expiry

To set password to 60 days for example:

   ALTER PROFILE MY_NEW_PROFILE LIMIT PASSWORD_LIFE_TIME 60;

To set password to never expire:

   ALTER PROFILE MY_PROFILE LIMIT PASSWORD_LIFE_TIME UNLIMITED;

9. View privileges granted to a user on other users tables

   SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE='USERNAME';

10. View all user privileges including the privileges that are indirectly granted through roles

   SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='USERNAME' or GRANTEE in (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE='USERNAME');