/ _ \
\_\(_)/_/
 _//"\\_     more on JOHLEM.net
  /   \	                 
  
0o0o0o0o0o0o0o0o0o0o0o0o0o0o0o0o0o0o0o0o0o0o0o0o0o0o0o0o0





Oracle Database Administration Cheat Sheet
==========================================

1. Connecting to the Database
-----------------------------
- SQL*Plus: sqlplus / as sysdba
  Connects to the database as the SYS user, with SYSDBA privileges.

2. Starting and Stopping the Database
-------------------------------------
- STARTUP
  Starts the database.
- SHUTDOWN IMMEDIATE
  Shuts down the database immediately, without waiting for current operations to complete.

3. Viewing Database Information
-------------------------------
- SELECT * FROM V$VERSION;
  Displays version information for the Oracle Database.
- SELECT INSTANCE_NAME, STATUS FROM V$INSTANCE;
  Displays the current status of the instance.

4. Managing Tablespaces
-----------------------
- CREATE TABLESPACE users DATAFILE 'users01.dbf' SIZE 500M;
  Creates a new tablespace named 'users'.
- ALTER TABLESPACE users ADD DATAFILE 'users02.dbf' SIZE 500M;
  Adds a new datafile to the 'users' tablespace.
- DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES;
  Drops the 'users' tablespace and its datafiles.

5. User Management
------------------
- CREATE USER db_user IDENTIFIED BY password;
  Creates a new database user.
- ALTER USER db_user QUOTA 100M ON users;
  Sets a quota for the 'db_user' on the 'users' tablespace.
- GRANT CONNECT, RESOURCE TO db_user;
  Grants the 'CONNECT' and 'RESOURCE' roles to 'db_user'.
- REVOKE CONNECT FROM db_user;
  Revokes the 'CONNECT' role from 'db_user'.

6. Backup and Recovery
----------------------
- RMAN> BACKUP DATABASE;
  Backs up the database using RMAN.
- RMAN> RESTORE DATABASE;
  Restores the database from a backup.

7. Monitoring and Tuning
------------------------
- SELECT SQL_ID, ELAPSED_TIME, CPU_TIME, EXECUTIONS FROM V$SQL ORDER BY ELAPSED_TIME DESC;
  Lists SQL queries with the highest elapsed time.
- EXPLAIN PLAN FOR SELECT * FROM employees;
  Displays the execution plan for a query.

8. Managing Sessions and Processes
----------------------------------
- SELECT SID, SERIAL#, USERNAME FROM V$SESSION;
  Lists active sessions.
- ALTER SYSTEM KILL SESSION 'sid,serial#';
  Kills a specific session.

9. Viewing and Managing Locks
-----------------------------
- SELECT SESSION_ID, LOCK_TYPE, MODE_HELD FROM DBA_LOCKS WHERE LOCKED_MODE != 0;
  Lists database locks.

10. Exporting and Importing Data
--------------------------------
- expdp system/password SCHEMAS=hr DIRECTORY=dp_dir DUMPFILE=HR.dmp LOGFILE=export.log
  Exports the 'hr' schema using Data Pump.
- impdp system/password SCHEMAS=hr DIRECTORY=dp_dir DUMPFILE=HR.dmp LOGFILE=import.log
  Imports data from a Data Pump export.

This cheat sheet provides a foundational overview of essential Oracle Database administration commands. It's designed to get you started with database management and to serve as a quick reference for common tasks. Remember to consult Oracle's documentation for detailed information and considerations specific to your database version and configuration.