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).
View 3 Replies
ADVERTISEMENT
Oct 18, 2013
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".
Database -Oracle10g
OS- SUSE Linux
View 9 Replies
View Related
Jan 17, 2013
can we change the existing materialized view to normal view? if yes how?
View 2 Replies
View Related
Dec 11, 2012
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?
View 5 Replies
View Related
Apr 25, 2007
I created a table by doing so:
create table Playlist (Artist string, Album string, Track int, Song string, Genre string);
But I get the following error message when trying to perform a query:
SQL> select artist
2 from playlist;
from playlist
*
ERROR at line 2:
ORA-00942: table or view does not exist
I entered data in for the artist, so why doesn't it work?
View 4 Replies
View Related
May 13, 2011
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.
View 1 Replies
View Related
Sep 14, 2011
I am currently getting an error when I try to access a table from a different schema from my Stored Procedure:
Error: PL/SQL: ORA-00942: table or view does not exist
But when I run the relevant SQL query in SQLPLUSW it is running fine.
cursor cur_prod 3 is
4 select rep.PROD_ID,
5 rep.PROD_NM,
6 rep.PROD_SHIP_DT,
7 from GWIN.T_PROD_SEG rep;
[code]....
View 2 Replies
View Related
Sep 10, 2012
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,
[code]....
View 6 Replies
View Related
Jun 2, 2010
I Need to develop a procedure which inserts data into a table reading from other table. I am executing a insert statement for this.
But im getting below error while compiling the procedure. Quote:Error(54,15): PL/SQL: ORA-00942: table or view does not exist
I am able to execute the insert statement independently. and also able to execute the procedure without this insert.
Created the tables and procedures in the same schema.
View 10 Replies
View Related
Oct 15, 2012
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.
View 4 Replies
View Related
Dec 21, 2010
what is wrong with this:
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
View 2 Replies
View Related
Jul 1, 2010
I am using the sid of v$mystat to create a unique filename in my pl/sql procedure.
I have granted access to v$mystat to the user that is accessing it from system user as:
SQL>GRANT SELECT ON V_$MYSTAT TO ar;
Grant succeeded.
SQL> commit;
Commit complete.
now when i login as user ar and do a select on v$mystat it works fine:
SQL> select sid from v$mystat WHERE ROWNUM = 1;
SID
----------
290
However, when i do the same from my PL/SQL procedure it throws an error saying :
SQL> @FILECREATE
53 /
Warning: Function created with compilation errors.
SQL> show errors
Errors for FUNCTION FILECREATE:
LINE/COL ERROR
-------- -----------------------------------------------------------------
22/8 PL/SQL: SQL Statement ignored
22/35 PL/SQL: ORA-00942: table or view does not exist
My PL/SQL function can be found as an attachment.
View 11 Replies
View Related
Mar 1, 2010
In toad 9.1.0.62 version, when i open a package in schema browser. It shows table or view doesn't exist.
But the package seems to be in valid state.
I also used other schema's to open that schema packages it opens with no problems.
View 5 Replies
View Related
Jun 8, 2011
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;
View 3 Replies
View Related
Jul 28, 2008
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
before adding this objects in the group i do like
SQL> SELECT oname FROM DBA_REPOBJECT ;
ONAME
------------------------------
AC_ACCOUNT
AC_ACCOUNT$RP
AC_ACCOUNT$RP
AC_ACCOUNT_MASTER
AC_ACCOUNT_MASTER$RP
AC_ACCOUNT_MASTER$RP
6 rows selected.
at both the sites. when i add product_master in the group then entries becomes like
SQL> SELECT oname FROM DBA_REPOBJECT ;
ONAME
------------------------------
AC_ACCOUNT
AC_ACCOUNT$RP
AC_ACCOUNT$RP
AC_ACCOUNT_MASTER
AC_ACCOUNT_MASTER$RP
AC_ACCOUNT_MASTER$RP
PRODUCT_MASTER
7 rows selected.
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.
View 8 Replies
View Related
Apr 6, 2012
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?
View 3 Replies
View Related
Apr 22, 2011
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;
View 2 Replies
View Related
Jul 26, 2012
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.
View 2 Replies
View Related
Oct 2, 2010
I am trying to do the following-
CREATE DIRECTORY my_dir AS 'd:oraclefiles';
GRANT READ,WRITE ON my_dir TO PUBLIC;
The above throws up an error-
ORA-00942: Table or view does not exist
View 9 Replies
View Related
Nov 21, 2011
I have created this store procedure:
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
View 1 Replies
View Related
Mar 6, 2012
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.
View 15 Replies
View Related
Jul 22, 2004
i imported a .dmp file but i got this error:
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.
View 20 Replies
View Related
Apr 26, 2011
difference between view and materialized view?
View 1 Replies
View Related
Aug 12, 2013
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?
View 7 Replies
View Related
Dec 2, 2010
I try to do this:
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?
View 4 Replies
View Related
Aug 6, 2010
DB1: Sql Server
DB2: Oracle 11g
Is it possible to create a MV log against table@DB1? If not, then am I limited to refresh complete if I need to create MVs against @DB1?
View 1 Replies
View Related
Aug 18, 2010
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.
View 2 Replies
View Related
Jun 6, 2011
Im facing the problem whenever I try to drop a user. Following thing that I m trying..
system@vahan> drop user knp cascade;
Error at line 1:
ORA-00604: error occured at recursive SQL level 1
ORA-00942: table or view does not exist
ORA-06512: at line 7
View 4 Replies
View Related
May 16, 2012
how to create materialized view on partition table?
View 1 Replies
View Related
Aug 28, 2013
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
how to create such MV on this scenario?
our environment is:
Oracle 11.2.0.3
View 1 Replies
View Related
