【DBA笔记①】ORACLE表空间数据文件

前言

今天学习了Oracle表空间的相关操作，因为第一次建库时路径未单独设置，创建表空间时数据文件自动放在了根目录下，这会导致根目录很快被撑满，故本次学习涉及如何创建表空间及数据文件，如何修改数据文件大小、是否自动扩展、路径等。

oracle安装完数据库的一般流程：
先建表空间（分区）–>再建用户（分配相应的表空间和用户权限）–>登陆用户，建表

1、创建表空间

create tablespace TEST_DATA
datafile ‘/data1/test/TEST_DATA01.dbf’ size 8G --存储地址,按需输入，这里初始大小8G
autoextend on next 1G maxsize 31G;--每次扩展1G，系统最大不能超过32G，若无限制扩展设置 unlimited

如果要求的初始容量大于32G，可另外追加数据文件

alter tablespace TEST_DATA 
add datafile ‘/data1/test/TEST_DATA02.dbf’ size 8G 
autoextend on next 1G maxsize 31G;

2、创建用户并授权

--创建用户
create user tester identified by tester default tablespace TEST_DATA;
 
--授权
grant connect,select, create to tester;

3、修改表空间数据文件对应路径

①查询数据文件的存储路径

select FILE_NAME,FILE_ID,TABLESPACE_NAME from dba_data_files;

②查询表空间及余量

select a.tablespace_name,
round(total/1024/1024/1024) "Total g",
round(free/1024/1024/1024) "Free g",
round((total-free)/total,4)*100 "USED%"
FROM (SELECT tablespace_name,SUM(bytes) free FROM DBA_FREE_SPACEGROUP BY tablespace_name ) a,
(SELECT tablespace_name,SUM(bytes) total FROM DBA_DATA_FILESGROUP BY tablespace_name) b
WHERE a.tablespace_name=b.tablespace_name
ORDER BY 4;

③修改表空间为offline

alter tablespace TEST_DATA offline；

④创建相应目录

mkdir /data/test/

⑤拷贝dbf数据文件到新目录

[oracle@***** ~]$ cp /data1/test/test_data01.dbf /data/test/
[oracle@***** ~]$ cp /data1/test/test_data02.dbf /data/test/

⑥数据库中修改表空间指向地址

alter database rename file '/data1/test/test_data01.dbf' to '/data/test/test_data01.dbf';
alter database rename file '/data1/test/test_data02.dbf' to '/data/test/test_data02.dbf';

⑦表空间状态设置为online

alter tablespace TEST_DATA online；

⑧确认变更结果

select FILE_NAME,FILE_ID,TABLESPACE_NAME from dba_data_files where tablespace_name ='TEST_DATA';

⑨删除原数据文件（生产环境下该项一定要找人复核！！！）

[oracle@*****]$  rm -rf /data1/test/test_data01.dbf
[oracle@*****]$  rm -rf /data1/test/test_data02.dbf

4、查询数据文件是否为自动扩展

SELECT t.tablespace_name, d.file_name, d.autoextensible, d.increment_by, d.bytes, d.maxbytes,
       d.status
  FROM dba_tablespaces t, dba_data_files d
 WHERE t.tablespace_name = d.tablespace_name
 ORDER BY tablespace_name, file_name;

5、修改数据文件为自动扩展 / 非自动扩展

--开启自动扩展
ALTER DATABASE datafile '/data/test/test_data01.dbf' autoextend ON NEXT 1G maxsize unlimited;
--关闭自动扩展
ALTER DATABASE datafile '/data/test/test_data01.dbf' autoextend OFF;