1,535 reputation
1614
bio website stackexchange.com/users/98359
location United Kingdom
age 42
visits member for 3 years, 2 months
seen 2 days ago

I've been picking up hints, tips and information from a variety of sources over the years, but somehow have always been a taker and not a giver. I'm finally getting involved and trying to give something back to the community. Still learning far more than contributing though.

I'm a bit of a jack-of-all-trades in a small UK-based team within a US subsidiary of a Japanese company, doing work for a large UK telecoms provider. Production support, developer, part-time (a.k.a accidental) DBA in dev/test, analyst, consultant... whatever needs doing right now, generally. Mostly Oracle (since about 1995, versions 6 to 11g), Java, WebLogic, shell scripting, HTML, CSS, a bit of C when pushed, but whatever's needed today. Work is mostly on Oracle Linux, but dragged into Windows and networking issues fairly often, and a Mac user at home.


Jan
28
comment Multiple Oracle TNS error
Well, I can't reproduce information from there. The oerr description of TNS-12518 is: "Cause: The process of handing off a client connection to another process failed. Action: Turn on listener tracing and re-execute the operation. Verify that the listener and database instance are properly configured for direct handoff. If problem persists, call Oracle Support. Comment: The problem can be worked around by configuring dispatcher(s) to specifically handle the desired presentation(s), and connecting directly to the dispatcher, bypassing the listener." So start by tracing?
Jan
27
comment How oracle Oracle Database 10g used index?
@Phil - query 2 is against id_res, not id_res2... but it's not clear if it has the PK or UI against it (since they can't both be there against the same column as written). I mentioned on the SO version that it might have switched from a full index scan to a full table scan because it's selecting r.* in query 2; but awaiting the execution plans to see why query 4 didn't do the same... I'm guessing stats, or because id_res2 is nullable. Maybe.
Jan
27
comment How oracle Oracle Database 10g used index?
Cross-posted on SO.
Jan
27
comment sqlldr: command not found
@MaheswaranRavisankar - the full client does; instant client doesn't. Instant client was just a thought though, and not relevant. I can't remember if the full install process lets you exclude certain executables, but I guess it does. Maybe that was down for some reason.
Jan
27
comment sqlldr: command not found
@user186414 - so what does ls -l $ORACLE_HOME/bin/sqlldr display? If you get 'no such file or directory' then possibly your DBA has removed it; but it may exist and you don't have execute permission. Either way you probably need to talk to your DBA.
Jan
26
comment Multiple Oracle TNS error
If you have access to the Oracle support site, see if note 550859.1 helps. It has a section on errors that specifically apply to Windows, but that doesn't seem to include error 203. If that note doesn't point you in the right direction you might need to raise an SR for assistance.
Jan
25
comment sqlplus startup - failure in processing system parameters
How was the database created? Has it started before? Does the initorcl.ora exist somewhere else, or do you have a valid spfile?
Jan
6
comment Oracle partition by interval using persian calendar
@AmirPashazadeh - with a local index on xdate the plan still has PARTITION RANGE ALL; but the index would mean it's only actually retrieving data from one partition, since there is no table scan. With a lot of data though the index might not help much, and could slow things down, or be ignored; depends on the distribution. Adding the virtual column to the query is still the only way to help in this case I think.
Jan
6
comment Oracle partition by interval using persian calendar
@AmirPashazadeh - you could probably schedule the partition creation; but again only on Gregorian-monthly intervals...
Jan
6
revised Oracle partition by interval using persian calendar
Added execution plan note
Jan
6
revised Oracle partition by interval using persian calendar
More useful query
Jan
6
revised Oracle partition by interval using persian calendar
Adjusted the partition slightly, so 2013-03-20 ends P0 and appears in the final output.
Jan
6
answered Oracle partition by interval using persian calendar
Jan
6
comment Oracle partition by interval using persian calendar
Intervals are sensitive to NLS_CALENDAR (note 227334.1), but for table/partition definitions I guess it would have to be the database setting - the session level doesn't apply, and you can't override it in the interval itself. Assuming the DB parameter is Gregorian, it doesn't look like you can do this with intervals, and will have to manually define all the partitions? Another approach, I guess, would be to add a virtual column with a numeric representation of the month and partition on that?
Jan
6
comment Oracle partition by interval using persian calendar
Well, it's a month, not 30 days; but the point is that it's always using the Gregorian equivalent of the Persian date in the P0 partition to calculate the intervals for future partitions. So the automatic interval partitions don't align with the Persian calendar.
Jan
2
comment How to rollback last executed sql script fired as sysdba
@user3154598 - Second David's comment about being extremely careful about what you drop or change when trying to undo this; you don't want to end up in this situation by dropping something crucial, and pretty much everything owned by SYS is crucial. If you are not the DBA I suggest you get advice and sign-off from them before doing anything else.
Jan
2
answered web interface port for oracle 11g
Jan
2
comment web interface port for oracle 11g
Is the first dbcontrol instance still port 1158 by default? I can't remember how to check that off-hand... apart from stopping and starting it from the command line, which I think displays the URL.
Dec
27
comment why archived log size is small but fast recovery area still has a lot free space
How much space do you have in the filesystem(s) where your online redo logs live? It's complaining about where it wants to restore the file, not where it's archived.
Dec
17
comment Oracle installation fails due to ORA-00443
Is there an alert log, or any trace files for the failing processes?