SQL & PL/SQL :: Materialized View - Table Or View Does Not Exist
May 2, 2012
I have a materialized view "pro_mview",I am trying to refresh the MVIEW by using the following statement.
EXEC DBMS_MVIEW.REFRESH('pro_mview','C');
But I am getting the below error.
*
Error at line 1:
ORA-12008: error in materialized view refresh path
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2256
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2462
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2431
ORA-06512: at line 1
I am able to fetch the data from that materialized view(pro_mview).
When I try to drop the table it gives "ORA-12083: must use DROP MATERIALIZED VIEW ..." . When I try to drop the materialized view it gives "ORA-12003: materialized view 'xxxx' does not exist".
I'm trying to create a Materialized View on a remote database from a simple view. The reason is, the data owners don't want to grant explicit tables privileges to external subscribers.
A new schema is created to publish data in the form of a view. I've created mlogs on the master tables, and granted them to the subscriber, but it's still complaining about a missing primary key on the view. A primary key does exist in the master table.
Is there another work around for this situation without having to work inside the data sources' environment?
I am trying to run the following code. The issue i am having is when running it in a function or procedure. (the SELECT statement works on it's own - so why doesn't it would in a procedure?)
SELECT LAST_DDL_TIME from SYS.dba_objects WHERE object_type='TABLE' AND OBJECT_NAME = 'CONT_ASSESSMENT'
i get an error saying "PL/SQL: ORA-00942: table or view does not exist"..I a quite new to oracle / SQL.
I'm trying to create a procedure but it's giving me errors:
16/3 PL/SQL: ORA-00942: table or view does not exist 4/2 PL/SQL: SQL Statement ignored 3/9 PLS-00341: declaration of cursor 'S_CUR' is incomplete or malformed 63/3 PL/SQL: Item ignored 69/33 PLS-00597: expression 'S_ARRAY' in the INTO list is of wrong type 69/3 PL/SQL: SQL Statement ignored 72/15 PL/SQL: ORA-00942: table or view does not exist 72/3 PL/SQL: SQL Statement ignored
I don't get why its giving table or view does not exists, because when I just execute the SELECT statement, it returned records. weird...
here's my procedure:
create or replace procedure proc_jrp_d_old_77a as cursor s_cur is select /*+ ALL_ROWS */&vtabdate, cd.cb_cardholder_no, ac.user_id,
I have written a function where I am trying to select data from a table located in a different schema. I have asked my DBA to do two things as I am getting Error(14,23): PL/SQL: ORA-00942: table or view does not exist.
a) Grant privileges. b) Create public synonym.
When I do create the table in the schema where the function is located and compile the function it compiles.
CREATE OR REPLACE PROCEDURE revoke_object_priviliges_user ( p_username IN VARCHAR2) AS l_username VARCHAR2 (30) := upper(p_username); BEGIN FOR rec IN (SELECT OWNER,TABLE_NAME,PRIVILEGE,GRANTABLE FROM dba_tab_privs WHERE GRANTEE=l_username) LOOP IF (rec.GRANTABLE = 'NO')
[code]....
When i execute it, i get the following errors:
6/12 PL/SQL: SQL Statement ignored 6/61 PL/SQL: ORA-00942: table or view does not exist 8/2 PL/SQL: Statement ignored 8/6 PLS-00364: loop index variable 'REC' use is invalid
The form actually consists of one block that refers to a table in DB(Datablock). The functionality of the form is to run some logic and then just insert data in the table .This happens on press of a button.
The problem is when I run the form thru' Admin Id it runs successfully but when I run the form through some other user it gives me the error.
The grants on the table are Public. GRANT DELETE, INSERT, SELECT, UPDATE ON XYZ TO PUBLIC;
I have two schema on the two servers for replication replication is working fine.
i export one schema to another so all the tables exists at both the sites. I am adding objects in the replication group using oracle enterprise manager console.
some of the tables added fine. but some gives me error like.
ORA-23309: object UMESH.PRODUCT_MASTER of type TABLE exists
but with the error in and when generate replication support.
SQL> select status,request,message,oname from dba_repcatlog; STATUS REQUEST -------------- ----------------------------- MESSAGE -------------------------------------------------------------------------------- ONAME ------------------------------ ERROR CREATE_MASTER_REPOBJECT
[code]....
sometimes i got error like
ORA-00942: table or view does not exist
when use CREATE_MASTER_REPOBJECT command to create object at master definition site while the the table exists at the master site.but in the same situation other objects are working fine.
When i try to create a trigger , i ended up with error.
SQL> create or replace 2 TRIGGER LOGON_TRG AFTER LOGON ON DATABASE 3 BEGIN 4 INSERT 5 INTO 6 user_audit_log 7 SELECT 8 user#,
[code]....
Warning: Trigger created with compilation errors.
SQL> SQL> show error Errors for TRIGGER LOGON_TRG: LINE/COL ERROR -------- ----------------------------------------------------------- 2/5 PL/SQL: SQL Statement ignored 17/17 PL/SQL: ORA-00942: table or view does not exist
The command used to resolve the error is
GRANT SELECT ON v_$session TO jack;
Jack user has sysdba privilege. My question is 'sysdba' is a super and special user which has all the privileges in database. Then why does it need SELECT privilege on v$session to user to create the trigger?
The SELECT at the end of this script fails with "ORA-00942: table or view does not exist". I think I added proper permission via GRANT command.
create table mudd_table ( a number, b varchar2(10) ); create user mudd_user identified by mudd_user; grant select, insert, update, delete on mudd_table to mudd_user; grant create session to mudd_user; conn mudd_user/mudd_user@quadoracle; select * from mudd_table;
I have a SQL query with a construction like: select a, b from tablea ,select * from (select a as a from tableb) where ....
The issue is that in sqldeveloper I don't get any errors and the select works fine.I use the same select in myEclipse report builder and I get an table or view doesn't exist.
create or replace PROCEDURE INSERT_TESTTABLE ( PrimaryKey IN NUMBER ,One IN VARCHAR2 ,Two IN VARCHAR2 ,Three IN VARCHAR2 ,Four IN VARCHAR2 [code].......
And I get this error: Error(15,13): PL/SQL: ORA-00942: table or view does not exist
connect user1/user1@dbstring CREATE TABLE A12 ( A1 NUMBER, A2 DATE ) / GRANT SELECT,UPDATE,DELETE on A12 to USER2 / DELETE FROM user1.A12 / --throwing error like ORA-00942: table or view does not exist
connect user2/user2@dbstring DELETE FROM user1.A12 / --throwing error like ORA-00942: table or view does not exist SELECT * FROM user1.A12 / --no rows returned
Above scenario has happened only for 2 tables out of 1000 tables in my schema.
Connected to: Oracle8i Release 8.1.6.0.0 - Production JServer Release 8.1.6.0.0 - Production IMP-00003: ORACLE error 942 encountered ORA-00942: table or view does not exist IMP-00023: Import views not installed, please notify your DBA IMP-00000: Import terminated unsuccessfully
Then i run the catexp.sql using SYS user. But i still got that error.
what would be the difference between a view and a materialized view? whether DML possible on a view? i think error occurs if DML tried on a view which is a combination of two or more tables, whether DML possible on a materialized view?
CREATE MATERIALIZED VIEW MV_NESTED_DATA NOCACHE LOGGING NOCOMPRESS NOPARALLEL BUILD IMMEDIATE USING NO INDEX REFRESH COMPLETE ON DEMAND START WITH ROUND(SYSDATE) NEXT ROUND(SYSDATE) + 1 WITH ROWID AS select NESTED_TABLE_FIELD from MY_TABLE@Y_DB_LINK;
where NESTED_TABLE_FIELD is a nested table stored as T_NESTED_TABLE
And I get the error: ORA-12014: table 'T_NESTED_TABLE' does not contain a primary key constraint
Why should it if I try to create a MV with "WITH ROWID" refresh option and not "WITH PRIMARY KEY" one?
We are in the process of migrating our databases to a hosting provider (10g Sun -> 11g LINUX.) A (former) data architect at our business had this (nightmare) situation implemented:
- have materialized views created - after creation of mat views(and associated tables), add additional audit columns to the table that are populated by triggers, not by the MV. All data is important.
In order to get all of the data to the hosting provider, we data pump export the full schemas (which include the associated MV tables), have the provider DBA's import (all the materialized views failed on import, but the associated MV tables were created/populated), and I'm now attempting to fix the MV code to get them recreated. Over 100 MV's, most of which have these extra audit columns, and a number of the remote master/source tables do not have primary keys so using the by rowid option. An edited example (object names changed to protect the innocent) - using CAST to include the audit columns:
CREATE MATERIALIZED VIEW SCHEMA1.ACCOUNT ON PREBUILT TABLE USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 512K NEXT 512K PCTINCREASE 0 BUFFER_POOL DEFAULT) TABLESPACE S_TS_01_INDX REFRESH FAST ON DEMAND WITH ROWID USING DEFAULT LOCAL ROLLBACK SEGMENT DISABLE QUERY REWRITE AS SELECT ACCOUNT.GROUP_ID GROUP_ID ... ... cast(null as date) CREATE_PROCESS_DATE, cast(null as varchar2(20 CHAR)) CREATE_PROCESS_ID FROM SCHEMA2.ACCOUNT@REMINSTANCE.WORLD ACCOUNT /
ORA-12058: materialized view cannot use prebuilt table
IF remove "with rowid", get error that cannot create because no primary key on source table.
Online options seem to be (1) do not use prebuilt table (in which case we'd lose the additional audit data) or (2) add a primary key on the master table (we're not in a timeline to make & test changes to various production source tables.)
Other thoughts on how to get this data migrated/populated? This needs to be a lift and drop as much as possible - any type of rewrite/restructuring is out of the question.
we are trying to create a materialized view (MV) which would access the remote database through db link. Now we need to do update on the local MV so that it should be reflected on the master table.
There is no primary key on this table and we are using "complete refresh" option. since we dont have control over remote database, we are not allowed to create MV log over there.
in this scenario, if i try to create updatetable MV with complete refresh, we are getting below error:
SQL Error: ORA-12013: updatable materialized views must be simple enough to do fast refresh
