Oracle TABLE Statement

Open SQL Shell

Open a new SSH session and login as oracle user
Open sqlplus shell
# sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Thu Dec 24 23:26:47 2020 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL>

GRANT Required Privileges

Create New User "john" with password "TstPas12"
SQL> CREATE USER john IDENTIFIED by TstPas12; User created.
GRANT Required Privileges

Grant Privileges to user john to create tables, views etc.

SQL> grant create session, create table, create view, unlimited tablespace to john; Grant succeeded.

CREATE Table

Open a new SQLPlus shell and login as john user
SQL> connect john; Enter password: Connected.
Create New Table

Create table with name "employees".

SQL> CREATE TABLE employees( employee_id NUMBER GENERATED BY DEFAULT AS IDENTITY, first_name VARCHAR2(50) NOT NULL, last_name VARCHAR2(50) NOT NULL, PRIMARY KEY(employee_id) ); Table created.
Show Newly created table

Show details of newly created table "employees".

DESCRIBE employees; Name Null? Type ----------------------------------------- -------- ---------------------------- EMPLOYEE_ID NOT NULL NUMBER FIRST_NAME NOT NULL VARCHAR2(50) LAST_NAME NOT NULL VARCHAR2(50)

ALTER Table

Add New column to a Table

Adding a new column "date_of_birth" to existing table "employees".

SQL> ALTER TABLE employees ADD date_of_birth DATE NOT NULL; Table altered.

Show the updated colums of table "employees".

SQL> DESCRIBE employees; Name Null? Type ----------------------------------------- -------- ---------------------------- EMPLOYEE_ID NOT NULL NUMBER FIRST_NAME NOT NULL VARCHAR2(50) LAST_NAME NOT NULL VARCHAR2(50) DATE_OF_BIRTH NOT NULL DATE
Rename a column in Table

Rename column "date_of_birth" to "DOB" in existing table "employees".

SQL> ALTER TABLE employees RENAME COLUMN date_of_birth TO dob; Table altered.

Show the updated colums of table "employees".

SQL> DESCRIBE employees; Name Null? Type ----------------------------------------- -------- ---------------------------- EMPLOYEE_ID NOT NULL NUMBER FIRST_NAME NOT NULL VARCHAR2(50) LAST_NAME NOT NULL VARCHAR2(50) DOB NOT NULL DATE
Rename a Table

Rename existing table "employees" to "hr_team".

SQL> ALTER TABLE employees RENAME TO hr_team; Table altered.

Show the updated colums of table "employees".

SQL> DESCRIBE hr_team; Name Null? Type ----------------------------------------- -------- ---------------------------- EMPLOYEE_ID NOT NULL NUMBER FIRST_NAME NOT NULL VARCHAR2(50) LAST_NAME NOT NULL VARCHAR2(50) DOB NOT NULL DATE

DROP Table

Drop Table hr_team
SQL> DROP TABLE hr_team; Table dropped.