It is quite normal for Oracle database objects to become INVALID, especially when a dependency chain is broken. This article takes a closer look at:
Here’s a prototype for using the SQL/XML function XMLTABLE to map XML data into relational rows and columns.
This solution uses the standard EMP table — the same can be extended to work with any XMLTYPE-relational mapping.
When querying v$instance or v$session[1] as a non-admin user, you might come across this error:
SQL> select version
2 from v$instance;
from v$instance
*
ERROR at line 2:
ORA-00942: table or view does not exist
The error suggests that the non-admin user does not have the SELECT privilege on the sys-owned v$ view. On the face of it, the fix appears as simple as: log in as sys and grant select on v$ views to the user.
Appearances are deceptive, they say. Try that and you will get an ORA-02030 error.
Just as you sometimes need to find out your database name when connected to Oracle, you may also need to find out your Oracle database version.
Here’s a rundown of ways to identify the Oracle database version using SQL and PL/SQL.
An Oracle view is like a window to the output of a SQL statement, but sometimes, a single SQL isn’t sufficient – you may need to do some procedural data manipulation before you get the output you want. Does this mean an Oracle view cannot be used if the underlying code cannot be framed as a single SQL? Not at all! This article shows you how to cast PLSQL function output as a view.
Other than the word "view" in their names and the fact that both are defined by an underlying SQL, there is little else common between Oracle views and materialized views – yet they can be a source of great confusion if you are new to these database objects.
Here’s a summary of the key differences between views and materialized views, to put an end to all mix-ups. By the end of the article, you should be in a position to decide if your specific scenario needs a view, a materialized view, or neither.
A view is like a virtual table. It takes the output of a query and treats it like a table.
A view can be based on one or more tables or other views. These tables/views are called base tables.
A view takes up no storage space other than for the definition of the view in the data dictionary.