0

ALL,

In MS SQL Server and Sybase one can use use <db_name> to select current database on the server.

As far as I understand from googling, Oracle 11 can have only 1 DB.

But if I have latest Oracle - is there a way to select current DB?

TIA!!

I'm currently using 11g with ODBC. After I'm done with this test I will update Oracle to latest.

Igor
  • 5,620
  • 11
  • 51
  • 103
  • You might be looking for `alter session set container = ....` –  Jan 23 '21 at 19:02
  • 2
    The very term 'database' has a very different meaning/concept in oracle than it does in MSSQL or Sybase. – EdStevens Jan 23 '21 at 19:42
  • @EdStevens, can you elaborate a little? The DBMS server is a server applicatin that can have multiple DBs running. Now one can connect to the server and then select a DB to work with or connect straight to that DB. With the native client you can choose, with ODBC you connect to the DB instance. Is it not how Oracle performs? Also see an update of an OP. – Igor Jan 23 '21 at 19:47
  • read the [documentation](https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/index.html) – OldProgrammer Jan 23 '21 at 21:49
  • Oracle's "pluggable databases" is the closest match to SQL Server's "database". But pluggable databases are not available in the no longer supported version 11 - they were introduced with 12.1 (which is also no longer supported) –  Jan 23 '21 at 22:06

2 Answers2

1

In SQL Server, a "database" is synonymous with "schema": a collection of objects like tables, views, etc. In Oracle, "database" is synonymous with "server" or "instance" - the set of running processes and the data files they support (stand-alone, CDB, or PDB). In Oracle, a "user" that owns objects (tables, etc.) is synonymous with "schema", so an Oracle "user" is conceptually the same as a SQL Server "database". When connecting to Oracle whether using OCI, ODBC, or JDBC clients, you must always choose an instance (using SID or Service Name) and a user/schema.

To answer your question on determining the current schema in Oracle: when connected to an Oracle database instance, the current_schema session parameter determines which schema oracle will direct your queries to by default if you do not fully qualify your object names. It can be set using the following command:

alter session set current_schema=[username];

Existing session parameter values can be found by querying the sys_context function:

select sys_context( 'userenv', 'current_schema' ) from dual;
select sys_context( 'userenv', 'session_user' ) from dual;

See here for more on sys_context: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SYS_CONTEXT.html#GUID-B9934A5D-D97B-4E51-B01B-80C76A5BD086

For more on how Oracle schemas and SQL Server databases compare, see these previous posts on the subject from dba.stackexchange.com and stackoverflow.com:

pmdba
  • 6,457
  • 2
  • 6
  • 16
  • thank you for an explanation and links. As I saiid I am trying to connect with ODBC for now. ODBC interface has `SQLGetInfo()` which can return database name. And when I tried it - it didn't return it. I presume I can use `current_schema` from PL/SQL in your answer to use it. – Igor Jan 24 '21 at 03:31
  • By default, current_schema will be the same as the username you use to connect to Oracle. It will not change unless you explicitly set it as described above using the "alter session" command. You can always reference objects in other schemas by fully qualifying the object name with the schema name (e.g. _[owner].[table_name]_; myuser.mytable), or by creating synonyms in your schema that point to objects in other schemas. – pmdba Jan 24 '21 at 03:37
  • Also note that **1)** though common, it is generally considered a bad security practice for a running app to connect directly to a user/schema that owns objects; **2)** if your are connecting to a user that is referencing objects in other schemas, then that user must have explicit permission to access those objects, with privileges granted directly or through a role. Without the proper permissions, any reference to those objects will throw an error. – pmdba Jan 24 '21 at 12:26
1

Oracle has another set of terms. Now I'm talking about 11g. DBMS is database + instance. Database is a set of files on disk, it is your software, datafiles, control files and so on. Instance is a set of processes and memory structures, it is your running programs. When you start Oracle DBMS you start your instance, instance mounts database and opens database. It all runs on one server, so on one server you have one Oracle DBMS with one database and one instance. If you have RAC, you can have multiple instances for one database. That means, you have one server with the database and two or more servers with instances and RAC will synchronize your memory structures, transactions and so on between instances. So, in Oracle 11g there is no "current database" on the server. There is one database and one or more (if you have RAC) instances on different servers. When you connect to Oracle, you connect to an instance.

The separation of "databases" in terms of MS SQL Server in Oracle 11 is done by "schemas". Schema is generally a user, and database objects - tables and so on belong to a schema == an user. When you connect to Oracle you give a username - and it will be your default schema, where you will keep your database objects. Schema where you work in can be changed with "alter session".

Since Oracle 12 there are "pluggable databases" - PDBs. That means you have one CDB - container database as a root and you can plug many PDB to a CDB. That is more or less equivalent to the MS SQL Server "databases".

Vladimir.V.Bvn
  • 1,050
  • 1
  • 13
  • 13