Oracle DBA Cheat Sheet: Essential Commands for Database Administration

Leave a reply

Managing an Oracle Database requires knowledge of various commands to ensure smooth operations, performance tuning, and troubleshooting. This cheat sheet provides essential Oracle DBA commands to help database administrators efficiently manage databases.

1. Database Startup and Shutdown

Start the Database:

STARTUP;

Shutdown the Database:

SHUTDOWN IMMEDIATE;

Shutdown with Options:

  • SHUTDOWN NORMAL; – Waits for users to disconnect.
  • SHUTDOWN IMMEDIATE; – Rolls back uncommitted transactions and disconnects users.
  • SHUTDOWN ABORT; – Forces shutdown without rollback (use cautiously).

2. User Management

Create a New User:

CREATE USER username IDENTIFIED BY password;

Grant Privileges to a User:

GRANT CONNECT, RESOURCE TO username;

Assign DBA Role:

GRANT DBA TO username;

Change User Password:

ALTER USER username IDENTIFIED BY newpassword;

Lock/Unlock a User Account:

ALTER USER username ACCOUNT LOCK;
ALTER USER username ACCOUNT UNLOCK;

3. Tablespace Management

Create a New Tablespace:

CREATE TABLESPACE tablespace_name
DATAFILE '/path/to/datafile.dbf' SIZE 100M;

Add a Datafile to Tablespace:

ALTER DATABASE ADD DATAFILE '/path/to/datafile.dbf' SIZE 200M;

Resize a Datafile:

ALTER DATABASE DATAFILE '/path/to/datafile.dbf' RESIZE 500M;

Drop a Tablespace:

DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;

4. Backup and Recovery (Using RMAN)

Connect to RMAN:

rman TARGET /;

Take Full Database Backup:

BACKUP DATABASE;

Take Tablespace Backup:

BACKUP TABLESPACE tablespace_name;

Restore Database:

RESTORE DATABASE;
RECOVER DATABASE;

5. Performance Tuning

Check Running Sessions:

SELECT SID, SERIAL#, USERNAME, STATUS FROM V$SESSION;

Kill a Session:

ALTER SYSTEM KILL SESSION 'SID,SERIAL#';

Check Long Running Queries:

SELECT SQL_ID, START_TIME, SQL_TEXT FROM V$SQL WHERE EXECUTIONS > 100;

6. Viewing Database Information

Check Database Name and Status:

SELECT NAME, OPEN_MODE FROM V$DATABASE;

Check Tablespace Usage:

SELECT TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024 AS SIZE_MB FROM DBA_DATA_FILES;

Check Free Space in Tablespaces:

SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 AS FREE_MB FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;

7. Miscellaneous Commands

Flush the Shared Pool:

ALTER SYSTEM FLUSH SHARED_POOL;

Flush the Buffer Cache:

ALTER SYSTEM FLUSH BUFFER_CACHE;

Enable/Disable Archive Log Mode:

ALTER DATABASE ARCHIVELOG;
ALTER DATABASE NOARCHIVELOG;

Conclusion

This Oracle DBA cheat sheet covers essential commands for user management, backup and recovery, performance tuning, and more. Bookmark this page and refer back whenever needed.

Are there any specific Oracle DBA topics you’d like to explore in more detail? Let us know in the comments!

Leave a Reply