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

This article transferred from: http://www.cnblogs.com/adforce/p/3312252.html

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 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:

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:

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 tablespace name  datafile          table space 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' 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:

User username by            password  DEFAULT     

Such as:

USER utest by   utestpwd  DEFAULT     

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

to Utest;  toutest;  --DBA is the most privileged, can create databases, tables, etc. 

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