Graphical analysis of the creation, use, renaming and deletion of Oracle table space

This article brings you relevant knowledge about Oracle, which mainly introduces related issues about table spaces, including the creation, use, renaming, deletion of table spaces, etc. , let’s take a look at it, I hope it will be helpful to everyone.

Recommended tutorial: "Oracle Video Tutorial"

oracle table space

Explanation:

Oracle table space is logically structured under the database. Table spaces can be used to plan the database structure more flexibly. A database can contain multiple table spaces, and each table space can contain multiple data tables. The table space will have independent physical files, which allows users to decide the size, location, etc. of the table space.

(1) Create Oracle table space

create tablespace test datafile 'D:\Database\data\test_data.dbf' size 20M 
autoextend 
on next 5M
maxsize 500M;

Explanation:

To create a table space, you should use the create tablespace command;
test: table space Name;
datafile 'D:\Database\data\test_data.dbf': Specifies the physical file of the table space;
size 20M: Specifies the initial size of the physical file
autoextend: Specifies when the amount of data exceeds the physical file When the storage space is increased, the file size will be automatically increased;
on next 5M: Specify the size of each increase as 5M;
maxsize 500M: Specify the maximum size of the table space as 500M;

Note:

datafile: The 'D:\Database\data' path here must already exist, otherwise an error will be reported if the table space cannot be created;
When executing a SQL statement, the trailing ";" must be written! ! !

Inspection:

select tablespace_name,file_name from dba_data_files  order by file_name;

The query results are as shown in the figure:

## (2) Use of table space

A very important role of table space is to plan data tables. In other words, each data table is a child object of a certain table space. The real data of the data table also exists in the physical files of the table space. Therefore, understanding the rules for using table spaces is of great significance for clarifying the structure of the Oracle database.

After a user logs in to the database and creates a new data table without specifying which table space the table should be created in, the data table will be automatically created in the user's default table space. The default tablespace is equivalent to the user's workspace.

Get the default tablespace of all users in the database

select user_id,username,default_tablespace from dba_users order by username;

The query results are as shown in the figure:

Use the alter database command to modify the default of the database Table space

alter database default tablespace test;

Query the result again as shown in the figure:

Supplement:

The default table space for ordinary users has two sources:

The first is that it is allocated when creating a user or manually modified later;
The second is that it has never been allocated or modified, then the default table space of the database is used.
The default table space of Oracle 11g database is not USERS. Therefore, users created without specifying a default table space use the table space USERS. When the default table space of the database is modified to TEST, users created without specifying a default table space will use the table space TEST.

(3) Rename and delete table space

Rename table space {Rename table space 'test' but not test_data}

alter tablespace test rename to test_data;

View the table space in the database:

View the default table space of the user used in the database:

Delete Table space {delete table space test_data}

drop tablespace test_data including contents and datafiles;

Explanation:

The above sql statement is used to delete the table space and includes all content and data files in the table space.

Notes on deleting table space:

The table space can be deleted only when it is not the default table space of any data table, otherwise an error will be reported! ! !

So replace the default table space with USERS at this time;

alter database default tablespace "USERS";

Just delete it again;

Recommended tutorial: "

Oracle Video Tutorial"

The above is the detailed content of Graphical analysis of the creation, use, renaming and deletion of Oracle table space. For more information, please follow other related articles on the PHP Chinese website!