366 reputation
311
bio website jeffkemponoracle.com
location Perth, Australia
age 39
visits member for 3 years, 10 months
seen yesterday

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).


Oct
10
comment Why does Oracle not support an autonumber data type?
The answer to the question "why didn't Oracle implement it until now" is simply that it's never been a priority, since the existing tools are more than adequate for the job at hand. The IDENTITY column is basically syntactic sugar for a sequence + column default anyway - with the addition of restricting the ability of applications to set the column to arbitrary values.
Oct
10
comment Why does Oracle not support an autonumber data type?
Firstly, a sequence is more flexible because you can use it for multiple purposes - not just for setting a column in a single table. Secondly, they're more flexible in that you can choose whether a row should use the sequence or not - you don't need to use a trigger if you don't want to.
Aug
2
comment How to get index last modified time in Oracle?
Well, you can get the modified time with select last_ddl_time from all_objects where object_type = 'INDEX' and object_name = 'MY_INDEX_NAME'; but to get who modified it you'd need to enable auditing.
Jun
28
comment What is the “other miscellaneous information” in the Shared Global Area (oracle database)?
Probably too many minor details that would cause a burden on the people maintaining the documentation to keep track of. Why do you ask?
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.