ORA-00942: Table or View does not exist
ORA-00942: Table or View does not exist
Cause of error: Oracle table or view does not exist in the database.
Oracle database privileges are missing or granted incorrectly.
Actions to take: Make sure that the table or view or synonym that you are referencing exists in the database and ensure they have necessary privileges.
For example if you try to access a table or view from another schema and you do not have select privilege to that table or view then you will get this error message. Even if you specify the table or view name using the schema name- you will still get this error.
Lets’ say there is a table called table1 in a schema called schema1. There is another schema called schema2. At the moment you are in Oracle schema2 and want to execute an Oracle select statement on table1 such as
select * from schema1.table;
But if you do not have Oracle select privilege granted to you then you will get ORA-00942.
To fix that login to Oracle in your schema1 schema and then run the following statement to grant select privilege to schema2.
SQL> grant select on schema1.table1 to schema2;
Now you should be able to access table1 data from schema2 as long as you qualify the Oracle table name with the schema name i.e. schema1.table1. This query should return you data now.
select * from schema1.table; Continue reading
Oracle 11G’s Strong Verifier and Case Sensitive Password
by: Paul Fleming
As an Oracle DBA, you probably have heard a lot about the new security features in Oracle 11G such as database vault, audit vault, strong verifier, enhanced ASO, etc. But with a busy schedule, how do you find the time to study all the topics in depth? Are all of them going to affect your daily DBA operations? What should you know first and foremost? Of course, depending on the particular database you are administering, you will have your unique need. But as a rule of thumb, what tends to affect you the most is almost never those cool new features. The reason is simple: if you don’t use them, they won’t affect you. What tends to affect you is the change in the existing features that might lead to compatibility or interoperability issues. When that script you have used for years suddenly stops working, you will be disappointed. When your boss or above calls to ask what happened, you could feel terrible.
In this article, we discuss the new password case sensitivity feature in 11G. Every Oracle session starts with authentication. If the authentication fails at the beginning of your script, everything that follows will almost certainly be broken. So what was changed, why, and what should you know? Continue reading
Oracle Synonym example
Oracle synonyms are short names (and easy to remember) for other Oracle object names. The other oracle objects can be tables, views, procedures, functions, packages and other Oracle objects and may be in the same schema or in another schema or in another database.
Synonyms can be PRIVATE or PUBLIC. If you create a private synonym then only you can use that synonym. The default is PRIVATE.
PUBLIC synonyms can be used by all database users. Continue reading
Oracle Ref Cursor And Cursor Variable
HTML clipboardMany of you may have heard of the terms REF CURSOR and cursor variable. This article here will explain you clearly – what is a ref cursor and cursor variable. Then it will demonstrate you how to use a ref cursor and cursor variables with example.
A REF CURSOR is a cursor which does not have a fixed SQL query associated with it. Basically you declare a TYPE of REF CURSOR type. Then you define a variable of that TYPE. That variable is called and cursor variable.
Then you assign a query to that cursor variable. After that you can use that cursor variable like any other cursor.
CURSOR VARIABLE is a variable whose data type is a TYPE of REF CURSOR.
On the other hand, in a normal cursor you write a select query and you give a name to that select query. You can open, fetch data and close that cursor whenever you want.
For example, you declare a normal cursor as:
cursor emp_cur is
select * from emp where deptno =10; Continue reading
Recent Comments