There can be different ways and you can have a look on the below code will identify columns having all NULLs. Similarly, you can write it to Delete such columns from Table if you don’t want them in your table.

Declare
l_count Number;
Begin
For col In (Select Table_Name, Column_Name
From User_Tab_Columns Where Table_Name=’EMP_DROP’)
Loop
Execute Immediate ‘Select Count(*) From ‘||col.Table_Name
||’ Where ‘||col.Column_Name
||’ Is Not Null And Rownum=1′ Into l_count;
If l_count = 0 then
Dbms_Output.Put_Line(‘Column :’||col.column_name||’ contains only Nulls’);
End If;
End Loop;
End;

SQL> SELECT * FROM EMP_DROP;

ID NAME SALARY STATUS DEPTNO MANAGERID COMM ORDERNUM ORDERSTATU PRODUCTDES
———- ———- ———- ———- ———- ———- ———- ———- ———- ———-
101 Ravi 89000 Active 103 Pending
104 Nikhil 69000 Active 101 Pending
107 Karan 101000 Active 112 Pending
108 Subho 78000 Active 101 Pending
109 Manu 777000 Active 103 Pending
110 Sajal 88000 Active 101 Pending
112 Pankaj 90000 Active 112 Pending
113 Abhishek 44000 Active 110 Pending
115 Tom 89000 Active 110 Pending

9 rows selected.

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
   2 l_count NUMBER;
   3 BEGIN
   4 FOR col IN(SELECT TABLE_NAME, COLUMN_NAME
   5 FROM USER_TAB_COLUMNS Where TABLE_NAME=’EMP_DROP’)
   6 LOOP
   7 EXECUTE IMMEDIATE ‘SELECT Count(*) From ‘||col.TABLE_NAME
   8 ||’ Where ‘||col.COLUMN_NAME
   9 ||’ IS NOT NULL And ROWNUM=1′ INTO l_count;
   10 IF l_count=0 Then
   11 DBMS_OUTPUT.PUT_LINE(‘Column :’||col.COLUMN_NAME||’ Contains Only NULLs’);
   12 END IF;
   13 END LOOP;
   14 END;
   15 /
Column :DEPTNO Contains Only NULLs
Column :COMM Contains Only NULLs
Column :ORDERNUM Contains Only NULLs
Column :PRODUCTDESC Contains Only NULLs

PL/SQL procedure successfully completed.