I have created a materialized view in schema SchemaBB of Server B with a trigger
CREATE MATERIALIZED VIEW WORKAREA.TABLA_TEST70
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
WITH PRIMARY KEY
AS
SELECT TEST_PK,
TEST_TEXTO
[code].........
All schemas have the appropriate grants.
When inserting in TABLA_TEST (SchemaAA) and refreshing MView TABLA_TEST70 things go nicely.
But, when updating a record in the original TABLA_TEST and refreshing the MView the results in TABLA_TEST in SchemaBA are as if I have deleted the record (FECHA_DELETE is set to SYSDATE).
I am trying to write a trigger on a new table. (dest_test) This is the first trigger that I have ever attempted (fairly new DBA) and I am having some trouble with the trigger body.It is a before insert trigger that will need to select from another table (dest) for a particular value being inserted (destination).
create table dest_test ( destination varchar2(4) not null, db_name varchar2(10) not null )
desc dest
[code]...
I am getting the exact opposite results than I want, though. If the value appears in dest, it is inserting into dest_test... NOT whatI want it to do!If the value doesn't appear in dest, it is throwing ora-6512 and ora-4088 errors. Is there a way to suppress these errors, or to graceful exit from the block so that the trigger completes without throwing these errors?
i want to create "on update" database trigger on materialized view which is on replication schema.I would like to update rows in table when update occurs on materialized view on master side.I wrote some code and tried with this:
CREATE OR REPLACE TRIGGER a after update on A@dirfep.us.oracle.com FOR EACH ROW BEGIN
I am using the following merge statement it's not working properly. If I tried to insert (or) update the existed record. I am getting unique key constraint violated error.
I'm trying to Create a procedure that generates: Customer Description of Order Amount of Order Total Of Order
As this Generates More than One Row I thought i would need to use cursor in order to store the results and then use them from the object So this is what i Coded:
CREATE OR REPLACE PROCEDURE UP_CustOrders AS /*I Am Selecting A Cursor Cause It Return More Than One Row*/ CURSOR ReportCursor IS SELECT Company
[code]..
It appears It creates the procedure but im not sure why it has compilation errors Perhaps im missing some symbols? Here are the tables:
CREATE TABLE Customers (CustNum NUMBER(10) NOT NULL, Company VARCHAR2(20) NOT NULL, CustRep NUMBER(10),
There are two servers A and B ,and i am maintaining one table suppose table1 and another table suppose table2 , DML triggers are made for both of these tables.The action of DML trigger is that the movement we are inserting values , suppose a table1 on server A so automatically the same set of value in table2 on server Bso my problem is that the movement i inserted value on table1 on server A ,
the same set of values were inserted into table2 of server B and as the values were inserted intotable2 , the same set of values were inserted into the tabld1 because of the trigger. How to stop the non ending loop which occurs due to the simultaneous firing of triggers and insertion of same records into both the table endlessly...
version :---Personal Oracle Database 10g Release 10.2.0.3.0 TNS for 32-bit Windows: Version 10.2.0.3.0
It seems some issue with With Clause along with Row Number. Output is strange when it comes with SELECT MIN()... But Looks fine Once you add "order by" or "where" clasue inside WITH Clause
SQL> select * from v$version;
BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi PL/SQL Release 10.2.0.5.0 - Production CORE 10.2.0.5.0 Production
[code]....
But Output looks fine once you add ORDER By clause inside WITH clause
SQL> WITH EM AS 2 ( 3 SELECT EMPNO, ROWNUM RN 4 FROM SCOTT.EMP T ORDER BY ROWNUM
$ srvctl add service -d dc1ext -s addval.dc1 -r "dc1ext1" -a "dc1ext2" -P BASIC $ srvctl start service -d dc1ext -s addval.dc1
begin DBMS_SERVICE.MODIFY_SERVICE( service_name=>'addval.dc1', aq_ha_notifications => true, failover_method=>DBMS_SERVICE.FAILOVER_METHOD_BASIC, [code]......
my attempt at verifying the TAF is working.
SQL> run 1 SELECT MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER, COUNT(*) 2 FROM GV$SESSION 3* GROUP BY MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER
MACHINE FAILOVER_TYPE FAILOVER_M FAI COUNT(*) ---------------------------------------------------------------- ------------- ---------- --- ---------- anetapp02.dc2 NONE NONE NO 10 anetapp01.dc2 NONE NONE NO 10 app02.st2 NONE NONE NO 5 racdb02 NONE NONE NO 44 anetapp03 NONE NONE NO 10 anetapp04 NONE NONE NO 10 GNOME\APTSEA-6GKJHF1 SELECT BASIC NO 3 app01.st2 NONE NONE NO 5 anetapp05 NONE NONE NO 10 anetapp01 NONE NONE NO 10 anetapp03 NONE NONE NO 10
so it looks as thought the sessions are failing over, but my app guy says he get a few errors during the process.. i need it setup to move the current query over without returning an error.. do i not have this setup properly?
i have created a tree hierarchy....when the user clicks on any node,that particular form will open....But there is some problem...i click on form A,it opens.,...then i exit that form via a exit button on the toolbar....and then I click on form B on the tree....but first the form A only opens...and then again i exit form A, then form B will open..
i have written the following code in when_tree_node_selected trigger :
if condition then call_form(path,no_hide,do_replace);
I have a query with FULL hint that is behaving in a strange manner. The query fetches around 700000 of data. Sometimes it fetches the data with the hint and sometimes it does not fetch any data with the hint and then I have to remove the hint and have to fetch the data. Below is the query,
I applied the Jan 09 CPU to my database this past Saturday and now my daily schema export is not running properly:
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport done in WE8MSWIN1252 character set and UTF8 NCHAR character set server uses UTF8 character set (possible charset conversion)
About to export specified users ... . exporting pre-schema procedural objects and actions . exporting foreign function library names for user TEST . exporting PUBLIC type synonyms . exporting private type synonyms
Every time I hit up, down, left, and right arrow keys to return to a previous sql command I got ^[[D, fix this issue? The backspace works fine with me.
I created one Form manually. how to Drag and drop from items in Data Blocks to Canvas. when I created text in Canvas it was automatically creating the item in Data block. but when i create the items in Data blocks and then trying to copy to canvas.
DECLARE TYPE SUBNO_TABLE_TYPE IS TABLE OF TRANS_DEICMAIN1.SUBNO%TYPE INDEX BY BINARY_INTEGER; SUBNO_TABLE SUBNO_TABLE_TYPE; K NUMBER := 1; S NUMBER := 1; [code]...
HERE CHECK1 IS A CHECKBOX.WHEN I AM USING THIS CODE ONLY ONE RECORD IS SAVED AT A TIME INSTEAD OF SAVING ALL RECORDS.
I have a form in which menu tree is populated with this sql
SELECT 1, Level, MenuLabel, MenuName, MenuId FROM MenuFile CONNECT BY PRIOR MenuId = ParentId START WITH menuId = :moduleid||'0000'
The result is attached in the CSV file enclosed in sheet mrnufile1As we can see in row 3 the item LPC has menuid BDME13. In my view This item should be in ROW 19.How can i change query do get the desired the result.I have also tried another sql query.
select * from menufile where applicationid = 'BD' order by parentid,menuid
I am using Apex 4.2, The Interactive report Search is not working properly, when i am entering complete date or time it is not showing any results...how to increase performance of the search...
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 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).
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?
I have requirement to create materialized views. The design states to use Complete refresh. Now I am using WITH ROW_ID clause.
1. Question - what are the criteria to decide between WITH PRIMARY KEY & WITH ROW_ID clauses? I referred oracle doc, but couldn't exactly get this.
2. Background - The oracle doc mentions that - Primary key materialized views are the default type of materialized view. The master table must contain an enabled primary key constraint, and the defining query of the materialized view must specify all of the primary key columns directly.
Question - I saw some existing materialized views in my project using WITH PRIMARY KEY clause, but all of the primary key columns are not part of the Select query, but still these views are working fine, how is this possible?
3. Background - The oracle doc mentions that - Rowid materialized views must be based on a single table and cannot contain any of the following:
■Distinct or aggregate functions ■GROUP BY or CONNECT BY clauses ■Subqueries ■Joins ■Set operations
Question - But I have created a Rowid materialized view selecting data from more than one table & defining query involving outer joins. This materialized view got created & getting refreshed (complete refresh). But again this is contradicting with Oracle' statement.
I have a trigger to automate grants to a Role whenever a table is created in my schema.I have dba rights.
CREATE OR REPLACE PROCEDURE deepa.myddl (p_ddl IN VARCHAR2) IS BEGIN EXECUTE IMMEDIATE p_ddl;
[Code].....
This works perfectly when i create a table as deepa user. But say when another User creates a table in my schema
like create table deepa.test1 ( a number) This trigger is not fired. Whenever a table is created by me or by any other user in my Schema, i need the trigger to be fired.
There are 2 databases, database A and database B. Database A is Oracle 11.2.0.2 which runs on linux and Database B is Oracle 11.2.0.2 which runs on windows xp machine. In database A, there are 100's of tables which are being updated every 10 minutes or 15 minutes. For reporting purpose, the developer wants to run report for the tables. But since database A is being updated every now and then, generating reports takes almost 15 to 20 minutes. So the reports can be generated in Database B. Once in a day the database B should have the updated data from database A so that the reports can be generated in database B with less time. What could be the best solution for the database B to have the updated data on daily basis from database A in oracle?
i have created FAST refresh materialized view.it is eligible for FAST refresh because when i executed SELECT * FROM USER_MVIEWS i could see FAST_REFRESHABLE=DIRLOAD_DML and STALENESS=STALE and it is not getting refreshed(through DBA_JOBS). Why it is in STALE status and how to resolve it. and one of my materialized views is in NEEDS_COMPILE status
A scheduler job is there to refresh some materialized views. All the views are run under single job name. Some mviews are not refreshing and those are not refreshing in the next run too.if we run those mviews manually then they are working fine from then . after some days those views which i told before are not refreshing and this happens frequently and i check the job_run_details views also and i am not seeing any errors in that.