"Go" Oracle-database instance, table space, user, table relationships

"Go" Oracle-database instance, table space, user, table relationships

A complete Oracle database is typically made up of two parts: an Oracle database and a database instance.
1) The database is a collection of a series of physical files (data files, control files, online logs, parameter files, etc.);
2) The Oracle DB instance is a set of Oracle background processes/threads and the shared memory area allocated on the server.

When you start an Oracle database server, you are actually creating an Oracle instance in the server's memory (that is, allocating shared memory in server memory and creating related back-end memory), and then this Oracle DB instance accesses and controls the data files on the disk. Oracle has a large memory that is fast and becomes the Global Zone (SGA).

I. Database, table space, data files

1. Database

A database is a collection of data. Oracle is a database management system, which is a relational database management system.
Usually we call the "database", and not only refers to the physical data set, he contains physical data, database management system. This is the combination of physical data, memory, and operating system processes.

When we install the Oracle database, we choose to install the startup database (that is, the default global database) such as:

Global database name: is the identity of a database, in the installation of the need to think well, later generally do not modify, change is also troublesome, because once the database is installed, the database name is written into the control files, database tables, many places will use the database name.

start the database: also called the Global database, is the entry of the database system, it will be built with some advanced permissions of users such as Sys,system. With these advanced rights accounts, we can create table spaces, users, and tables in a DB instance.

Query the current database name:

Select  from v$database;

2. DB instance

With Oracle official description: An instance is a subset of the computer memory and secondary processing background processes required to access an Oracle database, which is a collection of processes and the memory (SGA) used by those processes.

It is actually a piece of process used to access and use the database, it only exists in memory. Just like the new instance object in Java.

We visit Oracle to access an instance, but if the instance is associated with a database file, it can be accessed, and if not, you will get an error that the instance is not available.

The instance name refers to the name of the database management system that is used in response to a database operation. She is also called SID. The instance name is determined by the parameter instance_name.

Query the current DB instance name:

Select  from V$instance;

The database instance name (instance_name) is used for external connections. to get a connection to the database in the operating system, you must use the database instance name . For example, to connect to a database, we have to connect to the database instance name:

Jdbc:oracle:thin:@localhost:1521: ORCL (ORCL is the DB instance name)

A database can have multiple instances, which can be used when serving as a database service cluster.

3. Table Space

An Oracle database is a table space for storing physical tables, a database instance can have n table spaces, and a table space can have n tables .

With the database, you can create table spaces.

A tablespace (tablespace) is a logical division of a database, with at least one table space (called the system tablespace) for each database. To facilitate management and improve operational efficiency, you can use additional tablespaces to divide users and applications. For example, the user table space is for general users and the RBS table space is used for rollback segments. A table space can belong to only one database.

CREATE TABLE space Syntax:

Create tablespace table space name  DataFile          table Spatial data file path  size              table space Initial size   on 

Such as:

Create tablespace db_test   ' D:\oracle\product\10.2.0\userdata\db_test.dbf '   size 50m  on;

To view the table spaces that have been created:

Select Default_tablespace, Temporary_tablespace, D.username    from Dba_users D

4. Users

After the Oracle database is built, to build the table in the database, you must first establish the user for the database and specify the table space for the user.

Above we have built the database and table space, next build users:

To create a new user:

CREATE USER           user name   by          password   DEFAULT  tablespace   tablespace ( Default users)  Temporary

Such as:

CREATE USER utest    by utestpwd   DEFAULT tablespace db_test   Temporary Temp

With the user, to use the user account to manage their own table space, but also to give it sub-permissions:

GRANT  to utest;   GRANT  to utest;   GRANT  to Utest; -- DBA is the highest privilege and can create databases, tables, and so on. 

To view database users:

Select  *  from Dba_users;

5. Table

With a database, table space, and users, you can create tables in your own table space with a custom user. With the table, we can develop it.

Reprint: http://yuwenlin.iteye.com/blog/1882988

"Go" Oracle-database instance, table space, user, table relationships