Remember an ORACLE table space repair

1. Problem description

The 97 percent of Tablespace Users is full.

85 percent of the tablespace TSP_EMR is full.

85 percent of tablespace TSP_OUTPADM is full.

2. Problem analysis

Take the Users tablespace as an example, others are similar

The USERS tablespace is the default user tablespace. When a user is created and the user is not specified to use the tablespace, all the user information will be placed in the users tablespace. Use the query tablespace statement:

select file_name,tablespace_name,bytes/1024/1024 "bytes MB",maxbytes/1024/1024 "maxbytes MB"

from dba_data_files

where tablespace_name='USERS';

Query the users tablespace and find that it is full, use sql:

select t.TABLE_NAME,t.NUM_ROWS

from all_tables t

where tablespace_name='USERS' order by num_rows desc;


Query the tables that use the USERS table space and sort them in descending row-level order. It is found that multiple tables use the USERS table space, and there is a large amount of data that causes the USER table to be full.

3. Problem handling

1. Expand the table space:

alter datafile'/oracle_data/orcl/users02.dbf' resize 30G;


2. If the file is expanded to the maximum 30G (maximum), the file cannot be expanded, and the data file can be added:

alter tablespace users add datafile'/oracle_data/orcl/users27.dbf'  
 size 1024m autoextend on next 1024m maxsize 30G;


3. Truncate deletes useless tables to release space

If it is not released, shrink the TEST table and execute the following three statements:
 enable row migration: alter table TEST enable row movement;
 shrink table test: alter TABLE TEST shrink SPACE;
 close row migration: alter table TEST DISABLE row movement;
Note : After the data is deleted (either delete or truncate table), the size of the data file will not shrink, due to Oracle's "high water mark" (you can learn more about it), if you want to reduce the size of the data file, you need to reduce the high water mark. The correct way is to reduce it first HWM, then determine the actual occupied size, and then resize the data file, execute the following 4 statements:
(1) Query the table space file number:

select file#, name from v$datafile;
(2) Query the number of the largest data block (data block) of this data file according to the file ID: select max(block_id) from dba_extents where file_id=4;
(3) Calculate the actual table space For the occupied space, first query the data block size: select value from v$parameter where name='db_block_size', our default is 8192.
(4) Calculate the actual disk size occupied: select 65673 * 8/1024 from dual;
(5) Resize the data file size to be larger than the actual disk size:
 alter database datafile'/oracle/oradata/dbaxj/users01.dbf' resize 600m; in
this way, the data file size becomes smaller, saving space

4. The table that needs to be used, modify the table space

alter table USSD_UNREPORT_FAIL move tablespace new_tablespace

Need to develop a habit when building a table, specify a good table space

Reference: https://blog.csdn.net/u013050593/article/details/77160693