12,472 reputation
12036
bio website ddbcinc.com
location Rochester Hills, MI
age 38
visits member for 3 years, 10 months
seen 3 mins ago
Oracle ACE

Apr
7
answered Revised Question: How do I prevent Oracle 12c Database from starting on machine boot
Apr
4
revised How to know DB size using backup file without restoring it
edited tags
Apr
4
comment How to know DB size using backup file without restoring it
What database are you using (Oracle, SQL Server, MySQL, etc.)? What sort of backup do you have? For some types of backups the size of the backup is going to be roughly equivalent to the size of the restored database.
Apr
1
comment HOW does ROWNUM work with this “optimization”
Your most recent update does not appear to be deterministic. The ORDER BY is applied after the rownum is assigned in your inner query so there is no guarantee that the result will be the same across runs. It likely will be, assuming that the table remains constant, optimizer parameters are unchanged, etc. but it would be perfectly valid for Oracle to return the same value for every value of rowx that you pass in.
Apr
1
comment HOW does ROWNUM work with this “optimization”
@Sparksis - I've added to my answer to talk about the problem with the initial solution. Depending on what "sample data" means to you, you might be better off using the actual sample clause.
Apr
1
revised HOW does ROWNUM work with this “optimization”
added 1095 characters in body
Apr
1
answered HOW does ROWNUM work with this “optimization”
Apr
1
comment HOW does ROWNUM work with this “optimization”
A predicate rownum = rowx would only return rows if rowx was 1. If it was any other value, the query will return 0 rows. So the two queries are returning a different result in addition to having different performance, right?
Mar
28
revised Is there any disadvantage to using Oracle's Easy Connect syntax?
added 1279 characters in body
Mar
28
comment Is there any disadvantage to using Oracle's Easy Connect syntax?
@jpmc26 - It depends. If you deploy a bunch of applications that use easy connect syntax and then decide that you want to add load balancing later on, that means that you've got to go touch a bunch of applications. Large organizations generally come up with a standard tnsnames file (or store TNS aliases in LDAP) that gets pushed to everyone when configuration changes are made. Supporting both can create support issues when, for example, some applications fail over automatically and some don't and someone needs to keep track of which are which.
Mar
27
answered Is there any disadvantage to using Oracle's Easy Connect syntax?
Mar
22
comment Oracle strong ref cursor with dynamic sql
Are you certain that you need dynamic SQL to add the predicates? Are you sure you can't have a static query with predicates like where (parameter = 'A' and (something)) that would only evaluate the something predicate if parameter is A?
Mar
19
comment Stored Procedure with ROW instead of a list of parameters
@joe - Assuming that the caller also defines rec as table_name%rowtype, then yes.
Mar
19
answered Stored Procedure with ROW instead of a list of parameters
Mar
19
comment How to Upgrade from Oracle 10G to Oracle 11G without bringing down the database
How much downtime can you tolerate? How much effort are you willing to expend to achieve that goal? What edition of Oracle are you using? What is the architecture of your "resilient environment with shared storage"? Does that mean DataGuard? RAC? Something else? It's possible to do a zero downtime upgrade but that generally requires considerably more effort to configure, practice, test, etc. than a simpler upgrade.
Mar
11
comment How to export oracle 11g database partially
Do you want a random sampling from each table? Or do you need a referentially correct 1% sample? So, if you choose a particular child row from among the 1% of rows from the child table, must the parent row be chosen from the parent table as well? If you choose the parent do you need to choose all the child rows (and grandchild rows)? Or are you fine missing some of the child rows (i.e. you pull order 123 and some number of order lines for 123 but not all of them)? Depending on the answers, this is probably not something export and import are designed for.
Mar
10
comment Oracle dead with unknown reason
Is there a reason that you haven't applied any patchsets to your database? You're running the very first 11.2 release. Do you have a support contract? ORA-00600 and ORA-00745 errors are internal Oracle errors. Normally, you'd use the Metalink search tools to see whether the ORA-00600 and ORA-00745 error lookup tools find anything useful or whether you need to log a support request.
Mar
6
comment Oracle case insensitive passwords seemingly ignoring SEC_CASE_SENSITIVE_LOGON
Are you logged in to the database server when you do this? Are you sure that you're not using operating system authentication (in which case the password will be completely ignored)? If you enter a totally incorrect password, do you still log in successfully?
Feb
26
comment Slowness after migration to RAC 11gr2
Are the query plans for the problem statements the same in your 10.2 database and your 11.2 database? Did you do anything as part of the upgrade to ensure plan stability?
Feb
25
comment How to compare a result of a query to a number?
@BrunoLM - Edited my answer