View - Base Tables

Does View occupy memory? If we delete the base table of existing view, What happens?

Questions by manu4sql

Editorial / Best Answer

Mad Hatter  

  • Member Since Nov-2008 | Nov 4th, 2008


View does not occupy memory, but Oracle keeps view code in its dictionary (you can check in user_views, for example)
If you delete the base table, the view becomes INVALID.
In order to make it valid again, you have to ether build the deleted table again, or create anotehr view with the same name and structure as deleted table, or create synonym with the same name as deleted table, which will point to the table with the same structure.

Showing Answers 1 - 2 of 2 Answers

Mad Hatter

  • Nov 4th, 2008
 

View does not occupy memory, but Oracle keeps view code in its dictionary (you can check in user_views, for example)
If you delete the base table, the view becomes INVALID.
In order to make it valid again, you have to ether build the deleted table again, or create anotehr view with the same name and structure as deleted table, or create synonym with the same name as deleted table, which will point to the table with the same structure.

nalinbit

  • Dec 6th, 2008
 

View just stores the query defination and not the data. if base table or any one table involed with view is dropped we will get this error in 10g

ORA-04063: view "SCOTT.VEMP" has errors

 where vemp is view name

View is a named SQL query stored in data dictionary. View itself does not
contain any data, the execution of view retrieve data from base table (s).
If base tables are deleted, the following error will be reported.
ERROR at line 1:
ORA-04063: view "SCOTT.EMP1_VIEW" has errors
We can get the successful execution of view by recreating the related base
tables.


Actually view is a stored select statement and its code is stored in data dictionary. It doesn't occupy space in the memory. whenever view is called, the stored select statement executes and fetches the data from the base table.
If base table is deleted, view becomes invalid.

View does not occupy memory as it only provides a way for watching multiple views of an object: Table. Moreover, it is a virtual table which does not take space.
It provides DATA HIDING.For e.g. if an Administrator does not want his subordinates to have a view over all records then he can use view and assign the privileges of that view only to his subordinates and not of table.
If the base table is deleted then view will become inaccessible.

  Was this answer useful?  Yes

pkshve

  • Sep 12th, 2010
 

Views are database objects - they are just SQL code stored in database which will be executed every single time the view is called. Database will check the existance of columns and tables in the base SQL when view is created and it keep checks every single time the view is called.
When base table is deleted, DB marks the view as un usable/corrupted and raises the error when its called. The view code still remain in the database.

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions