ORACLE11G收縮表空間報錯ORA-03297:filecontainsuseddatabeyondrequestedRESIZEvalue

ORACLE11G收縮表空間報錯ORA-03297:filecontainsuseddatabeyondrequestedRESIZEvalue

測試環境磁盤空間不足，所以drop一些無用的大表，但是發現空間沒有變化，df -h還是沒有釋放出磁盤空間來。

SQL> set line 200

SQL> set pagesize 200

SQL> col name format A150

1，查看表空間使用情況

SQL> SELECTUPPER(F.TABLESPACE_NAME) "表空間名",
  2        　　D.TOT_GROOTTE_MB "表空間大小(M)",
  3        　　D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空間(M)",
  4        　　TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) /D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
  5        　　F.TOTAL_BYTES "空閒空間(M)",
  6        　　F.MAX_BYTES "最大塊(M)"
  7        　　FROM (SELECT TABLESPACE_NAME,
  8        　　ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
  9        　　ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
 10        　　FROM SYS.DBA_FREE_SPACE
 11        　　GROUP BY TABLESPACE_NAME) F,
 12        　　(SELECT DD.TABLESPACE_NAME,
 13        　　 ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
 14        　　FROM SYS.DBA_DATA_FILES DD
 15        　　GROUP BY DD.TABLESPACE_NAME) D
 16        　　WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
 17        　　ORDER BY 1;
 
表空間名                            表空間大小(M)     已使用空間(M) 使用比       空閒空間(M)    最大塊(M)
------------------------------------------- ------------- -------- ----------- ----------
HELP                                     500          5.19    1.04%     494.81     494.81
ORCTSTU                           32406.63      15545.69  47.97%    16860.94         72
SYSAUX                                   900        689.94  76.66%      210.06     204.94
SYSTEM                                  1110       1005.31  90.57%      104.69      95.44
UAAP                                     500        143.37  28.67%      356.63     290.38
UNDOTBS1                                6485        331.25    5.11%    6153.75       3534
USERS                                 461.25        394.44  85.52%       66.81      22.19
10 rows selected
 
SQL>

看到ORCTSTU表空間只使用了49%，使用了15G空間，而ORCTSTU表空間占據的總磁盤空間為32G，所以我們可以收縮ORCTSTU到16G左右，這樣就釋放出了將近16G的空間了。

去查看下此表空間所在的數據文件，如下所示：

SQL> SELECT file_id,file_name FROM DBA_DATA_FILES D WHERED.TABLESPACE_NAME = 'ORCTSTU';
   FILE_ID FILE_NAME
------------------------------------------------------------------------------------------
         5D:\ORACLE\ORASERVER\ORADATA\ORCTSTUEX\POWERDES\ORCTSTU01.DBF
 
SQL>

2，resize收縮報錯：

准備收縮到18G，執行如下報錯

SQL> alter database datafile'/home/oradata/powerdes/orctstu01.dbf' resize 1800M;

alter database datafile'/home/oradata/powerdes/orctstu01.dbf' resize 1800M

*

ERROR at line 1:

ORA-03297: file contains used data beyondrequested RESIZE value

SQL>

參考命令：

select file_id,max(block_id+blocks-1)HWM,block_id

from dba_extents

where file_id=5

group by file_id,block_id;

3，去分析情況這個數據文件

可以看到基本沒有任何改變，但是根據我的直觀感覺，確實沒有多少表了，空間也確實都騰出來了。可以簡單的驗證一下,數據文件是5號，使用dba_extents可以看到占用的空間情況和對應的塊的情況。

select file_id,max(block_id+blocks-1)HWM,block_id

from dba_extents

where file_id=5

group by file_id,block_id;

 

6519 5 4194047 4193920

3469 5 4187263 4186368

8137 5 4186367 4186240

3919 5 4186239 4186112

3033 5 4186111 4185984

9526 5 4185983 4185856

9113 5 4185855 4184832

9669 5 4184775 4184768

1166 5 4184767 4184760

2304 5 4184743 4184736

7215 5 4184735 4184728

4933 5 4184727 4184720

......

通過對比HWM和block_id的值，看到有很多都是空間占用差別比較大的。

4，查看以下數據文件的最大的block_id值

我們來在這個基礎上做一個簡單的分析。首先得到5號數據文件中，塊號最大的數據塊block_id。

 

SQL> SELECT MAX(block_id) FROMdba_extents WHERE tablespace_name = 'ORCTSTU';

MAX(BLOCK_ID)

-------------

4193920

SQL>

值為： 4193920

再查看下一個block的容量大小

SQL> show parameter db_block_size;

NAME TYPE VALUE

----------------------------------------------- ------------------------------

db_block_size integer 8192

SQL>

5，根據這個值查看對於的數據文件所占據的磁盤空間大小

SELECT 4193920*8192/1024/1024 FROM dual;

SQL> SELECT 4193920*8192/1024/1024/1024FROM dual;

4193920*8192/1024/1024/1024

---------------------------

31.9970703

SQL>

計算出來大概是32G左右，再去磁盤看實際的數據文件大小

[oracle@edustu4 ~]$ ll -h/home/oradata/powerdes/orctstu01.dbf

-rw-r-----. 1 oracle oinstall 32G May 1618:06 /home/oradata/powerdes/orctstu01.dbf

[oracle@edustu4 ~]$

也是32G左右，和實際的磁盤的數據文件的大小一致，

看看這個最大4193920的數據塊所在的extent對應的segment信息是否是已經被drop到的table？

select segment_name,owner from dba_extentswhere block_id=3507584;

SQL> select segment_name,owner fromdba_extents where block_id=1942656;

SEGMENT_NAME

--------------------------------------------------------------------------------

OWNER

------------------------------

RES_APPROVE_SHARE

ORCTSTU

SQL>

6，分析問題所在

這個表不是一件被drop的哪些表記錄，表RES_APPROVE_SHARE正在被應用程序使用著，也就說明了報錯，所以resize的時候報錯ORA-03297: file contains used data beyond requested RESIZE value，block不能釋放掉，因為正在被使用。

這個時候通過正常的resize已經不能解決問題了，怎麼辦？可以采用expdp、impdp的方式重新生成新的表空間和數據文件，然後刪除舊的表空間和數據文件。

7，開始新建表空間

create tablespace ORCTSTU_2

logging

datafile '/home/oradata/powerdes/orctstu02.dbf'

size 50m

autoextend on

next 50m ;

alter table RES_APPROVE_SHARE move ORCTSTU_2;

ORA-14133: ALTER TABLE MOVE cannot becombined with other operations

alter table orctstu.RES_APPROVE_SHARE movetablespace ORCTSTU_2;

8，使用expdp導出數據

先建立管道目錄

CREATEOR REPLACE DIRECTORY dir_dump_t1 AS'/home/oracle/expdpimpdp';

開始導出export

                              [oracle@edustu4~]$ expdp  orctstu/testpd2015@PD1directory=dir_dump_t1 schemas=orctstu dumpfile=TEST2_PD_20150518.dmp
 
                                Export:Release 11.2.0.1.0 - Production on Mon May 18 17:06:42 2015
 
                                Copyright(c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
 
                                Connectedto: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bitProduction
                                Withthe Partitioning, OLAP, Data Mining and Real Application Testing options
                                Starting"ORCTSTU"."SYS_EXPORT_SCHEMA_02":  orctstu/********@PD1 directory=dir_dump_t1schemas=orctstu dumpfile=TEST2_PD_20150518.dmp
                                Estimatein progress using BLOCKS method...
                                Processingobject type SCHEMA_EXPORT/TABLE/TABLE_DATA
                                Totalestimation using BLOCKS method: 7.483 GB
                                Processingobject type SCHEMA_EXPORT/USER
                                Processingobject type SCHEMA_EXPORT/SYSTEM_GRANT
                                .....................................................
                                Processingobject type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
                                .. exported "ORCTSTU"."RES_APPROVE_CONTENT"           44.01 MB  350923 rows
                                .. exported "ORCTSTU"."RECEIPT_BILL"                  569.3 MB 2064823 rows
                                .....................................................
                                .. exported "ORCTSTU"."ZS_PLAN_LEAVE_MESSAGE"             0 KB       0 rows
                                .. exported "ORCTSTU"."ZS_PLAN_MESSAGE"                   0 KB       0 rows
                                Mastertable "ORCTSTU"."SYS_EXPORT_SCHEMA_02" successfullyloaded/unloaded
                                ******************************************************************************
                                Dumpfile set for ORCTSTU.SYS_EXPORT_SCHEMA_02 is:
                                  /home/oracle/expdpimpdp/TEST2_PD_20150518.dmp
                                Job"ORCTSTU"."SYS_EXPORT_SCHEMA_02" successfully completed at17:11:13
 
                                [oracle@edustu4~]$

9，開始使用import導入數據

9.1 清理舊數據

刪除用戶

drop user orctstu cascade;

刪除表空間

drop tablespace orctstu including contents anddatafiles;

然後重啟oracle

shutdown immediate

startup

查看磁盤空間，已經釋放出來了

[oracle@edustu4 expdpimpdp]$ df -h

Filesystem Size Used Avail Use% Mounted on

/dev/sda3 57G 21G 34G 38% /

tmpfs 12G 2.1G 10G 18% /dev/shm

/dev/sda1 194M 32M 153M 18% /boot

/dev/mapper/vg001-lv001

63G 12G 49G 20% /home/oradata

df:`/root/.gvfs': Permission denied

[oracle@edustu4expdpimpdp]$

9.2開始建立新用戶

重新創建用戶

createtablespace ORCTSTU

logging

datafile'/home/oradata/powerdes/orctstu01.dbf'

size50m

autoextendon

next50m

extentmanagement local;

CREATEUSER orctstu PROFILE "DEFAULT" IDENTIFIED BY "testpd2015" DEFAULT TABLESPACE ORCTSTU ACCOUNTUNLOCK;

GRANTconnect,resource TO orctstu;

grantdba to orctstu;

SQL>create tablespace ORCTSTU

logging

datafile'/home/oradata/powerdes/orctstu01.dbf'

size50m

autoextendon

next50m

extentmanagement local; 2 3 4 5 6 7

Tablespacecreated.

SQL>

SQL>CREATE USER orctstu PROFILE "DEFAULT" IDENTIFIED BY "testpd2015"DEFAULT TABLESPACE ORCTSTU ACCOUNT UNLOCK;

Usercreated.

SQL>GRANT connect,resource TO orctstu;

Grantsucceeded.

SQL>grant dba to orctstu;

Grantsucceeded.

SQL>

9.3 開始導入備份的數據

導入命令：

impdporctstu/testpd2015@PD1 directory=dir_dump_t1 dumpfile=TEST2_PD_20150518.dmpnologfile=y

 

導入過程如下：

......

Processingobject type SCHEMA_EXPORT/EVENT/TRIGGER

ORA-39082:Object type TRIGGER:"ORCTSTU"."LOGON_DENIED_TO_ALERT" createdwith compilation warnings

ORA-39082:Object type TRIGGER:"ORCTSTU"."LOGON_DENIED_TO_ALERT"created with compilation warnings

Processingobject type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX

Processingobject typeSCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS

Processingobject type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processingobject type SCHEMA_EXPORT/JOB

Processingobject type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ

Job"ORCTSTU"."SYS_IMPORT_FULL_01" completed with 45 error(s)at 20:36:08

 

[oracle@edustu4admin]$

 

PS:impdp導入的時候，是從最大的表開始導入的，先導入數據，最後重建索引，導入各種其它objects。

 

 

10，報錯記錄

SQL> drop user orctstu cascade;

drop user orctstu cascade

*

ERROR at line 1:

ORA-04098: trigger 'ORCTSTU.LOGON_DENIED_TO_ALERT'is invalid and failed

re-validation

ORA-01940: cannot drop a user that iscurrently connected

SQL>

 

解決辦法：直接lsnrctl stop;然後重新執行drop user操作