Oracle View Not Updating After Column Type Change

oracleoracle-11gview

I noticed that the view column data type doesn't change when I change the datatype of the underlying table. Are there any extra steps I need to take?

Here is an example:

CREATE TABLE test ( amount NUMBER(10,2) );
CREATE VIEW test_vw AS SELECT * FROM test;

SELECT table_name, column_name, data_type, data_precision, data_scale 
FROM user_tab_columns 
WHERE table_name = 'TEST_VW';

ALTER TABLE test MODIFY ( amount  NUMBER(19,2) );

SELECT table_name, column_name, data_type, data_precision, data_scale 
FROM user_tab_columns 
WHERE table_name = 'TEST_VW';

Both queries return the same result:

TABLE_NAME COLUMN_NAME DATA_TYPE DATA_PRECISION DATA_SCALE
---------- ----------- --------- -------------- ----------
TEST_VW    AMOUNT      NUMBER                10          2

Best Answer

Dependent objects are invalidated when their parent changes:

select status from user_objects where object_name = 'TEST_VW';
-------    
INVALID

Invalid objects are automatically validated when they are next used:

select * from test_vw;

SELECT table_name, column_name, data_type, data_precision, data_scale 
FROM user_tab_columns 
WHERE table_name = 'TEST_VW';

TABLE_NAME COLUMN_NAME DATA_TYPE DATA_PRECISION DATA_SCALE
---------- ----------- --------- -------------- ----------
TEST_VW    AMOUNT      NUMBER                19          2

Alternatively, you can recompile the object yourself:

alter view test_vw compile;

It is worth noting that no data or precision is lost after modifying the table, but before re-validation:

SELECT table_name, column_name, data_type, data_precision, data_scale 
FROM user_tab_columns 
WHERE table_name = 'TEST_VW';

TABLE_NAME COLUMN_NAME DATA_TYPE DATA_PRECISION DATA_SCALE
---------- ----------- --------- -------------- ----------
TEST_VW    AMOUNT      NUMBER                10          2 

insert into test (amount) values (123456789012345);

select status from user_objects where object_name = 'TEST_VW';
-------    
INVALID

select * from test_vw;
---------------
123456789012345

select status from user_objects where object_name = 'TEST_VW';
-------    
VALID
Related Question