Oracle tablespace shrinking

Oracle tablespace shrinking

In Oracle, when creating tablespaces, there is generally no special need to use automatically scalable tablespaces for daily management. In this way, you do not need to monitor the tablespace size in daily situations, we recommend that you add new physical files to ensure the stability of tablespaces that do not use auto-growth at around 80%, but use auto-growth tablespaces in Oracle, in use, its physical files will increase with the increase of the tablespace. In actual conditions, the physical files may not use so many tablespaces due to business changes, at this time, we can use the tablespace reduction and recovery in Oracle to reclaim the disk resources used by unused tablespace physical files. It is very easy to reclaim tablespaces in Oracle, in addition to custom tablespaces, the Oracle system also has the following default tablespaces:

Tablespace	Description
EXAMPLE	If the "instance Scheme" option is selected during installation, the tablespace will be available in Oracle. If not selected, the tablespace will not store the data of various instances.
SYSAUX	Auxiliary space of the SYSTEM tablespace. It is mainly used to store other data objects in the data dictionary, which can reduce the load on the SYSTEM tablespace.
SYSTEM	Stores data dictionaries, including tables, views, stored procedures, and other related data.
TEMP	Stores the table and index information processed by SQL statements. For example, this tablespace is occupied during data sorting.
UNDOTBS1	Tablespace for storing the Undo data
USERS	It is generally used to store Oracle user data.

For details about the default tablespace, you can use the DBA_DATA_FILES, DBA_FREE_SPACE, and DBA_SEGMENTS dictionary tables in sys to find the data objects, types, and physical files in the tablespace. related records of the owner, the following describes how to use the tablespace in Oracle:

SELECT      F.TABLESPACE_NAME  "Tablespace name" , 　　D.TOT_GROOTTE_MB  "Tablespace size (MB )" , 　　D.TOT_GROOTTE_MB - F.TOTAL_BYTES  "Used space (M )" , 　　TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2), '990.99' ) ||  '%'  "Usage ratio" , 　　F.TOTAL_BYTES  "Free Space (M )" , 　　F.MAX_BYTES  "Maximum block (M )" 　　 FROM  ( SELECT  TABLESPACE_NAME, 　　ROUND( SUM (BYTES) / (1024 * 1024), 2) TOTAL_BYTES, 　　ROUND( MAX (BYTES) / (1024 * 1024), 2) MAX_BYTES 　　 FROM  SYS.DBA_FREE_SPACE 　　 GROUP  BY  TABLESPACE_NAME) F, 　　( SELECT  DD.TABLESPACE_NAME, 　　 ROUND( SUM (DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB 　　 FROM  SYS.DBA_DATA_FILES DD 　　 GROUP  BY  DD.TABLESPACE_NAME) D 　　 WHERE  D.TABLESPACE_NAME = F.TABLESPACE_NAME         AND  F.TABLESPACE_NAME <>  'EXAMPLE'         AND  F.TABLESPACE_NAME <>  'SYSAUX'         AND  F.TABLESPACE_NAME <>  'SYSTEM'         AND  F.TABLESPACE_NAME <>  'UNDOTBS1'         AND  F.TABLESPACE_NAME <>  'USERS'         AND  F.TABLESPACE_NAME <>  'EXAMPLE' 　　 ORDER  BY  1;

The use of table space shrinking is also very simple:

SELECT  FILE_NAME  FROM  SYS.DBA_DATA_FILES  WHERE  TABLESPACE_NAME =  'TEST' ; # Query the physical file path of the TEST tablespace ALTER  TABLESPACE TEST  COALESCE ; # Reclaim tablespace fragments ALTER  DATABASE  DATAFILE  '/usr/local/u01/oracle/oradata/oracle/test.dbf'  RESIZE 2 M; # reclaim tablespaces