SQL - View - Base Tables

GeekInterview.com > Interview Questions > Oracle > SQL

Go To First | Previous Question | Next Question

SQL | Question 170 of 171

View - Base Tables

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

Total Answers and Comments: 7

Last Update: October 28, 2009 Asked by: manu4sql

Sponsored Links

Best Rated Answer

—

Submitted by: Mad Hatter

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.

Above answer was rated as good by the following members:
chaitra murthy, rituk_15, Sowmya Viswanath

October 30, 2008 09:11:44

oozaifa

Member Since: October 2008 Contribution: 4

RE: View - Base Tables

If we delete the base table of view the view will prompt an error its wont execute

Is this answer useful? Yes | No

October 30, 2008 09:14:53

oozaifa

Member Since: October 2008 Contribution: 4

RE: View - Base Tables

View does not occupy an actual database memory it just an virtual table once we delete the base table the view will not execute.

Is this answer useful? Yes | No

November 04, 2008 17:49:16

Mad Hatter

Member Since: November 2008 Contribution: 6

RE: View - Base Tables

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.

Is this answer useful? Yes | No

3 0

Overall Rating: +3

November 26, 2008 04:04:51

laks_31

Member Since: May 2006 Contribution: 1

RE: View - Base Tables

Views does not occupy memory. When the base table is deleted view also throws an error

Is this answer useful? Yes | No

December 06, 2008 11:22:06

nalinbit

Member Since: November 2008 Contribution: 3

RE: View - Base Tables

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

Is this answer useful? Yes | No

1 0

Overall Rating: +1

August 17, 2009 10:55:24

Priya Murugesan

Member Since: August 2009 Contribution: 2

RE: View - Base Tables

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.

Is this answer useful? Yes | No

October 28, 2009 01:45:09

Rabindra_Kumar

Member Since: October 2009 Contribution: 6

RE: View - 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.

Is this answer useful? Yes | No

Go To Top

User Name		Remember Me?
Password

Question Bank