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

2d
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.
2d
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.
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
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.
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
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?
May
20
comment Oracle using index on partitioned table when full scan would have been better
@wrschneider99 - What have you set your default granularity to? Are you gathering partition-level statistics?
Apr
30
comment Problem with a trigger (Oracle 11gR2)
More fundamentally, even if you get your syntax errors resolved, a row level trigger on fin_transaction cannot generally query the fin_transaction table. You're going to get a mutating table exception at runtime. That strongly implies that you need to rethink your approach to whatever problem you're actually having.
Apr
25
comment Parking Lot table for employees
@MaxVernon - Ahh, I think I see. I was too dense last night to follow you alternate interpretation.
Apr
25
comment Parking Lot table for employees
@MaxVernon - That's why I was thinking initially that a 100 space parking lot would involve 100 rows in the ParkingSpace table. If the text of the assignment is exactly what was entered here and there is no additional context a table with 20 rows each with 5 columns does not make sense for a parking lot with 100 spaces.
Apr
25
comment Parking Lot table for employees
I can't find many ways of reading that statement in a way that creates a vaguely reasonable requirement in a class that is, presumably, trying to teach the rudiments of proper database design. Maybe if "100 spaces" is not intended to mean "100 parking spaces" but the intention is to create a table with 20 parking spaces where the parking_lot_id is one of 5 attributes the requirement might be plausible. But that's a pretty tortured reading of the text. Since this is a class, I'd strongly suggest seeking clarification from the professor.
Apr
25
comment Parking Lot table for employees
I have grave difficulty of a reasonable way to construct a ParkingSpace table that stores information about 100 spaces using 20 rows of 5 columns each. That goal appears to violate every rule of table construction known to mankind. I assume, therefore, that this is not really what your instructor wants. Columns represent attributes. What attributes of a parking space do you want to capture? Rows represent instances of a thing. If there are 100 parking spaces, you could have 100 rows in your table. Beyond that, though, I'm not sure quite what you're asking.
Apr
24
comment Is there a simple, safe and low cost way to implement a (limited) database to be outside (close) IT control?
I'd start by visiting your company's reporting group to see if they have anything in place that gives them a "sandbox" in which they can build tables to support ad-hoc reporting requests. If they do, you can see if you can get similar access.
Apr
22
comment DISTINCT not using an index
@Sparksis - Does "transactional" imply that this is part of an OLTP system rather than a data warehouse/ DSS sort of system?
Apr
22
comment DISTINCT not using an index
In a comment below, the original poster indicates that this is a "transactional" table. If that means that it is involved in OLTP operations, a bitmap index may not be appropriate both from a locking standpoint and from a growth standpoint.
Apr
13
comment I'm unable to open a datafile in oracle, looking for a solution
Does the file exist o the file system? It appears that it used to exist and now does not. If that's the case, you're looking at doing a database recovery. Hopefully, the DBA left excellent documentation on how to do that in your environment.
Apr
11
comment Difference between oracle database desktop and server class
Can you describe the network topology a bit? Is your laptop, for example, getting its IP address via DHCP or does it have a static IP address? On the remote machine, are you using the IP address to connect to the Oracle database? Or is the remote client using a host name which is resolved by a DNS server?
Apr
10
comment what is better in terms of allocated space in Oracle Tables: NUMBER or BINARY_FLOAT?
Did you mean to add this answer to a different question? I'm not sure that I see the relationship to the question...