Oracle-22-数据文件和表空间管理

最新推荐文章于 2022-07-21 17:21:54 发布

Paul_George

最新推荐文章于 2022-07-21 17:21:54 发布

阅读量167

点赞数

分类专栏： Oracle 文章标签：数据文件表空间

本文链接：https://blog.csdn.net/Paul_George/article/details/80375052

版权

Oracle 专栏收录该内容

38 篇文章 0 订阅

订阅专栏

查看数据库表空间

select tablespace_name,contents from dba_tablespaces;

TABLESPACE_NAME                CONTENTS
------------------------------ ---------
SYSTEM                         PERMANENT
SYSAUX                         PERMANENT
UNDOTBS1                       UNDO
TEMP                           TEMPORARY
USERS                          PERMANENT
EXAMPLE                        PERMANENT

6 rows selected.

Elapsed: 00:00:00.01

一、永久表空间（PERMANENT）：存储永久对象（表，索引）

创建永久表空间

create tablespace data01 datafile size 10m；

select tablespace_name,contents from dba_tablespaces order by contents;

TABLESPACE_NAME                CONTENTS
------------------------------ ---------
SYSTEM                         PERMANENT
SYSAUX                         PERMANENT
EXAMPLE                        PERMANENT
DATA01                         PERMANENT
USERS                          PERMANENT
TEMP                           TEMPORARY
UNDOTBS1                       UNDO

7 rows selected.

Elapsed: 00:00:00.01

查看表空间所对应的物理文件

select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME	            FILE_NAME
USERS	         +DATA/orcl/datafile/users.259.974086707
UNDOTBS1	 +DATA/orcl/datafile/undotbs1.258.974086707
SYSAUX	         +DATA/orcl/datafile/sysaux.257.974086707
SYSTEM	         +DATA/orcl/datafile/system.256.974086707
EXAMPLE	         +DATA/orcl/datafile/example.265.974086801
DATA01	         +DATA/orcl/datafile/data01.275.975123385

在表空间下创建表

create table scott.t01 tablespace data01 as select * from scott.emp;

查看表所在的表空间信息

select tablespace_name,table_name from dba_tables where table_name='T01';

TABLESPACE_NAME                TABLE_NAME
------------------------------ ------------------------------
DATA01                         T01

查看表空间状态

select tablespace_name,contents,status from dba_tablespaces order by contents;

TABLESPACE_NAME                CONTENTS  STATUS
------------------------------ --------- ---------
SYSTEM                         PERMANENT ONLINE
SYSAUX                         PERMANENT ONLINE
EXAMPLE                        PERMANENT ONLINE
DATA01                         PERMANENT ONLINE
USERS                          PERMANENT ONLINE
TEMP                           TEMPORARY ONLINE
UNDOTBS1                       UNDO      ONLINE

7 rows selected.

Elapsed: 00:00:00.00

修改表空间的状态

alter tablespace data01 read only;

在read only状态下是不能对表空间内的对象做改写的，查询、drop表是没有问题的

alter tablespace data01 read write;

系统表空间、系统辅助表空间、UNDO表空间、TEMP表空间是不能被设置为只读的

alter tablespace data01 offline;

select tablespace_name,contents,status from dba_tablespaces order by contents;

TABLESPACE_NAME                CONTENTS  STATUS
------------------------------ --------- ---------
SYSTEM                         PERMANENT ONLINE
SYSAUX                         PERMANENT ONLINE
EXAMPLE                        PERMANENT ONLINE
DATA01                         PERMANENT OFFLINE
USERS                          PERMANENT ONLINE
TEMP                           TEMPORARY ONLINE
UNDOTBS1                       UNDO      ONLINE

7 rows selected.

Elapsed: 00:00:00.01

select * from scott.t01;

select * from scott.t01
                    *
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '+DATA/sundb/datafile/data01.275.975123385'


Elapsed: 00:00:00.03

表空间在OFFLINE状态下是不能进行查询修改操作的，但是可以进行DROP操作

alter tablespace data01 online;

表空间的空间监控

select tablespace_name,sum(blocks)/128 curr_mb from dba_data_files group by tablespace_name;

TABLESPACE_NAME                   CURR_MB
------------------------------ ----------
UNDOTBS1                              545
SYSAUX                                500
USERS                                 190
SYSTEM                                680
EXAMPLE                               100
DATA01                                 10

6 rows selected.

Elapsed: 00:00:00.04

select tablespace_name,sum(blocks)/128 curr_mb,round(sum(maxblocks)/128,0) max_mb from dba_data_files group by tablespace_name;

TABLESPACE_NAME                   CURR_MB     MAX_MB
------------------------------ ---------- ----------
UNDOTBS1                              545      32768
SYSAUX                                500      32768
USERS                                 190      32768
SYSTEM                                680      32768
EXAMPLE                               100      32768
DATA01                                 10          0

6 rows selected.

Elapsed: 00:00:00.02

查看表空间空闲大小

select tablespace_name,round(sum(blocks)/128,0) free_mb from dba_free_space group by tablespace_name;

TABLESPACE_NAME                   FREE_MB
------------------------------ ----------
SYSAUX                                 31
UNDOTBS1                              142
USERS                                  10
SYSTEM                                  7
EXAMPLE                                22
DATA01                                  9

6 rows selected.

Elapsed: 00:00:00.02

如果表空间已经没有剩余空间了，那么它将从dba_free_space中消失

表空间扩容

1.修改现有数据文件大小

select file_name from dba_data_files where tablespace_name='DATA01';

alter database datafile '+DATA/sundb/datafile/data01.275.975123385' resize 20m;

select tablespace_name,round(sum(blocks)/128,0) free_mb from dba_free_space group by tablespace_name;

TABLESPACE_NAME                   FREE_MB
------------------------------ ----------
SYSAUX                                 31
UNDOTBS1                              142
USERS                                  10
SYSTEM                                  7
EXAMPLE                                22
DATA01                                 19

6 rows selected.

Elapsed: 00:00:00.03

select tablespace_name,sum(blocks)/128 curr_mb,round(sum(maxblocks)/128,0) max_mb from dba_data_files group by tablespace_name;

TABLESPACE_NAME                   CURR_MB     MAX_MB
------------------------------ ---------- ----------
UNDOTBS1                              545      32768
SYSAUX                                500      32768
USERS                                 190      32768
SYSTEM                                680      32768
EXAMPLE                               100      32768
DATA01                                 20          0

6 rows selected.

Elapsed: 00:00:00.02

2.打开数据文件的自动增长属性

alter database datafile '+DATA/sundb/datafile/data01.275.975123385' autoextend on;

select tablespace_name,sum(blocks)/128 curr_mb,round(sum(maxblocks)/128,0) max_mb from dba_data_files group by tablespace_name;

TABLESPACE_NAME                   CURR_MB     MAX_MB
------------------------------ ---------- ----------
UNDOTBS1                              545      32768
SYSAUX                                500      32768
USERS                                 190      32768
SYSTEM                                680      32768
EXAMPLE                               100      32768
DATA01                                 20      32768

6 rows selected.

Elapsed: 00:00:00.06

3.向表空间增加新的数据文件

alter tablespace data01 add datafile size 10m;

select tablespace_name,sum(blocks)/128 curr_mb,round(sum(maxblocks)/128,0) max_mb from dba_data_files group by tablespace_name;

TABLESPACE_NAME                   CURR_MB     MAX_MB
------------------------------ ---------- ----------
UNDOTBS1                              545      32768
SYSAUX                                500      32768
USERS                                 190      32768
SYSTEM                                680      32768
EXAMPLE                               100      32768
DATA01                                 30      32768

6 rows selected.

Elapsed: 00:00:00.03

数据库默认永久表空间

select * from database_properties where rownum<4;

PROPERTY_NAME			PROPERTY_VALUE		DESCRIPTION
DICT.BASE			2		dictionary base tables version #
DEFAULT_TEMP_TABLESPACE		TEMP		Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE	USERS		Name of default permanent tablespace

USER表空间现在属于默认永久表空间，当我们创建了一个用户，而且用户没有指明使用哪个表空间，那么这个用户所创建的所有对象都放到了USERS这个默认永久表空间里面，默认永久表空间是不允许被删除的

drop tablespace data01 including contents and datafiles;

Tablespace dropped.

Elapsed: 00:00:00.75

drop tablespace users including contents and datafiles;

drop tablespace users including contents and datafiles
*
ERROR at line 1:
ORA-12919: Can not drop the default permanent tablespace


Elapsed: 00:00:00.01

二、UNDO表空间：不保存任何对象，这里面存储ROLLBACK SEGMENT回滚段（为事物提供回退、恢复、读一致性、闪回）

创建UNDO表空间

create undo tablespace undo02 datafile size 10m;

select tablespace_name,contents,status from dba_tablespaces;

TABLESPACE_NAME                CONTENTS  STATUS
------------------------------ --------- ---------
SYSTEM                         PERMANENT ONLINE
SYSAUX                         PERMANENT ONLINE
UNDOTBS1                       UNDO      ONLINE
TEMP                           TEMPORARY ONLINE
USERS                          PERMANENT ONLINE
EXAMPLE                        PERMANENT ONLINE
UNDO02                         UNDO      ONLINE

7 rows selected.

Elapsed: 00:00:00.00

刚创建的UNDO表空间是没有用的，因为数据库在同一时刻只能使用一个UNDO表空间，

show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

undo_tablespace参数显示了数据库当前用的UNDO表空间

alter system set undo_tablespace=undo02;

show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDO02

三、临时表空间：不能保存永久对象，保存排序的中间结果，临时表的数据

创建临时表空间

create temporary tablespace temp02 tempfile size 10m;

select tablespace_name,contents,status from dba_tablespaces;

TABLESPACE_NAME                CONTENTS  STATUS
------------------------------ --------- ---------
SYSTEM                         PERMANENT ONLINE
SYSAUX                         PERMANENT ONLINE
UNDOTBS1                       UNDO      ONLINE
TEMP                           TEMPORARY ONLINE
USERS                          PERMANENT ONLINE
EXAMPLE                        PERMANENT ONLINE
UNDO02                         UNDO      ONLINE
TEMP02                         TEMPORARY ONLINE

8 rows selected.

Elapsed: 00:00:00.00

Paul_George

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle-22-数据文件和表空间管理

查看数据库表空间select tablespace_name,contents from dba_tablespaces;TABLESPACE_NAME CONTENTS------------------------------ ---------SYSTEM PERMANENTSYSAUX ...
复制链接

扫一扫