Master Oracle Database Essentials: Startup, Users, Tablespaces, Indexes & More

1. Starting and Stopping the Oracle Database

Log in as SYSDBA and use the STARTUP command (optionally with parameters) to start the database. The startup proceeds through three phases: instance startup, database mount, and database open.

To shut down, log in as SYSDBA and issue SHUTDOWN with the desired option. The shutdown also follows three phases: closing the database, dismounting, and stopping the instance.

2. Managing the Oracle Listener

The listener ( lsnrctl) provides client access on the default port 1521. Start it with the START command and stop it with STOP. The listener must be started before the database.

3. Tablespaces

Creating a tablespace requires a name, one or more datafiles, size, and optional AUTOEXTEND. Example:

CREATE TABLESPACE my_ts DATAFILE '/u01/app/oracle/oradata/mydb/my_ts01.dbf' SIZE 100M AUTOEXTEND ON;

Managing tablespaces includes resizing, adding datafiles, changing read/write status, and dropping tablespaces.

Resize: ALTER TABLESPACE my_ts RESIZE 200M; Add datafile:

ALTER TABLESPACE my_ts ADD DATAFILE '/u01/app/oracle/oradata/mydb/my_ts02.dbf' SIZE 50M;

Read/write: ALTER TABLESPACE my_ts READ WRITE; Drop:

DROP TABLESPACE my_ts INCLUDING CONTENTS;

4. User Management

Creating a user requires a username, password, default and temporary tablespaces, and optional quota settings. Example:

CREATE USER scott IDENTIFIED BY tiger DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA 100M ON users;

Modify a password with ALTER USER scott IDENTIFIED BY newpwd; and drop a user with DROP USER scott CASCADE;.

5. Privilege Management

System privileges (e.g., CREATE SESSION, CREATE TABLE) are granted with GRANT and revoked with REVOKE. Object privileges (e.g., SELECT on a table) are managed similarly. Common predefined roles include CONNECT, RESOURCE, and DBA.

GRANT CREATE SESSION TO scott;

REVOKE CREATE SESSION FROM scott;

6. Transaction Control

COMMIT

makes changes permanent, while ROLLBACK undoes them. Autocommit can be toggled with SET AUTOCOMMIT ON or SET AUTOCOMMIT OFF.

7. Indexes

Indexes improve query performance. Types include B‑tree, unique, reverse‑key, bitmap, function‑based, and composite indexes. Creation syntax:

CREATE INDEX idx_emp_name ON employees (last_name);
CREATE UNIQUE INDEX idx_emp_id ON employees (emp_id);
CREATE BITMAP INDEX idx_emp_dept ON employees (dept_id);

Maintenance operations include REBUILD, COALESCE, RENAME, and DROP.

8. Views and Materialized Views

Views are virtual tables defined by a SELECT statement. They can be created with CREATE OR REPLACE VIEW and support options such as FORCE, WITH READ ONLY, and WITH CHECK OPTION.

CREATE OR REPLACE VIEW emp_v AS SELECT emp_id, name FROM employees WHERE dept_id = 10;

Materialized views store query results physically and can be refreshed ON COMMIT or ON DEMAND, with refresh modes COMPLETE, FAST, FORCE, or NEVER.

CREATE MATERIALIZED VIEW mv_sales
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS SELECT * FROM sales;

9. Sequences

Sequences generate unique numeric values. Important parameters include START WITH, INCREMENT BY, MINVALUE, MAXVALUE, CYCLE/NOCYCLE, and CACHE/NOCACHE.

CREATE SEQUENCE order_seq START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;

Retrieve values with SELECT order_seq.NEXTVAL FROM dual; and view the current value with SELECT order_seq.CURRVAL FROM dual;.

10. Synonyms

Synonyms provide alternative names for database objects. Private synonyms are visible only to the owner; public synonyms are accessible to all users.

CREATE SYNONYM emp_syn FOR scott.employees;
CREATE PUBLIC SYNONYM emp_pub_syn FOR scott.employees;

Drop synonyms with DROP SYNONYM emp_syn; or DROP PUBLIC SYNONYM emp_pub_syn;.

11. Partitioned Tables

Partitioning splits large tables into smaller, manageable pieces stored in separate tablespaces. Oracle supports range, list, hash, composite, interval, and virtual‑column partitioning.

CREATE TABLE sales (
    sale_id NUMBER,
    sale_date DATE,
    amount NUMBER
) PARTITION BY RANGE (sale_date) (
    PARTITION p_2020 VALUES LESS THAN (TO_DATE('01-JAN-2021','DD-MON-YYYY')),
    PARTITION p_maxvalue VALUES LESS THAN (MAXVALUE)
);

Partitions can be added, split, merged, or dropped as data grows, and queries can target specific partitions for better performance.