【Oracle】表空间相关集合

创建表空间

1.语法

CREATE [SMALLFILE/BIGFILE] TABLESPACE tablespace_name
DATAFILE '/path/filename' SIZE num[k/m] REUSE
[,'/path/filename' SIZE num[k/m] REUSE]
[...]
[AUTOEXTEND [ON|OFF] NEXT num[k/m]]
[MAXSIZE EXTEND [UNLIMITED | num[k/m]]]
[MINIMUN EXTEND num[k/m]]
[DEFAULT STORAGE storage]
[ONLINE|OFFLINE]
[LOGGING|NOLOGGING]
[PERMANENT|TEMPORARY]
[EXTENT MANAGEMENT DIRECTORY|LOCAL [AUTOALLOCATE|UNIFORM SIZE num[k/m]]]]

语法中的关键字

• SMALLFILE/BIGFILE:表示创建的是小文件表空间还是大文件表空间
• AUTOEXTEND [ON|OFF] NEXT :表示数据文件为自动扩展(ON)或非自动扩展(OFF),如果是自动扩展，则需要设置NEXT的值。
• MAXSIZE ：表示当前数据文件扩展时，允许数据文件扩展的最大长度字节数，如果指定UNLIMITED关键字，则不需要指定字节长度。
• MINIMUN EXTEND：指定最小的长度，由操作系统和数据库的块决定。
• ONLINE|OFFLINE：创建表空间时可以指定为在线或离线
• PERMANENT|TEMPORARY：指定创建的表空间时永久表空间或临时表空间，默认是永久表空间。
• LOGGING|NOLOGGING：指定该表空间内的表在加载数据时是否产生日志，默认产生日志。即使设置为NOLOGGING,但在进行INSERT、UPDATE、DELETE操作时，Oracle仍将操作记录到Redo Log Buffer中。
• EXTENT MANAGEMENT DIRECTORY|LOCAL：指定表空间的扩展方式是使用数据字典管理还是本地化管理，默认本地化管理。Oracle不推荐使用数据字典表管理表空间。、
• AUTOALLOCATE|UNIFORM ：如果采用本地化管理表空间，在表空间扩展时，指定每次盘区扩展的大小是由系统自动指定还是按照同等大小进行。若按照同等大小进行，则默认每次扩展为1MB

语法中的参数说明

• tablespace_name：该参数表示要创建的表空间名称
• '/path/filename' ：该参数表示数据文件的名字与存储路径，REUSE表示该表空间存在，则清除该文件后重新创建，不存在，则直接创建。
• DEFAULT STORAGE storage：指定以后要创建的表、索引及簇的存储参数值，这些参数将影响以后表等的存储参数。
• num[K/M]：指文件的大小，例如10k 或者10M

2.创建表空间实例

案例一：创建一个扩展大小为5M的表空间

create tablespace tbs_test_1 datafile 'D:appOLIVERoradata	bs_file1.dbf'
size 20m
extent management local uniform size 5M;

注意：此种方法建立的表空间存储上限值为20M.

当表空间容量快要超过20M时，则会报错如下：

案例二：创建一个扩展大小为自动管理的表空间

create tablespace tbs_test_2 datafile 'D:appOLIVERoradata	bs_file2.dbf'
size 20m
extent management local autoallocate;

同理，当表空间大小快要达到20M时候报错如上图。

注意：此种方法建立的表空间存储上限值为20M.

案例三：创建自动扩容的表空间

create tablespace tbs_test_3 datafile 'D:appOLIVERoradata	bs_file3.dbf'
size 20m
autoextend on next 2M maxsize unlimited 
extent management local autoallocate;

当向数据表插入数据是，表空间会一直往数据表插入数据，并且表空间每增加一次为2M

案例四：通过段空间管理方式建立表空间

段空间管理方式是建立在本地化空间管理方式的基础之上

段空间管理的2种方式：（推荐使用自动段管理表空间）

• 手工段空间管理方式
• 自动段空间管理方式

1.手工段空间管理方式

    手工段空间管理方式是为了往后兼容而保留的。它使用自由块列表和PCT_FREE与PCT_USED参数来标识可供插入操作的数据块。

    在每个INSERT或UPDATE操作后，数据库都会比较该数据块中的剩余自由空间与该段的PCT_FREE设置。如果数据块的自由空间少于PCT_FREE自由空间（也就是说剩余空间已经进入系统的下限设置），则数据库就会从自由块列表上将其取下，不在对其进行插入操作。剩余的表空间保留给可能会增大数据库块中行大小的UPDATE操作。

    而在每个UPDATE或者DELETE操作之后，数据库会比较该数据块中的已用空间与PCT_USED设置，如果已用空间少于PCT_USED已用空间(也就是已用空间未达到系统的上限设置)，则该数据块会被加入到自由列表中，供INSERT操作使用。

1.1创建例子：

CREATE TABLESPACE tb_test_t3 datafile 'D:APPOLIVERORADATA	b_test_t3.DBF' 
size 20M
extent management local autoallocate
segment space management manual;

2.自动段空间管理方式

    如果采取自动段管理方式，那么数据库会使用位图而不是自由列表来标识哪些数据块可以用于插入操作，哪些数据块需要从自由块列表上将其取下。此时，表空间段的PCT_FREE和PCT_USED参数会被自动忽略。

Tips:

1.自动段空间管理方式不能用于创建临时表空间与系统表空间

2.Oracle本身推荐的是自动段管理方式，但是在其默认的情况下是手工管理方式，所以在创建表空间的时候需要制定为AUTO

2.1 创建例子

CREATE TABLESPACE tb_test_t4 datafile 'D:APPOLIVERORADATA	b_test_t4.DBF' 
size 20M
extent management local autoallocate
segment space management auto;

案例五：创建一个数据文件大小为2GB的大文件表空间

大文件表空间主要被用于存储区域网络上（SNA），磁盘阵列上、自动存储管理（ASM）上和类似的提供禁止数据访问多设备的存储解决方案上。

create bigfile tablespace tbs_test_big datafile 'D:APPOLIVERORADATATB_TEST_BIG.DBF'
size 2G

创建大文件表空间可能比较慢，耐心等待即可。

如果创建完毕后觉得给的存储空间太大，想修改为1G也是可以的，使用如下命令即可修改

alter tablespace tbs_test_big resize 1G

使用OEM创建表空间

登录到https://IP:1158/em  IP为Oracle所在服务器IP地址

输入用户名 密码登录

点击该页面的【创建】，其他默认值即可

在上图对应页面的右下角点击【添加】，然后填写文件名、文件目录、文件大小、扩展方式等信息后【继续】

此时返回到创建表空间页面

此时点击上图中的【存储】后如下图，在此图中可以设置表空间的区管理方式，段空间管理方式、是否压缩、以及是否启用事件记录等选项。如果需要查看SQL，直接点击右上角的【显示SQL】即可查看

显示SQL

确认无误后，返回点击【确定】即可完成表空间的创建。

删除表空间

1.语法

DROP TABLESPACE tbs_name[INCLUDING CONTENTS][CASCADE CONSTRAINTS]

• tbs_name：要删除的表空间名称
• INCLUDING CONTENTS：删除表空间的时候删除数据，如果不指定该参数，则删除时候会报错
• CASCADE CONSTRAINTS：表示当删除表空间时也删除相关的完整性限制,完整性限制包括主键及唯一索引等。如果完整性存在，而没有该参数，则表空间删除报错，并且表空间无法删除。

2.删除实例

drop tablespace TBS_TEST_3 including contents cascade constraints

 

查询表空间

1.查询表空间使用情况

查询表空间使用情况https://www.cnblogs.com/OliverQin/p/5217481.html

2.查询表空间的管理类型

select tablespace_name,extent_management,allocation_type from dba_tablespaces

表空间的管理模式：

• 本地化管理的表空间(Local Managed Tablespace)
• 数据字典管理的表空间(Directory Managed Tablesapce)

数据字典管理的表空间，存储效率低，存储参数难以管理以及磁盘碎片等问题，所以被淘汰。

3.查询表空间对应数据文件的名称、大小和路径信息

select * from dba_data_files where TABLESPACE_NAME='TEST_DM'

4.查询表空间的状态

select tablespace_name,status from dba_tablespaces where tablespace_name=’TEST_DM’

修改表空间

表空间修改包括以下几点：

• 为表空间增加新的数据文件
• 修改数据文件的大小
• 修改数据文件的自动扩展性
• 移动数据文件

1.为表空间增加新的数据文件

创建表空间时，需要创建指定的数据文件。在Oracle中，数据文件的大小决定表空间的大小，在表空间使用过程中如果出现表空间不足的情况，可以采用增加一个数据文件的方式增大表空间

（构成表空间的数据文件可以有很多个目录下的，表空间的大小为这些数据文件大小的合计）

alter tablespace TEST_DM
add datafile 'D:APPADMINISTRATORORADATAORCLTEST_DM01.DBF'
size 10M

2.修改数据文件的大小

另一个增大表空间的方法，增加数据文件的大小

alter database datafile 'D:APPADMINISTRATORORADATAORCLTEST_DM01.DBF' resize 500M

3.修改数据文件的自动扩展属性

alter database datafile 'D:APPADMINISTRATORORADATAORCLTEST_DM01.DBF' autoextend on next 5m

此时继续查询表：

select * from dba_data_files where TABLESPACE_NAME='TEST_DM'