276 reputation
211
bio website jeffkemponoracle.com
location Perth, Australia
age 38
visits member for 3 years, 3 months
seen Mar 14 at 4:36

Oracle Database Designer and Developer

Since 2000 Jeffrey has designed, developed, maintained and enhanced a variety of large and small custom applications for the government sector using Oracle Database 7.3, 8i, 9i, 10g and 11g; Application Express 3.1, 3.2, 4.0, 4.1, and 4.2; Oracle Forms 4.5, 6i, 9i and 10g; Oracle Reports 2.5, 6i and 9i.

He has particular interests and experience in Oracle SQL, PL/SQL, database performance tuning, and Oracle Application Express (APEX).


Feb
13
comment What does SQLCODE = -00942 signify?
Was it ORA-00942 or SQL*Loader-00942? They are different error codes.
Jan
31
comment Retrieve Data faster from a huge Oracle Table
If t$item is char(16) in both tables, I don't think you need the rpad. If you remove the rpad it might be able to take advantage of indexes on one or both of those columns. Also, the magic values (1, 2, 7) look suspicious - are they really like that in your code, or do you use bind variables?
Jan
13
comment How Oracle DB maintains client identification
It depends on the application, but in Apex (for example), before every DB call it sets CLIENT_IDENTIFIER and clears it at the end. This takes advantage of Oracle's CONTEXT feature.
May
23
comment Jobs view is not visible to read-only account users
Not sure - might be a better way, but a function that returns a table of records based on the view might do the trick.
May
23
comment Jobs view is not visible to read-only account users
The ALL_* views only return rows that are visible to the current user. If you login as another user, they'll only show rows that they are allowed to see. You can't use views to expose data that is otherwise hidden.
Apr
17
comment impdp failing with ORA-01031: insufficient privileges
@VincentMalgrat: thanks. Tried it as SYSTEM and it worked.
Apr
16
comment impdp failing with ORA-01031: insufficient privileges
Don't have MOS access myself as a mere contractor (might have to see if I can) - but I'll try the DBA role, thanks.
Mar
28
comment SYSDBA password in Oracle DB
Just a friendly tip: if you're not sure you know what you're doing, connecting as SYS (which is analogous to a "root"-type account in a unix system) is a recipe for disaster. The best thing you can do is create a lower-privileged user, then login as that user.
Mar
28
comment No network ACL, yet my user can successfully connect to external server
UAT was created separately. We deployed the new user using the same scripts used in DEV. However, the dba who set up DEV is no longer with the organisation; he sent our new dba some scripts (which basically just create the ACL) but he doesn't remember doing anything else differently in dev.
Feb
8
comment Use a Context namespace in a multi-tenant database?
Made the change. No significant change to performance as far as I can see, so that's good. I'm a bit happier that the details about the context are encapsulated better - no more calls to SET_CONTEXT sprinkled all over the app.
Feb
8
comment Use a Context namespace in a multi-tenant database?
Maybe this trick will help: docs.oracle.com/cd/E11882_01/appdev.112/e25519/…
Feb
8
comment Use a Context namespace in a multi-tenant database?
Thanks, good point - result cache is exactly the opposite of what I'd want.
Feb
7
comment Use a Context namespace in a multi-tenant database?
Nice simple idea. I think it'll work nicely, including that you include the schema in the context name. Performance in queries might be an issue, although this might be helped with RESULT_CACHE.
Apr
19
comment Why keeps Oracle writing into WARNING_SETTINGS$?
According to psoug.org/reference/dbms_warn_int.html it appears to be related to the PL/SQL compiler warnings. What if you run: ALTER SESSION SET PLSQL_WARNINGS='DISABLE:ALL'; whenever you compile anything? Not sure if this would be equivalent to your add_warning_setting_cat or not.