Oracle Database Cheatsheet
#
Oracle Database is a multi-model database management system developed by Oracle Corporation. It is widely used for its advanced features and high performance.
General Commands
#
| Command/Option | Example | Description |
|---|
sqlplus <user>/<password>@<service> | sqlplus system/password@orcl | Connect to an Oracle database using SQL*Plus |
exit; | exit; | Exit SQL*Plus |
Database Management
#
| Command/Option | Example | Description |
|---|
CREATE DATABASE <db_name>; | CREATE DATABASE mydb; | Create a new Oracle database |
DROP DATABASE <db_name>; | DROP DATABASE mydb; | Delete an Oracle database |
ALTER DATABASE <db_name> OPEN; | ALTER DATABASE mydb OPEN; | Open an Oracle database |
SHOW DATABASE; | SHOW DATABASE; | Show database information |
User Management
#
| Command/Option | Example | Description |
|---|
CREATE USER <username> IDENTIFIED BY <password>; | CREATE USER myuser IDENTIFIED BY mypassword; | Create a new user |
DROP USER <username>; | DROP USER myuser; | Delete a user |
GRANT <privileges> TO <username>; | GRANT CONNECT, RESOURCE TO myuser; | Grant privileges to a user |
REVOKE <privileges> FROM <username>; | REVOKE CONNECT, RESOURCE FROM myuser; | Revoke privileges from a user |
Table Management
#
| Command/Option | Example | Description |
|---|
CREATE TABLE <table_name> (column_definitions); | CREATE TABLE employees (id NUMBER PRIMARY KEY, name VARCHAR2(100)); | Create a new table |
DROP TABLE <table_name>; | DROP TABLE employees; | Delete a table |
ALTER TABLE <table_name> ADD (column_name datatype); | ALTER TABLE employees ADD (hire_date DATE); | Add a column to a table |
ALTER TABLE <table_name> DROP COLUMN <column_name>; | ALTER TABLE employees DROP COLUMN hire_date; | Drop a column from a table |
DESC <table_name>; | DESC employees; | Describe the structure of a table |
Data Manipulation
#
| Command/Option | Example | Description |
|---|
INSERT INTO <table> (columns) VALUES (values); | INSERT INTO employees (name) VALUES ('John Doe'); | Insert new data into a table |
SELECT * FROM <table>; | SELECT * FROM employees; | Retrieve all data from a table |
UPDATE <table> SET column = value WHERE condition; | UPDATE employees SET name = 'Jane Doe' WHERE id = 1; | Update existing data in a table |
DELETE FROM <table> WHERE condition; | DELETE FROM employees WHERE id = 1; | Delete data from a table |
Query Filtering and Sorting
#
| Command/Option | Example | Description |
|---|
SELECT column1, column2 FROM <table> WHERE condition; | SELECT name FROM employees WHERE id = 1; | Retrieve specific data based on a condition |
| `SELECT * FROM ORDER BY column [ASC | DESC];` | SELECT * FROM employees ORDER BY name ASC; |
Indexing
#
| Command/Option | Example | Description |
|---|
CREATE INDEX <index_name> ON <table> (column); | CREATE INDEX idx_name ON employees (name); | Create an index on a column |
DROP INDEX <index_name>; | DROP INDEX idx_name; | Delete an index |
Backup and Restore
#
| Command/Option | Example | Description |
|---|
exp <user>/<password> FILE=<file.dmp> LOG=<file.log> | exp system/password FILE=mydb_backup.dmp LOG=mydb_backup.log | Export a database using Oracle Export Utility |
imp <user>/<password> FILE=<file.dmp> LOG=<file.log> | imp system/password FILE=mydb_backup.dmp LOG=mydb_restore.log | Import a database using Oracle Import Utility |
Server Status and Configuration
#
| Command/Option | Example | Description |
|---|
SHOW PARAMETER <parameter>; | SHOW PARAMETER db_name; | Show the value of a specific configuration parameter |
SELECT * FROM v$version; | SELECT * FROM v$version; | Show the Oracle database version |
SELECT status FROM v$instance; | SELECT status FROM v$instance; | Show the status of the Oracle instance |
This cheatsheet covers the most commonly used Oracle Database commands and options, helping you manage databases, users, tables, data, indexing, backups, and server status effectively.