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

I don't know how you manage the tablespace at work, but you can be divided into two groups. In the previous company, we preferred to allocate all the space directly, such as 500G capacity planning, then prepare 500G in advance, and the other is that if I give 200g first, the subsequent space will automatically increase, anyway, the capacity is still 500G. In fact, to a large extent, it is the difference between personal habits and corporate process norms.

Why do you say that? because I received a strange alarm in a set of circumstances.

DBA: IP: xxxx Tablespace: PERFSTAT: 122.5% [critique!]

The key lies in this 122.5%. It seems very abnormal, if such an alarm can not find the crux of the problem, then the script that detects the tablespace still feels that there is a potential problem, or that the results of the test will be questioned.

As far as I know, this script has been used for many years, and there has really been no problem before. The current environment has thrown out a mistake, let's dig into it.

First of all, the script for this tablespace detection uses the second case I mentioned above, which is to constantly increase the data file, given a maximum value. In fact, this calculation is not the actual file size, and there is still a difference between the actual results.

If you are asked to check the usage rate of the table hole, it is obvious that we can get a current value and the maximum value of the file according to the data dictionary of the data file.

Select tablespace_name

Round (sum (bytes) / (1024 * 1024)) b

Round (sum (decode (maxbytes, 0, bytes, maxbytes)) / (1024 * 1024)) mb

From dba_data_files

Group by tablespace_name

There is another view that needs to be used, which is dba_free_space. The result of this view is the availability of the table space, which is very important. Internally, some data dictionaries are iteratively called to synthesize data about the availability of a tablespace.

Select tablespace_name, round (sum (bytes) / (1024 * 1024)) b

From dba_free_space

Group by tablespace_name

Combined with the two, the maximum minus the available value is the utilization. Let's look at the number of dba_data_files.

SQL > select file_name,bytes/1024/1024 size_MB, maxbytes/1024/1024 max_MB from dba_data_files where tablespace_name='PERFSTAT'

FILE_NAME SIZE_MB MAX_MB

/ U02/app/oracle/oradata/xxxx/perfstat01.dbf 3100 2000

/ U02/app/oracle/oradata/xxxx/perfstat02.dbf 10240 2000

It's strange to see here. The maximum maxsize turns out to be much lower than the current bytes.

It feels like it's not far from bug when I see it here. But in any case, the problem is not serious enough now, let's find a way to solve it.

One idea is to fix it. Let's set the maximum value of the tablespace.

SQL > alter tablespace perfstat autoextend on maxsize 14G

Alter tablespace perfstat autoextend on maxsize 14G

*

ERROR at line 1:

ORA-32773: operation not supported for smallfile tablespace PERFSTAT

Unexpectedly, this mode is not supported. Oerr's help message suggests that we can use alter database datafile to improve it.

So the way to fix it is to find the data file where the data dictionary is inconsistent and re-set the maxsize value.

SQL > alter database datafile'/ U02 autoextend on maxsize

Database altered.

After doing this, look at the tablespace detection script again and there will be no problem.

I looked at it on MOS, and this problem used to be very common.

Value in BYTES Column Greater than MAXBYTES Column in DBA_DATA_FILES (document ID 197244.1)

The document also writes an example to simulate the problem.

Create tablespace tst

Datafile'd:\ oracle\ tst01.dbf' size 5m autoextend on

Alter database datafile'd:\ oracle\ tst01.dbf' autoextend on maxsize 10m

Alter database datafile'd:\ oracle\ tst01.dbf' resize 20m

Select file_name, bytes, maxbytes, autoextensible from dba_data_files

FILE_NAME BYTES MAXBYTES AUT

-

D:\ ORACLE\ TST01.DBF 20971520 10485760 YES

It seems that the crux of the problem lies in the operation of resize before. My approach is somewhere in between. I like to create a file of initial size and then resize to a maximum. It seems that there will be a big deviation in some scenarios due to differences in usage and habits.

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.