Reputation
15,423
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
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
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
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?
Apr
12
comment Why switch execution plan for consecutive, identical queries?
@NetScr1be - You may know that the query is going to return 20 rows. It is unlikely that the database optimizer is going to guess that number correctly every time. It is entirely possible that it will be close most of the time but occasionally it's going to guess way too high or way too low (cardinality estimates with multiple predicates that have different levels of correlation are tricky). It is also possible that it is just flipping between different indexes.
Apr
7
comment Read from SQL Server into Oracle
You can still create a database link in Oracle that points to server. That requires using Generic Connectivity and the Transparent Gateway (the Transparent Gateway for ODBC is free and reasonably easy if your Oracle database is running on Windows). If you're trying to do real-time replication, that's going to be a challenge because all the replication technologies that are built on the database link won't work for heterogenous connections.
Apr
5
comment Oracle 11G, Move indexes from one datafile to another
Why do you assume that a single index exists in a single data file? Normally, you'd expect that indexes in the same tablespace would likely have extents in each of the data files.
Mar
18
comment How to create a function(table_name) that return a collection in Oracle?
Assuming that returning a cursor is an option rather than using a table function, I'm not sure why you bother to query all_tab_columns rather than just dynamically generating the statement 'select * from ' || table_name_in
Mar
17
comment ORACLE: Can I get results of “SHOW PARAMETER” into a variable or table?
Can you query v$parameter instead? show parameter is just SQL*Plus syntactic sugar for a query against that.
Mar
14
comment Why Does Oracle kill a session at the specific time?
It is incredibly unlikely that Oracle is killing the session. Something else is causing the session to be dropped. I would wager that something at the networking level is causing the issue.
Mar
11
comment What is the name of the default schema in Oracle?
The object will be created in the schema of whatever user you are logged in as at the time. Oracle's concept of "schema" is a bit different than other databases like, say, SQL Server. A database user has exactly 1 schema that has the same name as the user.
Mar
4
comment Inheritance between table and type in Oracle
So you have three object types, a parent object type and two child object types. And you have a two child object types that inherit from that parent? And then you create three separate tables one table per object. Is that correct? If you are trying to model "an entity must be exactly one of A or B" relationally, that would normally involve a different approach.
Mar
4
comment oracle : Effect of inserting new rows when row movement is enabled
As long as you and all the frameworks that you are using either ignore the rowid or use it correctly (with the understanding that it might have changed), I'd enable row movement permanently and be done with it. Obviously after first doing a thorough test to ensure that you don't have some hidden dependency in some random library. Row movement has no impact on rows that are inserted.