Testing the behavior of marking a field as unused in Oracle.

-- TESTING THE RESULT OF MARKING A TABLE FIELD AS UNUSED IN ORACLE DATABASE

-- CREATING A TABLE
CREATE TABLE test_unused
( field_one NUMBER,
  field_two NUMBER,
  field_three VARCHAR(1));

-- POPULATING WITH DATA
INSERT INTO test_unused VALUES ( 1, 10, 'a');
INSERT INTO test_unused VALUES ( 2, 20, 'b');
INSERT INTO test_unused VALUES ( 3, 30, 'c');

-- CREATING AN INDEX ON FIELD ONE
CREATE INDEX field_one_ndx ON test_unused(field_one);

SELECT index_name, table_name, status
FROM user_indexes WHERE table_name = 'TEST_UNUSED';

-- CREATING A SYNONYM OF THE TABLE
CREATE SYNONYM tu FOR test_unused;

-- CREATING A VIEW OF THE TABLE
CREATE VIEW tu_view AS
    SELECT field_one, field_three FROM test_unused;

SELECT object_name, status FROM user_objects
WHERE object_type = 'VIEW' AND object_name = 'TU_VIEW';

-- QUERYING TABLE CONTENTS USING VIEW AND SYNONYM
SELECT * FROM test_unused;
SELECT * FROM tu_view;
SELECT * FROM tu;

-- SETTING FIELD ONE AS UNUSED
ALTER TABLE test_unused SET unused(field_one);

-- PROOFING STATUS OF THE TABLE VIEW
SELECT object_name, status FROM user_objects
WHERE object_type = 'VIEW' AND object_name = 'TU_VIEW';

-- FIND OUT IF THERE IS STILL AN INDEX
SELECT index_name, table_name, status
FROM user_indexes WHERE table_name = 'TEST_UNUSED';

-- SYNONYM SHOULD BE WORKING FINE
SELECT * FROM tu;

-- VIEW WILL STATE IT IS INVALID AND ASK TO BE RECOMPILED
SELECT * FROM tu_view;