Basic DDL

Tables

CREATE TABLE TABLE_NAME(
  ID                   NUMBER,
  MANDATORY_FK_COLUMN  NUMBER NOT NULL,
  FOREIGN_KEY_COLUMN   NUMBER,
  NUMBER_COLUMN        NUMBER,
  STRING_COLUMN        VARCHAR2(500),
  DATE_TIME_COLUMN     DATE);

Add columns

alter table TABLE_NAME add (
  SOME_COLUMN      NUMBER,
  ANOTHER_COLUMN   DATE)

Renaming

-- Table
RENAME TABLE TABLE_NAME TO NEW_TABLE_NAME;
-- Column
alter table TABLE_NAME rename column OLD_NAME TO NEW_NAME;

Constraints

Primary key

alter table TABLE_NAME
  add constraint PK_TABLE_NAME primary key (ID);

Foreign key

CREATE TABLE PARENT_TABLE_NAME(
  ID                   NUMBER,
  ...
)

ALTER TABLE TABLE_NAME
  ADD CONSTRAINT FK_TABLE_NAME_KEY foreign key (FOREIGN_KEY_COLUMN) REFERENCES PARENT_TABLE (ID) ON DELETE SET NULL ENABLE

Indexes

Simple index

create index TABLE_NAME_FK_IDX on TABLE_NAME(
  MANDATORY_FK_COLUMN asc)
tablespace tablespace_name

drop index TABLE_NAME_FK_IDX

Multi column index

create index TABLE_NAME_FK_IDX on TABLE_NAME(
  MANDATORY_FK_COLUMN asc,
  NUMBER_COLUMN       asc)
tablespace tablespace_name;

drop index TABLE_NAME_FK_IDX

Functional index

create index TABLE_NAME_FK_IDX on TABLE_NAME(
  UPPER(STRING_COLUMN) asc)
tablespace tablespace_name;

Rebuilding indexes

alter index INDEX_NAME rebuild