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.
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.
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.
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.
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.
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.
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).
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?
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?
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.
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:
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).
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')
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].....
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.
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)
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,
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
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.
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.
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';
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 * 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 ?
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.
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.
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.
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;
