Cause of ora-01114 Error

I found a very strange problem during the test yesterday: the query speed started very fast in the query module of the program, but after a while the speed slowed down, in the end, an error is reported and it does not work. In the troubleshooting process, we found that the temporary tablespace in Oracle has soared to dozens of gigabytes. We tracked the session in Oracle and found that the disk space is still being consumed, almost every 5 s, the temporary tablespace will increase by about mb. The final error should be caused by the absence of disk space allocation. This is a terrible thing.

We know that the temporary Oracle tablespace is mainly used to query and store some cached data. One of the main reasons for disk consumption is to sort the query results, in the allocation of disk space (memory), Oracle uses the greedy algorithm. If the last disk space consumption reaches 1 GB, the temporary tablespace is 1 GB. If there is still growth, in this case, the temporary tablespace is always at the maximum. The analysis of the terrorism phenomenon mentioned above may be caused by the following reasons.
1. Do not set the upper limit for the temporary tablespace, but allow unlimited growth. However, if you set an upper limit, you may still encounter an error due to insufficient space. The temporary tablespace setting is too small, which affects performance. The temporary tablespace setting is too large, which also affects performance, as for how many cases need to be set, you need to perform a careful test.
2. During query, too many tables are used in table join queries. We know that during a table connection query, A discar product is generated based on the queried fields and the number of tables. The size of the discar product is the size of the temporary space required for a query, if too many fields are queried and the data is too large, a large temporary tablespace is consumed.
3. Some fields in the query are not indexed. In Oracle, if the table does not have an index, all the data will be copied to the temporary tablespace. If there is an index, the data of the index is generally copied to the temporary tablespace.
Based on the above analysis, the query statements and indexes are optimized to alleviate the problem, but further tests are required.

Summary:
1. SQL statements will affect disk consumption. Improper statements will cause disk spikes.
2. You need to carefully plan the query statements. do not define a query statement, especially in software that provides custom queries.
3. carefully plan the table index.
If you have encountered a similar problem, please give your suggestions.

 

Cause:The device on which the file resides is probably offline. if the file is a temporary file, then it is also possible that the device has run out of space. this cocould happen because disk space of temporary files is not necessarily allocated at file creation time.

Action:Restore access to the device or remove unnecessary files to free up space.

3. Check that the remaining space on the physical disk is more than 20 GB. (The reason may be that the disk space is insufficient ).

4. view the size of each data file:

SQL> select Bytes/1024/1024/1024 as "size (g)", name from V $ datafile order by bytes;

Size (g) Name
------------------------------------------------------------------------------------------
0.00488281 D:/Oracle/oradata/KDC/temp02.ora
0.01525878 C:/datafile/kdcwz_plan.ora
0.01678466 D:/Oracle/oradata/KDC/tools01.dbf
0.01953125 D:/Oracle/oradata/KDC/cwmlite01.dbf
0.01953125 D:/Oracle/oradata/KDC/drsys01.dbf
0.01953125 D:/Oracle/oradata/KDC/odm01.dbf
0.02441406 D:/Oracle/oradata/KDC/indx01.dbf
0.03723144 D:/Oracle/oradata/KDC/xdb01.dbf
0.14587402 D:/Oracle/oradata/KDC/example01.dbf
0.34765625 D:/datafile/kdcwz_bill.ora
0.41015625 D:/Oracle/oradata/KDC/system01.dbf
0.49328613 E:/datafile/kdcwz_stock.ora
1.50741577 D:/Oracle/oradata/KDC/undotbs01.dbf
2.03735351 D:/Oracle/oradata/KDC/users01.dbf

14 rows selected

 

SQL> select Bytes/1024/1024/1024 as "size (g)", name from V $ tempfile order by bytes;

Size (g) Name
------------------------------------------------------------------------------------------
8.00000000 D:/Oracle/oradata/KDC/temp01.dbf

We can see that the maximum data file D in the system:/Oracle/oradata/KDC/temp01.dbf is 8 GB. It is estimated that the temp tablespace cannot be expanded.

5. Recreate the temp tablespace:

1. Log On As sysdba

2. Create temporary tablespace temp2 for temporary transit

SQL> create temporary tablespace temp2 tempfile D:/Oracle/oradata/KDC/TEM
P02.dbf 'size 5 m reuse autoextend on next 640 K maxsize Unlimited
2/

The tablespace has been created.

3. Change the default temporary tablespace to temp2.

SQL> alter database default temporary tablespace temp2;

The database has been changed.

4. Drop the original temporary tablespace temp:

SQL> drop tablespace temp including contents and datafiles;

The tablespace is discarded.

5. Recreate the temporary tablespace temp:

SQL> create temporary tablespace temp tempfile D:/Oracle/oradata/KDC/TEM
P01.dbf 'size 5 m reuse autoextend on next 640 K maxsize Unlimited
2/

The tablespace has been created.

6. Change the default temporary tablespace to temp:

SQL> alter database default temporary tablespace temp;

The database has been changed.

7. Drop temporary tablespace temp2

SQL> drop tablespace temp2 including contents and datafiles;

The tablespace is discarded.

6. re-enter the [Material Supply System-transfer order and subaccount] window. The problem is solved!

 

 

 

-- Query temporary tablespace

Select * From database_properties

 

Where property_name = 'default _ temp_tablespace ';

 

-- View the temporary tablespace File Size

Select file_name, tablespace_name, Bytes/1024/1024 "MB", autoextensible from dba_temp_files;

 

-- SQL statement

Select * from V $ SQL;

 

-- Query SQL statements that occupy temporary tablespace

Select se. username, se. Sid, se. Serial #, Su. extents, Su. Blocks * to_number (rtrim (P. Value) as space,

 

Tablespace, segtype, SQL _text

 

From v $ sort_usage Su, V $ parameter P, V $ session se, V $ SQL s

 

Where p. Name = 'db _ block_size 'and Su. session_addr = Se. saddr and S. hash_value = Su. sqlhash

 

And S. Address = Su. sqladdr

 

Order by se. username, se. Sid;