Oracle notes

Create and alter tables

Create a table

The primary key identifies each task.

CREATE TABLE demo_tasks (
  task_id NUMBER PRIMARY KEY,
  title VARCHAR2(100 CHAR) NOT NULL
);

Add a column

Existing tasks get a null due date.

ALTER TABLE demo_tasks ADD (due_on DATE);

Widen a column

The title can now hold 200 characters instead of 100.

ALTER TABLE demo_tasks MODIFY (title VARCHAR2(200 CHAR));

Rename a column

Update queries that use the old name as well.

ALTER TABLE demo_tasks RENAME COLUMN due_on TO due_date;

Drop a column

The column’s values are lost. Oracle commits schema changes automatically, so ROLLBACK cannot recover the column.

ALTER TABLE demo_tasks DROP COLUMN due_date;

Inspect a table’s structure

In SQL*Plus or SQLcl, DESCRIBE shows columns, data types and whether nulls are allowed.

DESCRIBE demo_tasks

Work with rows

Insert and query a row

COMMIT saves the insert. The query finds the task by its primary key.

INSERT INTO demo_tasks (task_id, title) VALUES (1, 'Review documentation');
COMMIT;
SELECT task_id, title FROM demo_tasks WHERE task_id = 1;

Update a row

Check which row matches WHERE first. With autocommit off, you can choose COMMIT or ROLLBACK after reviewing the result.

SELECT task_id, title FROM demo_tasks WHERE task_id = 1;
UPDATE demo_tasks SET title = 'Publish documentation' WHERE task_id = 1;

Check storage

Check the size of each tablespace

Adds up the current size of each tablespace’s data files. Temporary tablespaces are excluded, and the query requires access to DBA_DATA_FILES.

SELECT tablespace_name, ROUND(SUM(bytes) / POWER(1024, 2), 1) AS allocated_mib
FROM dba_data_files
GROUP BY tablespace_name
ORDER BY allocated_mib DESC;

Check usage percentage

The percentage uses the maximum possible size, which can include autoextend. It requires access to the dictionary view.

SELECT tablespace_name, ROUND(used_percent, 1) AS used_pct
FROM dba_tablespace_usage_metrics
ORDER BY used_pct DESC;

Check how much space a table takes

Shows the space allocated to the example table in your schema. Indexes and LOBs are stored in separate segments.

SELECT segment_name, ROUND(bytes / POWER(1024, 2), 1) AS allocated_mib
FROM user_segments
WHERE segment_name = 'DEMO_TASKS' AND segment_type = 'TABLE';