1,635 reputation
1614
bio website stackexchange.com/users/98359
location United Kingdom
age 42
visits member for 3 years, 6 months
seen Aug 19 at 22:12

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