Manage tablespaces and data files <6>

Database Management-manage tablespaces and data files

? Introduction

Tablespace is a logical component of the database. Physically, the database data is stored in the data file; logically, the database is stored in the tablespace, and the table

A space consists of one or more data files.

Logical structure of the database

? Introduction

The Logical Structure in Oracle includes tablespaces, segments, partitions, and blocks.

The database is composed of tablespaces, while the tablespace is composed of segments. The segments are composed of partitions, And the partitions are composed of Oracle blocks.

To improve database efficiency.

To make everyone understand, we can draw a picture to illustrate the logical relationship: see the figure:

Tablespace

? Introduction

Tablespaces are used to logically organize data in databases. The database is logically composed of one or more tablespaces. Table space can serve the following purposes:

1. Control the disk space occupied by the database

2. DBA can deploy different data types to different locations, which improves I/O performance and facilitates management operations such as backup and recovery.

? Create a tablespace

The crate tablespace command is used to create a tablespace. In general, the creation of a tablespace is executed by a privileged user or dBA, as shown in figure

If other users are used to create a tablespace, the user must have the system permission to create tablespace.

? Create a data table space

After creating a database, it is best to create your own tablespace to facilitate table management.

Create tablespace data01 datafile 'd: \ test \ dada01.dbf 'size 20 m uniform size 128 K;

Note: After executing the preceding command, a tablespace named data01 is created and a data file named data01.dbf is created for the tablespace. The partition size is 128 kb.

? Use data table space

Create Table mypart (deptno number (4), dname varchar2 (14), Loc varchar2 (13) tablespace data01;

? Change the tablespace status

When a tablespace is created, the tablespace is in the online status. At this time, the tablespace is accessible and can be read and written, that is, the tablespace can be queried.

The data of the tablespace, and various statements can be executed in the tablespace. However, during system maintenance or data maintenance, the tablespace status may need to be changed. I

In general, privileged users or DBAs operate.

1. Take the tablespace offline

Alter tablespace name offline;

2. Bring the tablespace online

Alter tablespace name online;

3. Read-Only tablespace

When a tablespace is created, the tablespace can be read and written. If you do not want to perform the update, delete, and insert operations on the tablespace, you can change the tablespace

Read

Alter tablespace name read only;

(To be writable, It is alter tablespace name read write ;)

? Change the tablespace status

Let's give you an example to illustrate the read-only feature:

1. Know the tablespace name and display all tables included in the tablespace

Select * From all_tables where tablespace_name = 'tablespace name ';

2. Know the table name and view the table that belongs to the tablespace.

Select tablespace_name, table_name from user_tables where table_name = 'emp ';

2. We can know that Scott. EMP is in the system tablespace. Now we can change system to read-only, but we won't succeed because System

It is a system tablespace. If it is a common tablespace, we can set it as a read-only one. We can give you a demonstration to enhance our understanding.

3.

4. Make the tablespace readable and writable

Alter tablespace name read write;

? Delete a tablespace

Generally, privileged users or DBAs operate on the table. If the operation is performed by another user, the user is required to have the system permission to drop tablespace.

Drop tablespace 'tablespaces 'including contents and datafiles;

Note: Including contents indicates that all database objects in the tablespace are deleted when the tablespace is deleted, while datafiles indicates that the database files are also deleted.

? Extended tablespace

A tablespace is composed of data files. The tablespace size is actually the size after the data files are added. As we can imagine, assume that the table employee is stored in

In the data01 tablespace, the initial size is 2 m. when the data is full of 2 m space, if the data is inserted into the employee table, the error of insufficient space is displayed.

Case study:

1. Create a tablespace sp01

2. Create a common table named mydment on the tablespace. Its structure is the same as that of Dept.

3. insert into mydment select * from Dept into the table;

4. What should I do if there is a problem that cannot be expanded?

5. Expand the tablespace to add more storage space. There are three methods:

1. Add data files

SQL> alter tablespace sp01 add datafile 'd: \ test \ sp01.dbf' size 20 m;

2. Increase the data file size.

SQL> alter tablespace name 'd: \ test \ sp01.dbf' resize 20 m;

Note that the data file size cannot exceed 500 mb.

3. Set automatic file growth.

SQL> alter tablespace name 'd: \ test \ sp01.dbf 'autoextend on next 10 m maxsize 500 m;

? Move data files

Sometimes, if the disk where your data file is located is damaged, the data file will no longer be used. To make it available again, you need to move copies of these files to its

And then restore the disk.

The following uses the mobile data file sp01.dbf as an example:

1. Determine the tablespace where the data file is located

Select tablespace_name from dba_data_files where file_name = 'd: \ test \ sp01.dbf ';

2. Take the tablespace offline

Ensure data file consistency and change the tablespace to Offline state.

Alter tablespace sp01 (tablespace name) offline;

3. Use commands to move data files to the specified target location

Host move D: \ test \ sp01.dbf c: \ test \ sp01.dbf

4. Execute the alter tablespace command

After physically moving data, you must execute the alter tablespace command to modify the logic of the database file:

Alter tablespace sp01 rename datafile 'd: \ test \ sp01.dbf 'to 'C: \ test \ sp01.dbf ';

5. Bring the tablespace online

After a data file is moved, it must be changed to the online State to allow users to access the tablespace.

Alter tablespace sp01 (tablespace name) online;

? Display tablespace Information

Query the data dictionary view dba_tablespaces and display the tablespace information:

Select tablespace_name from dba_tablespaces;

? Display the data files contained in the tablespace

Query the data dictionary view dba_data_files to display the data files contained in the tablespace, as follows:

Select file_name, bytes from dba_data_files where tablespce_name = 'tablespace ';

? Table space summary

1. Understand the functions of tablespaces and data files

2. Master how to create common tablespaces, undo tablespaces, and temporary tablespaces.

3. Understand the functions of table space statuses (online, offline, read write, read only) and how to change the table space status.

4. Understand the reasons for moving data files, and use the alter tablespace and alter datatable commands to move data files.

? Other tablespaces

In addition to the most common table space, there are other types of table space:

1. Index tablespace

2. Undo tablespace

3. Temporary tablespace

4. Non-standard block tablespaces

I will not talk about these tablespaces here.

? Other Instructions

We will explain the table space components in the following sections.

Manage tablespaces and data files <6>