Solve the problem that Oracle occupies too much disk

background

After the database has been running for some time, the table space has been increasing, but the actual amount of data is not so large. Deleting data or truncate tables does not reduce the tablespace. This is because when the table space is insufficient, it will expand automatically, but it will not shrink automatically.

terms of settlement

1. Export before import

Directly export the database and then create a smaller table space import. The R & D environment is the simplest and most effective. You can also query the tables that occupy a large space in combination with the relevant sql in method 2, delete the useless data, and then export it.

D:\app\adaivskenan\product\11.2.0\dbhome_1\BIN\expdp  fssctest/fssctest@orcl dumpfile=202107.dmp

The exported and imported data is stored in D:\app\adaivskenan\admin\orcl\dpdump

D:\app\adaivskenan\product\11.2.0\dbhome_1\BIN\impdp fssctest/fssctest dumpfile=202107.dmp
impdp fssctest/fssctest dumpfile=XXX.dmp directory=dpdata1 remap_schema=fssctest:fssctest remap_tablespace=fssctest:newfssctest

2. Resize mode

The disk size occupied by the active shrink table space can only be shrunk with resize. If the shrink error ORA-03297 is reported, it indicates that the size of resize is too small and the value of reisze needs to be increased appropriately

  1. Query can directly shrink the tablespace data file and execute the resize command
select a.file#,a.name,a.bytes/1024/1024 CurrentMB,
          ceil(HWM * a.block_size)/1024/1024 ResizeTo,
          (a.bytes - HWM * a.block_size)/1024/1024 ReleaseMB,
          'alter database datafile '''||a.name||''' resize '||
          ceil(HWM * a.block_size/1024/1024) || 'M;' ResizeCMD
   from v$datafile a,
        (select file_id,max(block_id+blocks-1) HWM
          from dba_extents
          group by file_id) b
  where a.file# = b.file_id(+)
   and (a.bytes - HWM *block_size)>0;
  
alter database datafile 'D:\APP\ADAIVSKENAN\ORADATA\ORCL\FSSCTEST.DBF' resize 2080M;   
  1. Query all tablespaces and the size of each tablespace, used space, remaining space, utilization and idle rate
select a.tablespace_name, total, free, total-free as used, substr(free/total * 100, 1, 5) as "FREE%", substr((total - free)/total * 100, 1, 5) as "USED%" from 
(select tablespace_name, sum(bytes)/1024/1024 as total from dba_data_files group by tablespace_name) a, 
(select tablespace_name, sum(bytes)/1024/1024 as free from dba_free_space group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by a.tablespace_name;
  1. Query the top 10 occupied tables and spaces
SELECT * FROM (SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 MB FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = 
upper('fssctest') GROUP BY SEGMENT_NAME ORDER BY 2 DESC) WHERE ROWNUM < 10;
  1. Query the space occupied by a specific table, and replace "TABLE_NAME" with the name of the specific table to be queried:
select t.segment_name, t.segment_type, sum(t.bytes / 1024 / 1024) "Occupied space(M)"
from dba_segments t
where t.segment_type='TABLE'
and t.segment_name='TABLE_NAME'
group by OWNER, t.segment_name, t.segment_type;
  1. Query lob stored sys_ Lob related table (Format: SYS_LOB (10 digital object_id) C (5 digital col#), query with the middle 10 digits)

a. When we build a table, oracle gives us a segent to store data in the corresponding table space, and will expand due to the increase of the amount of data. However, when the table we created contains lob type data, oracle will generate an independent segment for each LOB field to store the data, and also establish an independent index segment oracle manages them separately.

b. Only one or two segment objects will be added to the normal TABLE The data type is. INDEX and TABLE The INDEX is placed in the INDEX segment. However, the lob column adds two additional segment objects, of which the types are lobsegment and lobindex. Lobindex is used to point to the lob segment and find out a part of it. Therefore, the lob stored in the TABLE stores an address, or a pointer. In fact, the lob column in the TABLE stores an address segment Then find all address segments in lobindex Then read the values of all address segments in lobsegment. Therefore, lobsegment saves the real data of LOG column, so it will be very large and exist independently of the original TABLE.

select object_name,status from dba_objects where object_id in('0000149596','0000152152','0000149860','0000151756')
  1. Object statistics of reservoir high water level

a. Compare the relationship between the number of rows of the table and the size of the table. If the number of rows is 0 and the current occupied size of the table is still large after deducting the initial_text, it indicates that the table has a high water level.

b. The ratio of the number of rows to the number of blocks, that is, to see how many rows of data a block can store. If the number of rows stored in a block is less than 5 or less, it indicates that there is a high water level. Note that neither of these two methods is very accurate, so you need to filter the query results. It should be noted that when querying the high water level of the table, first analyze the table to get the most accurate statistical information.

  1. View table spaces and sizes that waste space
SELECT D.OWNER,
       ROUND(D.NUM_ROWS / D.BLOCKS, 2),
       D.NUM_ROWS,
       D.BLOCKS,
       D.TABLE_NAME,
 ROUND((d.BLOCKS*8-D.INITIAL_EXTENT/1024)/1024)  t_size_MB
  FROM DBA_TABLES D
 WHERE D.BLOCKS > 10
   AND ROUND(D.NUM_ROWS / D.BLOCKS, 2) < 5
 AND d.OWNER='FSSCTEST' ;

Percentage of wasted space

SELECT OWNER,
       SEGMENT_NAME TABLE_NAME,
       SEGMENT_TYPE,
       GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS, 0) /
                      GREATEST(NVL(HWM, 1), 1)),
                      2),
                0) WASTE_PER
  FROM (SELECT A.OWNER OWNER,
               A.SEGMENT_NAME,
               A.SEGMENT_TYPE,
               B.LAST_ANALYZED,
               A.BYTES,
               B.NUM_ROWS,
               A.BLOCKS BLOCKS,
               B.EMPTY_BLOCKS EMPTY_BLOCKS,
               A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM,
               DECODE(ROUND((B.AVG_ROW_LEN * NUM_ROWS *
                            (1 + (PCT_FREE / 100))) / C.BLOCKSIZE,
                            0),
                      0,
                      1,
                      ROUND((B.AVG_ROW_LEN * NUM_ROWS *
                            (1 + (PCT_FREE / 100))) / C.BLOCKSIZE,
                            0)) + 2 AVG_USED_BLOCKS,
               ROUND(100 *
                     (NVL(B.CHAIN_CNT, 0) / GREATEST(NVL(B.NUM_ROWS, 1), 1)),
                     2) CHAIN_PER,
               B.TABLESPACE_NAME O_TABLESPACE_NAME
          FROM SYS.DBA_SEGMENTS A, SYS.DBA_TABLES B, SYS.TS$ C
         WHERE A.OWNER = B.OWNER
           AND SEGMENT_NAME = TABLE_NAME
           AND SEGMENT_TYPE = 'TABLE'
           AND B.TABLESPACE_NAME = C.NAME)
 WHERE GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS, 0) /
                      GREATEST(NVL(HWM, 1), 1)),
                      2),
                0) > 50
   AND OWNER NOT LIKE '%SYS%'
   AND BLOCKS > 100
 ORDER BY WASTE_PER DESC; 
  1. sqlplus execution statistics collection table

Before reducing the waste of space in the table, you must perform ANALYZE operation, and then collect and ANALYZE the results, otherwise the accuracy of HWM analysis is not high

ANALYZE TABLE  B0204_QUERYCUSTSET ESTIMATE STATISTICS;

ANALYZE TABLE  B0204_QUERYCUSTSET  COMPUTE STATISTICS FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS;

execute dbms_stats.gather_table_stats(ownname => 'fssctest', tabname => 'B0204_QUERYCUSTSET' , estimate_percent => null ,method_opt => 'for all indexed columns' ,cascade => true);
  1. How many BLOCKS can be released by HWM analysis
select t.TABLE_NAME,t.NUM_ROWS,t.BLOCKS,t.empty_blocks,t.LAST_ANALYZED from dba_tables t where table_name in ('B0204_QUERYCUSTSET') and t.TABLESPACE_NAME='FSSCTEST';

SELECT COUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)) USED_BLOCK FROM  B0204_QUERYCUSTSET;

View current size

select segment_name,bytes/1024/1024 TSize_MB from dba_segments where segment_name='B0104_BILLPIPE'  and TABLESPACE_NAME='FSSCTEST';
  1. Reduce the shrink or move of the table: it can not only reduce the disk occupation, but also an important way to optimize efficiency

a. Reduce the method shrink (you need to allow the move first)

alter table B0204_QUERYCUSTSET enable row movement;
alter table B0204_QUERYCUSTSET shrink space;

b. Reduce the method move (index reconstruction is required)

alter table CORE_METADATA_HIST move;
alter index idx_name rebuild;

Tags: Operation & Maintenance Database Oracle

Posted by lorewap3 on Wed, 18 May 2022 03:56:25 +0300