CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Jayhoffa75
Jayhoffa75

Reputation: 31

How do I use multiple schemas in an Oracle query?

Problem: Needing to use data in two (or more) different schemas or connections in Oracle SQL Developer. For example: Invoice data in the finance schema, and sales data in the inventory schema. Needing to get to product info for the invoice data, via a join of a invoiced product to the product in the inventory schema.

I am new to Oracle, coming from SQL Server. In T-SQL, I would just put the database name or the IP address. For example (random, made-up IP): 

[555.00.12302].Sales_DB.Item_Table.Item_Price_Column

In SQL Developer for Oracle (using 11g), I cannot figure out how to call another connection or schema, in a select query, to join in a table from that schema. Is this possible?

Upvotes: 3

Views: 22505

Answers (1)

Justin Cave
Justin Cave

Reputation: 231861

I'm pretty sure that you don't mean "tablespace"-- you don't reference the tablespace in which an object is stored when you query it. Some objects are stored in multiple tablespaces. You might mean "schema" or you might mean "database" where a schema in Oracle is most similar to a database in SQL Server.

If you want to access a table stored in a different schema in the same database

select *
  from schema_name.table_name

assuming you have privileges on table_name in the schema_name schema. You can create public or private synonyms or change your session's current_schema to avoid prefixing the schema name but that's all just syntactic sugar for the fully qualified name.

If you really want to access data stored in a different database, then you'd need to create a database link. This is an object that is stored in the database that creates a connection to another database. There are lots of options when creating a database link. One option, assuming the same user exists on both databases with the same password in each

CREATE DATABASE LINK my_link
  CONNECT TO CURRENT_USER
  USING '<<TNS alias of remote database>>';

SELECT *
  FROM schema_name.table_name@my_link;

Upvotes: 4

Related Questions