Solved

How to understand that an Oracle table is being written?

Posted on 2014-12-02
13
173 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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

744 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