oracle数据文件收缩,Oracle数据文件收缩

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

相关表：

DBA_DATA_FILES

DBA_DATA_FILES describes database files.

Column

Datatype

NULL

Description

FILE_NAME

VARCHAR2(513)

Name of the database file

FILE_ID

NUMBER

NOT NULL

File identifier number of the database file

TABLESPACE_NAME

VARCHAR2(30)

NOT NULL

Name of the tablespace to which the file belongs

BYTES

NUMBER

Size of the file in bytes

BLOCKS

NUMBER

NOT NULL

Size of the file in Oracle blocks

STATUS

VARCHAR2(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_FNO

NUMBER

Relative file number

AUTOEXTENSIBLE

VARCHAR2(3)

Autoextensible indicator

MAXBYTES

NUMBER

Maximum file size in bytes

MAXBLOCKS

NUMBER

Maximum file size in blocks

INCREMENT_BY

NUMBER

Number of Oracle blocks used as autoextension increment

USER_BYTES

NUMBER

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_BLOCKS

NUMBER

Number of blocks which can be used by the data

ONLINE_STATUS

VARCHAR2(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.

Column

Datatype

NULL

Description

TABLESPACE_NAME

VARCHAR2(30)

Name of the tablespace containing the extent

FILE_ID

NUMBER

File identifier number of the file containing the extent

BLOCK_ID

NUMBER

Starting block number of the extent

BYTES

NUMBER

Size of the extent (in bytes) --free bytes

BLOCKS

NUMBER

Size of the extent (in Oracle blocks)  --free block

RELATIVE_FNO

NUMBER

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.

Column

Datatype

NULL

Description

OWNER

VARCHAR2(30)

Owner of the segment associated with the extent

SEGMENT_NAME

VARCHAR2(81)

Name of the segment associated with the extent

PARTITION_NAME

VARCHAR2(30)

Object Partition Name (Set to NULL for non-partitioned objects)

SEGMENT_TYPE

VARCHAR2(18)

Type of the segment: INDEX PARTITION, TABLE PARTITION

TABLESPACE_NAME

VARCHAR2(30)

Name of the tablespace containing the extent

EXTENT_ID

NUMBER

Extent number in the segment

FILE_ID

NUMBER

File identifier number of the file containing the extent

BLOCK_ID

NUMBER

Starting block number of the extent

BYTES

NUMBER

Size of the extent in bytes

BLOCKS

NUMBER

Size of the extent in Oracle blocks

RELATIVE_FNO

NUMBER

Relative file number of the first extent block

SELECT 'alter database datafile '

||chr(39)

||file_name

||chr(39)

|| ' resize '

|| ceil(hwmsize*1.2)

||'M;'

FROM

(SELECT 　　a.file_id,

a.file_name,

a.filesize,

b.freesize,

(a.filesize-b.freesize) usedsize,

c.hwmsize,

c.hwmsize - (a.filesize-b.freesize) unsedsize_belowhwm,

a.filesize - c.hwmsize canshrinksize

FROM 　　( 　　select file_id,file_name,ROUND(bytes/1024/1024) filesize

FROM dba_data_files 　　) a,

( 　　select file_id,ROUND(SUM(dfs.bytes)/1024/1024) freesize

FROM dba_free_space dfs 　　group BY file_id 　　) b,

( 　　select file_id,ROUND(MAX(block_id)*8/1024) HWMsize

FROM dba_extents 　　group BY file_id) c 　　where a.file_id = b.file_id 　　and a.file_id = c.file_id 　　order BY unsedsize_belowhwm DESC

)

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的空间出来。