Shulou(Shulou.com)06/01 Report--

Because the original disk already occupies 97% of the space, it is possible that the disk is full at any time and the service is suspended, so as not to affect the original business as much as possible.

Turn off the self-growing feature of all self-growing table spaces and write all the new data files to the newly added / u02 disk

[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 to see which data files are self-growing

SQL > SELECT T. TABLESPACENAMEMagna D. FILESPACENAME

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 turn off self-growth and keep the original disk space no longer increasing

SQL > alter database datafile'/ u01 autoextend off

Database altered

SQL > alter database datafile'/ u01 autoextend off

Database altered

SQL > alter database datafile'/ u01 autoextend off

Database altered

SQL > alter database datafile'/ u01 autoextend off

Database altered

3 confirm that there is no self-growing tablespace

SQL > SELECT T. TABLESPACENAMEMagna D. FILESPACENAME

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 add data files to the new disk if the tablespace is more than 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 size oradata size 5G

ALTER TABLESPACE * _ ODS ADD DATAFILE'/ u02 size oradata size 10G

ALTER TABLESPACE * _ DW_16K ADD DATAFILE'/ u02 size oradata size 50G

ALTER TABLESPACE * _ DW ADD DATAFILE'/ u02 size oradata

SQL > ALTER TABLESPACE SYSAUX ADD DATAFILE'/ u02 size oradata size 5G

Tablespace altered

SQL > ALTER TABLESPACE * _ ODS ADD DATAFILE'/ u02 size oradata

Tablespace altered

SQL > ALTER TABLESPACE * _ DW_16K ADD DATAFILE'/ u02 size size 50G

Tablespace altered

SQL > ALTER TABLESPACE * _ DW ADD DATAFILE'/ u02 size oradata

Tablespace altered

The use of the added table space

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

Distribution of disk space

[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

From now on, you don't have to worry about the extreme situation of / U01 being written and exploded in the future.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.