电脑知识|欧美黑人一区二区三区|软件|欧美黑人一级爽快片淫片高清|系统|欧美黑人狂野猛交老妇|数据库|服务器|编程开发|网络运营|知识问答|技术教程文章 - 好吧啦网

您的位置:首頁技術文章
文章詳情頁

Oracle縮表空間的完整解決實例

瀏覽:95日期:2023-03-12 15:25:27
目錄
  • 備注:
  • 一. 需求
  • 二. 解決方案
    • 2.1 清理過期數據
    • 2.2 收縮表空間
    • 2.3 清理表碎片
    • 2.4 直接把相關的表drop掉
    • 2.5 把該表空間下其它的表移出此表空間
  • 總結

    備注:

    Oracle 11.2.0.4

    一. 需求

    近期有一個日志庫,占用了比較多的空間,需要將歷史的清理,然后收縮空間。

    如下圖所示,4T的空間已經差不多用完。

    二. 解決方案

    首先想到的是清理掉超過半年的數據,然后resize 表空間。

    2.1 清理過期數據

    因為業務的表是 tablename_yearmonth格式,例如 log_202204,每個月一個表,所以直接進行truncate即可。

    找到大表:

    select t.segment_name,t.BYTES/1024/1024/1024 GB,t.segment_typefrom user_segments twhere t.segment_type in ("TABLE","TABLE PARTITION")order by nvl(t.BYTES/1024/1024/1024,0) desc;

    truncate 大表:

    select  "truncate table "|| t.TABLE_NAME ||";"  from user_tables t where t.TABLE_NAME  like "LOG%";

    2.2 收縮表空間

    select a.tablespace_name,a.file_name,a.totalsize as totalsize_MB,b.freesize as freesize_MB,"ALTER DATABASE DATAFILE """ || a.file_name || """ RESIZE " ||round((a.totalsize - b.freesize) + 200) || "M;" as "alter datafile"from (select a.file_name,a.file_id,a.tablespace_name,a.bytes / 1024 / 1024 as totalsizefrom dba_data_files a) a,(select b.tablespace_name,b.file_id,sum(b.bytes / 1024 / 1024) as freesizefrom dba_free_space bgroup by b.tablespace_name, b.file_id) bwhere a.file_id = b.file_idand b.freesize > 100and a.tablespace_name  in ("TBS_LOG_DATA")order by a.tablespace_name

    將上一步的 alter datafile語句拷貝出來執行:

    有部分報錯:

    ORA-03297: file contains used data beyond requested RESIZE value

    2.3 清理表碎片

    因為我使用的是truncate,理論上不會受高水位的影響,在網上找了幾個博客,也是說要降低表的高水位,清理表碎片。

    select "alter table "||t.TABLE_NAME||" enable row movement;",       "alter table "||t.TABLE_NAME||" shrink space cascade;"  from user_tables t where t.TABLE_NAME like "LOG%";

    清理完碎片之后,重新執行,依舊報錯。

    2.4 直接把相關的表drop掉

    select  "drop table "|| t.TABLE_NAME ||"purge;"  from user_tables t where t.TABLE_NAME  like "LOG%";

    drop掉表之后,重新執行,依舊報錯。

    2.5 把該表空間下其它的表移出此表空間

    萬能的itpub上有個博客:

    Truncate table 或者 drop table 收縮數據文件,經常遇到ORA-03297: file contains used data beyond requested RESIZE value 查詢dba_free_space 也有空閑空間。經過查詢MOS(Doc ID 1029252.6)得知

    If you have a large extent in the middle of a datafile, and some object taking up room at the end of the datafile, you can use the query FINDEXT.SQL below to find this object. If you export this object, then drop it, you should then free up contiguous space at the end of your datafile so you will be able to resize it smaller.

    Make sure you leave enough room in the datafile for importing the object back into the tablespace.

    意思是說如果空閑的extent如果在文件的中間,此時無法進行resize ,必須把尾部的object drop 然后重建 再resize datafile。以下是本人做的測試;

    ?[oracle@bogon ~]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.1.0 - Production on Wed Jul 31 11:10:41 2013Copyright (c) 1982, 2005, Oracle. ?All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP and Data Mining optionsSQL> create tablespace test2 datafile "/u01/app/oracle/oradata/orcl/test2.dbf" size 10M autoextend on next 1M;Tablespace created.SQL> create table tab1 tablespace test2 as select * from dba_objects;Table created.SQL> select file#,name,bytes/1024/1024 bytes from v$datafile where name like "%test2%";FILE# NAME ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? BYTES----- ------------------------------------------------------------ -----? ?23 /u01/app/oracle/oradata/orcl/test2.dbf ? ? ? ? ? ? ? ? ? ? ? ? ?11SQL> create table tab2 tablespace test2 as select * from dba_objects;Table created.SQL> select file#,name,bytes/1024/1024 bytes from v$datafile where name like "%test2%";FILE# NAME ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? BYTES----- ------------------------------------------------------------ -----? ?23 /u01/app/oracle/oradata/orcl/test2.dbf ? ? ? ? ? ? ? ? ? ? ? ? ?21SQL> select SEGMENT_NAME,FILE_ID,EXTENT_ID,BLOCK_ID,blocks from dba_extents where file_id=23 order by BLOCK_ID;SEGMENT_NA ? ?FILE_ID ?EXTENT_ID ? BLOCK_ID ? ? BLOCKS---------- ---------- ---------- ---------- ----------TAB1 ? ? ? ? ? ? ? 23 ? ? ? ? ?0 ? ? ? ? ?9 ? ? ? ? ?8TAB1 ? ? ? ? ? ? ? 23 ? ? ? ? ?1 ? ? ? ? 17 ? ? ? ? ?8TAB1 ? ? ? ? ? ? ? 23 ? ? ? ? ?2 ? ? ? ? 25 ? ? ? ? ?8TAB1 ? ? ? ? ? ? ? 23 ? ? ? ? ?3 ? ? ? ? 33 ? ? ? ? ?8TAB1 ? ? ? ? ? ? ? 23 ? ? ? ? ?4 ? ? ? ? 41 ? ? ? ? ?8TAB1 ? ? ? ? ? ? ? 23 ? ? ? ? ?5 ? ? ? ? 49 ? ? ? ? ?8TAB1 ? ? ? ? ? ? ? 23 ? ? ? ? ?6 ? ? ? ? 57 ? ? ? ? ?8TAB1 ? ? ? ? ? ? ? 23 ? ? ? ? ?7 ? ? ? ? 65 ? ? ? ? ?8TAB1 ? ? ? ? ? ? ? 23 ? ? ? ? ?8 ? ? ? ? 73 ? ? ? ? ?8TAB1 ? ? ? ? ? ? ? 23 ? ? ? ? ?9 ? ? ? ? 81 ? ? ? ? ?8TAB1 ? ? ? ? ? ? ? 23 ? ? ? ? 10 ? ? ? ? 89 ? ? ? ? ?8SEGMENT_NA ? ?FILE_ID ?EXTENT_ID ? BLOCK_ID ? ? BLOCKS---------- ---------- ---------- ---------- ----------TAB1 ? ? ? ? ? ? ? 23 ? ? ? ? 11 ? ? ? ? 97 ? ? ? ? ?8TAB1 ? ? ? ? ? ? ? 23 ? ? ? ? 12 ? ? ? ?105 ? ? ? ? ?8TAB1 ? ? ? ? ? ? ? 23 ? ? ? ? 13 ? ? ? ?113 ? ? ? ? ?8TAB1 ? ? ? ? ? ? ? 23 ? ? ? ? 14 ? ? ? ?121 ? ? ? ? ?8TAB1 ? ? ? ? ? ? ? 23 ? ? ? ? 15 ? ? ? ?129 ? ? ? ? ?8TAB1 ? ? ? ? ? ? ? 23 ? ? ? ? 16 ? ? ? ?137 ? ? ? ?128TAB1 ? ? ? ? ? ? ? 23 ? ? ? ? 17 ? ? ? ?265 ? ? ? ?128TAB1 ? ? ? ? ? ? ? 23 ? ? ? ? 18 ? ? ? ?393 ? ? ? ?128TAB1 ? ? ? ? ? ? ? 23 ? ? ? ? 19 ? ? ? ?521 ? ? ? ?128TAB1 ? ? ? ? ? ? ? 23 ? ? ? ? 20 ? ? ? ?649 ? ? ? ?128TAB1 ? ? ? ? ? ? ? 23 ? ? ? ? 21 ? ? ? ?777 ? ? ? ?128SEGMENT_NA ? ?FILE_ID ?EXTENT_ID ? BLOCK_ID ? ? BLOCKS---------- ---------- ---------- ---------- ----------TAB1 ? ? ? ? ? ? ? 23 ? ? ? ? 22 ? ? ? ?905 ? ? ? ?128TAB1 ? ? ? ? ? ? ? 23 ? ? ? ? 23 ? ? ? 1033 ? ? ? ?128TAB1 ? ? ? ? ? ? ? 23 ? ? ? ? 24 ? ? ? 1161 ? ? ? ?128TAB2 ? ? ? ? ? ? ? 23 ? ? ? ? ?0 ? ? ? 1289 ? ? ? ? ?8TAB2 ? ? ? ? ? ? ? 23 ? ? ? ? ?1 ? ? ? 1297 ? ? ? ? ?8TAB2 ? ? ? ? ? ? ? 23 ? ? ? ? ?2 ? ? ? 1305 ? ? ? ? ?8TAB2 ? ? ? ? ? ? ? 23 ? ? ? ? ?3 ? ? ? 1313 ? ? ? ? ?8TAB2 ? ? ? ? ? ? ? 23 ? ? ? ? ?4 ? ? ? 1321 ? ? ? ? ?8TAB2 ? ? ? ? ? ? ? 23 ? ? ? ? ?5 ? ? ? 1329 ? ? ? ? ?8TAB2 ? ? ? ? ? ? ? 23 ? ? ? ? ?6 ? ? ? 1337 ? ? ? ? ?8TAB2 ? ? ? ? ? ? ? 23 ? ? ? ? ?7 ? ? ? 1345 ? ? ? ? ?8SEGMENT_NA ? ?FILE_ID ?EXTENT_ID ? BLOCK_ID ? ? BLOCKS---------- ---------- ---------- ---------- ----------TAB2 ? ? ? ? ? ? ? 23 ? ? ? ? ?8 ? ? ? 1353 ? ? ? ? ?8TAB2 ? ? ? ? ? ? ? 23 ? ? ? ? ?9 ? ? ? 1361 ? ? ? ? ?8TAB2 ? ? ? ? ? ? ? 23 ? ? ? ? 10 ? ? ? 1369 ? ? ? ? ?8TAB2 ? ? ? ? ? ? ? 23 ? ? ? ? 11 ? ? ? 1377 ? ? ? ? ?8TAB2 ? ? ? ? ? ? ? 23 ? ? ? ? 12 ? ? ? 1385 ? ? ? ? ?8TAB2 ? ? ? ? ? ? ? 23 ? ? ? ? 13 ? ? ? 1393 ? ? ? ? ?8TAB2 ? ? ? ? ? ? ? 23 ? ? ? ? 14 ? ? ? 1401 ? ? ? ? ?8TAB2 ? ? ? ? ? ? ? 23 ? ? ? ? 15 ? ? ? 1409 ? ? ? ? ?8TAB2 ? ? ? ? ? ? ? 23 ? ? ? ? 16 ? ? ? 1417 ? ? ? ?128TAB2 ? ? ? ? ? ? ? 23 ? ? ? ? 17 ? ? ? 1545 ? ? ? ?128TAB2 ? ? ? ? ? ? ? 23 ? ? ? ? 18 ? ? ? 1673 ? ? ? ?128SEGMENT_NA ? ?FILE_ID ?EXTENT_ID ? BLOCK_ID ? ? BLOCKS---------- ---------- ---------- ---------- ----------TAB2 ? ? ? ? ? ? ? 23 ? ? ? ? 19 ? ? ? 1801 ? ? ? ?128TAB2 ? ? ? ? ? ? ? 23 ? ? ? ? 20 ? ? ? 1929 ? ? ? ?128TAB2 ? ? ? ? ? ? ? 23 ? ? ? ? 21 ? ? ? 2057 ? ? ? ?128TAB2 ? ? ? ? ? ? ? 23 ? ? ? ? 22 ? ? ? 2185 ? ? ? ?128TAB2 ? ? ? ? ? ? ? 23 ? ? ? ? 23 ? ? ? 2313 ? ? ? ?128TAB2 ? ? ? ? ? ? ? 23 ? ? ? ? 24 ? ? ? 2441 ? ? ? ?128

    50 rows selected.

    Block_id 是連續的

    SQL> truncate table tab1? 2 ?;Table truncated.SQL> select * from dba_free_space where file_id=23;TABLESPACE_NAME ? ? ? ? FILE_ID ? BLOCK_ID ? ? ?BYTES ? ? BLOCKS RELATIVE_FNO-------------------- ---------- ---------- ---------- ---------- ------------TEST2 ? ? ? ? ? ? ? ? ? ? ? ?23 ? ? ? ? 17 ########## ? ? ? 1272 ? ? ? ? ? 23TEST2 ? ? ? ? ? ? ? ? ? ? ? ?23 ? ? ? 2569 ########## ? ? ? ?120 ? ? ? ? ? 23

    有原來tab1 的free blocks 1272

    SQL> alter database datafile "/u01/app/oracle/oradata/orcl/test2.dbf" resize 12M;alter database datafile "/u01/app/oracle/oradata/orcl/test2.dbf" resize 12M*ERROR at line 1:ORA-03297: file contains used data beyond requested RESIZE value

    無法進行resize

    下面把tab1 drop 再測試

    SQL> drop table tab1 purge;Table dropped.SQL> alter database datafile "/u01/app/oracle/oradata/orcl/test2.dbf" resize 12M;alter database datafile "/u01/app/oracle/oradata/orcl/test2.dbf" resize 12M*ERROR at line 1:ORA-03297: file contains used data beyond requested RESIZE value

    依然報錯

    然后truncate tab2 再進行測試

    SQL> truncate table tab2;Table truncated.SQL> select * from dba_free_space where file_id=23;TABLESPACE_NAME ? ? ? ? FILE_ID ? BLOCK_ID ? ? ?BYTES ? ? BLOCKS RELATIVE_FNO-------------------- ---------- ---------- ---------- ---------- ------------TEST2 ? ? ? ? ? ? ? ? ? ? ? ?23 ? ? ? ? ?9 ########## ? ? ? 1280 ? ? ? ? ? 23TEST2 ? ? ? ? ? ? ? ? ? ? ? ?23 ? ? ? 1297 ########## ? ? ? 1392 ? ? ? ? ? 23SQL> alter database datafile "/u01/app/oracle/oradata/orcl/test2.dbf" resize 12M;Database altered.SQL> alter database datafile "/u01/app/oracle/oradata/orcl/test2.dbf" resize 6M;alter database datafile "/u01/app/oracle/oradata/orcl/test2.dbf" resize 6M*ERROR at line 1:ORA-03297: file contains used data beyond requested RESIZE value

    此時只能收縮 tab2 的空間 但是不能收縮 tab1的空間

    然后再drop tab2

    SQL> drop table tab2 purge? 2 ?;Table dropped.SQL> alter database datafile "/u01/app/oracle/oradata/orcl/test2.dbf" resize 6M;Database altered.SQL> alter database datafile "/u01/app/oracle/oradata/orcl/test2.dbf" resize 1M;Database altered.

    可以收縮tab1的空間

    note:

    收縮數據文件和兩個因素有關

    1 降低高水位

    2 free extent在datafile 的尾部

    本篇文章直接解釋了第二個

    如果空閑的extent如果在文件的中間,此時無法進行resize ,必須把尾部的object drop 然后重建 再resize datafile。

    也就是說同時期該用戶下其它表的寫入,也在這個數據文件下,那么就不能進行resize。

    把其它表移動到users表空間:

    select "alter index "||index_NAME||" rebuild tablespace users;" from user_indexes where TABLE_NAME not like "LOG_%";select "alter table "||TABLE_NAME||" move tablespace users;" from user_tables where TABLE_NAME not like "LOG_%";

    再次運行壓縮空間,成功

    2.6 查看壓縮的空間

    可以看到一下子多出了2.1T 的空間

    收縮空間運行速度還不錯,50多個數據文件,幾分鐘就壓縮完成。

    總結

    到此這篇關于Oracle縮表空間的文章就介紹到這了,更多相關Oracle縮表空間內容請搜索以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持!

    標簽: Oracle
    主站蜘蛛池模板: 美甲贴片-指甲贴片-穿戴美甲-假指甲厂家--薇丝黛拉 | AGV无人叉车_激光叉车AGV_仓储AGV小车_AGV无人搬运车-南昌IKV机器人有限公司[官网] | 悬浮拼装地板_篮球场木地板翻新_运动木地板价格-上海越禾运动地板厂家 | 电缆接头_防水接头_电缆防水接头 - 乐清市新豪电气有限公司 | 网络推广公司_网络营销方案策划_企业网络推广外包平台-上海澜推网络 | STRO|DTRO-STRO反渗透膜(科普)_碟滤 | 建筑工程资质合作-工程资质加盟分公司-建筑资质加盟 | 聚合氯化铝厂家-聚合氯化铝铁价格-河南洁康环保科技 | 淄博不锈钢,淄博不锈钢管,淄博不锈钢板-山东振远合金科技有限公司 | 微学堂-电动能源汽车评测_电动车性能分享网 | 湖南印刷厂|长沙印刷公司|画册印刷|挂历印刷|台历印刷|杂志印刷-乐成印刷 | 不锈钢监控杆_监控立杆厂家-廊坊耀星光电科技有限公司 | 细石混凝土泵_厂家_价格-烟台九达机械有限公司 | 智慧旅游_智慧景区_微景通-智慧旅游景区解决方案提供商 | 武汉森源蓝天环境科技工程有限公司-为环境污染治理提供协同解决方案 | 信阳网站建设专家-信阳时代网联-【信阳网站建设百度推广优质服务提供商】信阳网站建设|信阳网络公司|信阳网络营销推广 | 超声波清洗机_大型超声波清洗机_工业超声波清洗设备-洁盟清洗设备 | 济宁工业提升门|济宁电动防火门|济宁快速堆积门-济宁市统一电动门有限公司 | 济南保安公司加盟挂靠-亮剑国际安保服务集团总部-山东保安公司|济南保安培训学校 | 高尔夫球杆_高尔夫果岭_高尔夫用品-深圳市新高品体育用品有限公司 | 数控车床-立式加工中心-多功能机床-小型车床-山东临沂金星机床有限公司 | 健康管理师报考条件,考试时间,报名入口—首页 | 阿尔法-MDR2000无转子硫化仪-STM566 SATRA拉力试验机-青岛阿尔法仪器有限公司 | 土壤有机碳消解器-石油|表层油类分析采水器-青岛溯源环保设备有限公司 | 碳纤维复合材料制品生产定制工厂订制厂家-凯夫拉凯芙拉碳纤维手机壳套-碳纤维雪茄盒外壳套-深圳市润大世纪新材料科技有限公司 | 都江堰招聘网-都江堰人才网 都江堰人事人才网 都江堰人才招聘网 邢台人才网_邢台招聘网_邢台123招聘【智达人才网】 | 飞歌臭氧发生器厂家_水处理臭氧发生器_十大臭氧消毒机品牌 | 智能垃圾箱|垃圾房|垃圾分类亭|垃圾分类箱专业生产厂家定做-宿迁市传宇环保设备有限公司 | 壹作文_中小学生优秀满分作文大全| 智能楼宇-楼宇自控系统-楼宇智能化-楼宇自动化-三水智能化 | 【ph计】|在线ph计|工业ph计|ph计厂家|ph计价格|酸度计生产厂家_武汉吉尔德科技有限公司 | 垃圾清运公司_环卫保洁公司_市政道路保洁公司-华富环境 | H型钢切割机,相贯线切割机,数控钻床,数控平面钻,钢结构设备,槽钢切割机,角钢切割机,翻转机,拼焊矫一体机 | 北京易通慧公司从事北京网站优化,北京网络推广、网站建设一站式服务商-北京网站优化公司 | 制丸机,小型中药制丸机,全自动制丸机价格-甘肃恒跃制药设备有限公司 | ISO9001认证咨询_iso9001企业认证代理机构_14001|18001|16949|50430认证-艾世欧认证网 | 贵州水玻璃_-贵阳花溪闽兴水玻璃厂 | 上海办公室设计_办公楼,写字楼装修_办公室装修公司-匠御设计 | 伸缩节_伸缩器_传力接头_伸缩接头_巩义市联通管道厂 | 传动滚筒_厂家-淄博海恒机械制造厂| 好看的韩国漫画_韩漫在线免费阅读-汗汗漫画 |