Oracle Lower table Space shrinkage

Under Oracle, when creating table spaces, there is typically no special requirement to use an auto-extensible table space for daily management, so that the table space is not monitored in everyday situations, and the need to add new physical files at around 80% is guaranteed to be stable without using the automatic growth table space. In Oracle, using an autogrow table space, the physical files in use increase as the table space increases and in practice may not use as many table spaces as the business changes. At this point, we can reclaim the disk resources used by the table space physical files used by the table space Reduction recycling under Oracle, and it is easy to reclaim the table space under Oracle, with the following default tablespaces in addition to the user-defined table spaces under Oracle's system:

Table Space	Description
EXAMPLE	If you have selected the instance Scheme option at installation time, there will be a tablespace in Oracle, and if not selected, this table space holds data in a variety of cases
Sysaux	The secondary space of the system table space. Other data objects that are primarily used to store data dictionaries, which can reduce the load on the system table space
SYSTEM	Holds data dictionaries, including tables, views, stored procedures, and so on related data
TEMP	Information that holds the tables and indexes processed by the SQL statement, such as the table space that is consumed when the data is sorted
UNDOTBS1	Table space to hold undo data
USERS	Typically used to store Oracle's user data

The default tablespace details can be found in the 3 dictionary tables under the SYS user dba_data_files, dba_free_space, dba_segments to the data objects stored in the relevant tablespace, The relevant records of the relevant physical files and owners of the type and tablespace are used to query the usage details of Oracle's table space below

select     f.tablespace_name  "Table space name", d.tot_grootte_mb  "tablespace size (M)", 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 ')  | |   '% '   ' use ratio ', f.total_bytes  ' free space (M) ', f.max_bytes  "Max 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&NBSP;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;

Shrinking with table space is also simple:

SELECT file_name from SYS. Dba_data_files WHERE tablespace_name = ' test '; #查询出TEST表空间的物理文件路径ALTER tablespace TEST coalesce; #回收表空间碎片ALTER DATABASE DataFile '/usr/local/u01/oracle/oradata/oracle/test.dbf ' RESIZE 2M; #回收表空间

Oracle Lower table Space shrinkage