With the procedure below you can find which columns are totally null, without data.
It is useful, when you need an empty column e.g. for a new field:
BEGIN
DECLARE
the_table VARCHAR2(100) := 'PER_ALL_PEOPLE_F';--change me!!!
rows_fetched NUMBER;
TYPE cursor_ref IS REF CURSOR;
c2 cursor_ref;
TYPE the_tab IS TABLE OF PER_ALL_PEOPLE_F%ROWTYPE;--change me!!!
rec_tab the_tab;
CURSOR C1 IS
SELECT COLUMN_NAME FROM all_tab_columns WHERE table_name=the_table;
BEGIN
FOR I IN C1 LOOP
OPEN C2 FOR 'SELECT * FROM ' || the_table || ' WHERE ' || I.COLUMN_NAME || ' IS NOT NULL';
FETCH c2 BULK COLLECT INTO rec_tab;
rows_fetched := c2%ROWCOUNT;
IF rows_fetched = 0 THEN
DBMS_OUTPUT.PUT_LINE(I.COLUMN_NAME||' : IS NULL');
END IF;
END LOOP;
END;
END;
/