Oracle: Recreate database view after modifying varchar column size

Question

Hi

I've made some changes to columns in a database table (increasing the size of varchar), however I need to have the view of the table refreshed to pick up this change. In SQL server 2005 I would use the script to -> alter to recreate the script for the view. I'm trying to figure out what the Oracle SQL command would be to rebuild the view to display the change of the column?

Answer 1

If you do so:

create table sample_table(text varchar2(10));
insert into sample_table (text) values('text...');
create view sample_view as select * from sample_table;
select * from sample_view;
alter table sample_table modify text varchar2(200);

You do not do anything to promote this change in view in Oracle database.

Or you can use "ALTER VIEW sample_view COMPILE" (how wrote @AlexPole or @devio). Oracle do this automatically when you firstly use select on sample_view after alter table.

Answer 2

Unless your view is explicitly restricting the size of the column, it will pick up the change automatically. It may have been invalidated by the table change but would be automatically recompiled when first used; or can be manually recompiled with alter view <view name> compile.

Answer 3

Try

ALTER VIEW MyView COMPILE;