Reputation
15,403
Top tag
Next privilege 20,000 Rep.
Access 'trusted user' tools
Badges
1 28 42
Newest
 Nice Answer
Impact
~1.0m people reached

Apr
25
comment Is it possible to perform skip N rows and fetch M rows without full scan
@MikayilAbdullayev - Yes. Though I would question why you would do such a thing. You use paging queries when you have humans that want to look through the first few rows of data. Humans aren't going to read row 10,000. A computer system that wants to read all the data eventually would not use this sort of query
Apr
25
revised Is it possible to perform skip N rows and fetch M rows without full scan
edited tags
Apr
25
answered Is it possible to perform skip N rows and fetch M rows without full scan
Apr
25
comment Is it possible to perform skip N rows and fetch M rows without full scan
What version of Oracle? Without an order by, the query you posted doesn't do what you appear to want.
Apr
24
comment Currently running TOP Sqls
OK. I yield. I can't figure out what you're trying to accomplish so I have no idea how to help you. I would expect this question to eventually get closed as unclear what you're asking. If you want to edit it to explain exactly what you want, you may get some assistance.
Apr
24
comment Currently running TOP Sqls
The query I posted gives you the currently running SQL statements. If that is not what you want, help us out by explaining how that differs from the results you want. Depending on the EM screen you're looking at, it is highly likely that it is looking at SQL over a period of time not just those SQL statements that are being executed at a particular instant by looking at various AWR tables.
Apr
24
comment Currently running TOP Sqls
No. By definition, an inactive session is not running anything at the moment. It might have executed something a millisecond ago. It might execute something else a millisecond from now. But it is not doing anything at the moment. A session that is killed is rolling back the changes that were made in the open transaction (if any), not executing a SQL statement. You still haven't explained exactly what you want to find so it's really hard to point you in a direction.
Apr
24
comment Currently running TOP Sqls
By definition, an inactive session is not executing a SQL statement at the moment.
Apr
24
comment Currently running TOP Sqls
"broken in some way" doesn't help us. select sql_id from v$session where status='ACTIVE' and sql_id is not null; will give you the sql_id of every statement that is currently running. If you want more information or if you want to do additional filtering (perhaps you want to exclude what background sessions are running), you'll have to tell us exactly what you want. Our ability to guess your requirements is limited.
Apr
24
comment Currently running TOP Sqls
Does either query give you the result that you are looking for? If not, how does the result differ from what you want? Do you want every SQL statement that is currently running? Or the "top SQL"? If you want the "top", you'd need to specify what criteria determines "top" for you. What attributes do you want to know? Just the sql_id? Do you want the text of the statement? Something about the session executing it? Something else?
Apr
20
comment Why does Oracle change my date fields?
How are you getting the value to store in the date column? Are you calling sysdate? Or is the application tier generating the value? Inside the database, time zones don't matter with a date column so my guess would be that the middle tier is generating the value and the conversion is happening in the process of writing data to the database.
Apr
20
comment PUBLIC privilege potential security threat - Oracle database
Let us continue this discussion in chat.
Apr
20
comment PUBLIC privilege potential security threat - Oracle database
@tesla747 - No. A would not be able to modify B's objects. Barring, again, someone doing something crazy with additional grants to public. And, as I said, you shouldn't be using connect or resource in real systems.
Apr
20
comment PUBLIC privilege potential security threat - Oracle database
@tesla747 - Because certain privileges rightly should be given to every user by default. It seems likely, for example, that you want a new user to be able to query user_tables to see the tables they own or all_tables to see what tables they have privileges on. It would be terribly annoying if every application had to ship with the full list of every data dictionary table it needed to query (some of which would vary based on how different JDBC/ODBC/OLE DB/ etc. providers implemented certain API functions). It is useful to have a small baseline of functionality that you get by default.
Apr
20
comment PUBLIC privilege potential security threat - Oracle database
@tesla747 - In a default Oracle install, a user that has just create session cannot create objects, cannot read objects in another user's schema, and certainly cannot modify data in another user's schema. Only if someone in your organization were crazy enough to do something like granting update any table to public would that happen. If someone has granted a raft of powerful privileges to public beyond what Oracle ships with, that would be a concern absent a compelling justification for each grant.
Apr
14
answered Oracle XE/SQLPlus query display more items
Apr
13
comment Cross-cutting I18N native support in databases
Functionally, how would this be different from a table that has an extra locale column?
Apr
12
comment How to recover auto committed transaction in Oracle with normal privilage
Talk with the DBA. Normal users aren't likely to be able to reverse a committed transaction. I'm not sure which "flashback command" you used-- there are lots of different commands in that family of technologies that do very different things under the covers. If you are lucky and the old data is still in UNDO, you may be able to run a flashback query and use the results of that query to update the data to the old values.
Apr
12
comment Why switch execution plan for consecutive, identical queries?
@NetScr1be - Potentially, you could look at using a plan guide to force a particular plan msdn.microsoft.com/en-us/library/ms190417.aspx or by specifying a particular index in a hint (assuming that you really have indexes). Or the optimize for approach I suggested in my answer.
Apr
12
comment Why switch execution plan for consecutive, identical queries?
@NetScr1be - If your comment that there are no indexes is correct, that would imply that there is only one possible query plan-- a table scan-- that the cardinality estimates are pointless and that the DBA's explanation is at least highly suspect and there is a lot more going on. It seems highly unlikely that you are running repeated queries against a table with absolutely no indexes. There isn't even a primary key?