Oracle 收缩表空间

发布时间：2020-12-12 14:59:15 所属栏目：百科来源：网络整理

导读：测试环境磁盘空间不足，所以 drop 一些无用的大表，但是发现空间没有变化， df -h 还是没有释放出磁盘空间来。 SQL set line 200 SQL set pagesize 200 SQL col name format A150 1 ，查看表空间使用情况 SQL SELECTUPPER(F.TABLESPACE_NAME) "表空间名", 2

测试环境磁盘空间不足，所以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.6947.97% 16860.94 72

SYSAUX 900 689.9476.66% 210.06 204.94

SYSTEM 1110 1005.3190.57% 104.69 95.44

UAAP 500 143.3728.67% 356.63 290.38

UNDOTBS1 6485 331.25 5.11%6153.75 3534

USERS 461.25 394.4485.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:ORACLEORASERVERORADATAORCTSTUEXPOWERDESORCTSTU01.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,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@PD1 directory=dir_dump_t1schemas=orctstudumpfile=TEST2_PD_20150518.dmp

Export:Release 11.2.0.1.0 - Production on Mon May 18 17:06:42 2015

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 21G34G 38% /

tmpfs 12G2.1G 10G 18% /dev/shm

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

/dev/mapper/vg001-lv001

63G12G 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 34 5 67

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"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操作

参考文章地址： http://mp.weixin.qq.com/s?__biz=MjM5ODEzNDA4OA==&mid=210054237&idx=1&sn=a8a10b9275233876cc218616c7075422&scene=1&key=c468684b929d2be2b0ffb1ce3cccad13f8704c151f3b15e9fea067c200d7b5bb313c06d9c65bf99921aa10386672b9ea&ascene=1&uin=MTAwMDg2MTU4Mw%3D%3D&devicetype=webwx&version=70000001&pass_ticket=%2BXWq8edLrEPcVZeIuoKStojLYf546G8W1JvUNaVQXBTp02zaoqaiSoTTOdqwc3IG

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!