Oracle数据学习笔记三——Oracle数据库结构和表空间的运用

Oracle数据学习笔记三——Oracle数据库结构和表空间的运用

一、前情提要

数据库结构由三类文件组成，分别是：

名字	文件名
控制文件	v$controlfile
数据文件	v$datafile
重做日志文件	v$logfile

controlfile(.ctl)：主要记录数据库的名称、数据库的数据文件存放位置和redolog文件的位置、大小、状态等信息。一个控制文件只能属于一个数据库。

datafile(.bdf)：用于保存用户应用程序数据和 Oracle 系统内部数据的文件，这些文件在操作系统中就是普通的操作系统文件。Oracle 在创建表空间的同时会创建数据文件。

logfile（.log）：日志文件在 Oracle 数据库中分为重做日志(Redo Log File)文件和归档日志文件两种。重做日志文件主要记录了数据库操作过程。用于备份和还原数据库，以达到数据库的最新状态。

接下来我们一一查看

注:所有操作均在oracle用户下完成

二、前提步骤

#1.导入实例
export ORACLE_SID=orcl
#2.数据库管理员登录
sqlplus / as sysdba
#3.启动数据库
startup

注：数据库不启动，是查看不了文件的

三、查看文件

3.1查看控制文件的信息

注：在oracle数据库下，查询文件信息一行最多80字节，故我们需要修改显示大小，来正确显示。

#格式化
col NAME format a50
#让一行扩展到显示120字节的长度大小
set linesize 120
#查询控制文件的信息
select * from v$controlfile;

正确显示：

3.2查看数据文件的信息

注：datafile文件下拥有许多列名 ，如图。因此用*来查询不合理，因此我们挑其中的一列来查询。

#查询数据文件名
select name,bytes/1024/1024 MB from v$datafile;

3.3 查询视图文件的信息

故需要调整其显示格式

#格式化到正确大小
col MEMBER format a50
#查询
select GROUP#,MEMBER from v$logfile; 

正确显示:

四、查看表空间

4.1查看表空间空间信息

#查看数据表空间中表的名字，块大小和状态
select TABLESPACE_NAME,BLOCK_SIZE,STATUS from dba_tablespaces;

有图可以看出，此时表空间中有6张表，块大小全是8K，状态均是已连接。

注：这里的BLOCK_SIZE的大小是指以多大的空间去格式化。

4.2查看表空间和数据文件相关联的信息

#格式化大小，求正确显示
col TABLESPACE_NAME format a20
#格式化大小，求正确显示
col  FILE_NAME format a50
#查看表空间和数据文件相关联的信息
select TABLESPACE_NAME,FILE_NAME ,BYTES/1024/1024 MB from dba_data_files; 

综上，总结：

system表空间：是数据字典表，数据文件存放在/u01/app/oracle/oradata/ORCL/system01.dbf
sysaux表空间：是system辅助表空间，把system表空间一部分的数据存放在该表空间，名称：sysaux01.dbf
undo表空间：存放表修改前的老数据，提供回滚使用。名称：undotbs01.dbf
temp表空间：称为临时表，排序操作时存放临时的数据。名称temp01.dbf
users表空间：存放用户数据的空间。名称：users01.dbf
用户自定义表空间:存放用户数据。图中txp表空间即是用户自定义的表空间

五、表空间的运用

5.1 创建表空间

#创建表空间 CREATE TABLESPACE关键字
CREATE TABLESPACE txp  DATAFILE '/u01/app/oracle/oradata/ORCL/txp01.dbf' size 5m;

5.1.1在表空间上创建一张表

#创建表 create关键字
CREATE TABLE tab1 (
stu_id  number,
name    varchar2(20),
birth   date,
zuanye  varchar2(20)
)tablespace txp;#将表放在txp表空间下

5.1.2 查看位置

#格式化大小 正确显示
col TABLE_NAME for a10
#验证表存放的位置
select TABLE_NAME,TABLESPACE_NAME from dba_tables where TABLE_NAME='TAB1'; 

注：这里的TABLE_NAME='TAB1'; ' '中的表名必须大写，否则会报错！

5.2 数据库的扩容

数据库的扩容这里介绍三个方法，分别是：

resize方法
添加新的数据文件方法
自动扩展方式

接下来一一介绍

5.2.1 前提准备

1.表中批量插入数据

这里我们向5.1.1小节中创建的表tab1中添加数据，values后的值类型必须要创建时规定的类型所匹配

#编写一个脚本
begin
  for i in 1..1000 loop
  	INSERT INTO tab1 VALUES (i,'txp','03-mar-25','silence');
  end loop;
  commit;
end;
/ #结束符号

2.查看表空间可用空间容量

#查询语句
select TABLESPACE_NAME,sum(BYTES)/1024/1024 MB from dba_free_space 
where TABLESPACE_NAME='TXP'
group by TABLESPACE_NAME;

表空间从起初定义的5MB变为3.9375MB，说明插入数据成功，占据了空间大小

注：这里的表空间名必须大写，否则会报错！

3.插入数据

#开启一个事物
insert into tab1 select * from tab1;

commit;

值得注意的是，表中现在共有2000条数据。

4. / 快捷命令的使用

/ 命令会快速执行上一条刚执行的命令

注：/命令后，表中有的数据个数是没执行/之前的数据个数*2

查询一下

64000记录，与5.2.1第四步相照应，实验到此无错误。

表空间显示不足，接下来我们介绍扩容的三种方法

5.2.2 resize扩容方法

接上一步操作，表空间已满，使用以下命令来进行扩容

#进行表空间扩容，resize方法
alter database datafile '/u01/app/oracle/oradata/ORCL/txp01.dbf' resize 10m;
#这里是将所创建的txp01.dbf扩容到 10MB

注：起初定义的表空间大小是5MB，这里resize了10MB，实际上是在原有大小上增加了5MB，而不是一共15MB，如下图

接下来继续让表空间装满，测试第二种方法

5.2.3 添加新文件扩容方法

在表空间下额外创建一个数据文件，以用来达到扩容的效果

#再次进行表空间扩容，添加新的数据文件
alter tablespace txp add datafile '/u01/app/oracle/oradata/ORCL/txp02.dbf' size 20m;

查询是否创建成功

select TABLESPACE_NAME,FILE_NAME ,BYTES/1024/1024 MB from dba_data_files; 

看到表空间中已存在两个数据文件txp01.dbf和txp02dbf，扩容成功

接下来继续让表空间装满，测试第三种方法

5.2.4自动扩容方式

这种方法是最实用的，只要磁盘空间足够，当你表空间不足时，会一直扩容空间大小，一劳永逸。

#打开数据文件的aotoextend的功能即可
alter database datafile '/u01/app/oracle/oradata/ORCL/txp02.dbf' autoextend on; 

由上图可知：自动扩容的方式已打开

5.3 表空间的改名

关键字：alter tablespace

语句格式：alter tablespace 旧表名 rename to 新表名

 #修改名
 alter tablespace txp rename to new_txp;

5.4 表空间的删除

关键字：drop

语句格式：drop tablespace 表空间名 INCLUDING CONTENTS and datafiles; （NCLUDING CONTENTS and datafiles 代表删除表空间名下的配置文件等）

#删除表空间
drop tablespace new_txp INCLUDING CONTENTS and datafiles; 

新表名`

 #修改名
 alter tablespace txp rename to new_txp;

5.4 表空间的删除

关键字：drop

语句格式：drop tablespace 表空间名 INCLUDING CONTENTS and datafiles; （NCLUDING CONTENTS and datafiles 代表删除表空间名下的配置文件等）

#删除表空间
drop tablespace new_txp INCLUDING CONTENTS and datafiles;