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.

2, creating a tablespace
The table space is done using the crate tablespace command, and it is important to note that, in general, the table space is created by a privileged user or DBA, and if you create a tablespace with another user, the user must have the Create System permissions for the 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 established for the tablespace, with a size of 128k
2), using the data table space
to create a table Mypart (
   Deptno Number (4),  
   dname varchar2 (+),  
   Loc varchar2 (+)
) tablespace DATA01;

3. Change the state of the table space
When a tablespace is established, the tablespace is in the online state, and 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 execute various statements in the table space. 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 added single quotation marks
2), bring the table space online
Alter tablespace table space name online;
eg, alter tablespace DATA01 online;
3), read-only table space
Table spaces can be read-write when table spaces are 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 table space name read only;
Note: modified 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.

The

5, extended tablespace
table space is made up of data files, and the size of the table space is actually the size of the data file after it is added. Then we can imagine, assuming that table employee is stored on the DATA01 table space, the initial size is 2M, when the data is full of 2M space, if the data is inserted into the employee table, this will show an insufficient space error.
Case Description:
1. Create a table space sp01
eg, create tablespace sp01 datafile ' D:\dev\oracle\product\10.2.0\dada01.dbf ' size 1m Uniform size 128k; 
2. Create a normal table on the tablespace mydment its structure and Dept
Create TABLE Mypart (
   deptno number (4 ),  
   dname varchar2 (+),  
   Loc varchar2 (+)
) tablespace sp01;
3. Add data to the table insert into mydment SELECT * from dept;
4. What if there is a problem that cannot be extended at some point?
5. Expand the tablespace to add more storage space for it.
There are three ways:
1. Add data file
Sql> alter tablespace sp01 add datafile ' D:\dev\oracle\product\10.2.0\dada02.dbf ' size 1m ;
2. Modify the size of the data file
sql> alter tablespace sp01 ' D:\dev\oracle\product\10.2.0\dada01.dbf ' resize 4m;
It is important to note that the size of the data file should not exceed 500m.
3. Set the automatic growth of the file.
Sql> alter tablespace sp01 ' D:\DEV\ORACLE\PRODUCT\10.2.0\DADA01.DBF ' autoextend on next 10m maxsize 500m;

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.

See also: http://www.cnblogs.com/linjiqin/archive/2012/02/16/2354328.html

Oracle Database Management-manage tablespaces and data files