Essential Oracle SQL Commands: From Connecting to Advanced Tablespace Management

This document compiles a series of practical Oracle SQL commands useful for daily database tasks.

Basic Session Operations

connect scott/123456;

– Connect to the database. disconn; – Disconnect.

Querying and Inspecting Objects

describe scott.emp;

– Show table structure. select empno, job, mgr, sal from scott.emp; – Retrieve data.

Saving and Reusing SQL Scripts

save scott_emp_query.sql

– Save buffer to a file. get scott_emp_query.sql list – Load file into buffer. start scott_emp_query.sql – Execute the script. edit – Open buffer in default editor (afiedt.buf). spool scott_emp_outcome.txt append – Append output to a file.

Variable Substitution

Single variable: where empno >= &temp; Two variables (separate prompts): where &column_name >= 7850; Two variables (single prompt):

where &&column_name >= 7850;

set define '@'

– Change substitution character.

Formatting Output

column empno heading '员工编号' format 9999

column ename heading '员工姓名' format a10

column mgr heading '上级编号' format 9999

column hiredate heading '受雇日期' justify center

column sal heading '员工工资' format $999,999.99

set pagesize 20

– Rows per page. set linesize 20 – Characters per line.

ttitle 'Header', btitle 'Footer'

Aggregations and Grouping

break on deptno;

compute sum of sal on deptno;

Tablespace Management (Permanent)

Create:

create tablespace myspace datafile 'path' size 100M autoextend on next 10M maxsize 500M;

View attributes:

select * from dba_tablespace where tablespace_name='MYSPACE';

Alter status: alter tablespace myspace offline; Rename: alter tablespace myspace rename to newspace; Resize datafile: alter database datafile 'path' resize 200M; Add datafile:

alter tablespace myspace add datafile 'newpath' size 100M autoextend on next 10M maxsize 500M;

Drop datafile: alter tablespace myspace drop datafile 'path'; Set offline drop: alter database datafile 'path' offline drop; Move datafile: offline, move file at OS level, then alter tablespace myspace rename datafile 'old' to 'new'; and bring online.

Drop tablespace:

drop tablespace myspace including contents and datafiles;

Temporary Tablespaces

Create:

create temporary tablespace temp_ts tempfile 'path' size 100M autoextend on next 10M maxsize 500M;

Assign to group: tablespace group group1; Change group:

alter tablespace temp_ts tablespace group group2;

Bigfile Tablespaces

create bigfile tablespace mybigspace datafile 'path' size 1G;

Default Tablespace

alter database default tablespace myspace;

Global Temporary Tables

Transaction‑level:

create global temporary table temp_student(...) on commit delete rows;

Session‑level:

create global temporary table temp_book(...) on commit preserve rows;

External Tables

Create directory: create directory external_card as 'E:\external'; Create external table with loader:

create table e_card(
  ...
) organization external (
  type oracle_loader
  default directory external_card
  access parameters (fields terminated by ',')
  location ('card.txt')
) reject limit unlimited;

Log bad rows: add badfile 'card_bad.txt' and logfile 'card_log.txt' clauses.

Partitioned Tables

Range partition:

create table part_book(...)
partition by range(booktime)(
  partition p1 values less than ('01-01-2008') tablespace mytemp1,
  partition p2 values less than ('01-01-2009') tablespace mytemp2,
  partition pmax values less than (maxvalue) tablespace mytemp3
);

Hash partition:

create table part_book(...)
partition by hash(bid)(
  partition p1 tablespace mytemp1,
  partition p2 tablespace mytemp2
);

List partition:

create table part_book(...)
partition by list(bookpress)(
  partition p1 values ('清华大学出版社') tablespace mytemp1,
  partition p2 values ('岭南师范出版社') tablespace mytemp2
);

Composite range‑hash partition:

create table part_book(...)
partition by range(booktime)
subpartition by hash(bid)
subpartitions 2 store in (mytemp1, mytemp2)(
  partition p1 values less than ('01-01-2008'),
  partition p2 values less than ('01-01-2009'),
  partition pmax values less than (maxvalue)
);