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

16h
awarded  Guru
21h
comment DBMS_METADATA.GET_DDL not displaying complete DDL for big tables
If you click on the ellipses, does a window open that has the full contents of the CLOB? Most GUIs won't try to display the full contents of a LOB since a single LOB could be multiple petabytes in length.
1d
comment column values combination
How do you know that node A starts chain 1? Should chain 1 start with the node that comes first alphabetically?
2d
comment Oracle: Mutually modifying two tables?
And I assume there is some reason that you can't make b a synonym for a or a view on a? Could b be a materialized view based on a?
2d
revised column values combination
added 54 characters in body
2d
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.
Aug
26
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
revised which is the cost of evaluation of the request?
Based on the edits, it appears that the tags need to be changed. I'm assuming "sql" in "sql and DB2" is supposed to be SQL Server.
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.