1,655 reputation
1615
bio website stackexchange.com/users/98359
location United Kingdom
age 42
visits member for 3 years, 8 months
seen yesterday

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.


Feb
22
answered Startup NOMOUNT and Startup MOUNT on dataguard oracle
Feb
22
comment EMCA port Issue on Installing Oracle on Windows Server 2008 guested on VMware
SYSDBA is not a user, it's a role. Do sqlplus /nolog, then from the SQL> prompt, connect / as sysdba. Please read at least the 2-day DBA guide for the basics. Your dbca/emca error suggests those ports are already in use; do you already have a database running on this VM?
Feb
10
comment exporting oracle tables on command line as sql files
I'd also suggest this sounds backwards. You seem to want the schema and data in your database to be the 'master'. It would be simpler to keep your DDL and DML (presumably for initial base data) statements in files that you maintain outside the database, and check those in; and make changes to your DB using those files. Making ad hoc changes and then trying to track them afterwards sounds painful. But, just my opinion of course.
Feb
10
comment exporting oracle tables on command line as sql files
Well, here's a list of reasons this isn't simple, and here's Tom Kyte's view; that has some attempts at generating the statements dynamically, but I'd be very careful about checking anything you find like this works for all your data *8-)
Feb
10
comment exporting oracle tables on command line as sql files
No, you'd need to write your own extract scripts. You can get the DDL with the dbms_metadata package, but you'll need to generate your own insert statements. (Or find an automated dynamic example on the 'net somewhere). What's wrong with a binary dump file though?
Feb
8
awarded  Yearling
Jan
29
comment Oracle DB Shutdown issue
@PhilSumner - yeah, it's just habit, not sure if it was ever advised but I've been doing it since at least 7. I don't do it often enough to have investigated if there was actually any benefit - I suspected not!
Jan
29
comment Oracle DB Shutdown issue
From the docs, with immediate: "Current client SQL statements being processed by the database are allowed to complete". I don't know if there's any real basis for this, but if I ever have to shutdown abort, I tend to do a startup followed by ` shutdown immediate` - gives me a bit more confidence that everything is OK, even if it doesn't really add anything...
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 does Oracle 10g use indexes?
@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 does Oracle 10g use indexes?
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.