Solution to abnormal growth of Oracle tablespace

1. First, use the statement to query the selectsegment_name, sum (bytes) 10241024fromdba_segmentsgroupbysegment_nameha data segment with a capacity greater than 1 GB.

1. First, use a statement to query data segments with a capacity greater than 1 GB: select segment_name, sum (bytes)/1024/1024 from dba_segments group by segment_name ha

1. First, use a statement to query data segments with a capacity greater than 1 GB.

Select segment_name, sum (bytes)/1024/1024 from dba_segments group by segment_name having sum (bytes)/1024/1024> 1000;

The following result is displayed:

SYS_LOB0000136091C00003 $255332 M

SYS_LOB0000136441C00004 $7170 M

SYS_C0082042 1305 M

SYS_C0080433 1340 M

2. query which table the LOB segment belongs to based on the lob segment.

Select table_name, segment_name from dba_lobs where segment_name = 'sys _ lob1_136091c00003 $ ';

It is found that the ADU _ * Table occupies a lot of capacity.

3. Use dbmongolizer to connect to the database and delete duplicate data rows. However, after the duplicate data is deleted, the disk space will not be released.

4. Release the space occupied by lob data

Alter table adu _ * move tablespace BFPICK lob (content) store as (tablespace bfpick)

5. the space occupied by the duplicate data is released, and then the index is re-created for the table.

Alter index ***** rebuild;