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 sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$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
ALTER TABLESPACE app_data ADD DATAFILE'D
:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF' SIZE 50M;
Meathod2: Add data files , And allow the data file to grow automatically
ALTER TABLESPACE app_data ADD DATAFILE'D
:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP04.DBF' SIZE 50M
AUTOEXTEND ON NEXT 5M MAXSIZE 100M;
Meathod3: Allow existing data files to grow automatically Increase
ALTER DATABASE DATAFILE'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF'
AUTOEXTEND ON NEXT 5M MAXSIZE 100M;
Meathod4: Manually change the size of existing data files
ALTER DATABASE DATAFILE'D:\ORACLE\ PRODUCT\10.2.0\ORADATA\EDWTEST\APP02.DBF'
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;