1. Database Architecture

• Components:
  • Instance: Memory structures and background processes.
  • Database: Physical files (data files, control files, redo log files).
• Physical Files:
  • Data Files: Store actual data.
  • Control Files: Metadata about the database.
  • Redo Log Files: Track changes for recovery.

2. Connecting to Oracle Database

• SQL*Plus Connection:
  
  Copy Code Copied Use a different Browser

  
  sqlplus sys as sysdba
  

3. User Management

• Create User:
  
  Copy Code Copied Use a different Browser

  
  CREATE USER username IDENTIFIED BY password;
  

• Grant Privileges:
  
  Copy Code Copied Use a different Browser

  
  GRANT privilege TO username;
  

• Create Roles:
  
  Copy Code Copied Use a different Browser

  
  CREATE ROLE role_name;
  GRANT privilege TO role_name;
  GRANT role_name TO username;
  
  

• Drop User:
  
  Copy Code Copied Use a different Browser

  
  DROP USER username CASCADE;
  

4. Schema Management

• Show All Users:
  
  Copy Code Copied Use a different Browser

  
  SELECT username FROM all_users;
  

• Show All Schemas:
  
  Copy Code Copied Use a different Browser

  
  SELECT username FROM dba_users;
  

5. Tablespace Management

• Create Tablespace:
  
  Copy Code Copied Use a different Browser

  
  CREATE TABLESPACE tablespace_name
  DATAFILE 'datafile_name.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
  
  

• Show Tablespaces:
  
  Copy Code Copied Use a different Browser

  
  SELECT tablespace_name, status FROM dba_tablespaces;
  

• Drop Tablespace:
  
  Copy Code Copied Use a different Browser

  
  DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;
  

• Add Datafile:
  
  Copy Code Copied Use a different Browser

  
  ALTER TABLESPACE tablespace_name ADD DATAFILE 'newfile.dbf' SIZE 50M;
  

6. Database Backup and Recovery

• Backup Command (RMAN):
  
  Copy Code Copied Use a different Browser

  
  RMAN> BACKUP DATABASE;
  

• Restore Command (RMAN):
  
  Copy Code Copied Use a different Browser

  
  RMAN> RESTORE DATABASE;
  

• Recover Command (RMAN):
  
  Copy Code Copied Use a different Browser

  
  RMAN> RECOVER DATABASE;
  

7. Monitoring and Performance Tuning

• View Current Sessions:
  
  Copy Code Copied Use a different Browser

  
  SELECT * FROM v$session;
  

• Check System Load:
  
  Copy Code Copied Use a different Browser

  
  SELECT * FROM v$load_average;
  

• View Wait Events:
  
  Copy Code Copied Use a different Browser

  
  SELECT * FROM v$load_average;
  

• View Top SQL Queries:
  
  Copy Code Copied Use a different Browser

  
  SELECT * FROM v$sql ORDER BY executions DESC;
  

• Check Instance Performance:
  
  Copy Code Copied Use a different Browser

  
  SELECT * FROM v$instance;
  

8. User and Role Management

• Show User Privileges:
  
  Copy Code Copied Use a different Browser

  
  SELECT * FROM dba_sys_privs WHERE grantee = 'username';
  

• Show Role Privileges:
  
  Copy Code Copied Use a different Browser

  
  SELECT * FROM role_sys_privs WHERE role = 'role_name';
  

• Revoke Privileges:
  
  Copy Code Copied Use a different Browser

  
  REVOKE privilege FROM username;
  

9. Network Configuration

• Listener Configuration:
  • Check Listener Status:
    
    Copy Code Copied Use a different Browser

    
    lsnrctl status
    

  • Start Listener:
    
    Copy Code Copied Use a different Browser

    
    lsnrctl start
    

  • Stop Listener:
    
    Copy Code Copied Use a different Browser

    
    lsnrctl stop
    

• TNS Configuration (tnsnames.ora):
  
  Copy Code Copied Use a different Browser

  
  DATABASE =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
      (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = service_name)
      )
    )
  
  

10. Backup Strategies

• Full Database Backup:
  
  Copy Code Copied Use a different Browser

  
  RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
  

• Incremental Backup:
  
  Copy Code Copied Use a different Browser

  
  RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;
  

• Validate Backup:
  
  Copy Code Copied Use a different Browser

  
  RMAN> VALIDATE BACKUPSET;
  

11. Data Dictionary Views

• User Views:
  • User Objects:
    
    Copy Code Copied Use a different Browser

    
    SELECT object_name, object_type FROM user_objects;
    

• Schema Views:
  • All Objects:
    
    Copy Code Copied Use a different Browser

    
    SELECT object_name, object_type FROM all_objects;
    

• DBA Views:
  • All Users:
    
    Copy Code Copied Use a different Browser

    
    SELECT username FROM dba_users;
    

12. Security Management

• Enable Auditing:
  
  Copy Code Copied Use a different Browser

  
  AUDIT SELECT ON table_name BY username;
  

• View Audit Trails:
  
  Copy Code Copied Use a different Browser

  
  SELECT * FROM dba_audit_trail;
  

13. Data Pump (Export/Import)

• Export Data:
  
  Copy Code Copied Use a different Browser

  
  expdp username/password DIRECTORY=dpump_dir DUMPFILE=export.dmp LOGFILE=export.log SCHEMAS=schema_name;
  

• Import Data:
  
  Copy Code Copied Use a different Browser

  
  impdp username/password DIRECTORY=dpump_dir DUMPFILE=export.dmp LOGFILE=import.log;
  

Download Elysium Spark Note