Re: understanding oracle terminology - instance, database, sid, schema
Date: Thu, 24 Jun 2010 20:21:04 +0000 (UTC)
Message-ID: <pan.2010.06.24.20.21.03_at_email.here.invalid>
On Thu, 24 Jun 2010 08:24:08 -0700, The Quiet Center wrote:
> hi, I'm an open source developer who has been cast into the wide wide
> world of Oracle. As a former MySQL user mainly (no boos please :), there
> is a lot of Oracle terminology that I dont understand.
No boos, both databases are owned by Oracle Corp. The customers paying for the enterprise versions of MySQL are the prime targets for the Oracle sales assassins. It's good that you remained within the Oracle family.
>
> Specifically:
> - instance
One copy of software managing one database.
> - database (it appears that a single host and port can have several
> databases)
Yes, one host and port can be used to connect to several instances. Each instance manages a single database. There can be databases managed by more than one instance, like this:
SQL> select inst_id,instance_name,host_name,version,status 2 from gv$instance order by inst_id;
INST_ID INSTANCE_NAME HOST_NAME VERSION STATUS
---------- ---------------- ---------- ----------------- ------------ 1 ADPRD1 oracle14 10.2.0.4.0 OPEN 2 ADPRD2 oracle15 10.2.0.4.0 OPEN 3 ADPRD3 oracle13 10.2.0.4.0 OPEN
Elapsed: 00:00:00.10
SQL>
That kind of database is called "screwed" database.
> - sid
SID= System IDentification is the identifier that uniquely identifies an oracle instance. It is embedded into the process names: [oracle_at_oracle17 ~]$ ps -ef|grep $ORACLE_SID|grep -v grep
oracle 19798 1 0 Apr18 ? 00:03:14 ora_pmon_VMSO oracle 19800 1 0 Apr18 ? 00:12:18 ora_vktm_VMSO oracle 19804 1 0 Apr18 ? 00:00:05 ora_gen0_VMSO oracle 19806 1 0 Apr18 ? 00:00:13 ora_diag_VMSO oracle 19808 1 0 Apr18 ? 00:00:06 ora_dbrm_VMSO oracle 19810 1 0 Apr18 ? 00:00:48 ora_psp0_VMSO oracle 19812 1 0 Apr18 ? 02:09:25 ora_dia0_VMSO oracle 19814 1 0 Apr18 ? 00:00:05 ora_mman_VMSO oracle 19816 1 0 Apr18 ? 00:01:38 ora_dbw0_VMSO oracle 19818 1 0 Apr18 ? 00:01:34 ora_dbw1_VMSO oracle 19820 1 0 Apr18 ? 00:03:55 ora_lgwr_VMSO oracle 19822 1 0 Apr18 ? 00:07:50 ora_ckpt_VMSO oracle 19824 1 0 Apr18 ? 00:02:36 ora_smon_VMSO oracle 19826 1 0 Apr18 ? 00:00:04 ora_reco_VMSO oracle 19828 1 0 Apr18 ? 00:08:56 ora_mmon_VMSO oracle 19830 1 0 Apr18 ? 00:08:04 ora_mmnl_VMSO oracle 19832 1 0 Apr18 ? 00:00:04 ora_d000_VMSO oracle 19834 1 0 Apr18 ? 00:00:04 ora_s000_VMSO oracle 19844 1 0 Apr18 ? 00:00:03 ora_qmnc_VMSO oracle 19858 1 0 Apr18 ? 00:06:22 ora_cjq0_VMSO oracle 19862 1 0 Apr18 ? 00:00:04 ora_q001_VMSO oracle 19914 1 0 Apr18 ? 00:00:06 ora_smco_VMSO oracle 28716 1 0 Apr20 ? 00:00:03 ora_q002_VMSO oracle 21453 1 0 15:17 ? 00:00:01 ora_j001_VMSO oracle 21496 1 0 15:21 ? 00:00:02 ora_j000_VMSO oracle 22102 1 0 16:06 ? 00:00:00 ora_w000_VMSO[oracle_at_oracle17 ~]$ echo $ORACLE_SID VMSO
[oracle_at_oracle17 ~]$
> - schema (it appears that a single database can have several schemas and
> that a schema is a collection of tables, views, triggers, etc)
That is precisely what the schema is. In oracle, schema corresponds to a username.
- user Users corresponds to the schemas. In the brave new world of Oracle, user==schema.
> (it appears that a user can have access to various schemas and various
> permissions within schemas... contrast with MySQL where there are only
> databases)
Yes, there are commands like GRANT and REVOKE which are used to grant privileges to your neighbor's objects. In the world of Oracle, it is not illegal to covet thy neighbor's objects, if he grants you the privilege to do so.
SQL> select count(*) from sys.obj$;
select count(*) from sys.obj$
*
ERROR at line 1:
ORA-00942: table or view does not exist
Elapsed: 00:00:00.09
SQL> connect system_at_vmso
Enter password:
Connected.
SQL> select count(*) from sys.obj$;
COUNT(*)
73440
Elapsed: 00:00:00.11
SQL>
>
> Beyond definitions for the above, my goal is to ask: how do you uniquely
> identify a schema? We are running both 10g and 11g here and so we cannot
> truly refer to a schema by the name alone without qualifying it with the
> database. But since the word "instance" is being thrown around here as
> well, I have to wonder what is meant by that also.
>
> Thanks,
> Terrence
There are database links. You can create a DB link and use it to retrieve the data from the remote database. You also have synonyms which can be used to hide the implementation details.
-- http://mgogala.byethost5.comReceived on Thu Jun 24 2010 - 15:21:04 CDT