Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to understand that an Oracle table is being written?

Posted on 2014-12-02
13
Medium Priority
?
198 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
0
Comment
Question by:GurcanK
  • 6
  • 3
  • 2
  • +2
13 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 40475525
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
 
LVL 14

Assisted Solution

by:Alexander Eßer [Alex140181]
Alexander Eßer [Alex140181] earned 668 total points
ID: 40475543
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
 

Author Comment

by:GurcanK
ID: 40475559
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 40475588
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
 

Author Comment

by:GurcanK
ID: 40475591
Yes I run some scheduled scripts, but sometimes the same script overlaps because the previous one takes longer than as usual.
0
 
LVL 14

Assisted Solution

by:Alexander Eßer [Alex140181]
Alexander Eßer [Alex140181] earned 668 total points
ID: 40475599
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
 

Author Comment

by:GurcanK
ID: 40475608
This is good feedback, however tasks are being scheduled by 3rd party program in Windows.
0
 

Author Comment

by:GurcanK
ID: 40475688
If you provide me a PL/SQL checking whether a table is being written it is enough.
0
 
LVL 35

Expert Comment

by:johnsone
ID: 40475705
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
 

Author Comment

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

Author Comment

by:GurcanK
ID: 40475721
I'm using append in SQLLOADER.
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 332 total points
ID: 40475749
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 40475762
>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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The password reset disk is often mentioned as the best solution to deal with the lost Windows password problem. In Windows 2008, 7, Vista and XP, a password reset disk can be easily created. But besides Windows 7/Vista/XP, Windows Server 2008 and ot…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question