1,600 reputation
1614
bio website stackexchange.com/users/98359
location United Kingdom
age 42
visits member for 3 years, 5 months
seen Jul 21 at 20:54

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.


Jul
8
comment ORA : 12519 No appropriate Service Handler
You don't need both SID and SERVICE_NAME. This ought to work with just the service name.
Jul
7
comment ORA : 12519 No appropriate Service Handler
Cross-posted on SO; that might be migrated here soon though.
Jun
25
comment Why does second instance of database not open?
Did you copy all the .dbf and .log files from test to orcl; and if so was test up at the time? What does your new init.ora look like? It might be useful to show the steps you went through in the order you did them.
Jun
11
comment Inner Join Performance
Please don't post the same question on multiple sites. There's a FAQ about this on meta.
May
27
comment How to startup a oracle database with sql plus or with any developer tool without connecting to the Database
How are you connecting (or trying to) at the moment? Are you sure the database is down and you have the right service name? Can you access the database server to check lsnrctl services or connect to the database locally, which you'll need to be able to do to start the database if it is down?
May
9
comment Oracle XE 11g (ORA-12514: TNS:listener does not currently know of service requested in connect descriptor )
I don't think you're quite getting to that point; looks like it would be after the connection is established, not during connection. Still means you can't use it of course, but doesn't explain what you're seeing. Maybe it is setting the user/password explicitly earlier, or doing something else weird.
May
9
comment Oracle XE 11g (ORA-12514: TNS:listener does not currently know of service requested in connect descriptor )
@AmirAvarzamani - I agree, I just don't understand what you're seeing, if amir/amir is getting ORA-01017. Just a thought.
May
9
comment Oracle XE 11g (ORA-12514: TNS:listener does not currently know of service requested in connect descriptor )
Do not grant anything to system, it has everything it needs - don't mess with built-in accounts or objects. For the other one you need at least grant create session to amir, which should let you log in to SQL*Plus. But I don't think that will resolve the ORA-01017 through WebGoat.
May
9
comment Oracle XE 11g (ORA-12514: TNS:listener does not currently know of service requested in connect descriptor )
Passwords are case sensitive, but it looks like you're using the same case as when you created the user. Have you granted connect privs to the new user? That would get a different error though. Not quite sure what's going on now... you can connect as amir/amir through SQL*Plus, or other clients? Maybe the app you're using is overriding the credentials somehow, but not familiar with it.
May
9
comment Oracle XE 11g (ORA-12514: TNS:listener does not currently know of service requested in connect descriptor )
And based on the other question you asked on DBA, does using amir/amir also get an ORA-01017?
May
9
comment Oracle XE 11g (ORA-12514: TNS:listener does not currently know of service requested in connect descriptor )
@AmirAvarzamani - your TNS-12514 error is gone. Are Amir-PC and localhost the same machine - all of this is on your own box, and Amir-PC resolves to 127.0.0.1? Otherwise you might be looking at two databases, which would explain a lot, but seems unlikely you'd have missed that.
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?