Preliminary Idea of temporary tablespace always full (AIX system, bare device)

Since the level of developers is high and low, dba has limited energy, and it is impossible to solve the SQL statement problem in time! We plan to solve the slow database operation problem by imitating the methods of redo logs!

1. View usage/remaining space of temporary files

1. Select (BYTES_USED + BYTES_FREE)/1024/1024."Total space M", Bytes _ used/1024/1024"Use space M"From v $ temp_space_header;

2. view the temporary tablespace to which the current user belongs

1. Select username, temporary_tablespace from dba_users;

USERNAME TEMPORARY_TABLESPACE
-----------------------------------------
SYS TEMP1
SYSTEM TEMP1
DBSNMP TEMP1
HUJINPEI TEMP1
ALAN1 TEMP1
PERFUSER TEMP1
ALAN2 TEMP1
MYUSER TEMP1
OUTLN TEMP1
WMSYS TEMP1

10 rows have been selected.

3. view the temporary files.

1. Select name from v $ tempfile;

4. Create a data file on the bare Device

A. create the desired link in the file system
For example, cd/sgerp5/sgerp5
Touch temp03.dbf

B. Create a soft link

Note: Please be careful during this step,The/dev/sgerp5_sgtemp03 directory cannot be wrong. If it is wrong, the mount is successful, but it may cause various strange errors. 

① -- Create a data file

Mklv-y sgerp5_temp03-t o-t raw-a ie-e x sgvg 60 hdisk2 hdisk3 hdisk4 hdisk5

② -- Create a link

Ln-fs/dev/sgerp5_sgtemp03/sgerp5/sgerp5/temp03.dbf

5. recreate a temporary tablespace: Check the script carefully. The temporary tablespace creation command is not the same as the temporary tablespace creation command.

1. CREATE TEMPORARY TABLESPACE"TEMP3"TEMPFILE
2. '/Sgerp5/sgerp5/temp03.dbf'SIZE 10G
3. Autoextend on next 655360 MAXSIZE 30G
4. Extent management local uniform size 1048576;

6. Set the created TEMP tablespace to the default temporary tablespace of the database:

1. Alter databaseDefaultTemporary tablespace temp;
2. -- View the default tablespace
3. Select username, temporary_tablespace from dba_users;

7. DROP the tablespace of the old TEMP1 :(Skip to Step 10 without dropping)

1. Drop tablespace temp1 including contents and datafiles;

8. If drop fails, the current temporary tablespace may be used. Wait until the session is released and check who occupies the temporary tablespace.

1. SELECT se. username,
2. Sid,
3. Serial #,
4. SQL _address,
5. Machine,
6. Program,
7. Tablespace,
8. Segtype,
9. Contents
10. FROM v $ session se,
11. V $ sort_usage su
12. HERE se. saddr = su. session_addr

1 2 Next Page