Reputation
13,615
Top tag
Next privilege 15,000 Rep.
Protect questions
Badges
1 23 38
Newest
 Caucus
Impact
~717k people reached

2d
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
27
awarded  Caucus
Jul
23
answered what exactly does an RMAN backup do?
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
22
answered Oracle instant client memory footprint
Jul
21
answered Oracle Performance - Varchar2 (x char) x Varchar2 (x byte)
Jul
17
answered SQL developer licensing
Jul
16
answered Is Oracle 10g smart enough to work on a WHERE clause identically as the equivalent INNER JOIN?
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.