Oracle查看磁盘及修改数据文件到指定磁盘

Oracle关于磁盘及修改数据文件

1、首先查看服务器磁盘占用情况（AIX小机为df -g; linux虚机为df -h）
根据客户要求或者自己选择一个足够大的磁盘卷组 本机为/kshfs

df -g

Filesystem GB blocks Free %Used Iused %Iused Mounted on
/dev/hd4 25.00 8.75 66% 52656 3% /
/dev/hd2 20.00 14.12 30% 100914 3% /usr
/dev/hd9var 10.00 9.59 5% 8620 1% /var
/dev/hd3 15.00 6.35 58% 3527 1% /tmp
/dev/hd1 20.00 20.00 1% 13 1% /home
/dev/hd11admin 5.00 5.00 1% 5 1% /admin
/proc - - - - - /proc
/dev/hd10opt 10.00 9.29 8% 13823 1% /opt
/dev/livedump 5.00 5.00 1% 4 1% /var/adm/ras/livedump
/dev/lv00 0.25 0.24 4% 18 1% /var/adm/csd
/dev/itjklv 5.00 5.00 1% 4 1% /itjkfs
/dev/kshlv 5110.00 4409.82 14% 42827 1% /kshfs
2、登录数据库查看表空间使用情况和数据文件路径位置

su - oracle 登录Oracle用户

$ . ./dbenv ------------切环境
$ sqlplus / as sysdba ------------ 登录数据库

SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 20 10:18:24 2019

Copyright © 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

---------------------------查看表空间使用情况------------------------
SQL> select df.tablespace_name “tablespace_name”,
2 totalspace - freespace “used_mb”,
3 totalspace “total_spaceM”,
4 freespace “free_spaceM”,
5 round((1 - freespace / totalspace) * 100, 2) “used%”
6 from (select tablespace_name, round(sum(bytes) / 1024 / 1024) totalspace
7 from dba_data_files
8 group by tablespace_name) df,
9 (select tablespace_name, round(sum(bytes) / 1024 / 1024) freespace
10 from dba_free_space
11 group by tablespace_name) fs
12 where df.tablespace_name = fs.tablespace_name(+) order by round((1 - freespace / totalspace) * 100, 2) desc;

---------------显示出所有表空间占用数据库所属情况--------------------
tablespace_name used_mb total_spaceM free_spaceM used%

---

USERS 373590 572412 198822 65.27
SYSAUX 824 1894 1070 43.51
SYSTEM 733 1764 1031 41.55
UNDOTBS1 55 1960 1905 2.81

-------------------查看数据文件路径位置----------------------------

SQL> select file#,name from v$datafile;

 FILE#

---

NAME

     1

/u01/app/oracle/oradata/bck/system01.dbf

     2

/u01/app/oracle/oradata/bck/sysaux01.dbf

     3

/u01/app/oracle/oradata/bck/undotbs01.dbf

     4

/kshfs/users01.dbf

     5

/kshfs/users02.dbf

3、把所需表空间offline (本次实验为users表空）
SQL> alter tablespace USERS offline;

Tablespace altered.

4、有已知目录这步可省略（本次实验需新建目录并授权Oracle）
#pwd ---------------查看目前所属目录位置
/kshfs
#mkdir datafile -------------------建立目录datafile

ls ------------------查看是否成功存在

DMDUMP cfmroot datafile

ls -g ---------查看目录权限

total 1195459520
drwxr-xr-x 2 oinstall 4096 Nov 20 10:17 DMDUMP
drwxr-x— 2 audit 256 Apr 19 2012 audit
lrwxrwxrwx 1 bin 8 Nov 05 13:00 bin
lrwxrwxrwx 1 system 20 Nov 05 13:00 cfmroot
drwxr-xr-x 3 system 256 Aug 16 03:58 csminstall
drwxr-xr-x 3 oinstall 256 Nov 18 11:56 data
drwxrwxrwx 2 system 256 Nov 20 10:21 datafile
drwxrwxr-x 5 system 20480 Nov 05 00:00 dev
drwxr-xr-x 16 system 4096 Aug 15 13:20 esa
drwxr-xr-x 2 system 256 Aug 23 21:49 lost+found
drwxr-xr-x 2 system 4096 Nov 20 10:15 sotware
-rw-r----- 1 oinstall 11855208448 Nov 14 22:25 temp01.dbf
drwxrwxr-x 4 oinstall 256 Nov 15 15:21 u01
-rw-r----- 1 oinstall 34355290112 Nov 20 10:05 users01.dbf
-rw-r----- 1 oinstall 33286004736 Nov 20 10:05 users02.dbf
-rw-r----- 1 oinstall 33286004736 Nov 20 10:05 users03.dbf
-rw-r----- 1 oinstall 33286004736 Nov 20 10:05 users04.dbf
-rw-r----- 1 oinstall 33286004736 Nov 20 10:05 users05.dbf
#chown -R oracle:oinstall datafile -----------授权目录权限Oracle:oinstall

ls -g --------检测已授权成功

total 1195459520
drwxr-xr-x 2 oinstall 4096 Nov 20 10:17 DMDUMP
drwxr-x— 2 audit 256 Apr 19 2012 audit
lrwxrwxrwx 1 bin 8 Nov 05 13:00 bin
lrwxrwxrwx 1 system 20 Nov 05 13:00 cfmroot
drwxr-xr-x 3 system 256 Aug 16 03:58 csminstall
drwxr-xr-x 3 oinstall 256 Nov 18 11:56 data
drwxrwxrwx 2 oinstall 256 Nov 20 10:21 datafile
drwxrwxr-x 5 system 20480 Nov 05 00:00 dev
drwxr-xr-x 16 system 4096 Aug 15 13:20 esa
drwxr-xr-x 2 system 256 Aug 23 21:49 lost+found
drwxr-xr-x 2 system 4096 Nov 20 10:15 sotware
-rw-r----- 1 oinstall 11855208448 Nov 14 22:25 temp01.dbf
drwxrwxr-x 4 oinstall 256 Nov 15 15:21 u01
-rw-r----- 1 oinstall 34355290112 Nov 20 10:05 users01.dbf
-rw-r----- 1 oinstall 33286004736 Nov 20 10:05 users02.dbf
-rw-r----- 1 oinstall 33286004736 Nov 20 10:05 users03.dbf
5、cp/mv 数据文件到新指定位置
（空间够足建议先cp，确定完成后无用再把旧位置数据文件删除，
不过cp执行会耗时较久）
#ls -----------------查看旧目录下所有数据文件
DMDUMP cfmroot datafile lost+found u01 users03.dbf users06.dbf users09.dbf users12,dbf users15.dbf users18.dbf
audit csminstall dev sotware users01.dbf users04.dbf users07.dbf users10.dbf users13.dbf users16.dbf
bin data esa temp01.dbf users02.dbf users05.dbf users08.dbf users11.dbf users14.dbf users17.dbf

---------------------------复制所有users数据文件到新目录----------------------

cp users01.dbf /kshfs/datafile

6.修改oracle表空间指向地址
alter database rename file ‘/kshfs/users01.dbf’ to ‘/kshfs/datafile/users01.dbf’
7.修改表空间为Online
alter tablespace USERS online;