We help IT Professionals succeed at work.

How to understand that an Oracle table is being written?

252 Views
Last Modified: 2014-12-02
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
Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Alex [***Alex140181***]Software Developer
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Yes I run some scheduled scripts, but sometimes the same script overlaps because the previous one takes longer than as usual.
Alex [***Alex140181***]Software Developer
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
This is good feedback, however tasks are being scheduled by 3rd party program in Windows.

Author

Commented:
If you provide me a PL/SQL checking whether a table is being written it is enough.
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
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.

Author

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

Author

Commented:
I'm using append in SQLLOADER.
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.