在oracle日常运维中，经常遇到表空间的使用率达到阀值，需要进行扩容的问题。我们可以通过 alter database datafile ' datafile name' resize XXXX m 或者通过 alter alter database add datafile 'datafile name' size XXXX m进行扩展。
当我们一不小心为表空间分配了很大空间，使用率非常低，需要对表空间继续收缩，那我们如何进行呢

1 确定需要收缩的表空间，FREE%值大的

点击(此处)折叠或打开

[oracle@wqdb01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 5 13:04:30 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> set linesize 20
SQL> set linesize 200
SQL> SELECT A.TABLESPACE_NAME,
(A.BYTES / 1024 / 1024) TOTAL_M,
2 3 (B.BYTES / 1024 / 1024) USED_M,
(C.BYTES / 1024 / 1024) FREE_M,
TO_CHAR(B.BYTES / A.BYTES * 100, '999.99') || '%' "USED%",
TO_CHAR(C.BYTES * 100 / A.BYTES, '999.99') || '%' "FREE%"
FROM SYS.SM$TS_AVAIL A, SYS.SM$TS_USED B, SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
AND A.TABLESPACE_NAME = C.TABLESPACE_NAME
order by "FREE%";
SQL> /

TABLESPACE_NAME TOTAL_M USED_M FREE_M USED% FREE%
------------------------------ ---------- ---------- ---------- -------- --------
BUSDATA 800 292 507 36.50% 63.38%
UNDOTBS1 51860 106.6875 51752.3125 .21% 99.79%
SYSAUX 5186 2350.3125 2834.6875 45.32% 54.66%
TOPO 4096 1610 2485 39.31% 60.67%
USERS 5 .125 3.875 2.50% 77.50%
SYSTEM 5186 813.875 4371.125 15.69% 84.29%
INTERFACE_INDEX 11777 1448.6875 10327.3125 12.30% 87.69%
UNDOTBS2 5186 117.0625 5067.9375 2.26% 97.72%
QUERYDATA 4096 2045.4375 2049.5625 49.94% 50.04%
INTERFACE_DATA 186840 9318.9375 177510.563 4.99% 95.01%
INDS_NEW 10240 1120.5 9118.5 10.94% 89.05%

TABLESPACE_NAME TOTAL_M USED_M FREE_M USED% FREE%
------------------------------ ---------- ---------- ---------- -------- --------
MTS 60000 29526.0625 30473 49.21% 50.79%
MAPDATA 13312 6435.5 6875.5 48.34% 51.65%

13 rows selected.

2 确定表的TS#，和最大的块值block_id

点击(此处)折叠或打开

SQL> select max(block_id) from dba_extents where file_id=12;

MAX(BLOCK_ID)
-------------
1392128

3 oracle系统的默认的块大小

点击(此处)折叠或打开

SQL> show parameter db_block_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 16384

SQL> select 1392128*16/1024 from dual;

1392128*16/1024
---------------
21752

4 收缩表空间的值必须大于21752m，否则报ORA-3297错误

[oracle@wqdb01 trace]$ oerr ora 3297
03297, 00000, "file contains used data beyond requested RESIZE value"
// *Cause: Some portion of the file in the region to be trimmed is
//          currently in use by a database object
// *Action: Drop or move segments containing extents in this region prior to
//          resizing the file, or choose a resize value such that only free
//          space is in the trimmed.

点击(此处)折叠或打开

SQL> alter database datafile '+DATA/inds/datafile/interface_data/interface_data3.dbf' resize 23000M;
Database altered.
SQL> SELECT A.TABLESPACE_NAME,
       (A.BYTES / 1024 / 1024) TOTAL_M,
       (B.BYTES / 1024 / 1024) USED_M,
       (C.BYTES / 1024 / 1024) FREE_M,
       TO_CHAR(B.BYTES / A.BYTES * 100, '999.99') || '%' "USED%",
       TO_CHAR(C.BYTES * 100 / A.BYTES, '999.99') || '%' "FREE%"
FROM SYS.SM$TS_AVAIL A, SYS.SM$TS_USED B, SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
   AND A.TABLESPACE_NAME = C.TABLESPACE_NAME
   order by "FREE%"; 4    5    6    7    8    9   10

TABLESPACE_NAME                   TOTAL_M     USED_M     FREE_M USED%    FREE%
------------------------------ ---------- ---------- ---------- -------- --------
QUERYDATA                            4096 2045.4375 2049.5625   49.94%   50.04%
MTS                                 60000 29526.0625      30473   49.21%   50.79%
MAPDATA                             13312     6435.5     6875.5   48.34%   51.65%
SYSAUX                               5186 2350.3125 2834.6875   45.32%   54.66%
TOPO                                 4096       1610       2485   39.31%   60.67%
BUSDATA                               800        292        507   36.50%   63.38%
USERS                                   5       .125      3.875    2.50%   77.50%
SYSTEM                               5186    813.875   4371.125   15.69%   84.29%
INTERFACE_INDEX                     11777 1448.6875 10327.3125   12.30%   87.69%
INDS_NEW                            10240     1120.5     9118.5   10.94%   89.05%
INTERFACE_DATA                     179120 9318.9375 169790.563    5.20%   94.79%

TABLESPACE_NAME                   TOTAL_M     USED_M     FREE_M USED%    FREE%
------------------------------ ---------- ---------- ---------- -------- --------
UNDOTBS2                             5186   117.0625 5067.9375    2.26%   97.72%
UNDOTBS1                            51860   106.6875 51752.3125     .21%   99.79%

13 rows selected.

ITPub博客

oracle 表空间的管理

最新文章