12,886 reputation
12236
bio website ddbcinc.com
location Rochester Hills, MI
age 38
visits member for 4 years, 2 months
seen yesterday
Oracle ACE

Mar
19
comment SQL - Optimize table population with dynamic queries
Are you saying that it will loop 1 million times each time inserting 10,000 rows so that it inserts a total of 10 billion rows? Or are you saying that it inserts a total of 1 million rows? Is there a reason that you're looping in the first place rather than simply dynamically constructing the entire INSERT SELECT statement? Do you need the interim commits?
Mar
12
comment Continuous replication between Oracle Database and Microsoft Azure SQL
Why do you believe that GoldenGate is deprecated? Oracle purchased the company and its direction in the future is based on GoldenGate technology (plus bits from Streams).
Mar
11
comment Stored Procedure result is not showing in Datagrid pane in Toad for Oracle
p_recordset is a local variable that goes out of scope as soon as the procedure returns. If you want an object that executes a query and returns a sys_refcursor you probably want a function not a procedure. If you really want to use a procedure, you'd need to add an OUT parameter of type sys_refcursor (or types.cursor_type if that is something meaningfully different).
Mar
6
comment Improve sql server lookup table performance
What does the query plan look like? What does "very slow" mean (minutes? hours?)? What sort of performance are you hoping to achieve? A single-table query using a single IN list predicate with 1000 values should just do 1000 index seeks using the primary key index which should be pretty efficient (though that's relative-- if you're counting milliseconds, efficiency is relative).
Feb
28
comment How to perform same DML statements on two different servers (Oracle and SQL-Server) simultaneously
Do you really mean simultaneously? So every transaction will incur the overhead of a two-phase commit and the system will be unusable if either database is unavailable? Generally, that's not what you want. Generally, you want to replicate data from one system to the other asynchronously which brings up the question of what sort of lag you can tolerate and whether the replication needs to be bidirectional.
Feb
26
comment How to Load Special characters in Database 11.2 & Client Character Set is US7ASCII through SQL * Loader
Are you trying to load the characters into a varchar2 column? Or an nvarchar2 column? If the database character set is US7ASCII and the "special characters" are not part of the 7-bit ASCII character set, they cannot be validly encoded in a varchar2 column.
Feb
17
comment Oracle: Inserting data using a database link
@KellyCook - The error you are getting cannot possibly be the result of something being wrong on the client machine (assuming that it is actually the INSERT statement that throws the error). The error you're getting indicates that the database link is set up incorrectly.
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
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
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
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
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"?