1,695 reputation
1615
bio website stackexchange.com/users/98359
location United Kingdom
age 42
visits member for 3 years, 9 months
seen Nov 20 at 18:29

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.


May
9
comment Oracle XE 11g (ORA-12514: TNS:listener does not currently know of service requested in connect descriptor )
@AmirAvarzamani - what is ${USER} set to, also system/system? Is that actually your system password; and can you try it with other non-privileged credentials that you know work locally?
May
9
answered Oracle XE 11g (ORA-12514: TNS:listener does not currently know of service requested in connect descriptor )
May
9
comment Oracle XE 11g (ORA-12514: TNS:listener does not currently know of service requested in connect descriptor )
Having /${USER} at the end isn't valid for a native JDBC connection string, but might be for HSQL (also commented out). Invalid logon is a very different error - just confirm it now gives you ORA-01017 not TNS-12514? If so then adding /XE did work (not sure why as (XE) is valid in the listener.ora), but your system password is not system. If USER is set somewhere to a valid username/password, perhaps jdbc:oracle:thin:${USER}@//localhost:1521/XE will work, but depends where the substitution is done.
May
9
comment Oracle XE 11g (ORA-12514: TNS:listener does not currently know of service requested in connect descriptor )
Then that isn't quite what you showed at the start of the question; the jdbc:oracle:thin:@127.0.0.1:1521:xe/${USER} is commented out, it's using jdbc:oracle:thin:@localhost:1521? You don't have a service name in that, so it'll use the DEFAULT_SERVICE_LISTENER, which is XE... so it's the same really but the ${USER} isn't relevant. I think. Unless it isn't valid to have it in parentheses, in the listener.ora, as (XE)? Does it make any difference if you specify /XE in that parameter?
May
2
comment SQL Query Rewrite - Help Needed (Oracle)
You did say the size was for the DB; but the tables don't seem very big, from the rows in the plan output, so I don't see the correlation. Are those accurate or are your stats out of date?
May
2
comment SQL Query Rewrite - Help Needed (Oracle)
Is the size of these tables proportional to the overall size of the database - saying one is 400G and the other 600G doesn't mean much if these tables are the same size in each. Is the plan in the 'fast' database the same as in the 'slow' one, and do you a reason to think it's sub-optimal - do you know something about the data that Oracle seems to not be taking into account from its stats?
May
2
answered How can I select rows from a hierarchical query with the lowest level?
Apr
24
comment How can I find out what makes my archived redo logs grown so fast
So what is your backup strategy? Archived logs can be removed once they've been backed up but you obviously don't want to risk removing something you shouldn't, making recovery impossible. If they aren't being cleaned out then they will grow continuously - that doesn't imply there's something wrong with your system, just that normal activity is creating redo activity, which is being archived and not cleared down.
Apr
23
comment How can I find out what makes my archived redo logs grown so fast
Do you really mean your redo logs, or do you mean your undo tablespace? Or your archived redo logs, perhaps? What are you measuring, and how are you resizing?
Apr
2
comment Oracle RAC not starting up after reboot of system
Maybe you should put the details of what you found and did as an answer? Not sure I can useful convert my vague hints into anything coherent or helpful to future visitors...
Apr
2
comment Oracle RAC not starting up after reboot of system
And it shows free space now? It sounds like it think's it's full. How did you delete the archived logs? Are there any other full mount points?
Apr
2
comment Oracle RAC not starting up after reboot of system
OK, so is your resized archive destination mounted, and is it still owned by the Oracle user?
Apr
2
comment Oracle RAC not starting up after reboot of system
What is in the alert log, particularly at the point you get the ORA-03113? Why was a hard reboot necessary?
Mar
18
revised maximum number of interval partitions in Oracle 11g
Added note about maximum value
Mar
18
comment maximum number of interval partitions in Oracle 11g
Isn't your interval(1) clause the deciding factor here, once the fixed range partitions have been counted? The legal intervals are determined by that combination, even if the interval partitions are only theoretical at this point - they don't really exist until data is inserted. But yes, it's an important distinction, that the number of intervals and the interval size effectively constrain the maximum value allowed in the partition key column.
Mar
18
awarded  oracle
Mar
17
answered maximum number of interval partitions in Oracle 11g
Mar
3
comment I have mistakenly deleted Datafiles.
Did you actually restore the files, and to the right place? The messages suggest that they maybe still don't exist where expected, or have the wrong ownership. Recovery doesn't magically reinstate the deleted files, it makes the database consistent (hopefully) once you have put an old copy back, by reapplying redo logs. If this is new to you, try not to make things worse by experimenting. But, does 'the files are not important' mean that you don't care about the data in the tablespaces the specific deleted files relate to?
Feb
28
comment Startup NOMOUNT and Startup MOUNT on dataguard oracle
@DevYudh - pretty sure; it goes through these stages on a normal startup. Is there a reason you think there is a difference?
Feb
22
comment EMCA port Issue on Installing Oracle on Windows Server 2008 guested on VMware
Don't post it here; read it, see what it says is wrong, see if it has more detail than the dialog, and then decide how to proceed...