Oracle database table space (tablespace) --- No longer afraid of interviews, one of the series

Oracle database table space (tablespace) --- No need to be afraid of interviews, one of the series

Needless to say, the position of the Oracle database is not one of the essential databases for large companies. In our country, although there is a slogan-de-o, but the slogan is loud, but the body is very honest. Therefore, Oracle is also one of the basic knowledge that needs to be mastered during operation and maintenance or development interviews.

The characteristics of the Oracle database are heavy (all aspects of the database are covered, such as fast backup, automated backup, task planning, triggers, views, view functions, super-segmented role user management system, memory management, table Space management, security audits, etc., but in fact, there are traces of related configurations in all aspects. Compared with the so-called light and compact software, those software may be unreasonable and unpredictable when configured), and can be fast Used, in terms of architecture, a relational database that can be quickly combined into a cluster. Well, since it is a relational database, there are naturally a lot of logic (whether internal or external). What we need to learn is to master these logics and use them in production.

One, the concept of table space (tablespace)

Oarcle database really stores data in data files. Oarcle tablespaces are actually a logical concept, which does not exist physically, so if a group of data files is twisted together, it becomes one Table space.

Table space attributes:

1) A database can contain multiple table spaces, and a table space can only belong to one database.

2) A table space can contain multiple data files, and a data file can only belong to one table space.

3) The table space has an actual size, which can be understood as Linux disk quota, which is a special file system with disk quota. If the autoextensiable field of automatic dynamic adjustment is not set to no, and the table space is occupied by data, then, This table space of the database will not be able to store any data .

From a logical point of view, a database (database) can be divided into multiple tablespaces (tablespace); a tablespace can be divided into multiple segments (segment); a data table occupies a segment (segment), an index Also account for a segment (segment). A segment is composed of multiple extents, and then a segment is composed of a set of continuous data blocks. This continuous data block is logically continuous and may be scattered on the physical disk.


From a physical point of view, a table space is composed of multiple data files, and the data files are files that actually exist on the disk. These files are composed of the blocks of the oracle database operating system. Usually, the file extension is dbf.

SELECT * FROM DBA_DATA_FILES; 输出如下:C:\APP\ADMINISTRATOR\ORADATA\MYORACLE\USERS01.DBF	4	USERS	5242880	640	AVAILABLE	4	YES	34359721984	4194302	160	4194304	512	ONLINEC:\APP\ADMINISTRATOR\ORADATA\MYORACLE\UNDOTBS01.DBF	3	UNDOTBS1	110100480	13440	AVAILABLE	3	YES	34359721984	4194302	640	109051904	13312	ONLINEC:\APP\ADMINISTRATOR\ORADATA\MYORACLE\SYSAUX01.DBF	2	SYSAUX	545259520	66560	AVAILABLE	2	YES	34359721984	4194302	1280	544210944	66432	ONLINEC:\APP\ADMINISTRATOR\ORADATA\MYORACLE\SYSTEM01.DBF	1	SYSTEM	723517440	88320	AVAILABLE	1	YES	34359721984	4194302	1280	722468864	88192	SYSTEMC:\APP\ADMINISTRATOR\ORADATA\MYORACLE\EXAMPLE01.DBF	5	EXAMPLE	104857600	12800	AVAILABLE	5	YES	34359721984	4194302	80	103809024	12672	ONLINE

The autoextensiable field yes indicates that the file will be dynamically resized. As you can see, currently, my database has 5 tablespaces named users, undobs1, sysaux, system, example.


Query the size and usage rate of all existing tablespaces:

SELECT a.tablespace_name, a.bytes total, b.bytes used, c.bytes free, (b.bytes * 100) / a.bytes "% USED ", (c.bytes * 100) / a.bytes "% FREE " FROM$ts_avail a,$ts_used b,$ts_free c WHERE a.tablespace_name = b.tablespace_name AND a.tablespace_name = c.tablespace_name; 

The unit of the above output is bytes. It can be seen that the approximate total size of the sysaux tablespace (can be understood as the total quota) is 550m, and the approximate total size of the system tablespace (can be understood as the total quota) is 720m. In the figure below, you can see that the actual physical file size is slightly smaller than the query, but basically close (estimated, so there is a difference).


Query the remaining used capacity of the table space:

SELECT tablespace_name, file_id, file_name, round(bytes / (1024 * 1024), 0) total_space FROM dba_data_files ORDER BY tablespace_name; select tablespace_name from dba_data_files;SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name FROM dba_free_space GROUP BY tablespace_name;

The above unit is M, the remaining quota of sysaux is 28.9m, and the remaining quota of system is 5.3m. Of course, in actual production, we don't need to care about these, because the table space will be autoextensiable, that is, the table space quota will be used up, and Oracle will automatically increase it. As long as your physical hard disk has enough free space, it will continue to increase automatically.


Query oracle database about db (database) parameters:

show parameter db;  输出如下:NAME                          TYPE        VALUE                                    ----------------------------- ----------- ---------------------------------------- db_16k_cache_size             big integer 0                                        db_2k_cache_size              big integer 0                                        db_32k_cache_size             big integer 0                                        db_4k_cache_size              big integer 0                                        db_8k_cache_size              big integer 0                                        db_block_buffers              integer     0                                        db_block_checking             string      FALSE                                    db_block_checksum             string      TYPICAL                                  db_block_size                 integer     8192                                     db_cache_advice               string      ON                                       db_cache_size                 big integer 0                                        db_create_file_dest           string                                               db_create_online_log_dest_1   string                                               db_create_online_log_dest_2   string                                               db_create_online_log_dest_3   string                                               db_create_online_log_dest_4   string                                               db_create_online_log_dest_5   string                                               db_domain                     string                                               db_file_multiblock_read_count integer     128                                      db_file_name_convert          string                                               db_files                      integer     200                                      db_flash_cache_file           string                                               db_flash_cache_size           big integer 0                                        db_flashback_retention_target integer     1440                                     db_keep_cache_size            big integer 0                                        db_lost_write_protect         string      NONE                                     db_name                       string      myoracle                                 db_recovery_file_dest         string      C:\app\Administrator\flash_recovery_area db_recovery_file_dest_size    big integer 3912M                                    db_recycle_cache_size         big integer 0                                        db_securefile                 string      PERMITTED                                db_ultra_safe                 string      OFF                                      db_unique_name                string      myoracle                                 db_writer_processes           integer     1                                        dbwr_io_slaves                integer     0                                        rdbms_server_dn               string                                               standby_archive_dest          string      %ORACLE_HOME%\RDBMS                      standby_file_management       string      MANUAL                                   xml_db_events                 string      enable                                   

Then, the block used by my Oracle database is the default 8192, and the database name is myoracle .

Segment (segment): A segment is a general term that occupies data file space, or a collection of space used by database objects; segments can include table segments, index segments, rollback segments, temporary segments, and cache segments.

Extent (interval): Any continuous block allocated to an object (such as a table) is called an interval; an interval is also called an extension, because when it runs out of the allocated interval, a new record must be inserted in the allocation of a new interval (ie Extend some blocks); once the interval is assigned to an object (table, index, and cluster), the interval cannot be assigned to other objects.

Second, create a new table space

The new table space here is the corresponding file of the new table space. The SQL statement is as follows:

create tablespace test datafile'C:\app\Administrator\oradata\myoracle\test02.dbf' size 200m;

Indicates that the name of a new table space with a size of 200m is test. Of course, this table space has no self-growth and no maximum limit.

create tablespace test datafile'C:\app\Administrator\oradata\myoracle\test02.dbf' size 200m autoextend on next 50m maxsize 20480m extent management local;

This SQL statement means to create a new table space named test with an initial size of 200m, and increase to 50m each time, and increase to 20480m, which is 20g, at most.

Three, delete the table space

The delete table space statement is:

alter database datafile'C:\app\Administrator\oradata\myoracle\test02.dbf' offline; It should be noted here that it must be in archive mode to be offline. If it is not in archive mode, you need to execute the following command (using SQLplus, log in to the sys user to sysdba permission to execute the following command):

start database nomount;

alter database mount;

alter database archivelog;

alter database open;

archive log list;

Make sure it is in the archive mode before you can execute alter database datafile'C:\app\Administrator\oradata\myoracle\test02.dbf' offline; otherwise an error will be reported :

错误报告 -SQL 错误: ORA-01145: 除非启用了介质恢复, 否则不允许立即脱机01145. 00000 -  "offline immediate disallowed unless media recovery enabled"*Cause:    ALTER TABLESPACE ... OFFLINE IMMEDIATE or ALTER DATABASE DATAFILE           ... OFFLINE is only allowed if database is in ARCHIVELOG mode.*Action:   Take tablespace offline normally or shutdown abort. Reconsider your           backup strategy. You could do this if you were archiving your logs.ORA-01031: 权限不足

Finally execute the delete command:

drop tablespace TEST including CONTENTS and datafiles CASCADE CONSTRAINTS;

Delete the test tablespace, context and physical files created in the previous example, and all dependencies are completely deleted. Of course, all tables, views, etc. created in the table space will be deleted.

Fourth, the designated use of table space

create table test(id int) tablespace test;

Create a new table named test, and specify the new tablespace created above for a table with a field id int.

Five, the expansion of the table space

In many cases, the table space may be overwhelmed by a large amount of data in a short period of time. For example, my Oracle system table space has reached more than 99%. If it is a production database, it is obviously very dangerous. Therefore, manual expansion is a better choice.

Four ways to increase the size of the table space
Meathod1: Add data files to the table space
Meathod2: Add data files , And allow the data file to grow automatically
Meathod3: Allow existing data files to grow automatically Increase
Meathod4: Manually change the size of existing data files

The expansion SQL statement is:

1. Query the size of each table space first, and the result of the query is in m:

select a.tablespace_name,total,free,total-free used from    ( select tablespace_name,sum(bytes)/1024/1024 total from dba_data_files    group by tablespace_name) a,    ( select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space    group by tablespace_name) b   where a.tablespace_name=b.tablespace_name;

2. This example takes the expansion of the table space of system as an example. From the original 690m to 800m, the fourth method is used:

alter database datafile 'C:\APP\ADMINISTRATOR\ORADATA\MYORACLE\SYSTEM01.DBF' resize 800m;