14. Oracle Database Management--manage tablespaces and data files

First, the concept
A tablespace is a logical part of a database.
Physically, the database data is stored in the data file;
Logically, the database data is stored in a table space, and the tablespace consists of one or more data files.

Second, the logical structure of the database
The logical structure in Oracle includes table spaces, segments, extents, and blocks.
Note that the database is composed of table space, and the table space is composed of segments, and the section is composed of the region, and the area is composed of Oracle blocks such a structure, can improve the efficiency of the database.

Three, table space
1. Concept
Table spaces are used to logically organize the data for a database. A database is logically composed of one or more table spaces. The following functions can be achieved through Tablespace:
1), control the disk space occupied by the database
2), DBAs can deploy different data types to different locations, which helps improve I/O performance while facilitating management operations such as backup and recovery.

The table space is created using the Crate tablespace command, and it is important to note that, in general, creating tablespaces is performed by privileged users or DBAs. If a table space is created with another user, the user must have system permissions for create tablespace.
1), creating a data table space
After the database is established, it is best to create your own tablespace for easy management of tables
--path D:\dev\oracle\product\10.2.0\ to exist, otherwise creation is unsuccessful
Create Tablespace data01 datafile ' D:\dev\oracle\product\10.2.0\dada01.dbf ' size 20m uniform size 128k;
Description: After executing the above command, a tablespace named DATA01 is established and a data file named DATA01.DBF is created for the tablespace, with a size of 128k
2, and a data table space
CREATE TABLE Mypart (
   deptno Number (4),
   dname varchar2 (+),
   Loc varchar2 (+)
) tablespace DATA01;

when a tablespace is established, the tablespace is in the online state, at which time the tablespace is accessible and the table space is readable and writable. That is, you can query the table space's data, and you can also execute various statements in the tablespace. However, in the case of system maintenance or data maintenance, you may need to change the state of the tablespace. Typically, this is done by a privileged user or DBA.
1), take the table space offline
alter tablespace table space name offline;
eg, alter tablespace data01 offline;--table space name cannot be enclosed in single quotation marks
2), bring tablespace online
alter tablespace table space name online;
eg, alter tablespace DATA01 online;
3), read-only tablespace
table space can be read and written when the tablespace is established, and if you do not want to perform update,delete,insert operations on that tablespace, you can modify the tablespace to read-only
Alter TABLESPACE tablespace name read only;
Note: Modify to writable is alter TABLESPACE tablespace name read write;)

Let's give you an example that shows the read-only feature:
1), knowing the tablespace name, showing all tables included in the table space
SELECT * from all_tables where tablespace_name= ' table space name ';
Eg, select * from All_tables where tablespace_name= ' DATA01 '; --data01 to capitalize formatting
2), know the name of the table, and see if the table belongs to that table space
Select Tablespace_name, table_name from user_tables where table_name= ' EMP ';
By 2 we can know that scott.emp is on the system table space, and now we can change the system to read-only, but we will not succeed, because systems are table space, if it is a normal table space, then we can set it as read-only, to give you a demonstration, to enhance understanding.
3),
4), Make table space readable and writable
Alter tablespace table space name read write;

4. Delete Table space
Typically, a privileged user or DBA is operating, and if it is another user operation, the user is required to have the drop tablespace system permission.
Drop tablespace ' table space ' including contents and datafiles;
eg, drop tablespace DATA01 including contents and datafiles;
Description: Including contents indicates that all database objects for the space are deleted when the tablespace is dropped, and datafiles means that the database files are also deleted.

6. Moving Data files
Sometimes, if your data file is on a disk that is damaged, the data file will no longer be available, and in order to be able to reuse it, you need to move a copy of those files to another disk and then restore.
The following is an example of moving data file sp01.dbf:
1. Determine the tablespace in which the data file resides
Select Tablespace_name from Dba_data_files where File_name=upper (' D:\dev\oracle\product\10.2.0\dada01.dbf ');
2. Take the table space offline
--Ensure the consistency of the data files and turn the tablespace into a offline state.
Alter tablespace SP01 offline;
3. Use the command to move the data file to the specified target location
Host move D:\dev\oracle\product\10.2.0\dada01.dbf c:\dada01.dbf;

4. Execute the ALTER tablespace command
After you physically move the data, you must also perform the ALTER tablespace command to logically modify the database file:
Alter tablespace sp01 rename datafile ' D:\dev\oracle\product\10.2.0\dada01.dbf ' to ' c:\sp01.dbf ';
5. Make Table Space Online
After the data file has been moved, it must be turned into the online state in order for the user to have access to the tablespace.
Alter tablespace SP01 Online;

7. Display table Space information
Query the data dictionary view dba_tablespaces to display information about the tablespace:
Select Tablespace_name from Dba_tablespaces;
Show the data files that the tablespace contains
Query the data dictionary view Dba_data_files to display the data files that the tablespace contains, as follows:
Select file_name, bytes from dba_data_files where tablespace_name= ' table space ';

Iv. Summary of the table space
1. Understanding the role of tablespaces and data files
2. Master common table space, undo table space and the establishment of temporary table space method
3. Understand the role of the various states of the tablespace (online, offline, read write, read only), and how to change the state of the tablespace.
4. Understand the reasons for moving data files, and how to move data files using the ALTER TABLESPACE and ALTER DataTable commands.

V. Other table spaces
In addition to the most commonly used data table spaces, there are other types of table spaces:
1. Index Table Space
2. Undo Table Space
3. Temporary table space
4. Table spaces for non-standard blocks
These kinds of table space, the big guy can refer to the book study, here I do not speak.

Six, other description
about the part of the table space/area/block, we will explain to you later.