Reputation
13,920
Top tag
Next privilege 15,000 Rep.
Protect questions
Badges
1 25 39
Newest
 Guru
Impact
~746k people reached

Aug
13
comment Differentiate between a row modification and add?
Without some sort of immutable key, you can't. In this example, I'd expect that to be vin. A vehicle's VIN is never going to change, it's a unique, immutable natural key. But that's the one value that did change. You can guess by looking to see how many columns match, potentially assigning different priorities to different columns (i.e. a match on color is less definitive than a match on make which is less definitive than a match on driver). But that's going to be probabilistic and highly data dependent.
Aug
11
comment ORA-01410 Error
What exactly does your trigger look like? You're referencing the :old and :new pseudorecords so it must be a row-level trigger. Are you certain that the rowid values that you are getting from these psrudorecords are valid in the context of the function call? It looks like you're trying to work around a mutating table exception which causes you to look for a rowid that either does not yet exist in the context of the function call or that no longer exists in the context of the function call.
Aug
10
answered Parsing out date_entered field to capture TIME 'hh:mm:ss' based on time range
Aug
9
awarded  Nice Answer
Aug
8
revised Oracle Jobs: Can I create a job that will not overlap with itself?
added 425 characters in body
Aug
8
answered Oracle Jobs: Can I create a job that will not overlap with itself?
Aug
8
comment Oracle Jobs: Can I create a job that will not overlap with itself?
Are you using the dbms_job or the dbms_scheduler package?
Aug
7
answered If you don't rollback a transaction will the change still be made?
Aug
7
answered How to match regular expressions in Oracle?
Aug
5
comment Oracle Error: the sga requires more space than was allocated for it
Do you have a backup of your spfile? Or an alternate pfile that you can use to start your database? If not, you should be able to manually edit the spfile while the database is shut down. You'd never want to have an sga_target that was greater than the amount of RAM on your server-- a 75 GB SGA would only make sense if your server had at least 128 GB of RAM available.
Aug
4
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...
Aug
3
awarded  Constituent
Aug
3
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.