12,646 reputation
12236
bio website ddbcinc.com
location Rochester Hills, MI
age 38
visits member for 4 years
seen 13 hours ago
Oracle ACE

1d
comment How to connect to Oracle Database using SQL Plus?
You can't install just the SQLPlus client. You either did a full Oracle client install, which includes SQLPlus and a number of other utilities or you installed the Instant Client and the SQL*Plus supplement which installs far fewer utilities. I'd still guess that you have either a networking issue or that your connection information is incorrect.
2d
comment How to connect to Oracle Database using SQL Plus?
My initial guess is that something on the network is preventing the connection (a firewall, for example, possibly a firewall running on your local machine possibly a firewall running elsewhere on the network) or that your connection information is incorrect. Did you install the full Oracle client? Or just the instant client to get the sql*plus client.
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"?
Dec
18
comment DB Design storing products sold in bulk, priced by vastly different units
Is each product always sold by a single unit? Or are there products that can be sold by multiple units? It sounds like you are saying that a particular product will always be sold by a single unit.
Dec
10
comment Extract from Oracle at given time
Total recall/ flashback data archive was introduced in 11g, I believe, so it wouldn't be available to the original poster that is trying to use 9i and 10g. Since you mention licensing, flashback data archive becomes part of the database license in 12.1, it's no longer an extra cost option.
Oct
23
comment The most important oracle database performance metrics
What is the problem that you're trying to solve? Identifying 10 metrics which are most relevant to my systems probably does you little good because your systems probably have very different characteristics. Some systems have I/O as their primary bottleneck, some have CPU, some have networking, some have user locks. My systems might be more (or less) memory constrained than yours leading me to care about different metrics than you.
Oct
21
comment Is there anyway to search PL/SQL reserved words with contains function?
What do you consider "Oracle reserved words"? "do", for example, is not a reserved word in v$reserved_words.
Oct
20
comment Is there anyway to search PL/SQL reserved words with contains function?
Are you trying to search the text of code stored in Oracle (which is what I initially read your question as asking)? Or are you trying to search for data stored in a user-defined table? If you're searching for data in a user-defined table, can you create an Oracle Text index on that table?
Oct
20
comment Is there anyway to search PL/SQL reserved words with contains function?
@Andy - Sorry, I'm afraid that comment doesn't clarify anything for me. It may be useful to expand your question to include, say, a few example pieces of code, the types of searches you want to run, and the expected results of those searches on that sample code. Neither "for test" or "do test" are keywords in PL/SQL-- "for" is a keyword but neither "do" nor "test" are.
Oct
17
comment grant permissions in oracle from one database to another
@KyleM - It would be whatever the TNS alias on the EmployeeDB database for the ContractorDB database is. That's generally going to be the same as the name of the database but that's just a naming convention-- your TNS alias might be different.
Oct
14
comment What do these pstart and pstop values mean?
@jubil - Short of tracing the session, I don't know of a way to do that.
Oct
13
comment work with json in oracle
What version of Oracle? 12c introduces native JSON types.