Oracle Database Cheatsheet

Oracle Database is a powerful and widely used relational database management system that provides a robust platform for storing, managing, and retrieving data. Whether you’re a seasoned DBA or a developer working with Oracle, having a handy cheatsheet can be invaluable. This cheatsheet provides a quick reference for some common Oracle Database tasks and commands.

Connecting to Oracle Database

Connect to a Database

sqlplus username/password@hostname:port/service_name

Replace username, password, hostname, port, and service_name with your specific values.

Connect as SYSDBA

sqlplus / as sysdba

Basic SQL Commands

Create a Table

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
);

Insert Data

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Select Data

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Update Data

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Delete Data

DELETE FROM table_name
WHERE condition;

Database Information

Show Tables

SELECT table_name
FROM all_tables;

Describe a Table

DESC table_name;

Show Columns

SELECT column_name, data_type
FROM all_tab_columns
WHERE table_name = 'your_table_name';

Managing Users and Security

Create User

CREATE USER username IDENTIFIED BY password;

Grant Privileges

GRANT privilege
ON object
TO user;

Revoke Privileges

REVOKE privilege
ON object
FROM user;

Change User Password

ALTER USER username IDENTIFIED BY new_password;

Backup and Recovery

Perform Full Database Backup

BACKUP DATABASE;

Restore Database

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN;

Advanced Queries

Subquery

SELECT column1, column2, ...
FROM table_name
WHERE column_name operator (SELECT column_name FROM table_name WHERE condition);

Join Tables

SELECT *
FROM table1
JOIN table2 ON table1.column_name = table2.column_name;

Aggregate Functions

SELECT AVG(column_name)
FROM table_name;

This Oracle Database cheatsheet covers some fundamental tasks and commands for efficiently working with Oracle databases. Whether you’re a beginner or an experienced user, keeping this cheatsheet handy can save you time and help you navigate the complexities of Oracle Database management. Remember to adapt the commands to your specific database environment and requirements.

FAQ