Oracle数据文件收缩

随着数据库的使用，数据文件越来越大，有没有办法将标记为free的block挤掉

相关表：

DBA_DATA_FILES

DBA_DATA_FILES describes database files.

ColumnDatatypeNULLDescriptionFILE_NAMEVARCHAR2(513) Name of the database fileFILE_IDNUMBERNOT NULLFile identifier number of the database fileTABLESPACE_NAMEVARCHAR2(30)NOT NULLName of the tablespace to which the file belongsBYTESNUMBER Size of the file in bytesBLOCKSNUMBERNOT NULLSize of the file in Oracle blocksSTATUSVARCHAR2(9) File status: AVAILABLE or INVALID (INVALID means that the file number is not in use, for example, a file in a tablespace that was dropped)RELATIVE_FNONUMBER Relative file numberAUTOEXTENSIBLEVARCHAR2(3) Autoextensible indicatorMAXBYTESNUMBER Maximum file size in bytesMAXBLOCKSNUMBER Maximum file size in blocksINCREMENT_BYNUMBER Number of Oracle blocks used as autoextension incrementUSER_BYTESNUMBER The size of the file available for user data. The actual size of the file minus the USER_BYTES value is used to store file related metadata.USER_BLOCKSNUMBER Number of blocks which can be used by the dataONLINE_STATUSVARCHAR2(7) Online status of the file:

SYSOFF

SYSTEM

OFFLINE

ONLINE

RECOVER

DBA_FREE_SPACE

DBA_FREE_SPACE describes the free extents in all tablespaces in the database.

Note that if a datafile (or entire tablespace) is offline in a locally managed tablespace, you will not see any extent information. If an object has extents in an online file of the tablespace, you will see extent information about the offline datafile. However, if the object is entirely in the offline file, a query of this view will not return any records.

Related View

USER_FREE_SPACE describes the free extents in the tablespaces accessible to the current user.

ColumnDatatypeNULLDescriptionTABLESPACE_NAMEVARCHAR2(30) Name of the tablespace containing the extentFILE_IDNUMBER File identifier number of the file containing the extentBLOCK_IDNUMBER Starting block number of the extentBYTESNUMBER Size of the extent (in bytes) --free bytesBLOCKS NUMBER Size of the extent (in Oracle blocks)  --free blockRELATIVE_FNONUMBER Relative file number of the file containing the extent

DBA_EXTENTS

DBA_EXTENTS describes the extents comprising the segments in all tablespaces in the database.

Note that if a datafile (or entire tablespace) is offline in a locally managed tablespace, you will not see any extent information. If an object has extents in an online file of the tablespace, you will see extent information about the offline datafile. However, if the object is entirely in the offline file, a query of this view will not return any records.

Related View

USER_EXTENTS describes the extents comprising the segments owned by the current user's objects. This view does not display the OWNER, FILE_ID, BLOCK_ID, orRELATIVE_FNO columns.

ColumnDatatypeNULLDescriptionOWNERVARCHAR2(30) Owner of the segment associated with the extentSEGMENT_NAMEVARCHAR2(81) Name of the segment associated with the extentPARTITION_NAMEVARCHAR2(30) Object Partition Name (Set to NULL for non-partitioned objects)SEGMENT_TYPEVARCHAR2(18) Type of the segment: INDEX PARTITION, TABLE PARTITIONTABLESPACE_NAMEVARCHAR2(30) Name of the tablespace containing the extentEXTENT_IDNUMBER Extent number in the segmentFILE_IDNUMBER File identifier number of the file containing the extentBLOCK_IDNUMBER Starting block number of the extentBYTESNUMBER Size of the extent in bytesBLOCKSNUMBER Size of the extent in Oracle blocksRELATIVE_FNONUMBER Relative file number of the first extent block

alter database datafile 'F:\ORACLE\ORADATA\PURPLE\USERS_NEW' resize 5755M;alter database datafile 'F:\ORACLE\ORADATA\PURPLE\SYSTEM01.DBF' resize 4574M;alter database datafile 'F:\ORACLE\ORADATA\PURPLE\INDEX_NEW' resize 825M;alter database datafile 'F:\ORACLE\ORADATA\PURPLE\USERS01.DBF' resize 17980M;alter database datafile 'F:\ORACLE\ORADATA\PURPLE\SYSAUX01.DBF' resize 670M;alter database datafile 'F:\ORACLE\ORADATA\PURPLE\EXAMPLE01.DBF' resize 90M;alter database datafile 'F:\ORACLE\ORADATA\PURPLE\UNDOTBS01.DBF' resize 72M;

总共释放出7G的空间出来。