ora-01114 Error Reason

Yesterday in the test to find a very strange problem: The query module in the program to do the query, the beginning of a very fast, but after a period of time after the slow, and finally simply error, do not work. In the process of debugging, found that the Oracle temporary table space soared, reached dozens of GB, the session in Oracle tracking, found that disk space is still being consumed, almost every 5s, temporary table space will grow about 500MB, The final error should be due to the fact that there is no disk space to allocate. This is a very scary thing.

        We know that the Oracle temporary table space is primarily used for querying and storing some cached data, and one of the main reasons for disk consumption is the need to sort the results of the query. If there is no mistake, on the allocation of disk space (memory), Oracle uses the greedy algorithm, if the last disk space consumption reached 1GB, then the temporary table space is 1GB, if there is growth, so on, and so on, the temporary table space is always maintained at a maximum limit. The analysis of the terrorist phenomenon, as mentioned above, may be due to the following reasons.
        1. Does not set an upper limit for temporary table spaces, but allows unlimited growth. However, if you set an upper limit, you may end up with a problem with insufficient space, the temporary table space settings are too small to affect performance, temporary table space is too large also affect performance, as to how much need to set the test carefully.
        2. Query with too many tables in the table query. We know that in the table query, according to the query field and the number of tables will generate a Diescal product, the size of the Diescal product is a query the size of the temporary space, if the query of too many fields and data is too large, then will consume very large temporary table space.
       3. Some fields of the query are not indexed. In Oracle, if the table has no indexes, all of the data is copied to the temporary tablespace, and if there is an index, the index's data is typically copied to the temporary tablespace.
       for the above analysis, the query's statements and indexes are optimized, and the situation is mitigated, but further testing is required.

Summarize:
1.SQL statements can affect disk consumption, and improper statements can cause disk inflation.
2. The query statement needs careful planning, do not take it for granted to define a query, especially in software that can provide user-defined queries.
3. Carefully plan the table index.
If there is a similar problem with that person, please give your advice.

Cause: The device on which the file resides is probably offline. If the file is a temporary file, then it are also possible that device has run out of spaces. This could happen because disk space of temporary files are not necessarily at file allocated time.

Action: Restore access to the device or remove unnecessary.

3. View the physical disk remaining space and more than 20 g, to eliminate the reasons for insufficient disk space. (I'm probably experiencing a problem with insufficient disk space.)

4. View individual data file sizes:

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 Lines 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

See the maximum data file D:/oracle/oradata/kdc/temp01 in the system. The DBF size is 8G and is initially estimated to be the reason that the temp table space cannot be expanded.

5. Rebuild Temp Table space:

1. Log in as Sysdba

2. Create temporary table space for temporary transit TEMP2

sql> Create temporary tablespace TEMP2 tempfile D:/oracle/oradata/kdc/tem
P02. DBF ' SIZE 5M reuse autoextend on NEXT 640K MAXSIZE Unlimited
2/

The table space has been created.

3. Modify the default temp table space for TEMP2

sql> ALTER DATABASE default temporary tablespace temp2;

The database has changed.

4.drop Original temporary table space temp:

sql> Drop Tablespace temp including contents and datafiles;

Table space has been discarded.

5. Re-create temporary table space Temp:

sql> Create temporary tablespace TEMP tempfile D:/oracle/oradata/kdc/tem
P01. DBF ' SIZE 5M reuse autoextend on NEXT 640K MAXSIZE Unlimited
2/

The table space has been created.

6. Change the default temporary table space to temp:

sql> ALTER DATABASE default temporary tablespace temp;

The database has changed.

7.drop Temporary table Space TEMP2

Sql> drop tablespace temp2 including contents and datafiles;

Table space has been discarded.

6. Re-entry (Material supply system-transfer list) window, success, problem solving.

--Find temporary table spaces

SELECT * FROM Database_properties

where property_name= ' default_temp_tablespace ';

--View temporary table space file size

Select file_name,tablespace_name,bytes/1024/1024 "MB", autoextensible from Dba_temp_files;

--sql statement

SELECT * from V$sql;

--Find SQL that occupies a temporary table space

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;