Dropping Unused Columns

View All Scripts Login to Run Script

Script Name Dropping Unused Columns
Description Uses ALTER TABLE to modify a table definition and drop unused columns.
Area SQL General / SQL Query
Referenced In Database SQL Language Reference
Contributor Oracle
Created Monday October 05, 2015

Statement 1
Creating a new table to manipulate the data. To avoid inserting rows, the new table is created using an existing table.
Create a new table using Jobs table
```
CREATE TABLE JOBS_Temp AS SELECT * FROM HR.JOBS
```
```
Table created.
```

Statement 2

Verify, if the rows exist in the newly created tables using the select statement.

Select information from the newly created table

SELECT * FROM JOBS_Temp WHERE MAX_SALARY > 20000


JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
AD_PRES President 20080 40000
AD_VP Administration Vice President 15000 30000
SA_MAN Sales Manager 10000 20080

3 rows selected.

Statement 3
Adding two new columns. These columns will be manipulated in our example.
Adding two new columns.
```
ALTER TABLE JOBS_Temp  
      ADD (DUMMY1 NUMBER(2), DUMMY2 NUMBER(2))
```
```
Table altered.
```

JOB_ID	JOB_TITLE	MIN_SALARY	MAX_SALARY
AD_PRES	President	20080	40000
AD_VP	Administration Vice President	15000	30000
SA_MAN	Sales Manager	10000	20080

Statement 4

Inserting value into these columns.

Insert values into the newly added columns.

INSERT INTO JOBS_Temp(JOB_ID, JOB_TITLE, DUMMY1, DUMMY2) VALUES ('D','DUMMY',10,20)

1 row(s) inserted.

Statement 5

Repeating the step above.

Insert values into the newly added columns.

INSERT INTO JOBS_Temp(JOB_ID, JOB_TITLE, DUMMY1, DUMMY2) VALUES ('D','DUMMY',10,20)

1 row(s) inserted.

Statement 6

Displaying the information to verify the data in the new columns

Displaying the row information for new columns

SELECT * FROM JOBS_TEMP WHERE JOB_ID = 'D'


JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY DUMMY1 DUMMY2
D DUMMY  -  - 10 20
D DUMMY  -  - 10 20

2 rows selected.

Statement 7
Setting the columns to unused.
Setting the newly added columns to Unused
```
ALTER TABLE JOBS_TEMP SET UNUSED (DUMMY1, DUMMY2)
```
```
Table altered.
```

JOB_ID	JOB_TITLE	MIN_SALARY	MAX_SALARY	DUMMY1	DUMMY2
D	DUMMY	-	-	10	20
D	DUMMY	-	-	10	20

Statement 8

Display the count for the unused columns.

Displaying the number of unused columns count

SELECT * FROM USER_UNUSED_COL_TABS WHERE TABLE_NAME='JOBS_TEMP'


TABLE_NAME COUNT
JOBS_TEMP 2

TABLE_NAME	COUNT
JOBS_TEMP	2

Statement 9

Select the information after setting the columns to unused.

Display the table information

SELECT * FROM JOBS_TEMP WHERE MAX_SALARY > 10000


JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
AD_PRES President 20080 40000
AD_VP Administration Vice President 15000 30000
FI_MGR Finance Manager 8200 16000
AC_MGR Accounting Manager 8200 16000
SA_MAN Sales Manager 10000 20080
SA_REP Sales Representative 6000 12008
PU_MAN Purchasing Manager 8000 15000
MK_MAN Marketing Manager 9000 15000
PR_REP Public Relations Representative 4500 10500

9 rows selected.

Statement 10
Drop the columns set as unused.
Drop the unused columns
```
ALTER TABLE JOBS_TEMP DROP UNUSED COLUMNS
```
```
Table altered.
```

JOB_ID	JOB_TITLE	MIN_SALARY	MAX_SALARY
AD_PRES	President	20080	40000
AD_VP	Administration Vice President	15000	30000
FI_MGR	Finance Manager	8200	16000
AC_MGR	Accounting Manager	8200	16000
SA_MAN	Sales Manager	10000	20080
SA_REP	Sales Representative	6000	12008
PU_MAN	Purchasing Manager	8000	15000
MK_MAN	Marketing Manager	9000	15000
PR_REP	Public Relations Representative	4500	10500

Statement 11

Displaying the table after dropping the unused columns.

Dispaly the table after dropping the unused columns

SELECT * FROM JOBS_TEMP


JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
AD_PRES President 20080 40000
AD_VP Administration Vice President 15000 30000
AD_ASST Administration Assistant 3000 6000
FI_MGR Finance Manager 8200 16000
FI_ACCOUNT Accountant 4200 9000
AC_MGR Accounting Manager 8200 16000
AC_ACCOUNT Public Accountant 4200 9000
SA_MAN Sales Manager 10000 20080
SA_REP Sales Representative 6000 12008
PU_MAN Purchasing Manager 8000 15000
PU_CLERK Purchasing Clerk 2500 5500
ST_MAN Stock Manager 5500 8500
ST_CLERK Stock Clerk 2008 5000
SH_CLERK Shipping Clerk 2500 5500
IT_PROG Programmer 4000 10000
MK_MAN Marketing Manager 9000 15000
MK_REP Marketing Representative 4000 9000
HR_REP Human Resources Representative 4000 9000
PR_REP Public Relations Representative 4500 10500
D DUMMY  -  - 
D DUMMY  -  - 

21 rows selected.

JOB_ID	JOB_TITLE	MIN_SALARY	MAX_SALARY
AD_PRES	President	20080	40000
AD_VP	Administration Vice President	15000	30000
AD_ASST	Administration Assistant	3000	6000
FI_MGR	Finance Manager	8200	16000
FI_ACCOUNT	Accountant	4200	9000
AC_MGR	Accounting Manager	8200	16000
AC_ACCOUNT	Public Accountant	4200	9000
SA_MAN	Sales Manager	10000	20080
SA_REP	Sales Representative	6000	12008
PU_MAN	Purchasing Manager	8000	15000
PU_CLERK	Purchasing Clerk	2500	5500
ST_MAN	Stock Manager	5500	8500
ST_CLERK	Stock Clerk	2008	5000
SH_CLERK	Shipping Clerk	2500	5500
IT_PROG	Programmer	4000	10000
MK_MAN	Marketing Manager	9000	15000
MK_REP	Marketing Representative	4000	9000
HR_REP	Human Resources Representative	4000	9000
PR_REP	Public Relations Representative	4500	10500
D	DUMMY	-	-
D	DUMMY	-	-

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database