Shulou(Shulou.com)06/01 Report--

Data file related knowledge

To see how many data files are in the system, we can view them through dynamic data dictionary or static data dictionary: View data files through static data dictionary

View data files via dynamic data dictionary

The contents of a data file cannot be viewed directly, but must be viewed through a logical structure, namely a table space. A table space is a logical storage structure, while a data file is a physical structure.

How to view tablespaces

Tablespaces can also be viewed through dba_tablespaces

Data dictionary is a logical storage structure, and data file is a real storage structure, if there is a table space, but no data file is not possible, table space and data file are corresponding, if there is no data file, table space cannot exist independently, if only table space does not have data file is also not possible, just like if the computer does not have a hard disk, you can no longer logically see C disk D disk, where the hard disk is equivalent to a data file, Disk C is equivalent to table space. We can put multiple data files into a table space, and then store data in the table space, table space and data file are corresponding, a table space can be composed of one or more data files, but a data file can only be in a table space, how do we check the correspondence between table space and data file?

You can also view the size of the tablespaces

You can also create new data files in the table spaces described above. If you run out of table space, simply create the appropriate data file. The size of the table space is the sum of the sizes of the data files, and the final data files are stored in the hard disk, but we can store the data files in different hard disks, as long as they are in a table space, so that the hard disk size is limited to the files, and the data files in multiple hard disks can be formed into a table space.

So DBAs will store a lot of tables, views, indexes, synonyms and other objects in the table space.

Segment: For example, in the xx table space DBA created a table named test, this table space occupied space, we call it segment. According to the different objects stored in the segment, it is divided into data segment, index segment, fallback segment, temporary segment, etc.

You can view segment information

For segments, there is a smaller unit called a zone.

Zone (extent): Zone is the smallest unit of disk space allocation. Disk is divided into zones. At least one zone is allocated at a time. Zone is composed of continuous data blocks. A segment is mainly composed of one or more zones. When a segment is created, it contains at least one zone. When all the space in the segment has been used, the system automatically allocates a new zone for the segment. Zones cannot exist across data files, but can only exist in one data file.

A segment is composed of regions, and the segment must be an integer multiple of the region. If a table is 25MB and a region is 4MB in size, then the size of the segment is 28MB. If 3MB of data is added to the table, the space for this period can still accommodate the table. Now the size of the table is 28MB. If 1MB is added, then the space for the segment is 32 MB.

View the distribution of extents and segments for the EXAMPLE tablespace

The smallest unit of storage in an Oracle database is a block-8KB, in which data is stored. Data block is the smallest storage unit that oracle server can read or write. Oracle server manages the storage space of data files in units of data block. The value range of data block is 2-64KB, and its default size is related to oracle version.

View minimum storage unit for operating system

[root@orclsrv ~]# tune2fs -l /dev/sda1

This information can also be viewed through OEM

Creating tablespaces and data files

SQL>create tablespace xx datafile '/u01/xx.dbf' size 100m autoextend on

2 next 5m maxsize 200m;

Tablespace created.

Change the name of the table space to yy

For administrative purposes, we want the name of the table space to match the name of the data file.

Solution:

Use operating system commands to make a copy of the data file

Put the table space offline

Then rename the data file

Set the table space offline

Rename data files

View modifications

Put Tablespace Online

Prompt to restore/u01/yy.dbf

Put Tablespace Online

How to expand the size of tablespaces

Change data file size directly

2. Add data files to tablespaces

If the partition in which the table space is located runs out of space

Delete Tablespace

Create temporary tablespaces

Deleting temporary tablespaces is the same command as deleting tablespaces.

If you have any questions about this article, please add WeChat below to discuss it together.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.