04 | 11 | 2024
Latest Articles
Popular Articles

12C

Move datafiles online

User Rating:  / 2
PoorBest 

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