1. 首页
  2. IT资讯

oracle 表空间关闭自增长 autoextend off

因为原来的一个磁盘已经占据了 97% 的空间,随时有可能磁盘被写满导致服务挂起,为最大限度不影响原来业务的情况下, 将所有能够自增长的表空间的自增长特性关闭,将新的数据文件全部写到新添加的/u02 磁盘上 [root@BI-Database ~]# df -h Filesystem Size Used Avail Use% Mounted on /dev/mapper/VolGroup00-LogVol00 275G 11G 251G 4% / tmpfs 63G 16G 48G 25% /dev/shm /dev/sda1 190M 55M 126M 31% /boot /dev/dfa1 3.0T 2.8T 118G 97% /u01 /dev/dfb 5.9T 34M 5.9T 1% /u02 1 查看有哪些数据文件是自增长的 SQL> SELECT T.TABLESPACE_NAME,D.FILE_NAME, 2 D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS 3 FROM DBA_TABLESPACES T,DBA_DATA_FILES D 4 WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME 5 and D.AUTOEXTENSIBLE != ‘NO’ 6 ORDER BY TABLESPACE_NAME,FILE_NAME 7 ; TABLESPACE_NAME FILE_NAME AUTOEXTENSIBLE BYTES MAXBYTES STATUS —————————— ——————————————————————————– ————– ———- ———- ——— SYSAUX /u01/app/oracle/oradata/******/sysaux01.dbf YES 4173332480 1374388879 AVAILABLE SYSTEM /u01/app/oracle/oradata/******/system01.dbf YES 1289748480 1374388879 AVAILABLE UNDOTBS1 /u01/app/oracle/oradata/******/undotbs01.dbf YES 1374388879 1374388879 AVAILABLE USERS /u01/app/oracle/oradata/******/users01.dbf YES 3757309952 1374388879 AVAILABLE 2 关闭掉自增长,保持原有的磁盘空间不再增加 SQL> alter database datafile ‘/u01/app/oracle/oradata/******/sysaux01.dbf’ autoextend off; Database altered SQL> alter database datafile ‘/u01/app/oracle/oradata/******/system01.dbf’ autoextend off; Database altered SQL> alter database datafile ‘/u01/app/oracle/oradata/******/undotbs01.dbf’ autoextend off; Database altered SQL> alter database datafile ‘/u01/app/oracle/oradata/******/users01.dbf’ autoextend off; Database altered 3 确认没有自增长的表空间 SQL> SELECT T.TABLESPACE_NAME,D.FILE_NAME, 2 D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS 3 FROM DBA_TABLESPACES T,DBA_DATA_FILES D 4 WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME 5 and D.AUTOEXTENSIBLE = ‘YES’ 6 ORDER BY TABLESPACE_NAME,FILE_NAME 7 ; TABLESPACE_NAME FILE_NAME AUTOEXTENSIBLE BYTES MAXBYTES STATUS —————————— ——————————————————————————– ————– ———- ———- ——— 4 表空间超过 70% 的在新的磁盘上添加数据文件 SQL> select total.tablespace_name,round(total.MB, 2) as Total_MB,round(total.MB – free.MB, 2) as Used_MB,round((1 – free.MB / total.MB) * 100, 2) || ‘%’ as Used_Pct 2 from (select tablespace_name, sum(bytes) / 1024 / 1024 as MB 3 from dba_free_space 4 group by tablespace_name) free, 5 (select tablespace_name, sum(bytes) / 1024 / 1024 as MB 6 from dba_data_files 7 group by tablespace_name) total 8 where free.tablespace_name = total.tablespace_name; TABLESPACE_NAME TOTAL_MB USED_MB USED_PCT —————————— ———- ———- —————————————– ******_TEST 30720 5918.75 19.27% ******_DW_16K 849920 609492.44 71.71% SYSAUX 3980 3786.56 95.14% UNDOTBS1 242687.94 1625.75 .67% ******_DM_IDX 102400 29779.56 29.08% ******_DW 716800 509206.13 71.04% USERS 56312.5 12278.56 21.8% SYSTEM 11470 1607.94 14.02% ******_INFAREP 10240 1479.44 14.45% ******_DM 112640 58144.25 51.62% UTL_TBS 10240 3.94 .04% ******_STG 51200 2390.31 4.67% ******_DW_IDX 215040 125395.25 58.31% ******_ODS_IDX 10240 2423.19 23.66% ******_ODS 10240 8655.56 84.53% ******_MONITOR_TBS 215040 41819.75 19.45% ******_RECON_TBS 10240 4632.31 45.24% 17 rows selected ALTER TABLESPACE SYSAUX ADD DATAFILE ‘/u02/oradata/******/SYSAUX02.dbf’ size 5G; ALTER TABLESPACE ******_ODS ADD DATAFILE ‘/u02/oradata/******/******_ODS_02.dbf’ size 10G; ALTER TABLESPACE ******_DW_16K ADD DATAFILE ‘/u02/oradata/******/******_dw_16k_16.dbf’ size 50G; ALTER TABLESPACE ******_DW ADD DATAFILE ‘/u02/oradata/******/******_dw_11.dbf’ size 50G; SQL> ALTER TABLESPACE SYSAUX ADD DATAFILE ‘/u02/oradata/******/SYSAUX02.dbf’ size 5G; Tablespace altered SQL> ALTER TABLESPACE ******_ODS ADD DATAFILE ‘/u02/oradata/******/******_ODS_02.dbf’ size 10G; Tablespace altered SQL> ALTER TABLESPACE ******_DW_16K ADD DATAFILE ‘/u02/oradata/******/******_dw_16k_16.dbf’ size 50G; Tablespace altered SQL> ALTER TABLESPACE ******_DW ADD DATAFILE ‘/u02/oradata/******/******_dw_11.dbf’ size 50G; Tablespace altered 添加后的表空间的使用 select total.tablespace_name,round(total.MB, 2) as Total_MB,round(total.MB – free.MB, 2) as Used_MB,round((1 – free.MB / total.MB) * 100, 2) || ‘%’ as Used_Pct from (select tablespace_name, sum(bytes) / 1024 / 1024 as MB from dba_free_space group by tablespace_name) free, (select tablespace_name, sum(bytes) / 1024 / 1024 as MB from dba_data_files group by tablespace_name) total where free.tablespace_name = total.tablespace_name; TABLESPACE_NAME TOTAL_MB USED_MB USED_PCT —————————— ———- ———- —————————————– ******_TEST 30720 5918.75 19.27% ******_DW_16K 901120 609493.44 67.64% SYSAUX 9100 3787.56 41.62% UNDOTBS1 242687.94 1721.75 .71% ******_DM_IDX 102400 29779.56 29.08% ******_DW 768000 509207.13 66.3% USERS 56312.5 12278.56 21.8% SYSTEM 11470 1607.94 14.02% ******_INFAREP 10240 1479.44 14.45% ******_DM 112640 58144.25 51.62% UTL_TBS 10240 3.94 .04% ******_STG 51200 2390.31 4.67% ******_DW_IDX 215040 125395.25 58.31% ******_ODS_IDX 10240 2423.19 23.66% ******_ODS 20480 8656.56 42.27% ******_MONITOR_TBS 215040 41819.75 19.45% ******_RECON_TBS 10240 4632.31 45.24% 17 rows selected 磁盘空间的分布 [root@******-Database ******]# df -h Filesystem Size Used Avail Use% Mounted on /dev/mapper/VolGroup00-LogVol00 275G 11G 251G 4% / tmpfs 63G 16G 48G 25% /dev/shm /dev/sda1 190M 55M 126M 31% /boot /dev/dfa1 3.0T 2.8T 118G 97% /u01 /dev/dfb 5.9T 116G 5.8T 2% /u02 从此不用担心以后会出现/u01 被写爆的极端情况出现

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29578568/viewspace-2148829/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:布吉卡,转转请注明出处:http://www.cxybcw.com/195067.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code