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
GurcanKAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>however tasks are being scheduled by 3rd party program in Windows.
then either :
* that tool should be able to avoid parallel runs
* you change the tool
* you adapt the script itself to avoid parallel runs
what script language are you using?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
well, sort of, check out this asktom's article:
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:454580500346047540

however, this is not really efficient.
note that normally INSERT statements should be committed very fast, so I wonder what process you are doing where you need such a check?
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
You could also check the built-in dynamic view V$TRANSACTION (or GV$TRANSACTION when using RAC) and you might join it to V$SESSION to decode the COMMAND column (in order to get the driving statement)... Well, in general, as always it depends on what you are trying to archive?!?
You also could work with FGA and/or DML triggers...
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
GurcanKAuthor 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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you should handle that from externally, using semaphore on the OS level, for example
I do presume that you are using some scripts to actually run the sql loader?
0
 
GurcanKAuthor Commented:
Yes I run some scheduled scripts, but sometimes the same script overlaps because the previous one takes longer than as usual.
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
but sometimes the same script overlaps because the previous one takes longer than as usual.

If you're running the scripts via cron (Linux) for instance, you might want to "sync" them with the help of a lock file as shown here (there are numerous samples out there):
http://stackoverflow.com/questions/11178857/make-cron-job-wait-till-another-job-finished
0
 
GurcanKAuthor Commented:
This is good feedback, however tasks are being scheduled by 3rd party program in Windows.
0
 
GurcanKAuthor Commented:
If you provide me a PL/SQL checking whether a table is being written it is enough.
0
 
johnsoneSenior Oracle DBACommented:
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.
0
 
GurcanKAuthor Commented:
The table will only be written by SQL*Loader. No other process is going to run, make sure.
0
 
GurcanKAuthor Commented:
I'm using append in SQLLOADER.
0
 
sdstuberCommented:
you could try NOT using sqlloader but external tables instead and oracle scheduler with small pl/sql procedures to do the data loads.

Those procedures could call dbms_lock to ensure only one is running at a time.

Putting all of that logic into a package would help self-document the dependencies too.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.