PL/SQL – create and alter table

Creating tables

--Create simple table with heap organization:
create table hr.people
id NUMBER(10) NOT NULL, -- any integer up to 10 places
first_name VARCHAR2(30 CHAR) NOT NULL, -- text up to 30 characters (single non english character can take more than one byte)
last_name VARCHAR2(30 BYTE) NOT NULL,  -- text up to 30 bytes
local_hello NVARCHAR2(50), -- unicode text, multilingual data (it means you can mix in this column different languages - including english like and non english exotic asian languages)
height NUMBER(3,2), -- any number up to 3 digits where up to two are on the right of comma and 3-2 are on the left
skills_level NUMBER(1) DEFAULT 3, -- default must be constant value, cannot base on function
knowledge_level NUMBER(1) DEFAULT 3,
final_level as (skills_level * knowledge_level) -- function-based virtual column

Notice that when you can insert into height column value 1.7554432 (it will be rounded), but you cannot insert 10.7 (it will fail).

--Create table copy (DDL + DML):
create table hr.people2 as select * from hr.people;

--Create table copy (DDL only):
create table hr.people3 as select * from hr.people where 1=2;

Altering tables

alter table hr.people add profession nvarchar2(20);
alter table hr.peple modify height number(4, 3) NOT NULL;
alter table hr.people rename column height to height_cm;
alter table hr.people drop column final_level; -- can be time consuming as every row must be rebuilt
alter table hr.people set unused column first_name; -- do not removes column, but it won't be available in queries, views, etc. Use when dropping column can take long time.
alter table hr.people drop unused columns; -- it should follow a few 'set unused' commands when it is allowed to consume some resources to clean up not needed columns.
alter table hr.people read only; -- table will reject any modifications, alter table privilege is needed for this
alter table hr.people read write; -- will turn off read only mode
drop table hr.people; -- it will delete the table (also if it is in read only mode)

For more information about differences between NVARCHAR2 and VARCHAR2 and encoding go to:


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s