45

How do I query the database name in Oracle SQL Developer? I have tried the following and they all fail:

SELECT DB_NAME();

SELECT DATABASE();

Why do these basic MySQL queries fail in SQL Developer? Even this one fails too:

show tables;

EDIT: I can connect to the database and run queries such as:

select * from table_name_here;

EDIT 2: The database type is Oracle, this is why MySQL queries are failing. I thought it was related to the database client not the database itself. I was wrong. I'll leave the question as is for other as lost as I was.

Improve this question
6
  • 2
    Everyone has their own extensions to basic SQL, and mysql's no exception. Commented Jan 23, 2012 at 20:25
  • I'm realizing this more and more! This is using an Oracle database. I'm not sure if there's another name for that. Commented Jan 24, 2012 at 13:52
  • 6
    Figured it out once I recognized I'm running an Oracle database, not a MySQL one: select * from v$database; and select ora_database_name from dual; Commented Jan 24, 2012 at 14:04
  • If you're going to minus the question, please indicate why. Perfectly legitimate question seeing I didn't know the answer when I asked it, and the discussion within here lead me to the answer. Commented Jan 24, 2012 at 17:11
  • 1
    @MatthewDoucette: not even knowing what RDBMS you were connected to probably made people waste their time trying to figure out your problem. I can understand why people would see this negatively. And as you said, once you realize you're talking to an Oracle server, finding the answer to your question is a trivial search. Commented Jan 24, 2012 at 17:27

7 Answers 7

Reset to default
78

Once I realized I was running an Oracle database, not MySQL, I found the answer

select * from v$database;

or

select ora_database_name from dual;

Try both. Credit and source goes to: http://www.perlmonks.org/?node_id=520376.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Note: apparently the security rights to these are not the same. In my case select ora_database_name from dual works, the other gets an invalid table error.
the result from these two sql is different....which is correct?
22

try this:

select * from global_name;
Improve this answer

1 Comment

this allows to query even through a dblink: select global_name from global_name@dblink_identifier
10

You can use the following command to know just the name of the database without the extra columns shown.

select name  from v$database;

If you need any other information about the db then first know which are the columns names available using

describe v$database;

and select the columns that you want to see;

Improve this answer

Comments

3

I know this is an old thread but you can also get some useful info from the V$INSTANCE view as well. the V$DATABASE displays info from the control file, the V$INSTANCE view displays state of the current instance.

Improve this answer

Comments

0

Edit: Whoops, didn't check your question tags before answering.

Check that you can actually connect to DB (have the driver placed? tested the conn when creating it?).

If so, try runnung those queries with F5

Improve this answer

2 Comments

I can connect to it and run queries and have been for months. Just added that in the question.
The answer, once I realized I was running an Oracle database: 'select * from v$database;' and/or 'select ora_database_name from dual;'
0

To see database name, startup;

then type show parameter db_name;

Improve this answer

Comments

0

DESCRIBE DATABASE NAME; you need to specify the name of the database and the results will include the data type of each attribute.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.