Tables

Create

CREATE TABLE [schema.]<TableName>
column datatype [DEFAULT expr] [column_constraint(s)] ,
column datatype [DEFAULT expr] [column_constraint(s)] , ...
[table_constraint | table_ref_constraint]
TABLESPACE <TableSpaceName>;

CREATE TABLE scott.kb (
kb_id          number,
title          varchar2(50) CONSTRAINT kb_title_nn  NOT NULL,
detail         CLOB         CONSTRAINT kb_detail_nn NOT NULL,
author         varchar2(25),
last_modified  date,
reviewer       varchar2(25),
last_reviewed  date,
keywords       varchar2(100),
CONSTRAINT kb_pk PRIMARY KEY("KB_ID") USING INDEX TABLESPACE users
)
TABLESPACE users;

Note: UPPER case used for PRIMARY KEY("KB_ID")

Create With Multiple Contrainints on One Field

CREATE TABLE SCOTT.TIN (
    id varchar2(9) CONSTRAINT tin_id_nn  NOT NULL, 
                   CONSTRAINT tin_id_unq UNIQUE(id)  
) TABLESPACE SCOTT;

Clone Table Method Examples

CREATE TABLE emp2_with_data   AS SELECT * FROM emp;
CREATE TABLE emp2_with_nodata AS SELECT * FROM emp WHERE 1=2;

-- Clone a table structure (just structure and no data).
CREATE TABLE new_table AS select * from Table2Clone WHERE 1=0;

When you create do a CTAS (create table as select) of a table you only get the structure and not any indexes, PK, FK etc.

Display

-- By Schema (All)

SELECT table_name FROM dba_tables ORDER BY owner,table_name; 

SELECT table_name FROM dba_tables WHERE owner='&SCHEMA' 
ORDER BY table_name; 

-- By Size (All)

SELECT segment_name, bytes FROM dba_segments ORDER BY bytes DESC;

-- By Size (One Schema)

SELECT segment_name, bytes FROM dba_segments 
WHERE owner='&SCHEMA' ORDER BY bytes DESC; 

-- Size of a Table

SELECT sum(bytes)/(1024*1024) "Size [mb]" FROM dba_segments 
WHERE segment_name = '&TABLE_NAME'; 

-- User Created Tables

SELECT owner,table_name FROM dba_tables
WHERE owner NOT IN('CTXSYS','DBSNMP','DMSYS','MDSYS','PUBLIC','OLAPSYS',
      'OUTLN','SYS','SYSMAN','SYSTEM','TSMSYS','WMSYS')
ORDER BY owner;

Drop

DROP TABLE [schema.]<TableName> [CASCADE CONSTRAINTS];

DROP TABLE scott.emp CASCADE CONSTRAINTS;

Move

ALTER TABLE [schema.]<TableName>
MOVE [PARALLEL] [NOLOGGING] TABLESPACE <TableSpaceName>;

ALTER TABLE hr.emp2 MOVE PARALLEL NOLOGGING TABLESPACE dev;

After moving a table you must rebuild any associated indexes.

<- Schema