understanding oracle terminology - instance, database, sid, schema
The Quiet Center
world of Oracle. As a former MySQL user mainly (no boos please :),
there is a lot of Oracle terminology that I dont understand.
Specifically:
- instance
- database (it appears that a single host and port can have several
databases)
- sid
- schema (it appears that a single database can have several schemas
and that a schema is a collection of tables, views, triggers, etc)
- user (it appears that a user can have access to various schemas and
various permissions within schemas... contrast with MySQL where there
are only databases)
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
The Quiet Center
wrote:
> there is a lot of Oracle terminology that I dont understand.
> Specifically:
> - instance
Found it - http://www.orafaq.com/wiki/Database_Concepts_and_Architecture
An instance can mount and open one and only one database.
rogergorden@....gmail.com
A handy conversion chart:
MySQL ORACLE Notes
instance instance/ An oracle database is simply the processes
and memory structures running on the machine, Oracle can have more
database than one database instance running on
the same machine or from the same Oracle home/version. This is also
true for
MySQL as we have both as long as
you're on a different port and socket. I've had 4 different MySQL
instances running
on the same machine.
database schema A collection of related tables/views etc.
sid A unique identifier of an instance/
database on a server DEMO and DEMO10G would be valid names for 2
different
instances. You can emulate this in
MySQL by having aliases that will connect to different MySQL instances
running on
different sockets and ports
user user a user can have access to various
schemas and various permissions within schemas in oracle. In MySQL a
user can
have access to various databases
and various permissions.
I hope that helps.
Roger Gorden
joel garry
> On Jun 24, 11:24 am, The Quiet Center <thequietcen...@gmail.com>
> wrote:
>
> > there is a lot of Oracle terminology that I dont understand.
> > Specifically:
> > - instance
>
> An instance can mount and open one and only one database.
The database concepts manual is required reading first. After that,
check out books by Tom Kyte, he is good at relating concepts you
already know to their Oracle equivalents. He also rewrote the
concepts manual for 11g, btw.
Over the years, I've noticed the most important thing for developers
coming from other dbms's is to understand Oracle's locking and
concurrency model.
jg
--
@home.com is bogus.
http://groups.google.com/group/comp.os.linux.development.system/msg/30ff122e2dd33c69?dmode=source
rogergorden@....gmail.com
I've also seen that developers coming from other rdbms's should
understand is Oracle's Sequence, rather than "MAX(id) + 1" for
inserts.
Roger Gorden
The Boss
Not exactly an answer to your questions, but I guess following
document might help you comparing Oracle vs. MySQL:
http://download.oracle.com/docs/cd/E12151_01/doc.150/e12155/overview_mysql.htm
HTH
--
Jeroen
Mladen Gogala
> 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@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@oracle17 ~]$ echo $ORACLE_SID
VMSO
[oracle@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@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.