SQL & PL/SQL :: ORA-23404 - Refresh Group Does Not Exist?
Jul 11, 2011
When I create a Materialized view using:
CREATE MATERIALIZED VIEW LOAD.M_VW_ABN_PROD_921
[color=red][i]REFRESH FORCE ON DEMAND START WITH sysdate+0
NEXT (TRUNC(SYSDATE+1) + 9.5/24)[/i][/color]AS
SELECT
...............
......................
it creates a DBMS_JOB for me with the required interval.
But now my requirement is that I want to create a job on the existing MV.
Now I tried the code mentioned in the previous post to schedule the job.
This job does not work. When I tried to execute it manually using:
DBMS_IJOB.RUN(12345) as sys user is throws the follwing error.
Error listed in the alert.log file is as below:
ORA-12012: error on auto execute of job 17497
ORA-23404: refresh group "CLARITY"."M_MV_INBASKET_ANALYSIS"
does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_REFRESH", line 23
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1
Sun Jul 10 20:07:51 2011
View 5 Replies
ADVERTISEMENT
Mar 13, 2012
FRM-41827:Group does not exist.
I am getting the above error in Oracle Forms as I am using it in Oracle Apps 11.5.10.2 as a custom form... how to resolve the above error....
View 4 Replies
View Related
Sep 11, 2013
I have 5 MViews that I want to refresh in two occassions, every sundays and at the 1st of the month. I created a Refresh Group for the weekly and that work fine. But when I tried to created the second Refresh Group for the monthly I get a "materialized view is already in a refresh group".
You can only have a materialized view in one refresh group? What options to I have to refresh it in different intervals?
View 1 Replies
View Related
Jul 25, 2008
How to reset the status of Refresh Group?
In our database, it is showing the status of Refresh group to 'Broken'.
View 6 Replies
View Related
Jan 6, 2010
I am working on materialized view replication but i have an crucial issue. I have a refresh group which contain many MVs some are Read only and some are update but all are in Fast refresh mode (logs are created on both replication and MV sites on these objects) and are referentially integrated to each other i.e they have parent child relationship with each other as at replication site on base tables.
When refresh group job runs to refresh these MVs i got the errors
ORA-02292: integrity constraint (string.string) violated - child record found
After that job status has changed to break,but in the same time all the pending transactions at the MVs sites are pushed to Replication site i e ( Base tables at replication site and MVs of these tables are synchronized).In order to change the status of job to Normal, i have to disable some constraints(not all) on the MVs and manually run the job .After that job status will become Normal even i enable these constraints with validation.
What will be the reason ? Are there some MVs trying to complete refresh instead of Fast or increment refresh in this refresh group?If yes how i can trace out MVs and solve this problem?
Some other information ,
Replication site running with 10gR2 Enterprise Edition.
Materialized View site running with 10gR2 Slandered Edition.
View 1 Replies
View Related
Mar 24, 2010
How to refresh the dependent materialized views in a snapshot group. It refreshes in the order we add the materialized views to snapshot group?
Here below is the scenario.
MATVW_A
CREATE MATERIALIZED VIEW MATVW_A BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS SELECT * FROM EMP;
MATVW_B
CREATE MATERIALIZED VIEW MATVW_B BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS SELECT * FROM MATVW_A;
MATVW_C
CREATE MATERIALIZED VIEW MATVW_C BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS SELECT * FROM MATVW_B;
I created a snapshotgroup SNGRP with all the above materialized views.
DBMS_REFRESH.MAKE(name => 'SNGRP',
tab => 'MATVW_A,MATVW_B,MATVW_C',
next_date => TRUNC(sysdate) + 1,
interval => 'SYSDATE + 1');
The order of refreshing the materialized views would be like first materialized view MATVW_A should get refresh then after MATVW_B,MATVW_C.
As they are dependent views.Is there anyway where we can mention the order of refreshing materialized views in snapshot group.
View 2 Replies
View Related
Mar 25, 2009
Suppose I have materialized view A,B,C. Now I have a new materialized view D which is created from A,B,C. Can I put D into same refresh group?
I am not sure whether D will be refreshed first before A,B,C, resulting that D do not get any update If that is the case, the result will be wrong
If we cannot use refresh group to update nested materialized view, what should we do?
View 13 Replies
View Related
Aug 17, 2012
tell me if a REFRESH GROUP is automatically created when a materialized view is created?
View 3 Replies
View Related
Aug 14, 2010
I am getting ORA -32320 when I tried to fast fresh MVs that were truncated.I am able to complete refresh all MVs. However, some MVs are still giving me ORA-32320 when I try to do fast refresh even after complete refresh.
DBMS_MVIEW.EXPLAIN_PLAN says the MVIEW can be fast refreshed.
ERROR at line 1:
ORA-32320: REFRESH FAST of "dev"."Claim_line" unsupported after cointainer table
PMOPs
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 841
ORA-06512: at line 1
Quote:ORA-32320:
REFRESH FAST of "string"."string" unsupported after container table PMOPs Cause: A Partition Maintenance Operation (PMOP) has been performed on the materialized view, and no materialized view supports fast refersh after container table PMOPs. Action: Use REFRESH COMPLETE. Note: you can determine why your materialized view does not support fast refresh after PMOPs using the DBMS_MVIEW.EXPLAIN_MVIEW() API.
how to again make the MVs fast refreshable?
View 4 Replies
View Related
Nov 1, 2013
I'm trying to group sets of data based on time separations between records and then count how many records are in each group.
In the example below, I want to return the count for each group of data, so Group 1=5, Group 2=5 and Group 3=5
SELECT AREA_ID AS "AREA ID",
LOC_ID AS "LOCATION ID",
TEST_DATE AS "DATE",
TEST_TIME AS "TIME"
FROM MON_TEST_MASTER
WHERE AREA_ID =89
AND LOC_ID ='3015'
AND TEST_DATE ='10/19/1994';
[code]....
Group 1 = 8:00:22 to 8:41:22
Group 2 = 11:35:47 to 11:35:47
Group 3 = 15:13:46 to 15:13:46
Keep in mind the times will always change, and sometime go over the one hour mark, but no group will have more then a one hour separation between records.
View 4 Replies
View Related
Jun 23, 2011
I read that rownum is applied after the selection is made and before "order by". So, in order to get the sum of salaries for all employees in all departments with a row number starting from 1, i wrote :
select ROWNUM,department_id,sum(salary) from employees group by department_id
If i remove rownum, it gives the correct output. Why can't rownum be used here ?
View 16 Replies
View Related
May 17, 2011
Refer to the txt file to create table and insert data.
I executed the following query-
SELECT priority, detail, COUNT(1) FROM TEST GROUP BY priority, detail
and got the following result-
PRIORITYDETAIL COUNT(1)
StandardPatch 27
StandardInitial TSS 1
StandardInitial development 10
StandardProduction deployment5
High PriorPatch 1
Now I want that Initial TSS and Initial development should be combined as Initial together and I should get the result as follows:
PRIORITYDETAIL COUNT(1)
StandardPatch 27
StandardInitial 11
StandardProduction deployment5
High PriorPatch 1
View 3 Replies
View Related
May 9, 2011
Is there any way to add one disk group space to another disk group. Because One of my disk group is full i want to add space in to that group.
View 1 Replies
View Related
Mar 23, 2013
I'm using this code, and it performs fine, but I'm wondering if there is a more elegant way to do it--maybe with "ROLLBACK". Basically (as you can see) I need to get a normal count for each group but also for each group take a percentage of the total count (so all groups pct adds up to 100 (oh yeah, don't test for zero below, but just a test... )
select
c.Event,
c.code,
count(1) as calls,
total.total_count,
count(1) / total.total_count * 100 as pct_of_total
from
table1 c
[Code]....
[Edit MC: add code tags, do it yourself next time]
View 4 Replies
View Related
Apr 23, 2013
i installed oracle XE and odac tool kit for asp.net in my pc.when i am trying to run InstallOracleProfile.sql i am getting
ORA-01917: user or role 'ORA_ASPNET_PROF_FULLACCESS' does not exist.
View 2 Replies
View Related
Feb 3, 2012
i wrote a program to that takes tablename as input parameter and returns true or false based on table exist or not
the below code works differently when table has data and when table does not have data ? how to improve the below code to make sure my function always retuns true if table exists and false if does not exist regardless of 0 records or more than one record
CREATE OR REPLACE FUNCTION is_tab_present_g (pi_tab_name IN VARCHAR2)
RETURN BOOLEAN
IS
row_cnt NUMBER := 0;
sql_stm VARCHAR2 (4000);
l_tab_name VARCHAR2 (4000);
[Code]....
my verification block
BEGIN
if is_tab_present_g('chk_pk') then
dbms_output.put_line('yessssss');
execute immediate 'drop table chk_pk cascade constraints';
else
dbms_output.put_line('nooo');
end if;
END;
/
View 8 Replies
View Related
Mar 31, 2010
I've been trying to write some code to add a column if it does not exist as the code will be run numerous times and will be parameterized in other software to run across multiple tables.
Here is what I have made so far:
DECLARE
COLEXISTS integer;
vCmdStr varchar2(4000);
[Code]....
I can't figure out where the invalid character is.
View 3 Replies
View Related
Nov 25, 2010
In Oracle11g (R1 and R2) we can create the user in +ASM instance just like normal oracle database. But If the user already exist I get an error
CREATE USER test IDENTIFIED BY test
*
ERROR at line 1:
ORA-01920: user name 'TEST' conflicts with another user or role name
So, I would like to know if there is any data dictionary view which can tell me if the user already exist or not.
View 5 Replies
View Related
Nov 22, 2011
finding the difference between IN and EXIST.
View 15 Replies
View Related
Jul 28, 2012
I need to put amount '0' if the row exist but has no amount in my "where " conditions. the orginal commad is :
select t.aaa, count (t.bbb), sum (t.ccc) from nrb t where t.vvv IN ('3','4','5','6','D','E','F') and t.ddd like '50%' and t.eee >= TO_DATE('2012/03/21','YYYY/MM/DD') and t.eee <= TO_DATE('2012/07/21','YYYY/MM/DD') group by t.aaa order by t.aaa
and the result is : "result" tab in excel atached file.i need this result: "result 2" tab in excel atached file.
View 8 Replies
View Related
Oct 11, 2012
i am using one if exist condition in sql server that is like this.
if exists(select 1 from ShiftEmployee where Empid=@EmpID and Month=@month and year=@year)
begin
some value is passing here...
end
how to use the same in oracle?
View 4 Replies
View Related
Aug 27, 2010
I created a stored procedure(A) in user1@db1. This stored procedure will insert data from db1 to tables in user2@db2 using synonyms created in user1@db1 referencing table at user2@db2.
In the stored procedure (A),
insert into synonym select seq_xx.nextval from table@db1;
Then I will execute (A) in user2@db1. Execute right on (A) is given to user2@db1.Select and insert grants on db2 tables are given to user1@db1. Seq is created in db1. Compilation is successful, however when i execute (A) it has an error of
ORA-02289: sequence does not exist
ORA-02063: preceding line from db1
ORA-02063: preceding 2 lines from db2
View 6 Replies
View Related
Jan 18, 2013
delete table if exist
How do we write this code in oracle / sqlplus
View 4 Replies
View Related
Nov 20, 2012
In my 11g Oracle i have the following issue:
create table TEST
(
COL_DATE DATE
)
partition by range (COL_DATE)
(
partition TEST2012 values less than (TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
[Code]....
when i execute this i receive the following error:
ORA-02149: Specified partition does not exist?
View 2 Replies
View Related
Apr 3, 2012
I am trying to do a impdp using a network link and it fails with the ORA-31626: job does not exist.It worked with a different database on the same server. The network link is there, data pump directory exists, the read and write privileges are granted to the oracle user.There are no other data pump jobs running:
SQL> select JOB_NAME,STATE from DBA_DATAPUMP_JOBS;
no rows selected
My database details:
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
The whole error is listed below:
omdx16dd$ impdp oracle@xtst080 SCHEMAS=BRM REMAP_SCHEMA=BRM:MBR_SLN CONTENT=METADATA_ONLY DIRECTORY=DATA_PUMP_DIR NETWORK_LINK=po02
Import: Release 10.2.0.4.0 - 64bit Production on Tuesday, 03 April, 2012 17:20:33
Copyright © 2003, 2007, Oracle. All rights reserved.Password:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-00942: table or view does not exist
ORA-00942: table or view does not exist
[code]...
View 1 Replies
View Related
Apr 19, 2011
Can there be an impact on performance if below two index exists on the same table:
t_idx(col1,col2, nvl(col7,col6));
t_idx1(col1,col2,nvl(col6,col7));
View 5 Replies
View Related
Mar 21, 2013
I listed invalid object, but when compile object, say that not exist.
SQL> SELECT OBJECT_NAME, OBJECT_TYPE FROM DBA_OBJECTS WHERE STATUS='INVALID' AND OBJECT_NAME ='PCK_PIR_NFEL_MILLENIUM' AND OWNER='PIRAMIDE'
OBJECT_NAME OBJECT_TYPE
--------------------------------- --------------------
PCK_PIR_NFEL_MILLENIUM PACKAGE
SQL> ALTER PACKAGE PCK_PIR_NFEL_MILLENIUM COMPILE;
ALTER PACKAGE PCK_PIR_NFEL_MILLENIUM COMPILE
*
ERROR at line 1:
ORA-04043: object PCK_PIR_NFEL_MILLENIUM does not exist
View 2 Replies
View Related
Feb 10, 2011
I am running Oracle 10g on my machine. I have create an ER diagram in Toad Data Modeler, which includes all the keys, contraints etc.I have generated a DDL script which I want to biuld my tables with in Oracle.I have loaded the DDL script using SQL*Plus Worksheet and there are no problems.then tryed to insert some test data into my tables and I keep getting an error code of:
ORA-00942, table or view does not exist.Now I know the tables have been created and also by verifying this with the data dictionary using
select table_name
from user_tables;
TABLE_NAME
=========
Table1
Table2
Table3
.
.
etc
It then displays all 20 of my tables, as above. I am using a Visual Basic front end and I can see all 20 tables listed there also, with attribute names for each table.Also I get the following by typing:
select owner, object_type from
all objects where object name = 'Customer';
OWNER OBJECT_TYPE
------------------ -----------------
SYSADMIN TABLE
View 4 Replies
View Related
Feb 16, 2011
How can we refresh a database ?
View 3 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
