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;