Users are unable to see DML Changes after commit.

Thanks for you guys quick reply:
My friends here is a little clue I am able to find through internet surfing it may or may not be the issue I'll check when I'll return to work on Monday.

Sync (On Commit) new in Oracle 10g
Creating an index with this option might look like this:
  create index my_index on my_table (text_col)
  indextype is ctxsys.context
  parameters ('lexer my_lexer sync (on commit)');

This means that a sync is launched immediately after the commit occurs. The way this works is slightly unusual, in that the SQL call to commit will not return until the sync is complete, and yet the sync is done in a separate transaction from the main commit.
So from the point of view of the user calling the commit, the index will appear to be updated as soon as the commit completes. However, another user might see a period when the rows have been committed to the table, but the index has not yet been updated. The period during which this occurs will depend on the amount of data to be indexed - that is, how long the sync takes. Using Sync (On Commit) can be bad for index performance if small numbers of rows are committed at a time. On the other hand, if the commit is only called every few thousand rows, this might be a reasonable option.

You comments on this are appreciated.

Thanks again,
Zee

May be you need to monitor the DB activities for a while , or may be rubn a trace for a certain user's transactions  that doens't see the data as you see , and see what's happening in the backend ...

since that sounds illogical to me too !

Your question was about DML activity (which includes: insert, update and delete statements) but the example you found describes a particular type of DDL statement ("create index...").  DDL statement and DML statements are two different things in Oracle.  DDL statements are automatically commtted immediately.  DML statements are not.  I don't think that particular example that you found explains what your users see.

It is possible (and common in some applications) for a user to make a number of changes (inserts, updates and/or deletes) but not commit them immediately.  The user who made the changes will see them all, but no one else does until the user (or the application) issues a "commit".  At that point everyone can see them *IF* they repeat a query or start a report *AFTER* the commit.  If they had a query screen open though with data displayed, or started a report before the "commit", they will *NOT* see the latest DML changes from other users that are not committed.

My Friend Markqeer,

I guess you did concentrate on the final comment it tells u that the delays may occur due to this indexing . I am quoting the point of intrest three sentences here read it carefully please:

"So from the point of view of the user calling the commit, the index will appear to be updated as soon as the commit completes. However, another user might see a period when the rows have been committed to the table, but the index has not yet been updated."

SO it may be the above case with me but as I said the backend DB user is SAME. Even in above mention sentence the period of delay may occur if the DB user are different.

I just qouted it hear to pop another stream of ideas for brainstorming. I agree that example in text is a DDL but it is impacting a DML operation and this is the point to be noted here.

Thanks and regards,
Zee

Please tell us:
1. Does your application include Oracle Text indexes, or not?
2. Does your application allow users to make multiple changes before doing a commit, or not?

My Friends,

Q1: Does your application include Oracle Text indexes, or not?
No, It doesn't
Q2: Does your application allow users to make multiple changes before doing a commit, or not?
Yes, But the issue is that some application users not database users can't see data DB user is same for all application users.

Then I login with a different Application user which is application super user run the same report it shows me data and relogin with the who wasn't seeing data and it start seeing it. Again on this option or report the rights in application are same for super user and normal user.

I don't know what to check in the database. I came back after a long time in development and DBA side, means I working as a Project Manager then I relocate and got this job as interim solution. So if you guy could be a little more elabrate on answer i'll appreciate that.

Thanks and Regards,
Zee

if you are not using Oracle Text then sync on commit has no relevence. In any case as you are saying that the backend database user is same for all users, it would nots t have any impact.

The only reason that i can think of for this strange behaviour is application logic. One reason can be that the reports that you are running are based on some summary views (either application defined or may be materialized views). These views are not getting updated on commit. They are getting updated either at specific time or at specific event. Specific event can be Application Super User running the report. Once the views are updated you can see the data.

I would discuss the problem with Application design team or application vendor to understand how these reports are working

HTH
Vishal

Hi Friends,

Thank you very much for your kind opinions and suggestions. And I am sorry again for not coming back to you guys for a while. I was working on this issue and I agreed with vishal that it was a application logic issue or rather logical error.

Actually we have two triggers written on login form key-next-item and when-mouse-click to proceed after entering data mean id & password. I know it sound silly but one of the triggers has a hard coded last year user and password so when user enter then he/shoe login into the currrent year user and when user click he/she enters into last year user so this was the bug in the code.

Which again justify Vishal's opinion of checking application logic. but as a matter of knowledge I am considering to use materialized views for one of my remote site can you guy comment on it. The site will be read only web users only see day end data and for that export and import data delete table and so on which is not a good practice in my opinion. I wana use materialized views replication using script for this purpose.

An other issue we were using & in our report data model/queries for making it generic now e.g.
Select a,b,c
From &table
where column in(&list);
it was working fine up until we migrate to Oracle 10g R2 and just to remind u guys we have 6i as a development environment.

Can u guy give comments on our we can resolve this issue since it says invalid Identifier now on all of the report. We have solve this issue partially like we pass parameter from calling form for a particilar value or if we wana run it  on all data we pass null and in report we usse col1=nvl(particulatvalue,col1) so it partially resolve the issue.

We have two other senarios

1) We are Stock Brokrage House and we have different type of house clearing processess about 5 types. User may run reports on 1 type, 2 types, 3 types or all types like in('ATYPE','BTYPE' .....) which is not resolved with nvl type of solution.

2) At from level user can run report from current transaction, history table or both means combine and select the desired option in parmeter form (not report builder but our own customize para form) for this we need a variable at from level &table.

Or if it is possible to change the query in before report trigger like query='select ....'

Please advice.
Thanks and Regards,
Zee

I am not an expert on developer 6i. But i do know that developer 6i has issues working with 10g DB. Oracle certifies only developer 6i with patch set 17 to work with 10g db. Please ensure that you are using developer 6i with patch set 17 that is report version 6.0.8.26.0

HTH
Vishal