How to Query Database Name in Oracle SQL Developer

How to Query Database Name in Oracle SQL Developer?

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.

Checking oracle sid and database name

I presume SELECT user FROM dual; should give you the current user

and SELECT sys_context('userenv','instance_name') FROM dual; the name of the instance

I believe you can get SID as SELECT sys_context('USERENV', 'SID') FROM DUAL;

How to find Oracle Service Name

Overview of the services used by all sessions provides the distionary view v$session(or gv$session for RAC databases) in the column SERVICE_NAME.

To limit the information to the connected session use the SID from the view V$MYSTAT:

select SERVICE_NAME from gv$session where sid in (
select sid from V$MYSTAT)

If the name is SYS$USERS the session is connected to a default service, i.e. in the connection string no explicit service_name was specified.

To see what services are available in the database use following queries:

select name from V$SERVICES;
select name from V$ACTIVE_SERVICES;

How to find database name in oracle without using sqlplus or srvctl?

Without even understanding why you'd need this, which is kind of odd, a way to do this would be ( keeping in consideration that you are using Oracle RAC )

The utility in this case is csrsctl

Example

srvctl status database -d otcgr2ng
Instance otcgr2ng1 is running on node scglvdoraci0009
Instance otcgr2ng2 is running on node scglvdoraci0010
db_name=$(crsctl stat res -t | grep ".db$" | grep -v mgmt | awk -F '.' '{print $2}')
srvctl status database -d ${db_name}
Instance otcgr2ng1 is running on node scglvdoraci0009
Instance otcgr2ng2 is running on node scglvdoraci0010

What I did was using crsctl to get the database name. Obviously, it would only work in your scenario that you have one database.

CRSCTL is an interface between you and Oracle Clusterware, parsing and calling Oracle Clusterware APIs for Oracle Clusterware objects. You can use CRSCTL commands to perform several operations such as Starting and stopping Oracle Clusterware resources, Enabling and disabling Oracle Clusterware daemons, Checking the health of the cluster, etc...

I remove the mgmtdb which is an internal database of the Grid Infrastructure. If you had more than one database in Oracle RAC , then

for db_name in $(crsctl stat res -t | grep ".db$" | grep -v mgmt | awk -F '.' '{print $2}')
do
echo $db_name
done

How to find schema name in Oracle ? when you are connected in sql session using read only user

To create a read-only user, you have to setup a different user than the one owning the tables you want to access.

If you just create the user and grant SELECT permission to the read-only user, you'll need to prepend the schema name to each table name. To avoid this, you have basically two options:

1. Set the current schema in your session:

ALTER SESSION SET CURRENT_SCHEMA=XYZ

2. Create synonyms for all tables:

CREATE SYNONYM READER_USER.TABLE1 FOR XYZ.TABLE1

So if you haven't been told the name of the owner schema, you basically have three options. The last one should always work:

1. Query the current schema setting:

SELECT SYS_CONTEXT('USERENV','CURRENT_SCHEMA') FROM DUAL

2. List your synonyms:

SELECT * FROM ALL_SYNONYMS WHERE OWNER = USER

3. Investigate all tables (with the exception of the some well-known standard schemas):

SELECT * FROM ALL_TABLES WHERE OWNER NOT IN ('SYS', 'SYSTEM', 'CTXSYS', 'MDSYS');

Find the server name for an Oracle database

SELECT  host_name
FROM    v$instance

How can I get column names from a table in Oracle?

You can query the USER_TAB_COLUMNS table for table column metadata.

SELECT table_name, column_name, data_type, data_length
FROM USER_TAB_COLUMNS
WHERE table_name = 'MYTABLE'

How to display databases in Oracle 11g using SQL*Plus

You can think of a MySQL "database" as a schema/user in Oracle. If you have the privileges, you can query the DBA_USERS view to see the list of schemas:

SELECT * FROM DBA_USERS;

---

Related Topics