Reputation
12,976
Top tag
Next privilege 15,000 Rep.
Protect questions
Badges
1 22 36
Newest
 Yearling
Impact
~617k people reached

Feb
17
answered Oracle: Inserting data using a database link
Feb
17
comment Oracle: Inserting data using a database link
Are there three machines involved here? A client machine, a machine for DB1, and a machine for DB2? Do you have one machine with three Oracle Homes (one for the client, one for the DB1 database, and one for the DB2 database)? It seems unlikely that you'd do an Instant Client install on a machine that is already running the database so I'm guessing multiple machines are involved. If so, the tnsnames.ora file that needs to have DB2's connection information would need to exist on the machine (and Oracle Home) running DB1. Your client's TNS settings are irrelevant.
Feb
17
comment Oracle alter column if exists
dba_tab_columns has data for every table in the database. all_tab_columns has data for whatever tables the user you're logged in as has access to. Every user has access to all_tab_columns (though each will see different data), not every user has access to dba_tab_columns. I would expect, though, that a user that is installing an application would have full access to the data dictionary.
Feb
17
answered Oracle alter column if exists
Feb
17
comment How to know when a query was executed?
Are you licensed to use the AWR? Is it reasonable to expect that the query that was executed was one of the top resource consumers in the window during which it was executed? How long (roughly) did the query take to run?
Feb
17
comment How to know when a query was executed?
If a query was last executed a couple of days ago, it's pretty unlikely that it is going to still be present in v$sql. Not impossible, but I certainly wouldn't bet on it.
Jan
23
comment how to update 'update trigger fired record's column?
What is the "business role issue" that requires an AFTER trigger? You'd need to use a BEFORE trigger to be able to modify the row since you want to modify the data before inserting it into the database.
Jan
22
comment In Oracle, how to get a list with the names of schemas that a user has read permission?
Unfortunately, if you're not sure what you're looking for, it's hard for anyone else to guess what you're looking for. My wild guess is that you want to convert any code that is looking at user_tables (or any user_* data dictionary tables) to use the equivalent all_tables (or all_* views) and then prefix the schema name when building queries. But that's a pretty wild guess based on skimming the description of the bug you're trying to fix.
Jan
19
awarded  Yearling
Jan
19
comment What is {Oracle}… {Oracle Off}?
That's not valid SQL or PL/SQL syntax. I would assume that your script is going through some sort of pre-processor that is looking for the text in the brackets and replacing it with valid syntax.
Jan
19
comment AlwaysOn with TDE
What database are you using? Did you mean to tag this "SQL Server" (a database engine) rather than "SQL" (a programming language)?
Jan
15
awarded  Pundit
Jan
14
answered What's the best practice to avoid collisions in throwaway temp tables?
Jan
9
comment Dynamic ORDER BY clause in PL/SQL cursor
@MonkeyZeus - Dynamic SQL depends on building up a SQL statement as a string and executing it. I can't think of a ternary operator that SQL or PL/SQL supports off the top of my head. You can pass any number of bind variable values in with a dynamic SQL statement but you can't bind column names, just values.
Jan
9
answered Dynamic ORDER BY clause in PL/SQL cursor
Jan
5
comment Compare two big tables in PL/SQL
Why are you doing row-by-row processing of your data? It sounds like you want a MERGE statement. I'm having a bit of trouble figuring out why you have three separate loops in your code, though.
Jan
2
comment In Oracle, how to get a list with the names of schemas that a user has read permission?
Users do not have privileges on schemas. Users have privileges on objects (directly or (potentially recursively) through roles). Are you asking for the set of schemas that a particular user has access to at least one object in that schema? Or to all the objects in that schema? What roles, if any, do you want to include in this analysis (i.e. non-default roles, password-protected roles, etc.)? Do you need to include the various ANY system privileges (i.e. SELECT ANY TABLE)?
Dec
19
comment Oracle - Slow queries without prepending schema name or providing hint
Can you post the actual query plans? It seems very unlikely that the first query would do a full scan on an index rather than a min/max scan like the other two unless it's using a different index than the other two or there is something you're leaving out (possibly inadvertently). Are you sure that the first query is consistently taking 300 seconds?
Dec
19
comment How to know my DB user?
If you log in with the username "prodcollops", the database user will always be "prodcollops" so the question doesn't make a great deal of sense. What sort of distinction are you trying to make between a "database user" and a "login name"?
Dec
18
answered Enumerate Oracle tables in PLSQL