12C
Move datafiles online
User Rating: / 2
- Details
-
Parent Category: Articles
-
Created on Thursday, 05 March 2015 14:15
-
Last Updated on Thursday, 05 March 2015 14:15
-
Published on Thursday, 05 March 2015 14:15
-
Written by Guy Lambregts
-
Hits: 7407
Move datafiles online with 12C
SQL> set lines 200
SQL> select name from v$datafile;
NAME
---------------------------------------------------------------------------------
/u01/oradata/PLATINA/system01.dbf
/u01/oradata/PLATINA/sysaux01.dbf
/u01/oradata/PLATINA/undotbs01.dbf
/u01/oradata/PLATINA/users01.dbf
SQL> ! df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 4.0G 892M 2.9G 24% /
tmpfs 3.9G 3.6M 3.9G 1% /dev/shm
/dev/sda1 504M 174M 305M 37% /boot
/dev/mapper/vg00-lvhome 16G 9.3G 5.7G 62% /home
/dev/mapper/vg00-lvopt 4.0G 858M 2.9G 23% /opt
/dev/mapper/vg00-lvtmp 4.0G 280M 3.5G 8% /tmp
/dev/mapper/vg00-lvusr 16G 5.6G 9.5G 38% /usr
/dev/mapper/vg00-lvvar 4.0G 1.6G 2.2G 43% /var
/dev/mapper/vg00-lvvm200G 94G 96G 50% /vm
/dev/mapper/vg00-lvu0162G 28G 32G 47% /u01
/dev/mapper/vg00-lvu0231G 3.3G 26G 12% /u02
SQL> alter database move datafile '/u01/oradata/PLATINA/users01.dbf' to '/u02/oradata/PLATINA/users01.dbf';
Database altered.
There are some attributes - KEEP and REUSE - one can optionally use.
We observe the space hasn' t been reclaimed yet from OS level ( /u01 )
SQL> ! df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 4.0G 892M 2.9G 24% /
tmpfs 3.9G 18M 3.9G 1% /dev/shm
/dev/sda1 504M 174M 305M 37% /boot
/dev/mapper/vg00-lvhome 16G 9.3G 5.7G 62% /home
/dev/mapper/vg00-lvopt 4.0G 858M 2.9G 23% /opt
/dev/mapper/vg00-lvtmp 4.0G 280M 3.5G 8% /tmp
/dev/mapper/vg00-lvusr 16G 5.6G 9.5G 38% /usr
/dev/mapper/vg00-lvvar 4.0G 1.6G 2.2G 43% /var
/dev/mapper/vg00-lvvm 200G 94G 96G 50% /vm
/dev/mapper/vg00-lvu01 62G 28G 32G 47% /u01
/dev/mapper/vg00-lvu02 31G 6.5G 23G 23% /u02
SQL> ! lsof | grep users
ora_dbw0_ 7049 oracle 263uW REG 253,8 3479183360 1835012 /u02/oradata/PLATINA/users01.dbf
oracle_70 7083 oracle 263u REG 253,8 3479183360 1835012 /u02/oradata/PLATINA/users01.dbf
oracle_74 7408 oracle 259u REG 253,7 3479183360 1448521 /u01/oradata/PLATINA/users01.dbf (deleted)
oracle_74 7408 oracle 260u REG 253,8 3479183360 1835012 /u02/oradata/PLATINA/users01.dbf
Only after leaving SQL plus and the linux shell ( PID 7408 ) the space is reclaimed at os level
[oracle@pcguyl ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 4.0G 892M 2.9G 24% /
tmpfs 3.9G 1.4M 3.9G 1% /dev/shm
/dev/sda1 504M 174M 305M 37% /boot
/dev/mapper/vg00-lvhome 16G 9.3G 5.7G 62% /home
/dev/mapper/vg00-lvopt 4.0G 858M 2.9G 23% /opt
/dev/mapper/vg00-lvtmp 4.0G 280M 3.5G 8% /tmp
/dev/mapper/vg00-lvusr 16G 5.6G 9.5G 38% /usr
/dev/mapper/vg00-lvvar 4.0G 1.6G 2.2G 43% /var
/dev/mapper/vg00-lvvm 200G 94G 96G 50% /vm
/dev/mapper/vg00-lvu01 62G 24G 35G 41% /u01
/dev/mapper/vg00-lvu02 31G 6.5G 23G 23% /u02