top of page
CerebroSQL

Oracle:

ALTER TABLE

ALTER TABLE [ schema. ] table
[ alter_table_properties
| column_clauses
| constraint_clauses
| alter_table_partitioning
| alter_external_table
| move_table_clause
]
[ enable_disable_clause
| { ENABLE | DISABLE } { TABLE LOCK | ALL TRIGGERS }
] ...
;

alter_table_properties::=
{ { { physical_attributes_clause
| logging_clause
| table_compression
| supplemental_table_logging
| allocate_extent_clause
| deallocate_unused_clause
| { CACHE | NOCACHE }
| RESULT_CACHE ( MODE {DEFAULT | FORCE} )
| upgrade_table_clause
| records_per_block_clause
| parallel_clause
| row_movement_clause
| flashback_archive_clause
}...
| RENAME TO new_table_name
} [ alter_iot_clauses ] [ alter_XMLSchema_clause ]
| { shrink_clause
| READ ONLY
| READ WRITE
| REKEY encryption_spec
}
}

example

ALTER TABLE print_media MODIFY NESTED TABLE ad_textdocs_ntab
RETURN AS VALUE;

ALTER TABLE customers PARALLEL;

ALTER TABLE employees
ENABLE VALIDATE CONSTRAINT emp_manager_fk
EXCEPTIONS INTO exceptions;

ALTER TABLE employees
ENABLE NOVALIDATE PRIMARY KEY
ENABLE NOVALIDATE CONSTRAINT emp_last_name_nn;

ALTER TABLE locations
MODIFY PRIMARY KEY DISABLE CASCADE;

ALTER TABLE employees ADD CONSTRAINT check_comp
CHECK (salary + (commission_pct*salary) <= 5000)
DISABLE;

ALTER TABLE employees
ENABLE ALL TRIGGERS;

ALTER TABLE employees
DEALLOCATE UNUSED;

ALTER TABLE customers
RENAME COLUMN credit_limit TO credit_amount;

ALTER TABLE t1 DROP (pk) CASCADE CONSTRAINTS;

ALTER TABLE sales SPLIT PARTITION SALES_Q4_2000
AT (TO_DATE('15-NOV-2000','DD-MON-YYYY'))
INTO (PARTITION SALES_Q4_2000, PARTITION SALES_Q4_2000b);

ALTER TABLE sales
MERGE PARTITIONS sales_q4_2000, sales_q4_2000b
INTO PARTITION sales_q4_2000;

ALTER TABLE print_media_part ADD PARTITION p3 VALUES LESS THAN (400)
LOB(ad_photo, ad_composite) STORE AS (TABLESPACE omf_ts1)
LOB(ad_sourcetext, ad_finaltext) STORE AS (TABLESPACE omf_ts2)
NESTED TABLE ad_textdocs_ntab STORE AS nt_p3;

ALTER TABLE list_customers SPLIT PARTITION rest
VALUES ('MEXICO', 'COLOMBIA')
INTO (PARTITION south, PARTITION rest);

ALTER TABLE list_customers
MERGE PARTITIONS asia, rest INTO PARTITION rest;

bottom of page