Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to understand that an Oracle table is being written?

Posted on 2014-12-02
13
Medium Priority
?
196 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
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.
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

715 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