366 reputation
311
bio website jeffkemponoracle.com
location Perth, Australia
age 39
visits member for 3 years, 10 months
seen Nov 24 at 6:56

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.
Oct
10
answered Why does Oracle not support an autonumber data type?
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?
May
21
awarded  Notable Question
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.
Sep
20
awarded  Popular Question
Jun
8
awarded  Commentator
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.
May
21
awarded  Caucus
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.
Apr
16
asked impdp failing with ORA-01031: insufficient privileges
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.
Mar
28
asked No network ACL, yet my user can successfully connect to external server