Tables | Oracle DBA Tutorial pdf

Tables

Creating tables

Create table MANUFACTURER
(
MFDNO NUMBER not null,
MFDNAME VARCHAR2(200) not null,
ADDRESS VARCHAR2(200),
CITY VARCHAR2(50),
STATE CHAR(2),
COUNTRY VARCHAR2(100),
POSTCODE VARCHAR2(10),
PHONE VARCHAR2(50),
EMAIL VARCHAR2(100),
USERNAME VARCHAR2(20) default USER,
DATESTAMP DATE default SYSDATE
) tablespace SUPERMARKET_DATA
CREATE TABLE MANUFACTURER
NOLOGGING PARALLEL
AS
SELECT * FROM COMPANY

Reorganizing tables

To move a table to a different table space
ALTER TABLE product MOVE TABLESPACE supermarket

Dropping a table

DROP TABLE schema.table_name (CASCADE CONSTRAINTS)
TRUNCATE TABLE table_name
Truncate resets HWM where delete does not.
Truncate is not logged but delete is logged.

Modifying columns

ALTER TABLE schema.table_name DROP COLUMN column_name (CASCADE CONSTRAINTS)
ALTER TABLE schema.table_name SET UNUSED COLUMN column1_name, column2_name (CASCADE CONSTRAINTS)
ALTER TABLE product ADD Mfd VARCHAR2(30) DEFAULT ‘abc’

Table related data dictionary views

DBA_TABLES
DBA_TAB_COLUMNS – all columns of all tables