Reputation
13,910
Top tag
Next privilege 15,000 Rep.
Protect questions
Badges
1 24 39
Newest
 Nice Answer
Impact
~746k people reached

3h
comment Identifying high load sql
Why do you believe that there was a SQL statement that "caused very high CPU load"? If your snapshot interval is sufficiently small and your AWR report is sufficiently localized and there was a SQL statement in that interval that was consuming a large amount of CPU, it should be on the AWR report (and the top wait events should include significant CPU waits). If you're not seeing that, either your theory that there was a rogue SQL statement was incorrect, your AWR report is insufficiently localized, or you're reading the AWR report incorrectly.
2d
comment Script to find unused indexes over a period of time doesn't work in Oracle 11g
Is Statspack installed in your database? It's never installed by default. Are you licensed to use the AWR? If so, you could just query the dba_hist* tables.
Aug
24
comment Opening an Oracle database with a corrupted redo log
If you have no backup of the database and have no other source for the data, I would strongly recommend first that you back up whatever you have so that you are absolutely certain that you can get back to the current state. And then I would recommend getting someone to look at the problem and figure out how best to move forward rather than trying trying out suggestions from the internet. Very few of the things that you might try are reversible and there are generally lots (and lots) of questions that would need to be answered before proceeding.
Aug
24
comment which is the cost of evaluation of the request?
By "sql" do you mean "Microsoft SQL Server"? SQL is a language that is common to all relational databases.
Aug
24
comment Opening an Oracle database with a corrupted redo log
Do you have a backup of the database? Can you restore that backup?
Aug
24
comment which is the cost of evaluation of the request?
Oracle does not have "pages". There are blocks, which are going to generally be 8 kb in length, extents, and segments. When you're talking about "pages" you're generally doing so because you're using a database that uses pages to do things like page-level locks. That terminology doesn't make sense in Oracle. Oracle also does not have clustered or non-clustered indexes and "overflow" would only come into play if the table was index-organized but you state at the outset that the table is heap organized. If you're really using Oracle, most of these questions don't make sense.
Aug
24
comment Can a Table Lock eliminate a deadlock or make it worse Oracle
What does "locks table X" mean here? Forcing explicit table locking with the lock table command is very, very uncommon. I'm hard-pressed to imagine many situations where that would be reasonable in an OLTP application. Perhaps you mean that the code is locking a row in table X and a row in table Y and deadlocking on that?
Aug
24
comment Can a Table Lock eliminate a deadlock or make it worse Oracle
Might adding explicit table locks mitigate a deadlock problem? Possible, I guess. Might it make the problem worse? Sure. Might it do nothing? Absolutely. Rather than speculating, though, was does the deadlock trace file tell you is the actual source of the deadlock? What is the issue that causes your application to generate that deadlock?
Aug
20
comment How to avoid a “duplicate key” error?
Your update seems to indicate that you shouldn't be trying to prevent the duplicate key error. The duplicate key error is an expected condition given your description of the calling service. Your procedure needs to expect this error, catch it, and handle it. I would guess that you simply want to catch the error and retry the operation potentially with a small delay to give the other session time to commit.
Aug
20
comment How to avoid a “duplicate key” error?
@JustinCave - That's why I said "at least across sessions". No one session may see duplicate rows in the temporary table. But they have to be there in the underlying data structure.
Aug
20
comment How to avoid a “duplicate key” error?
#t must have duplicates at least across sessions. If you have two sessions that are both trying to insert the same primary key value at the same time, one of them will have to get an error. If you want to prevent the error, you'd have to prevent two sessions from trying to insert the same primary key value at the same time. In this case, that would mean ensuring that whatever populates #t populates a different set of rows in each session.
Aug
20
comment About audits in Oracle 11g
I'm guessing that English isn't your first language. Unfortunately, I can't follow the question you're trying to ask. Perhaps adding a bit of code would clarify things a bit (i.e. I do X and I want Y to be recorded, I do A and I want B to be recorded)?
Aug
13
comment Oracle 11g XE - Giving granting privileges to a stored procedure
We'd need to see a reproducible test case. For example, the CREATE VIEW, the GRANT statement(s) on the view, the stored procedure you're using, etc. Not knowing who owns the view, what table(s) the view references, what other object(s) the view references, what the stored procedure is doing, who owns the stored procedure, etc. it's hard to guess what privilege you're missing.
Aug
13
comment Oracle 11g XE - Giving granting privileges to a stored procedure
Assuming you are creating a definer's rights stored procedure, it will run with the privileges that are assigned directly (not via a role) to the user that owns the procedure. So the owner of the procedure would need to have permission to give another user access to the view granted directly. Beyond that, it's hard to be very specific without seeing an example of what you're struggling with. I'm not sure what you're trying, what error(s) you're getting, or what your views look like so it's hard to guess what problems you might be having.
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
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
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
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.