Oracle tablespace table space creation and management

1. Overview of Table Spaces

1. Tablespace is a logical part of a database. 2. Physically, the database data is stored in the data file;  3. Logically, a database is stored in a table space, and the tablespace consists of one or more data files.

Logical composition of 2.oracle

1. The logical structure of Oracle consists of a chunk  of the tablespace segment 2. The database is made up of table spaces, and the table space is composed of sections, which are made up of blocks of Oracle, which ismade to improve  the efficiency of the database 3 . Role               A. Controlling the disk space occupied by the database                B DBAs can deploy different data types to different locations, which facilitatesthe performance of I/0, while facilitating management of backup and recovery  

3. Querying the table space of Oracle

Select Tablespace_name from Dba_tablespaces;  

4. Syntax and steps for creating a tablespace

1. Create table space with the Create tablespace command  complete

5. Case: Create a table space

Crate tablesapce  data01 datafile ' d:\test\data01.dbf ' size  20m uniform size 128k; // Description: Create a tablespace named DATA01 and establish a data file named data.01.dbf for the tablespace with a size of 128k

The above is associated with a data file, the following is associated with two data files

6. Case: Use a custom table space

CREATE TABLE Mydept (deptno number (4), dname varchar2 (+), loc varchar2 () tablespace data01; // If you do not specify a tablespace, it is placed by default in the SYSTEM table space CREATE TABLE emp (empno,ename) tablespace data01 as select Empno,ename from Scott.emp; // Create your own tables with tables from other scenarios and place them in a custom table space  

7. What is the state of a tablespace

When the tablespace is established, the tablespace is in the online state, where the tablespace is accessible and can be read and written, and can execute various statements on the tablespace. However, in the case of system maintenance or data maintenance, it may be necessary to change the state of the tablespace (offline, online, read-only). Typically operated by privileged users and DBAs  

8. Table Space Offline

Alter tablespace table space name offline;  

9. Bring the table space online

Alter tablespace table space name online;  

10. Read-Only table space

alter tablespace table space Read only; // Table spaces can be read- write when table spaces are established, and if you do not want to perform update, delete, insert operations on a tablespace, you can modify the  tablespace to read-only

11. Read and write table space instead

Alter Tablespace tbs001 Read write;  

12. Display all tables in the Tablespace

SELECT * from User_tables where tablespace_name= ' DATA01 '; // Personal Discovery system tables or views in the Oralce system are distinguished by uppercase  )

13. View the table space where a table is located (knowing the table name)

Select Tablespace_name,table_name from User_tables where table_name= ' EMP ';

14. Delete Table spaces

drop tablespace DATA01 including contents and datafiles; // Description: Including contents indicates that all data objects for the tablespace are deleted when the tablespace is dropped, and datafiles means that the database files are also deleted.  

15. Extension table Space Oralce there are 3 methods

1. Add data file  alter tablespace TBS001 add datafile ' d:\tbs002.dbf ' size 20m;   2. Increase the size of the data file ALTER DATABASE datafile ' d:\tbs001.dbf ' resize  20m; //

16. Move data files (migrate data files when processing disk corruption)

1. Find the table space corresponding to the data file      select Tablespace_name from dba_data_files where file_name= ' D:\TBS001.DBF ';   2. Take the table space offline to ensure consistency of data files      alter TABLESPACCE tbs001  offline; 3. Use the command to move the data file to the specified target location (Sqlplus can be successful, pl/SQL should not be successful should be I do not know)      host move d:\tbs001.dbf c:\tbs001.dbf;   4. Modify the table space corresponding data style for the new disk under the data      file ' D:\TBS001. DBF ' to ' E:\TBS001. DBF ';   5. Make the table space online      alter tablespace TBS001 online  ;

17. Querying Table Space Information

SELECT * from Dba_tablespaces;  

18. Querying all the data files of the Tablespace

Select File_name,bytes from Dba_data_files where Tablesapce_name= ' TBS001  

19. Status of the Tablespace

Offline,online, read only, read write;  

20. Other common table spaces

1. Index tablespace   2.  undo Tablespace   3. Temporary tablespace 4. Table space for non-standard blocks  

Sometimes when we are building a table, we encounter the following commands:

CREATE TABLE X_small_area (small_area_id number (null--10--block reserved 10%1--  255------1----No limit to the maximum sector);

For Pctfree knowledge, you can view the article "Oracle Pctfree and pctused Details"

Original link: Oracle tablespace table space creation and management

Oracle tablespace table space creation and management