Prev: Traditional locking wisdom trashed.
Next: transaction tables consistent reads - undo records applied
From: Mladen Gogala on 24 Jun 2010 16:21
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(a)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(a)oracle17 ~]$ echo $ORACLE_SID
VMSO
[oracle(a)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(a)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.com
 | 
Pages: 1
Prev: Traditional locking wisdom trashed.
Next: transaction tables consistent reads - undo records applied