Database instances, tablespaces, users, and their tables under Oracle

Oracle Database Composition:

Consists of two parts: Oracle database and database instance;

A database is a collection of data files, which is the Oracle background process/thread and the shared memory area allocated by the server;

Querying the current database name

1 SQL>Select from v$database; 2 3 NAME 4 -- ------- 5 ORCL

Querying the current DB instance name

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:

SQL>Select from v$instance;instance_name---------------- ORCL

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 a system tablespace) per 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:

Sql> SelectDefault_tablespace, Temporary_tablespace, D.username fromdba_users d;default_tablespace temporary_tablespace USERNAME------------------------------ ------------------------------ ------------------------------------------------- -------------------------------------------------------------------------------USERSTEMPoracle_ocmusersTEMPC # #WENUSERSTEMPojvmsysusersTEMPsyskmusersTEMPxs$NULLUSERSTEMPgsmcatuserusersTEMPmddatausersTEMPsysbackupusersTEMPC # #BINBUSERSTEMPdipusersTEMPSysdg

User

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 (defaults to 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;

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.

Database instances, tablespaces, users, and their tables under Oracle