Master Oracle Database: Startup, Shutdown, Users, Tablespaces, Indexes, Views, and Partitions

1. Starting and Stopping Oracle Database

Log in as SYSDBA and use the STARTUP command (optionally with startup options) to start the instance, which proceeds through three phases: instance startup, database installation, and opening. To stop the database, log in as SYSDBA and issue SHUTDOWN with appropriate options, which also has three phases: closing the database, uninstalling it, and shutting down the Oracle instance.

2. Managing the Oracle Listener

The listener (default port 1521) is started with the lsnrctl start command and stopped with lsnrctl stop. The listener must be running before the database is started so that client connections can be accepted.

3. Tablespaces

To create a tablespace, specify a name, one or more datafiles, their sizes (K/M), and optional AUTOEXTEND. Example syntax:

CREATE TABLESPACE tablespacename DATAFILE 'filename' SIZE 100M AUTOEXTEND ON;

Managing tablespaces includes resizing with ALTER TABLESPACE ... RESIZE, adding datafiles, changing read/write status ( ALTER TABLESPACE name READ WRITE or READ ONLY), and dropping with DROP TABLESPACE name [INCLUDING CONTENTS];.

4. User Management

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

CREATE USER username IDENTIFIED BY password DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA 100M ON users;

Change a password with ALTER USER username IDENTIFIED BY new_password; and drop a user with DROP USER username CASCADE;. Before creating a user, plan the username/password, tablespace, quota, and default/temporary tablespaces.

5. Database Privilege Management

System privileges (e.g., CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SEQUENCE) grant broad rights, while object privileges (e.g., SELECT, INSERT on a specific table) are more granular. Roles such as CONNECT, RESOURCE, and DBA simplify privilege assignment. Grant privileges with GRANT privilege TO username; and revoke with REVOKE privilege FROM username;.

6. Transaction Control

Use COMMIT to make changes permanent and ROLLBACK to undo them. Autocommit can be toggled with SET AUTOCOMMIT ON or SET AUTOCOMMIT OFF.

7. Indexes

Indexes improve query performance. Types include B‑tree (default), unique, reverse‑key, bitmap, function‑based, and composite indexes. Create a B‑tree index with:

CREATE INDEX index_name ON tablename (column1, column2) TABLESPACE tablespace_name;

Unique indexes enforce uniqueness, reverse‑key indexes store column values in reverse order, and bitmap indexes are efficient for low‑cardinality columns. Index maintenance commands include ALTER INDEX ... REBUILD, ALTER INDEX ... COALESCE, ALTER INDEX ... RENAME TO new_name, and DROP INDEX index_name;.

8. Views

Views are virtual tables defined by a SELECT statement. They provide security, simplify queries, and hide underlying table structures. Create a view with:

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name (alias1, alias2, ...) AS SELECT ...;

Views can be queried like tables, but DML on views is limited to simple views (single base table, no aggregates, no joins). Drop a view with DROP VIEW view_name;. Materialized views store results physically and can be refreshed ON COMMIT or ON DEMAND, with refresh types COMPLETE, FAST, FORCE, or NEVER.

9. Sequences

Sequences generate unique numeric values. Create with options such as START WITH, INCREMENT BY, MINVALUE, MAXVALUE, CYCLE/NOCYCLE, CACHE/NOCACHE. Example:

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

Retrieve values using seq_name.NEXTVAL (advances) and seq_name.CURRVAL (current). Alter with ALTER SEQUENCE ... and drop with DROP SEQUENCE seq_name;.

10. Synonyms

Synonyms are aliases for database objects, simplifying SQL and providing location transparency. Private synonyms are visible only to the owner; public synonyms are visible to all users. Create with:

CREATE [OR REPLACE] SYNONYM synonym_name FOR schema.object_name;

Public synonym syntax adds the PUBLIC keyword. Drop with DROP [PUBLIC] SYNONYM synonym_name;.

11. Partitioned Tables

Partitioning splits large tables into smaller, manageable pieces stored in separate tablespaces. Benefits include improved query performance, easier maintenance, independent backup/restore, and enhanced data security. Tables larger than 2 GB with clear partitioning criteria are good candidates. Oracle supports range, list, hash, composite, interval, and virtual‑column partitions.

Example: create a range‑partitioned table on the AGE column with a MAXVALUE partition for out‑of‑range rows.

CREATE TABLE employees (
  id NUMBER,
  name VARCHAR2(50),
  age NUMBER
) PARTITION BY RANGE (age) (
  PARTITION p_0_30 VALUES LESS THAN (30),
  PARTITION p_30_60 VALUES LESS THAN (60),
  PARTITION p_max VALUES LESS THAN (MAXVALUE)
);

Insert data; rows falling outside defined ranges go to the MAXVALUE partition. Query specific partitions with predicates on the partition key. View all partitions with SELECT * FROM user_part_tables WHERE table_name='EMPLOYEES';.