Master Oracle SQL*Plus: Essential Commands for Tablespaces, Partitions, and External Data

Connecting and Basic Queries

Use connect scott/123456 to log in and disconn to disconnect. View a table’s structure with describe scott.emp; and retrieve data using select empno, job, mgr, sal;.

Saving and Running Scripts

Save the current buffer to a file: save scott_emp_query.sql. Load a script back into the buffer with get scott_emp_query.sql list and execute it using start scott_emp_query.sql. Export the buffer to a file via edit (default afiedt.buf).

Spooling Output

Direct query results to a file: spool scott_emp_outcome.txt append.

Variable Substitution

Single‑variable query example:

select empno, ename, mgr, sal, deptno
from scott.emp
where empno >= &temp;

Two‑variable query (prompted twice):

select &column_name, deptno
from scott.emp
where &column_name >= 7850;

Two‑variable query (single prompt):

select &&column_name, deptno
from scott.emp
where &&column_name >= 7850;

Suppressing Verification Messages

Turn off the display of old and new values with set verify off;. Change the variable prefix character with set define '@' so that & no longer acts as a variable marker.

Executing SQL Files Directly

Run a script by specifying its full path (case‑sensitive): @E:\TEMP\temp.sql.

Defining and Managing Constants

View defined constants with define;. Create a constant: define temp=7850. Check its value with define temp. Remove it using undefine temp.

Formatting Query Output

Set column headings and formats, for example:

column empno heading 'Employee ID' format 9999

column ename heading 'Name' format a10

column sal heading 'Salary' format $999,999.99

Adjust page size with set pagesize 20, line size with set linesize 20, and add titles using ttitle and btitle. Use break on deptno; and compute sum of sal on deptno; for grouping and aggregation.

Tablespace Management

Create a permanent tablespace:

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

Key syntax elements include temporary/undo tablespaces, datafile specifications, autoextend options, logging, storage parameters, compression, extent management, and segment space management.

Query tablespace properties:

select * from dba_tablespace where tablespace_name='MYSPACE';

Alter tablespace state, rename, or drop it, e.g., alter tablespace myspace offline; then alter tablespace myspace rename to newspace; or drop tablespace myspace including contents and datafiles;.

Temporary and Bigfile Tablespaces

Create a temporary tablespace:

create temporary tablespace temp_ts
 tempfile 'path/to/tempfile' size 50M
 autoextend on next 5M maxsize unlimited;

Assign it to a tablespace group with tablespace group group1; and later modify the group using alter tablespace temp_ts tablespace group group2;.

Create a bigfile tablespace:

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

Global Temporary Tables

Transaction‑level temporary table:

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

Use it with select * from temp_student; and commit; (data cleared after commit).

Session‑level temporary table:

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

Data persists until the session ends.

External Tables

First, create a directory object pointing to the OS path: create directory external_card as 'E:\external'; Then define the external table:

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

Use reject limit unlimited to allow unlimited bad rows, or specify error handling files: badfile 'card_bad.txt' logfile 'card_log.txt'.

Partitioned Tables

Range partitioning example:

create table part_book (
   ...
) partition by range (booktime) (
   partition part1 values less than (to_date('01-01-2008','DD-MM-YYYY')) tablespace mytemp1,
   partition part2 values less than (to_date('01-01-2009','DD-MM-YYYY')) tablespace mytemp2,
   partition part3 values less than (maxvalue) tablespace mytemp3
);

Hash partitioning example:

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

List partitioning example:

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

Composite range‑hash partitioning example:

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