Record Deletes Not Being Replicated?

Jul 23, 2010

I have materialized view replication setup in an Oracle 10g environment. Inserts and Updates are being propogated as expected. When a record in the master table is deleted, there are no entries being written to the materialized view logs, and hence, the delete is not propogated to the materialized view on the remote server.

The Materialized View is defined with FAST Refresh and the refesh occurs avery 5 minutes. This allows me to see what entries are written to the mv logs before they are consumed. I thought that all DML statements get written to the mv logs and am lost to explain this behavior. The matser table definition is not very complex. It has a PK defined and two UK Constaints but does not even have any FK constraints defined. There are a couple of triggers defined on this table for Insert, Update and Delete which write audit table records but I can't see how this could affect things. There are no errors being generated on the master table side in the DB or that the application sees.

View 5 Replies


ADVERTISEMENT

Bulk Deletes Of Data From Huge Table

Jun 13, 2013

I am trying to delete 3 million records of data from huge table which already consists of 3 billion records.

This is hitting performance of DB and halting other activities of my users. Is there any easy way to delete such data fast. I have tried with forall delete but it is even taking lot of time.

View 5 Replies View Related

PL/SQL :: Track Table Data Changes (inserts / Updates / Deletes) Including Os_user

Mar 12, 2013

I was given a task by manager to keep track of changes on a given table including os_user who made it.Should I create a trigger on it (on any update, insert, delete etc.) or there is a better way of doing it ?I think there could be some info already in some data dictionary views or something like it.

If I CREATE MATERIALIZED VIEW LOG on that table.

View 4 Replies View Related

Replication :: Oracle 8i - Web Application Working With Replicated Database

Feb 26, 2008

I'm looking for solutions to make real time replications from an Oracle 8i database.

For more details :
- we have an existing database with Oracle 8i
- we want to replicate this database: the replicated database will be used by a web application.
- we need the replication to be in real-time (we wish to have the shortest lag)

For the moment i didn't find much information on the web and it seems that replication solutions for the version 8i are quiet limited.

Some more details :

- The responsible want imperatively that the web application work with a replicated database, without interacting directly with the original one.
- An evolution of oracle should be done but within some years so were trying to find a solution rapidly with 8i.

View 3 Replies View Related

Replication :: Two Schemas To Be Moved / Replicated To New Reporting Database

Oct 19, 2011

I would like to know the Replication method which is fast and the best approach,we need two schemas to be moved/replicated to a new reporting database.It appears that data is to be flown in one way,do we proceed with Materialized view replication or please clarify about Oracle Streams and Advanced replication. what are the factors to decide the replication method.

View 3 Replies View Related

Data Guard :: Maintain Replicated Copy Of Database In Remote Office

Feb 18, 2011

I was considering a solution to maintain a replicated copy of a database in a remote office. However we are using SE One edition of oracle, so native support for dataguard is not available. There definitely should be some scripting solutions for this task, but I can't find any to date.

View 7 Replies View Related

Forms :: Populating Values To Record Group From Multi Record Datablock

Jul 26, 2012

My procedure proc_ex is in when_validate_item trigger

I have one Multi Record data block in my form with values in its items

I need to Populate multi record block values to one Record Group using

add_group_row,
add_group_column,
set_group_char_cell to populate values to record group

Let us suppose my multi record data block looks like

item1 item2 item3 item4
10 20 50 70
25 15 30 45
45 90 47 38
75 25 85 90
30 56 78 80

how to populate these multi record datablock values to Record Group ???..Eagerly waiting for your Replies

View 3 Replies View Related

Server Utilities :: How To Associate Header Record To Its Corresponding Detail Record

May 16, 2012

I am loading data file using SQL Loader in TOAD 10.5.1.3 in Oracle 10g using the control file below and loading data into 2 tables post1.thead and post1.tdetl. THEAD contains item level transaction and TDETL is detail level when a transaction has a discount or promo attached to for that item. When the

LOAD DATA
APPEND
INTO TABLE post1.thead
WHEN (1:5) = 'THEAD'
TRAILING NULLCOLS
(file_type POSITION(1:5)CHAR,

[code]....

A particular THEAD value may have 0, 1 or many TDETL corresponding values.below is a sample data file. When the position 21 in the TTAIL has a value of 1 or 2, then we know that there is a promo or discount applicable to the ITEM (THEAD).

THEAD0000000002201109142011091400000000000002091 1 0 -1
TTAIL0000000003000000
THEAD0000000012201109142011091400000000000002091 1 0 -1 20110914-1
TDETL0000000013CMPSPL1004 0 000000010000P0000000000000019990000000000000000200000
TTAIL0000000014000001
THEAD0000000021201109142011091400000000000002091 1 0 -1
TDETL0000000022EMPDSC1005 0 000
TDETL0000000023SCOUP 1006 0 973452 000
TTAIL0000000024000002

What I want to acheive is to accurately reflect a TDETL to its corresponding THEAD, as both THEAD and TDETL are loaded into separate tables. How can we have the 2 records correlated?

View 11 Replies View Related

PL/SQL :: Selecting Single Record From Multiple Record Based On Date?

Aug 26, 2013

I have a table which contains the multiple records for single ID No. Now i have to select single record which contains the latest date. here is the structure Name  

Null Type  ------ ---- ------------ ID_P        NUMBER       NAME_P      VARCHAR2(12) DATE_P      TIMESTAMP(6) Records---------------------1 loosi     22-AUG-13 01.27.48.000000000 PM1 nammi  26-AUG-13 01.28.10.000000000 PM2 kk        22-AUG-13 01.28.26.000000000 PM2 thej      26-AUG-13 01.28.42.000000000 PM 

now i have to select below 2 rows how can write select qurie for this?

1 loosi 26-AUG-13 01.27.48.000000000 PM2 thej  26-AUG-13 01.28.42.000000000 PM

View 4 Replies View Related

Forms ::create History Record For Each Record Whether Updated Or Not

Sep 13, 2011

I have a fairly standard Purchase Order form which contains pre-loaded data (been uploaded from an XML file).When the Purchase Order is processed, the form updates a Price History table only if the Price on the PO_Details changes.The code for updating the price history table is contained in a PRE_UPDATE trigger on the PO_Details Data Block.

No other data changes on the PO_Details table.I now want to change this so that the Price History table is updated even if the price does not change i.e I want to create a history record for each record on the PO_Details irrespective of whether it was updated or not.

Is there an alternative trigger that I can move my code to (ie move it from PRE_UPDATE) to some other trigger that is fired for each PO_Details record even if there is no change.

View 4 Replies View Related

SQL & PL/SQL :: Create A Query That Returns Record By Record A Field

Apr 21, 2010

I have the following case to solve:

Example Table:

Nr_ordPos1Pos2Itemqty
O4018510000107 170,00
O4018520000107 30,00
O40651010000107 500,00
O40651020000107 50,00
O4114510000107 300,00
O31141010000107 50,00
O3114520000107 50,00

I need to create a query that returns record by record a field qty_progr with the cumulate qty considering previous records. The result should be the following:

Nr_ordPos1Pos2Itemqty qty_progr
O4018510000107 170,00 170,00
O4018520000107 30,00 200,00
O40651010000107 500,00 700,00
O40651020000107 50,00 750,00
O4114510000107 300,00 1050,00
O31141010000107 50,00 1100,00
O3114520000107 50,00 1150,00

View 8 Replies View Related

Forms :: Only New Record Have Saved Last Record No Longer Exist

Jun 29, 2013

I create a data block on a table when I am inserting new record only one record have been saved. Last record no longer exist.

View 4 Replies View Related

Forms :: Choose Lov In First Record That Won't Be Display In Second Record Lov

Sep 10, 2012

I have one multi record block in that i have one LOV..If i choose lov in first record that won't be display in second record lov...

View 4 Replies View Related

PL/SQL :: Code Erases Previous Month Record While Updating Current Month Record

May 16, 2013

Using 11gR2, windows 7 client machine. I need to update the table missing_volume (below), where I need to calculate the estimated_missing column. The calculation of estimated_missing column for current month needs previous month numbers (as commented inside the code below). I want the output like the first table. Notice the records start from January, hence estimated_missing for January can't be calculated, but for the the rest of the months it can be done by simply changing 'yr' and 'mnth' (commented inside the code towards the end).

yr          mnth          location     volume          actual_missing          expected_missing     estimated_missing
---------------------------------------------------------------------------------------------------------------------------------
2013            January          loc1          48037          24               57                         
2013             February     loc1          47960          3660               53                      24
2013             March          loc1          55007          78               57                      28
2013             April          loc1          54345          72               58                  77The code:

UPDATE missing_volume g

[Code]....

The code does calculate correct number for 'estimated_missing' as I run the code for each month, but the problem is while updating the current month it also erases the record for previous month. E.g. as can be seen below, after I updated April the column only has the record for April, previous month record is gone, similarly updating March removed February, etc. I can't understand why it's happening!! Here is the output I get:

yr          mnth          location     volume          actual_missing          expected_missing     estimated_missing
---------------------------------------------------------------------------------------------------------------------------------
2013            January          loc1          48037          24               57                         
2013             February     loc1          47960          3660               53
2013             March          loc1          55007          78               57
2013             April          loc1          54345          72               58                   77

why it's happening (I mean where is the flaw in the code) and how to get the desired output (first table).

View 5 Replies View Related

SQL & PL/SQL :: Record Between Seconds

Nov 9, 2011

I have a date column in a table, I need to fetch records from 27:11:11 00:00:10 to 30:11:11 00:00:10 (DD:MM:YY HH:MI:SS). i.e between seconds/min/hrs.

expecting Something like this,

SELECT * FROM TABLENAME WHERE MODEDATE BETWEEN
TO_CHAR('27/11/11 00:00:10','DD-MN-YY HH24:MI:SS') AND
TO_CHAR('30/11/11 01:10:10','DD-MN-YY HH24:MI:SS')

View 6 Replies View Related

SQL & PL/SQL :: How To Get Last Month Record

Jun 10, 2010

i want to get the last month starting from today onwards.

1) select the record of last month that is if this month is june then select the record for 1st may to 31st of may

View 7 Replies View Related

SQL & PL/SQL :: Going To Next Record In Loop

May 25, 2011

I have a cursor which I am opening and then looping through. Within this loop I am comparing attributes within this cursor with attributes from another loop that this one is within.

you will see in the IF statements (there are several distinct IF statements within the loop) that there is a check which assesses if the attributes are not equal.

If they are NOT, the value of v_mismatch is set to 1

What I need to do instead of setting this to 1, is to go to the next record in the loop. How can I achieve this?

OPEN c_distMatrix;
LOOP
FETCH c_distMatrix INTO r_dist;
EXIT
WHEN c_distMatrix%NOTFOUND;
--compare each field and update the counter
[code].....

View 5 Replies View Related

SQL & PL/SQL :: Select Record As Specified

May 30, 2012

Based on ACCOUNT_NUMBER column we have to check the GUID data in test table.

for example ACCOUNT_NUMBER =11 as duplicate it has 2 values then only, we need to check the corresponding GUID if any occurence ("9f680174-cb87-4f71-887a-92" and "9f680174-cb87-4f71-887a-91"),
we should select,if not leave it.

CREATE TABLE test
(
GUID VARCHAR2(32 BYTE),
ACCOUNT_NUMBER NUMBER(30),
INDIVIDUAL_ID NUMBER(13)
)

[Code]...

it tried as like as below it's working.

select t2.* from (select distinct t1.guid
from test t1,( select ACCOUNT_NUMBER from test
group by ACCOUNT_NUMBER having count(ACCOUNT_NUMBER

[Code]...

View 15 Replies View Related

PL/SQL :: Last Inserted Record

Aug 7, 2012

I need select last added/inserted records where value = 'test1' and = 'test2' and = 'test3' in column 'NAME' should be one record for each 'test1','test2' ...

aslo..table have column 'DATE_USR' with data_type = timestamp(6)

View 12 Replies View Related

PL/SQL :: How To Get The Record Before Updated

Sep 3, 2012

I have a problem where my record is updated yesterday. How to get the record before updated?

View 4 Replies View Related

PL/SQL :: LAST Record In A Loop?

Aug 27, 2012

in my loop with cursor in a procedure body i am displaying some field values to create a report and after every record i am displaying horizontal line(-------) but i don t want this line to appear after the last record displayed like below,

gfsfsf gsgfsfds gsdgfdg
------------------------------------
edyet gdgtdgt gtdfdfdgd
------------------------------------
dfds hedhgg idudhdh

how can i achieve this within pls/sql procedure body.

View 20 Replies View Related

Limiting Subquery To First Record?

Oct 1, 2007

restricting a subquery's results to the first record. If I use the following:

SELECT WU.DISCREP, M.PART, M.ETA
FROM DB.MICAP M
LEFT JOIN DB.WRITEUPS WU ON M.WRITEUPID=WU.WRITEUPID
LEFT JOIN DB.WUC WUC ON WU.WUCID=WUC.WUCID
WHERE (WU.AIRCRAFTID=205) AND (WU.CORRECTED=0)
ORDER BY WU.PACER DESC, WUC.WUCCODE

I get

-------------------------------------------------------
#5 GEN KICKED OFFLINECABLE02-MAR-08
#5 IDG HARNESS REQUIRES WIREWIRE24-MAY-08
#1 GEN WENT OFFLINECABLE02-MAR-08
AERIAL REFUEL DOORS W/N OPENLINEAR DEL29-SEP-07
AERIAL REFUEL DOORS W/N OPENCHECK VLV30-SEP-07
WEATHER RADAR TILT KNOB HASRADAR COMP05-OCT-07

But I need the highlighted line eliminated. I've tried DISTINCT subqueries in the WHERE M.WRITEUPID IN (SELECT DISTINCT... various experiments with joins, etc.

View 4 Replies View Related

Pulling One Record For Each Page?

Dec 1, 2009

I have an alerts system for a web site built in an Oracle DB. There are multiple alerts that can be assigned per page, but I want to display only the most recent. I have 5 pages but each page can have different alerts.

I have a parent table of all the alerts and a child table with the page assignments. I want to pull one record (the newest) for each page assignment. The pages can increase so I can't hardcode this.

I keep thinking I need to do something like:

SELECTalertID, headline
FROMalerts_V
WHEREalertID IN (
SELECTdeptID
FROMso_departments
)

But it's obviously not quite what I need and not the correct syntax.

View 5 Replies View Related

Unable To Select Only Record For 07 Nov 06

Nov 13, 2006

I am unable to select only record for 07Nov06. if i use between i get records e.g. :07 November ,2006-0941

Select
TO_CHAR(session_START_time,'dd month,yyyy-hh24mi') LOG_IN_TIME,
TO_CHAR(session_END_time,'dd month,yyyy-hh24mi') LOG_OUT_TIME
from SESSION_LOG
where SESSION_LOG.SESSION_START_TIME
between '06Nov06' AND '07Nov06';

View 2 Replies View Related

Getting Different Record Counts When Select Overall?

Nov 7, 2011

I have a UNION query having 3 parts, 1st gets date, 2nd data and 3rd displays the formatted data count :WHERE clause of 2nd and 3rd queries are same.

Problem is that I an getting different record counts when I select the overall count of records given by the whole UNION query and when I run to count the records given by each query individually.First count. Here I am selecting the overall count of records given by the query :

select count(1)
from (
SELECT SUBSTR ( '0'
|| TO_CHAR (SYSDATE, 'MM/DD/YYYY')
|| TO_CHAR (SYSDATE, 'HH:MI:SS')
|| LPAD (' ', 180)

[code]...

This count is : 1751525 Second count. Now when I run to count the records given by each query individually, here is the result

select count(1) a
from (
SELECT SUBSTR ( '0'|| TO_CHAR (SYSDATE, 'MM/DD/YYYY') || TO_CHAR (SYSDATE, 'HH:MI:SS')
|| LPAD (' ', 180)|| chr(13), 1, 200 ) dtl_record from dual
select count(1) b from (
SELECT '1'

[code]...

why there is difference of 1 (1751526 - 1751526) in the count results.

View 1 Replies View Related

SQL & PL/SQL :: Display Some Value When There Is No Record In Table

Jul 8, 2010

I have a following table,

create table test ( col1 date,col2 number);

Data:

col1 Col2
----------
NULL NULL

select * from test where col2=123 will give me null because It doesnt have any values, But can we display some harcoded value when I do not have anything ?

View 9 Replies View Related

Forms :: Do You Want To Save Record

Aug 22, 2005

I have created a form which as two block as master and detail.I am facing two problem in my detail block.

First: When I populate records in detail block, it prompt a message 'Do you want to save record ?'.
Second : When I alter any value in detail block and move to next record, it prompt a message 'Do you want to save record ?'

Is there any way where, system don't prompt me a message for saving record and user can continue with changes and save the records when he desire.

View 16 Replies View Related

Forms :: Record Is Getting Saved Twice

Mar 8, 2012

My record is getting saved twice every time I push my button to commit changes to my DB.I have already records from my table and when I click for that record to update the grade. the same record is getting saved twice.I have put my queries in on-update trigger to update the grade of my record. I check my query, don't have errors. I don't know what's wrong with this.

View 24 Replies View Related

Forms :: Each Second Record With Different Background_color

Jul 9, 2004

To improve legibility, I would like to display always the second row with another (lets say green) background_color. Therefore I created a BLOCK - Post-Query Trigger containing the following lines:

--IF (:EMP.COMM IS NULL) THEN
IF (MOD (TO_NUMBER(:SYSTEM.CURSOR_RECORD), 2) = 0 ) THEN
SET_ITEM_INSTANCE_PROPERTY('EMP.ENAME', CURRENT_RECORD, VISUAL_ATTRIBUTE ,'GREEN_BACKGROUND');
END IF;

NULL;

My first problem: I do not know how to the determine the whole record, therefore I just used EMP.ENAME

Second problem: The green background starts at record number 7 (if the first record is number 1, (is this so, or does it start with zero ??)), and then number 9, 11, 13 (from scott.emp) has a green background-color.

View 2 Replies View Related

SQL & PL/SQL :: Checking Whether Record Exists

Jan 22, 2013

Which is the best way to check whether a record exists.

DECLARE
l_count NUMBER;
BEGIN
SELECT COUNT(*) INTO l_count FROM emp WHERE empno=7839;
IF l_count=1 THEN
dbms_output.put_line('exists');
ELSE
dbms_output.put_line('not exists');
END IF;
END;

[Code]...

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved