Solved

How to understand that an Oracle table is being written?

Posted on 2014-12-02
13
180 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 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 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 13

Assisted Solution

by:Alexander Eßer [Alex140181]
Alexander Eßer [Alex140181] earned 167 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 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 13

Assisted Solution

by:Alexander Eßer [Alex140181]
Alexander Eßer [Alex140181] earned 167 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 34

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 73

Assisted Solution

by:sdstuber
sdstuber earned 83 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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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 how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

816 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now