Index usage in different databases

[pdtt]

Hi,

I do most of my work in MS SQL and Sybase so these two questions may not make much sense in Oracle.

I am loading data from a remote server on to a local Oracle server. Once on the local server I am transforming the data.
In MS SQL and Sybase, I usually put my initial loaded data into a seperate database on the local server called "staging". I then transform the data into the final database.

Question 1) When using Oracle, would you be able to have two distinct databases on the same server? The Oracle users seem confused when I say this. They only seem to react to different "schemas" on the same server. Would I just move the staging tables into a staging schema and then transform the data into my reporting schema?

Question 2) If you can have more than one database on an Oracle server, when querying between databases, will a query executed in database "A" that selects data from database "B" use the indexes on the table(s) in Database B?

Any advice is appreciated.

 

[carp]

Yes, you can have more than one database on a server.

A schema is defined at the database level. If you want to run a query that involves more than one database, you will need to create a database link from one database to the other and reference this link in your query. Otherwise, the query will only look within its own database.

 

[shiongss]

Hi,

How to create a link between 2 database?

Last time i try to use a SQL statement to cross database, eg:

Insert into databaseA.tableA
select * from databaseB.tableA

both table have a same set of data type and structure, i used to compile following scripts in SQL Server and Sybase which have no problem, but when i try to compile it in Oracle database, it gave an error msg, i forget what was the message already....

Thanks.

 

[sem]

In fact the thing called DATABASE in Sql Server is about the same as SCHEMA in Oracle. Each schema maintais its own NAMESPACE, thus you're not limited by existing table names and all schemas may be completely independent and selfsufficient. Each resource may be referenced within its schema by [short] name or globally by fully qualified name (shema_name.resource_name). Moreover, you may expose your resources to other database via dblink.
So, you do not need to create a number of databases, just a number of schemas (users) within a single database. Of course, you may run a number of databases and connect them via dblink, but I suppose it's more than redundant. Regards, Dima

 

[pdtt]

Thank you for the answers.
It clears up a few things in my mind.

For shiongss you can
create an Oracle database link in the database you are running the query from with this statement: create public database link production.world connect to DIS using 'production';

where 'production.world' is the Oracle Net string and
'DIS' is the user

your query would then be Insert into databaseA.tableA
select * from tableA@production.world;