Link to home
Start Free TrialLog in
Avatar of GurcanK
GurcanK

asked on

How to understand that an Oracle table is being written?

Dear Experts,

I'm writing an PL/SQL Procedure and I need to check whether a table is currently being written or not. By being written, I mean Insert operation. But please take care, I do not want to check the locking status of table; rather I would like to check whether one or more uncommitted Insert operations exist on specific table.

BR
SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of GurcanK
GurcanK

ASKER

INSERT is being done thru SQLLOADER. I do not want simultaneous write of more than one SQLLOADER to write to the same table, after coming SQLLOADER  should wait for the previous one to complete.

Best Regards
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of GurcanK

ASKER

Yes I run some scheduled scripts, but sometimes the same script overlaps because the previous one takes longer than as usual.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of GurcanK

ASKER

This is good feedback, however tasks are being scheduled by 3rd party program in Windows.
Avatar of GurcanK

ASKER

If you provide me a PL/SQL checking whether a table is being written it is enough.
How would you distinguish between SQL*Loader writing to the table versus a regular user writing to the table?  As far as the database is concerned, I don't think it can tell the difference.

Also, if you are doing an APPEND in SQL*Loader, I'm not sure that any locking scenario or transaction information would show that information.  No locks are required because the writing is being done above the high water mark.
Avatar of GurcanK

ASKER

The table will only be written by SQL*Loader. No other process is going to run, make sure.
Avatar of GurcanK

ASKER

I'm using append in SQLLOADER.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial