Alter oracle table
We can alter oracle table after it is created. And most of the altering of oracle tables are done on the logical structure of an existing table.
In this topic, we will going to see on how to add, modify, drop, mark unused and rename column of a table. We will also going to see practically on how to rename a table and add constraints to an existing table using SQL*PLUS oracle command-line tool.
To begin with, let us first create a table named tblcustomer through SQL*PLUS command-line tool.
Example: D:\app\Admin\virtual\virtual\product\12.2.0\dbhome_1\bin>sqlplus/nolog SQL*Plus: Release 12.2.0.1.0 Production on Sat Feb 26 19:58:54 2022 Copyright (c) 1982, 2016, Oracle. All rights reserved. SQL> conn mereba/mereba@orcl Connected. SQL> create table tblcustomer ( 2 cust_no number generated as identity, 3 customer_type char(1), 4 cust_fullname varchar2(50), 5 country varchar2(30), 6 nationality varchar2(30) 7 ); Table created.
Now, here below are examples of alter oracle table based on the table created above.
Adding new column named ‘swift_code’
Example: SQL> Alter table tblcustomer add ( swift_code varchar2(20)); Table altered.
To confirm whether the ‘swift_code‘ column is created or not, run the following SQL command:
Example: SQL> describe tblcustomer; Name Null? Type ----------------------------------------- -------- ---------------------------- CUST_NO NOT NULL NUMBER CUSTOMER_TYPE CHAR(1) CUST_FULLNAME VARCHAR2(50) COUNTRY VARCHAR2(30) NATIONALITY VARCHAR2(30) SWIFT_CODE VARCHAR2(20)
As you can see from the query above, the new column ‘swift_code‘ is added to tblcustomer table.
Modifying the data type of column named ‘customer_type’ of tblcustomer table
Example: SQL> Alter table tblcustomer modify( customer_type varchar2(30)); Table altered.
And then to check whether the ‘customer_type‘ column data type is changed or not, run the following SQL command:
Example: SQL> describe tblcustomer; Name Null? Type ----------------------------------------- -------- ---------------------------- CUST_NO NOT NULL NUMBER CUSTOMER_TYPE VARCHAR2(30) CUST_FULLNAME VARCHAR2(50) COUNTRY VARCHAR2(30) NATIONALITY VARCHAR2(30) SWIFT_CODE VARCHAR2(20)
As you can see also from the query above, the data type of column ‘customer_type‘ is changed to VARCHAR2(30).
Dropping a column named ‘COUNTRY’ from tblcustomer table
Example: SQL> alter table tblcustomer drop column COUNTRY; Table altered.
And to confirm whether the ‘COUNTRY‘ column is dropped or not, run the following SQL command:
Example: SQL> describe tblcustomer; Name Null? Type ----------------------------------------- -------- ---------------------------- CUST_NO NOT NULL NUMBER CUSTOMER_TYPE VARCHAR2(30) CUST_FULLNAME VARCHAR2(50) NATIONALITY VARCHAR2(30) SWIFT_CODE VARCHAR2(20)
As you can see also from the query above, the column ‘COUNTRY‘ is removed from tblcustomer table.
Renaming column ‘CUST_FULLNAME’ of tblcustomer table
Example: SQL> alter table tblcustomer rename column CUST_FULLNAME to CUST_NAME; Table altered.
And then to check whether the ‘CUST_FULLNAME‘ column is renamed or not, run the following SQL command:
Example: SQL> describe tblcustomer; Name Null? Type ----------------------------------------- -------- ---------------------------- CUST_NO NOT NULL NUMBER CUSTOMER_TYPE VARCHAR2(30) CUST_NAME VARCHAR2(50) NATIONALITY VARCHAR2(30) SWIFT_CODE VARCHAR2(20)
Marking column ‘SWIFT_CODE’ of tblcustomer table unused
Example: SQL> alter table tblcustomer set unused column SWIFT_CODE; Table altered.
And to confirm whether the ‘SWIFT_CODE‘ column is marked as unused or not, run the following SQL command:
Example: SQL> describe tblcustomer; Name Null? Type ----------------------------------------- -------- ---------------------------- CUST_NO NOT NULL NUMBER CUSTOMER_TYPE VARCHAR2(30) CUST_NAME VARCHAR2(50) NATIONALITY VARCHAR2(30)
As you can see also from the query above , the column ‘SWIFT_CODE‘ is not displayed.
Renaming table tblcustomer to tbl_customer
Example: SQL> alter table tblcustomer rename to tbl_customer; Table altered.
Marking table tbl_customer as read-only
Example: SQL> alter table tbl_customer read only; Table altered.
And then to check whether the ‘tbl_customer‘ table is in read-only mode or not, run the following SQL command:
Example: SQL> col table_name format a20; SQL> col read_only format a30; SQL> select table_name, read_only from user_tables where read_only='YES'; TABLE_NAME READ_ONLY -------------------- ------------------------------ TBL_CUSTOMER YES
Marking table tbl_customer as read write
Example: SQL> alter table tbl_customer read write; Table altered.
Dropping unused columns from tbl_customer table(SWIFT_CODE column was marked unused on example05)
Example: SQL> ALTER TABLE tbl_customer DROP UNUSED COLUMNS; Table altered.
Adding primary key constraint
Example: SQL> alter table tbl_customer add constraint cust_pk primary key (CUST_NO); Table altered.
How to add CHECK constraint
Example:
SQL> alter table tbl_customer add constraint chk_cust CHECK (CUSTOMER_TYPE IN ('I','C','B'));
Table altered.
SQL>
Adding UNIQUE constraint
Example: SQL> alter table tbl_customer add constraint uk_cust_name UNIQUE (CUST_NAME); Table altered.
SHOWPIN 122 in 1 Precision Computer Screwdriver Kit, Laptop Screwdriver Sets with 101 Magnetic Drill Bits, Electronics Tool Kit Compatible for Tablet, PC, iPhone, PS4 Repair
30% OffPowerful Functions: 122 in 1 precision screwdriver set contains 98 screwdriver bits, 3 Torx TR screwdriver bits and 21 auxiliary tools, such as screwdriver handle, flexible shaft, extension rod, magnetizer, magnetic pad, cleaning brush etc. Own this ... read more
Compressed Air Duster, 3 Gear to 51000RPM Cordless Electric Air Duster, 6000mAh Rechargeable Air Blower with LED Light for Laptop Computer Keyboard Cleaning Pet Hair Crumb Replaces Compressed Air Can
$29.98 (as of 25/06/2024 15:32 GMT -04:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)TRUE 51000RPM: Our air duster adopters the most advanced motor, the maximum speed can reach TRUE 51000RPM, low energy consumption and longer lifespan. Great to help you deep cleaning, computers, Camera, electronic products, or for inflation. The cool... read more
Baseus USB C to HDMI Adapter, 4K@60Hz USB C Docking Station, 7 in 1 USB C Hub with 3 USB-A, PD 100W, TF/SD Card Reader, USB C Dock Compatible for iPhone 15/Mac/Dell/Acer/HP/ASUS/Steam Deck/Rog Ally
50% Off【7-in-1 Design】USB hub for laptop with 1 HDMI port, 3 USB3.0 ports, 1 SD/TF card slot, and 1 USB-C PD port, excellent connectivity to satisfy all your expansion needs at once, much higher efficiency at work. 【4K@60Hz Ultra Clarity】USB c to hdmi adapt... read more
ASUS Vivobook 15.6” FHD Laptop, AMD Ryzen 3 3250U, 8GB RAM, 128GB SSD, Windows 11 Home in S Mode, Transparent Silver, M515DA-WS33
$319.33 (as of 25/06/2024 15:28 GMT -04:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)【Day-to-day Productivity】: This Windows laptop equipped with Windows 11 Home in S Mode, an AMD Ryzen 3 Processor, and AMD Radeon graphics, is designed to deliver a fast and smooth experience. 【Enhanced Protection】: Crafted with exceptional durability... read more