oracle 创建 temporary tablespace

最新推荐文章于 2025-02-13 08:26:17 发布

数据库人生

最新推荐文章于 2025-02-13 08:26:17 发布

阅读量2k

点赞数

分类专栏： # oracle basic knowledge 文章标签： tablespace temporary tablespace

本文链接：https://blog.csdn.net/ctypyb2002/article/details/88662202

版权

oracle basic knowledge 专栏收录该内容

60 篇文章

订阅专栏

博客基于CentOS 7.4系统和Oracle 12.1.0.2数据库，介绍了大文件表空间和小文件表空间的特点，如大文件表空间只能含一个文件，小文件表空间可含1022个数据文件。还提及创建临时大小文件表空间、添加临时小文件及调整临时小文件表空间大小等操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

os: centos 7.4
db: oracle 12.1.0.2

A bigfile tablespace contains only one data file or temp file, which can contain up to approximately 4 billion (2的32次方) blocks. The minimum size of the single data file or temp file is 12 megabytes (MB) for a tablespace with 32K blocks and 7MB for a tablespace with 8K blocks. The maximum size of the single data file or temp file is 128 terabytes (TB) for a tablespace with 32K blocks and 32TB for a tablespace with 8K blocks.

A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 data files or temp files, each of which can contain up to approximately 4 million (2的22次方) blocks.

bigfile tablespace 只能包含一个文件,不可以再添加.block 是 2的32次方(4G) 寻址
smallfile tablespace 是可以添加至1022个数据文件.block 是 2的22次方(4M) 寻址

create temporary smallfile tablespace


CREATE TEMPORARY TABLESPACE peiyb_temp 
TEMPFILE '/data/orcl/pdborcl/peiyb_temp01.dbf' 
SIZE 1G 
AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED
TABLESPACE GROUP ''
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512M;

create temporary bigfile tablespace


CREATE BIGFILE TEMPORARY TABLESPACE peiyb_temp 
TEMPFILE  '/data/orcl/pdborcl/peiyb_temp01.dbf' 
SIZE 1G 
AUTOEXTEND ON NEXT 1G MAXSIZE 34359738344K
TABLESPACE GROUP ''
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512M;

设置成 default temporary tablespace

SQL> alter database default temporary tablespace peiyb_temp ;
SQL> drop tablespace temp;

add temporary smallfile

ALTER TABLESPACE peiyb_temp
  ADD TEMPFILE '/data/orcl/pdborcl/peiyb_temp02.dbf'
  SIZE 1G
  AUTOEXTEND ON
  NEXT 1G
  MAXSIZE UNLIMITED;

resize temporary smallfile tablespace

select 'alter database tempfile '''||dtf.file_name||''' autoextend on next 1g maxsize UNLIMITED; ', 
       dtf.*
from dba_temp_files dtf
where 1=1
;

ALTER DATABASE TEMPFILE '/data/orcl/pdborcl/peiyb_temp02.dbf'
RESIZE 1G;

ALTER DATABASE TEMPFILE '/data/orcl/pdborcl/peiyb_temp02.dbf' 
AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;

参考:
https://docs.oracle.com/database/121/SQLRF/statements_7003.htm#SQLRF01403