Shulou(Shulou.com)05/31 Report--

This article mainly introduces the Oracle tablespace database file shrinkage example analysis, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian take you to understand.

We often encounter the problem of full disk space in the database, or the disk is in short supply due to the sudden increase of archive logs, or due to too many or large data files. The main scenario here is that the disk space itself is very large, but the data file corresponding to the table space is directly filled with disk space when initializing, resulting in frequent alarms of full disk space.

1. Error message

The alarm content is as follows:

[found abnormal] Real estate customer storage system database Oracle_192.168.xx.xx,192.168.xx.xx, database customer, connection error, 0 ORA-00257: archiver error. Connect internal only, until freed.

[time of occurrence] 09:12:21 on July 04, 2018.07

Second, the cause of the error

At a glance, we can roughly see that the above error is due to insufficient disk space, so that the archive cannot be completed. We only need to clean up enough disk space. However, when the disk is cleaned, it is found that the disk space itself can not be cleaned much, and it is occupied by a lot of large data files, while the actual segment size is less than 400G, and the disk space itself is 1T, so we can recover disk space by shrinking data files.

Data file initialization method:

1. We generally have two ways to initialize its data file when creating a tablespace, that is, specifying an initial size of 32G (a very large value) or an initial size of 100m (a very small value) and then slowly growing as needed through automatic expansion.

two。 The downside of the first initial data file method is that it takes up so much disk space at first, whether you use it or not (this kind of data migration can be used). The second initialization method grows on demand and can better monitor the actual disk space used, so it is recommended that the initial value is very small and automatic expansion is used to grow slowly.

Third, processing steps

1. View disk space size

two。 View database tablespace size

#! / bin/bashsqlplus-S / nolog = 20 then''else' * 'end) alrtFROM sys.dba_tablespaces d, (SELECT tablespace_name, SUM (bytes) bytes FROM dba_temp_files GROUP BY tablespace_name order by tablespace_name) a, (SELECT tablespace_name, SUM (bytes_used) bytes FROM v\ $temp_extent_pool GROUP BY tablespace_name) t, (SELECT tablespace_name MAX (bytes_cached) large FROM v\ $temp_extent_pool GROUP BY tablespace_name order by tablespace_name) lWHERE d.tablespace_name = a.tablespace_name (+) AND d.tablespace_name = t.tablespace_name (+) AND d.tablespace_name = l.tablespace_name (+) AND d.extent_management LIKE 'LOCAL' AND d.contents LIKE' TEMPORARY' ORDER by 1/promptexitEOF

3. Query can directly shrink tablespace data files

What you can see here is the size of the data file that can be directly shrunk. For example, if the initial initialization data file is 32 GB and the data file below the high water level is 20 GB, then the data file size that can be directly recycled is 12 GB.

Select a.bytes1024 CurrentMB, ceil (HWM * a.block_size) / 1024 HWM 1024 ResizeTo, (a.bytes-HWM * a.block_size) / 1024 ReleaseMB, 'alter database datafile''| | a.name |''resize''| | ceil (HWM * a.block_size/1024/1024) | |'M | 'ResizeCMD from v$datafile a, (select file_id,max (block_id+blocks-1) HWM from dba_extents group by file_id) b where a.file# = b.file_id (+) and (a.bytes-HWM * block_size) > 0

4. Shrink the data file directly

Alter database datafile'/ oracle/oradata/bi/data01.dbf' resize 1548m

5. Check the disk space again, a lot has been freed, and the archiving test can be completed manually.

IV. Summary

For oracle data file contraction (disk space contraction), we can generally view the current disk space (df-h)-> execute query commands and shrink commands that can be directly shrunk-- > execute large table high water level contraction-> perform tablespace high water level contraction (lower file high water mark)-> execute the command to directly recycle tablespace data files again.

The way to shrink the data file directly can be completed by referring to the above steps in this article.

So how to reduce the high water level of tablespace data files, and then complete the collection of tablespace data files?

1. View data files greater than 10G

Select file_name,file_id,tablespace_name, (bytes/1024/1024/1024) file_size_gb from dba_data_files where (bytes/1024/1024/1024) > 10 order by file_id

two。 View block information corresponding to data files greater than 10G

Select file_id,max (block_id+blocks-1) HWM,block_id from dba_extents where file_id = 14 group by file_id,block_id order by hwm desc

3. View the block information corresponding to the large table

# # View large table select file_name,file_id,tablespace_name, (bytes/1024/1024/1024) file_size_gb from dba_data_files where (bytes/1024/1024/1024) > 10 order by file_id;## to view block select owner,segment_name,file_id,block_id,blocks from dba_extents where segment_name='TABLE_NAME' corresponding to large table

4. Lower the high water level of the meter

Alter table table_name move;alter index idx_name rebuild

5. View the largest block_id corresponding to the data file

SELECT MAX (block_id) FROM dba_extents WHERE tablespace_name = 'TABLESPACE_NAME'

6. Perform data file contraction

(block_id+blocks-1) HWMalter database datafile'/ oracle/oradata/bi/data01.dbf' resize xxxM; of the data file thank you for reading this article carefully. I hope the article "sample Analysis of Oracle Tablespace Database File shrinkage" shared by the editor will be helpful to you. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.