11,997 reputation
11833
bio website ddbcinc.com
location Rochester Hills, MI
age 38
visits member for 3 years, 7 months
seen 1 hour ago
Oracle ACE

23h
comment Escape characters in Oracle dump
What does "take a dump" mean in this context? A "dump file" is a common term for the output of the Oracle export utility, either the regular version or the DataPump version. If you are using the Oracle export utility, however, the question doesn't make sense. That leads me to believe that you are using some other tool to generate some other sort of file.
Aug
15
comment Select on Table times out waiting for lock
And that "ORA Error" was? It doesn't make a lot of sense to make us guess the error.
Aug
13
comment Exporting scheduled jobs in Oracle
@ik_zelf - D'oh, you're right. Updated my answer to use the old PROCOBJ trick. I thought that was no longer necessary but apparently I was mistaken.
Aug
5
comment How to move 200 materialized views without having to re-create in the new home
What does "moving" mean to you? I would read that to mean something like "taking an RMAN backup, shutting down the existing database, restoring the backup on the new server, starting up the database". But if that's what you're doing, I don't see why you would need to re-create anything.
Aug
4
comment Oracle 12c, exclude rows wher two columns are both null
Are the varchar2(508 byte) columns expected contain non-numeric characters (i.e. you have some rows that have the string "foo" and other rows that have the string "1.25" and you expect that predicates in the view return only those strings that have numeric characters)?
Aug
4
comment Oracle 12c, exclude rows wher two columns are both null
Are these two columns in the view selecting data from table(s) where the data is numeric? Or is the view selecting data from table(s) where the data is a varchar2 and you are implicitly or explicitly casting the data to a number in the view? Are there rows in the underlying table that have non-numeric data in those columns?
Jul
31
comment Oracle Tables Visible to Other Users
A DBA would generally have the SELECT ANY TABLE privilege so a DBA would be able to query any table in the database without needing any additional grants. The SELECT ANY TABLE privilege is part of the DBA role so any user with the DBA role would be able to select any table in the database. Of course, as @Vérace points out, that doesn't really constitute "tightening up security".
Jul
31
comment Oracle Tables Visible to Other Users
Post exactly what statements you are executing and what errors, if any, you are getting. If you are actually granting a user select access on a table and that user is using the fully qualified table name to query the table, that would be successful. If your query isn't successful, we'd need to see what you're doing before we can guess where you're going wrong.
Jul
31
comment Oracle Tables Visible to Other Users
What does "no tables visible to the users" mean? Are you using some sort of front-end GUI? Are you querying all_tables? Or user_tables? Are you running a query against the table? If so, are you using the fully qualified table name?
Jul
25
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.
Jul
25
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.