11,877 reputation
11832
bio website ddbcinc.com
location Rochester Hills, MI
age 37
visits member for 3 years, 6 months
seen 7 hours ago
Oracle ACE

15h
comment different outcome based on function execution method
@Andrew - If there was one feature that I could remove, or at least require an extensive examination before it was used, it would be autonomous transactions. They are very useful in a couple of very specific situations. But they are giant "manual override" lets you overrule Oracle when it attempts to stop you from doing something stupid. And the vast majority of people using them are not smarter than the database.
18h
comment different outcome based on function execution method
@Andrew - When I see an autonomous transaction, I read that as "Please note that this code is about to do something incredibly stupid that will have a large number of poorly understood side effects". If you are doing anything other than writing to a log that you want to persist the logging if the parent transaction fails, you're doing something wrong. Among other things, yes, you'd expect to get a metric crud load of deadlocks if you have autonomous transactions all over the place.
1d
answered different outcome based on function execution method
Jul
16
comment Dynamic permissions
A DDL statement implicitly commits before the DDL statement starts. The DDL statement runs. As part of that transaction, the trigger fires. Once all the triggers fire, the DDL statement commits (and is rolled back if the trigger were to throw an exception). The trigger is part of the transaction in which the actual DDL is run. That's why you cannot do DDL (or use dbms_scheduler) from within the trigger.
Jul
16
comment Dynamic permissions
dbms_job participates quite happily in the underlying transaction. If, somehow, the DDL transaction failed (I can't think of a way that it would fail after the trigger was fired but let's assume that it would) then the dbms_job.submit would be rolled back as well. A dbms_scheduler job would not be rolled back which is why you cannot in general use dbms_scheduler in triggers.
Jul
16
comment Dynamic permissions
dbms_job is certainly not as powerful as dbms_scheduler in general. I don't know of a way, however, to use dbms_scheduler in this case because submitting a dbms_scheduler job causes a commit and you can't have a commit in a trigger. You wouldn't want the commit in the trigger because you don't want the job to be executed until just after the transaction that submitted it closed.
Jul
16
answered Dynamic permissions
Jul
11
comment Oracle average active sessions high threshold
@toddlermenot - Sure, if your sessions are mostly waiting on I/O, you can probably tolerate a larger number of average active sessions without adding wait time due to scheduler contention. But you still have, say, 7 sessions simultaneously competing for I/O requests to be serviced which tends to impact the throughput available to each session.
Jul
11
answered Oracle average active sessions high threshold
Jun
25
revised Row Level security or Multiple Tables
added 1361 characters in body
Jun
24
revised Block user access to certain tables
Since you link to the MySQL documentation, I've added the MySQL tag. Different databases will have different features
Jun
24
revised Row Level security or Multiple Tables
edited body
Jun
24
answered Row Level security or Multiple Tables
Jun
4
answered Oracle structural synchronization
May
27
answered How to reuse code in Oracle Script?
May
27
answered Invalid create index syntax
May
22
answered Alternatives for Dblink in oracle?
May
22
comment Using EXECUTE IMMEDIATE inside PL\SQL Block
Any DDL statement causes an implicit commit. Not every ALTER statement, however, is DDL so not every ALTER statement does an implicit commit. For example, ALTER SESSION SET nls_date_format = ... is an ALTER statement that is not DDL and that does not implicitly commit.
May
21
answered Oracle service “lsnrctl” are missing (not present) after the installation of 32bits Oracle client
May
21
comment Oracle: separate column histograms for partitions
dba_tab_histograms (or all_tab_histograms) will have the table-level histograms. It sounds like you're saying those are correct. dba_part_histograms will have the partition-level histograms. Are those values accurate?