Oracle databases, instances, users, tablespaces, and tables

Oracle databases, instances, users, tablespaces, and tables

1. Database

A database is a collection of data. Oracle is a database management system and a relational database management system.

Generally, the "Database" refers to not only physical data sets, but also physical data and database management systems. That is, the combination of physical data, memory, and operating system processes.

Query the current database name:

Select name from v $ database;

2. database instances

Official Oracle Description: an instance is a part of the computer memory required to access the Oracle database and assists with background processes. It is a collection of processes and the memory used by these processes (SGA.

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

When we access Oracle, we access an instance. However, if this instance is associated with a database file, it is accessible. If it does not, it will get an error of instance unavailability.

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

Query the current database instance Name:

Select instance_name from v $ instance;

The database instance name (instance_name) is used for external connections. To contact the database in the operating system, you must use the database instance name. For example, for development, to connect to the database, you need to connect to the database instance Name:

Jdbc: oracle: thin: @ localhost: 1521: orcl (orcl is the database instance name)

A database can have multiple instances and can be used as a database service cluster.

3. tablespace

Oracle databases store physical tables through tablespaces. One database instance can have N tablespaces and one tablespace can have N tables.

With the database, you can create a tablespace.

A table space is the logical division of a database. Each database has at least one table space (called a SYSTEM table space ). To facilitate management and improve operation efficiency, some additional table spaces can be used to divide users and applications. For example, the USER tablespace is used by general users, and the RBS tablespace is used by rollback segments. A tablespace can belong to only one database.

Create tablespace Syntax:

Create TableSpace name

DataFile tablespace data file path

Size tablespace initial Size

Autoextendon

For example:

Create tablespace db_test

Datafile 'd: \ oracle \ product \ 10.2.0 \ userdata \ db_test.dbf'

Size 50 m

Autoextend on;

View the created tablespace:

Select default_tablespace, temporary_tablespace,

D. username from dba_users d;

View the default tablespace of the current user:

Select

Username, default_tablespace from user_users;

4. Users

After the Oracle database is created, to create a table in the database, you must first create a user for the database and specify a tablespace for the user.

We have created databases and tablespaces above, and then we will create users:

Create a new user:

Create user Username

IDENTIFIEDBY Password

Default tablespace (default users)

Temporary tablespace (default TEMP)

For example:

Create user utest

Identified by utestpwd

Default tablespace db_test

Temporary tablespace temp;

(The temporary tablespace cannot use the db_test we created. Why ?)

If you want to use your account to manage your tablespace, you must grant the following permissions:

Grant connect to utest;

Grant resource to utest;

GRANT dba TO utest; -- dba is the highest level of permissions. You can create databases and tables.

View database users:

Select * from dba_users;