Reputation
13,625
Top tag
Next privilege 15,000 Rep.
Protect questions
Badges
1 24 38
Newest
 Constituent
Impact
~720k people reached

10h
comment Getting results without using IN
Where are these version_id values coming from? 99 times out of 100, they're coming from somewhere else in the database in which case you just need to take the select statement that returns the values and put that in the in clause. If the values are coming from somewhere else, you'd often want to load them into a collection or temporary table and use that in your in clause. Other than that, you can have multiple in clauses or get even more esoteric but that rarely produces code that people are happy to maintain...
1d
comment Should the PK on an identity column be non-clustered?
If the primary key is non-clustered and the primary use case is looking for a range of A values, why wouldn't you create ix_A as a clustered index?
1d
comment How to tune a stored procedure that is fast on test data but slow in production
Does the development database have the same data volume and data distribution as the production system? Or is the development database running with a relatively small amount of data compared to production? If your development database doesn't have a similar data volume and distribution, it's going to be almost impossible to do any sort of meaningful performance analysis in that environment.
Jul
30
comment will table partitioning and compression help heavily used OLTP database?
Why wouldn't it work? There may be valid reasons but nothing in what you've posted here would seem to indicate that your boss is wrong. If the table is partitioned by audit_date and your purge process is just deleting rows based on their audit_date, dropping an old partition is going to be vastly more efficient than deleting rows. Perhaps, though, you know something about your data that we don't that tells you that your boss's idea won't work.
Jul
28
comment Tables Visible to a Specific User?
I would start with Pete Finnigan's who_can_access.sql script petefinnigan.com/who_can_access.sql from his very useful script collection petefinnigan.com/tools.htm . That's going to handle the needs of the vast majority of use cases.
Jul
28
comment Tables Visible to a Specific User?
Getting the list of tables that the user has direct access to is relatively easy. The complexity comes in dealing with privileges granted through roles. And that gets complicated because roles can be password protected (so a user may or may not have access to them), they can be enabled and disabled within a session, they can be default or non-default, etc. So figuring out which set of roles to consider, in general, is a hard problem (thus privileges granted via roles aren't available in definer's rights procedures). Do you want to consider all roles regardless of default, password, etc?
Jul
28
comment Export backup from a different location
No, it's not a separately downloadable utility. I don't believe there is a supplement for the Instant Client that would include the DataPump export utility. I expect that you'd need to have the full Oracle client. I generally do custom installs because I don't remember from version to version exactly what gets installed with each of the predefined install options.
Jul
28
comment Export backup from a different location
@miracle173 - Agreed. You could, presumably, configure the database server to mount a drive shared by the app server or you could move the files from the database server to the app server after they are generated. Or you could install a database on the app server that has no data and use the network_link parameter to generate files on the app server. Or you could go back to using the old exp utility.
Jul
28
comment Export backup from a different location
"oracle client" is a very imprecise term. That could mean that you installed the Oracle Instant Client with or without half a dozen possible supplements. It could mean one of a few pre-defined bundles or a custom install where you may or may not have installed the export and import utilities. It could mean that you just installed a type 4 JDBC driver or a wire protocol ODBC driver. You'd have to tell us whether the DataPump export utility is installed on your app server.
Jul
28
comment Export backup from a different location
Is it possible? Sure. Assuming that the client you installed on the app server includes the export utility, you'd simply invoke the exp executable, pass it the connection information for the database and the parameters to specify what sort of export you want, and let it run. Is that really your question, though? Of course, the export will be much less efficient when it's running on the app server than it would be running on the database server. And, of course, an export should always be a complement to rather than a replacement of a proper physical backup.
Jul
23
comment what exactly does an RMAN backup do?
Are you restoring to a point in time? Or are you applying all the archived logs? If you restored to a point in time before you dropped the tables, the tables would be there (and anything you did after that would not have taken place). If you roll forward through the last redo log, however, you'd re-apply the DROP TABLE commands. RMAN has no idea which statements you want to apply and which you want to skip unless you tell it somehow.
Jul
22
comment Oracle instant client memory footprint
@tesla747 - Not sure what you're asking for an explanation on. It would be odd but you might decide to build an executable that was going to be deployed to a server and that you were going to spawn 10's of thousands of instances of that executable rather than having the executable spawn multiple threads or deploying a client application to a client machine. At that point, you might care about things like whether each instance of the executable is storing frequently used error messages in physical memory rather than having a single shared copy in memory.
Jul
16
comment Oracle: Creating Case Insensitive Database with NLS_COMP and NLS_SORT
I believe your only option is to apply these settings in each session and to ensure that all your indexes are created (or recreated) with the NLS settings that you want. I don't believe it is possible to create an Oracle database that is case insensitive by default.
Jul
15
comment Database design for multi foreign keys
How are you going to report on votes? Are you frequently going to want to run queries that combine votes on questions and on answers? Or are you going to want data on one or the other? Are you going to apply rules to votes in general or to one type of vote? My guess would be that it would be pretty unusual to want to combine the data so you'd be better off with separate tables for AnswerVote and QuestionVote. But if this is an abstraction of your real requirements, my guess might not match your reality.
Jul
15
comment Restoring oracle database
If you had an 11.2.0.4 database to restore into and assuming that the file that you have was generated via RMAN, yes, there would be a way to restore it. But we'd generally want to know the RMAN command that was used to produce it. An export (which would commonly have a .dmp extension) can be restored (within reason) to a different version of the database running on a different platform so it's generally much easier to deal with.
Jul
15
comment Restoring oracle database
Conceptually similar, yes. I don't know off the top of my head how fiddly SQL Server backup files are at going across minor patch level versions. But RMAN backups are going to be very version-specific.
Jul
15
comment Restoring oracle database
Can you ask whoever provided you with these files to tell you how the backup was generated? Assuming that RMAN was used to generate this file in the first place, it appears that the source is using 11.2.0.4 in which case you won't be able to restore it to an 11.2.0.2 database. You'd need to restore it to a database on exactly the same platform running exactly the same version of Oracle. If the database is small enough to fit in an XE database, I'd wager that you want to ask for an export (a logical backup) rather than a physical backup.
Jul
15
comment Oracle Autotrace vs Explain
What "traceroute" are you using? Normally, traceroute is an application that tracks network hops not something dealing with query plans. Are you comparing actual vs. estimated query plans? Or is this some third party utility with an unfortunately confusing name? As an aside, I would not say that B is obviously more efficient than A. It's certainly possible that's the case. But a cost that is too low is just as likely to indicate a problematic query plan as a cost that is too high.
Jul
13
comment How do I prevent an ORA-08102 error
Have you contacted Oracle support? The fact that you're getting corrupted index structures often enough to have solutions that work "sometimes" and to be looking to prevent it implies that you have an underlying problem that needs to be diagnosed. You could try dropping and recreating the index rather than just rebuilding it. You could try rebuilding the table and then rebuilding the indexes. But I'd definitely get a support ticket opened with the trace file(s) you're getting.
Jul
13
comment Oracle DataGuard
I'm not sure that I understand what you are asking. Oracle provides three Data Guard modes (maximum availability, maximum performance, and maximum protection). If one was preferred, there would be no point to offering the other two. Each of the options comes with trade-offs that you need to understand. Which trade-offs you prefer is a very project- and environment-specific question. No one can tell you, for example, whether the performance hit that you get from running in maximum availability is acceptable to your customers running your applications in your environment.