Oracle数据库的空间管理技巧

在oracle数据库中，dba不妨经过察看确定的表或视图来领会暂时空间的运用情景，从而作出大概的安排确定。 一.表空间的自在空间 　　经过对表空间的自在空间的查看，可用来确定调配给某个表空间的空间是太多仍旧不够。请看下列的语句 sql > select a.file_id "fileno",a.tablespace_name "tablespace_name", 2 a.bytes "bytes",a.bytes-sum(nvl(b.bytes,0)) "used", 3 sum(nvl(b.bytes,0)) "free", 4 sum(nvl(b.bytes,0))/a.bytes*100 "%free" 5 from dba_data_files a, dba_free_space b 6 where a.file_id=b.file_id(+) 7 group by a.tablespace_name , 8 a.file_id,a.bytes order by a.tablespace_name; file tablespace no _namebytes used free %free ------ --------- -------- --------- --------- --------- 11idx_jf .146e+09 849305600 1.297e+09 60.431806 9 jfsjts 2.146e+09 1.803e+09 343793664 16.016961 10jfsjts 2.146e+09 1.359e+09 787431424 36.685546 2 rbs523239424 359800832 163438592 31.235909 12rbs1.610e+09 1.606e+09 3104768 .19289495 8 rbsjf 3.220e+09 2.716e+09 504356864 15.662396 7 sfglts 2.146e+09 1.228e+09 918159360 42.776014 6 sfsjts 2.146e+09 1.526e+09 620093440 28.889457 1 system 523239424 59924480 463314944 88.547407 3 temp 523239424294912 522944512 99.943637 4 tools 15728640 12582912 314572820 5 users 7340032 81927331840 99.888393 12 rows selected. 　　不妨看出，在fileno为12的表空间rbs中，惟有0.19%的调配空间未被运用，这个比率太小了，而在system及temp等表空间中，高达80%之上的空间未被运用，对于消费型数据库，这个表空间的树立有些偏高。 　　对于自在空间的处置，有底下的少许倡导： 　　运用export及import吩咐卸出和装入表空间不妨开释洪量的空间，进而缓和减少其余的数据文献的诉求。 　　即使包括具备高插入(insert)和革新(update)震动的表的表空间中自在空间的比例低沉到了15%以次，要为此表空间减少更多的空间。 　　对于一个基础是静态表数据的表空间，即使有多于20%的自在空间，则不妨商量缩小调配给它的文献空间量。 　　缩小system表空间的空间量比拟艰巨，由于那要重修数据库。 二 表及索引的扩充 　　a.为了提防表或索引被过度扩充，准时实行对数据库的安排，用户该当常常对相关东西举行查看。 　　咱们不妨觉得，扩充地区大于5个的表或索引为过度扩充(overextended)。请看底下的语句： sql > select substr(segment_name,1,15) segment_name,segment_type, 2 substr(tablespace_name,1,10) tablepace_name,extents,max_extents 3from dba_segments 4where extents >5 and owner='jfcl' 5order by segment_name; segment_namesegment tablepace_ extents max_extents _type -------------- --------- ---------- chhdfyb table jfsjts 11121 chhdfyb_dhhmindex jfsjts9121 djhzfyb_bf table jfsjts 17500 djhzfyb_djhmindex idx_jf6500 djhzfyb_jzhmindex idx_jf7500 gsmfyb table jfsjts 11121 jfdhtable jfsjts 14500 jfdh_dhhm index idx_jf 61500 jfdh_jzhm index idx_jf 64500 xykfyb table jfsjts7121 yhdatable jfsjts6500 yhda_baktable jfsjts6500 yhhzfyb_12 table jfsjts 10500 13 rows selected. 　　经过查看， dba不妨准时创造题目并举行相映的处置。 咱们不妨运用export卸出表，而后简略表，再运用import吩咐将表装入，如许，不妨将不贯串的地区兼并成一个贯串的空间。 　　b.即使用户蓄意对表的空间树立举行优化，比方，须要变换表emp的initial参数，不妨沿用底下的本领： 　　1.在将emp表卸出并简略后实行imp吩咐时运用indexfile参数： 　　imp userid=scott/tiger file=emp.dmp indexfile=emp.sql oracle把表和索引的创造消息写到指定的文献，而不是把数据写回。 2.翻开emp.sql文献： rem create table "scott"."emp" ("empno" number(4, 0), "ename" rem varchar2(10), "job" varchar2(9), "mgr" number(4, 0), "hiredate" date, rem "sal" number(7, 2), "comm" number (7, 2), "deptno" number(2, 0)) rem pctfree 10 pctused 40 initrans 1 maxtrans 255 logging storage(initial rem 10240 next 10240 minextents 1 maxextents 121 pctincrease 50 freelists rem 1 freelist groups 1 buffer_pool default) tablespace "user_data" ; rem ... 14 rows 　　对它举行编纂，去除"rem"等消息，找到initial参数，按照须要变换它。 　　3.在sql*plus中实行emp.sql。 　　4.装入数据： mp userid=scott/tiger ignore=y file=emp.dmp 　　须要提防的是，ignore参数必需设为y. 　　c.不妨用底下的语句来查看表或索引隔绝到达最大扩充的情景，“unuse”为隔绝到达最大扩充的值，在user_extents表中，extent_id是从0发端记叙数的。 sql >select a.table_name "table_name",max (a.max_extents) "maxextents" , 2 max(b.extent_id)+1 "in use", max (a.max_extents)-(max(b.extent_id)+1) "unuse" 3 from user_tables a, user_extents b 4where a.table_name=b.segment_name 5 group by a.table_name order by 4; table_name maxextents in useunuse ---------- ----------- -------- --------- yzphb 98 1 97 shjyb 121 1 120 shfyb 121 1 120 rchdb 121 1 120 sjtxdzb121 1 120 sjtxdab121 1 120 chyhb 121 1 120 jfdh 50014 486 8 rows selected. 　　即使“unuse"小到确定的水平，咱们就该当加以关心，举行符合的安排处置。 三 对于贯串空间 　　不妨用底下的语句来察看数据库中的自在空间： sql > select * from dba_free_space where tablespace_name='sfsjts' 2 order by block_id; tablespace file_id block_id bytesblocks _name ----------- --------- -------------- sfsjts 6 133455 1064960 130 sfsjts 6 133719 1032192 126 sfsjts 6 133845 1064960 130 sfsjts 6 135275 1064960 130 sfsjts 6 135721 606208 74 sfsjts 6 139877 901120 110 sfsjts 6 143497 737280 90 sfsjts 6 220248 737280 90 sfsjts 6 246228 491520 60 sfsjts 6 261804 1064960 130 10 rows selected. 　　咱们不妨经过吩咐的截止来估量相邻自在空间的真实数目。对每一条龙，用开始快的id(block_id)加上自在块(blocks)的数目，即使其和与下一条龙的块id(block_id)十分，则此两行是贯串的。如上例第二行和第三行，133719+126=133845，而1338456+130!=135275,以是从block_id为133719发端，有126+130=256个block的贯串空间。 　　在oracle数据库的后盾，体例监督器(smon)周期性地兼并自在空间相邻的块，以获得更大的贯串块。而dba不妨用sql吩咐来实行这个处事： alter tablespace tablespace_name coalesce; 　　oracle空间处置对数据库的处事本能有要害感化，其处置本领犯得着咱们刻意探求接洽。