VII. Oracle manages tablespaces and data files

Manage tablespaces and data files

1. 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, which is composed of one or more data files.

 

1. Logical structure of the database

Logical results in Oracle include tablespaces, segments, partitions, and blocks.

Note: The database is composed of tablespaces, while the tablespace is composed of segments, and the segments are composed of partitions, And the partitions are composed of Oracle blocks. This can improve the efficiency of the database.

 

1. Tablespace

Tablespaces are used to logically organize data in databases. A 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.

 

1. Create a tablespace

The create tablespace command is used to create a tablespace. In general, a tablespace must be created by a privileged user or DBA. If other users create a tablespace, the user must have the system permission to create tablespace.

Create a data table space

After creating a database, you 'd better create your own tablespace to facilitate table management. The command is as follows:

Create tablespace [ts_name] datafile 'd: \ test \ data001.dbf '20 M uniform 12 K;

Note: After executing the preceding command, a tablespace named ts_name will be created in the corresponding path, and a data file named data001.dbf will be created for the tablespace. The partition size is 128 K.

 

1. Use data table space

Create Table my_table (deptno number (4), dname varchar2 (20), Loc varchar2 (30) tablespace ts_name;

 

1. 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 data can be queried, you can also execute various SQL statements in the tablespace. However, during system or data maintenance, the tablespace status may need to be changed. In general, privileged users or DBAs are used to perform operations.

(1) take the tablespace offline

Alter tablespace name offilne;

(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 update, insert, or delete operations on the tablespace, you can change the tablespace to read-only

Alter tablespace name read only;

(4) change the tablespace to readable.

Alter tablespace name read write;

 

1) Know the tablespace name and display all the tables in the tablespace. Select * From all_tables where tablespace_name = 'tablespace name '; 2) Know the table name and view the table space Select tablespace_name, table_name from user_tables where table_name = 'emp ';

 

1. Delete a tablespace

Generally, privileged users or DBAs must have the system permission to drop tablespace.

Drop tablespace 'tablespace name' including contents and datafiles;

Note: Including contents indicates that when a tablespace is deleted, all database objects in the tablespace are also deleted, and datafiles indicates that the database disk files will also be deleted.

 

1. Extended tablespace

The 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 EMP is stored in the data001 tablespace and the initial size is 2 MB, if you insert data into the EMP table after the data reaches 2 MB, the system displays the error of insufficient table space.

Therefore, you need to expand the tablespace. There are three ways to expand the tablespace:

1) add data files Alter tablespace sp001 add datafile 'd: \ test \ sp002.dbf 'size 20 m; 2) increase the data file size Alter tablespace sp001 add datafile 'd: \ test \ sp001.dbf 'resize 20 m; Note: The data file size cannot exceed 500 mb 3) set Automatic File Growth Alter tablespace sp001 add datafile 'd: \ test \ sp001.dbf 'autoextend on next 10 m maxsize 500 m;

 

1. Troubleshooting and moving data files

Sometimes, if the disk where the data file is located is damaged, the data file cannot be used. To make it available again, you need to move copies of these files to other disks and restore them.

The following uses the mobile data file sp001.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 \ sp001.dbf '; 2) Take the tablespace offline Ensures data file consistency and converts the tablespace status to offline status. Alter tablespace sp001 offline; 3) use commands to move data files to the specified target location Host move D: \ test \ sp001.dbf c: \ test \ sp001.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 sp001 rename datafile 'd: \ test \ sp001.dbf 'to 'C: \ test \ sp001.dbf '; 5) Bring the new tablespace online Alter tablespace sp001 online;

Display tablespace information:

Query the data dictionary view dba_tablespace and display the tablespace information.

Select tablespace_name from dba_tablespace;

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.

Select filename, bytes from dba_data_files where tablespace_name = 'tablespace name ';