Oracle创建表空间、扩容、删除

Oracle表空间（tableSpace）、段（segment）、盘区（extent）、块（block），这些都是Oracle用来保存数据库对象的分配单元。Oracle中存储的层次结构如下：1、数据库由一个或多个表空间组成。2、表空间由一个或多个数据文件组成，一个表空间包含段。3、段由一个或多个盘区组成，段存在于表空间中，但在表空间中可以有许多数据文件中的数据。4、盘区是在磁盘上连续的块的组，一

文章共1,958字 · 阅读需要大约7分钟

一键AI生成摘要，助你高效阅读

问答

小百菜

6533人浏览 · 2021-04-13 11:46:24

小百菜 · 2021-04-13 11:46:24 发布

Oracle表空间（tableSpace）、段（segment）、盘区（extent）、块（block），这些都是Oracle用来保存数据库对象的分配单元。

Oracle中存储的层次结构如下：
1、数据库由一个或多个表空间组成。
2、表空间由一个或多个数据文件组成，一个表空间包含段。
3、段由一个或多个盘区组成，段存在于表空间中，但在表空间中可以有许多数据文件中的数据。
4、盘区是在磁盘上连续的块的组，一个盘区在一个表空间中，而且总是在表空间中单一的文件中。
5、块是数据库中最小的分配单元，块是数据库使用的最小的I/O单元。

extent--最小空间分配单位 --tablespace management
block --最小i/o单位      --segment    management


create tablespace james
datafile '/export/home/oracle/oradata/james.dbf'
size 100M 　　　　　　--初始的文件大小　
autoextend On　　　　 --自动增长-默认为off
next 10M　　　　　 --每次自动增长大小　
maxsize 2048M　　 　 --最大文件大小
extent management local --表空间采用本地表空间管理 --默认就是本地local默认就是自动system可不用指定。
uniform size 128k　　　 --uniform设置extent每次分配的大小统一为128k(如果是db_block_size=8k,则每次分配16个块)
                       　　　　 --如果不指定大小，则为1M，即为1024/8个block
                       　　　　--autoallocate设置extent大小由系统自动分配
                      　　　　 --不管系统大小分配为多少，但统一尺寸是64k（在bitmap中标记位的大小）。
                       　　　　 --autoallocate在dba_extents中的allocation_type中显示为 SYSRTEM
segment space management auto;   --默认就是auto
                        --segment中的block管理有两种：MSSM(Manual Segment Space Management)，
ASSM(Auto Systemt Space Management)
                        --Auto 模式时只有pctfree参数起作用
--Manual 模式时freelist,pctfree,pctused参数起作用。

1、表空间剩余大小

SELECT a.tablespace_name "表空间名",
       round(total / (1024 * 1024 * 1024), 2) "表空间大小(G)",
       round(free / (1024 * 1024 * 1024), 2) "表空间剩余大小(G)",
       round((total - free) / (1024 * 1024 * 1024), 2) "表空间使用大小(G)",
       round((total - free) / total, 4) * 100 "使用率 %"
  FROM (SELECT tablespace_name, SUM(bytes) free
          FROM dba_free_space
         GROUP BY tablespace_name) a,
       (SELECT tablespace_name, SUM(bytes) total
          FROM dba_data_files
         GROUP BY tablespace_name) b
 WHERE a.tablespace_name = b.tablespace_name

临时表空间剩余大小

select c.tablespace_name "临时表空间名",
       round(c.bytes / 1024 / 1024 / 1024, 2) "临时表空间大小(G)",
       round((c.bytes - d.bytes_used) / 1024 / 1024 / 1024, 2) "临时表空间剩余大小(G)",
       round(d.bytes_used / 1024 / 1024 / 1024, 2) "临时表空间使用大小(G)",
       round(d.bytes_used * 100 / c.bytes, 4) || '%' "使用率 %"
  from (select tablespace_name, sum(bytes) bytes
          from dba_temp_files
         GROUP by tablespace_name) c,
       (select tablespace_name, sum(bytes_cached) bytes_used
          from v$temp_extent_pool
         GROUP by tablespace_name) d
 where c.tablespace_name = d.tablespace_name;

2、查看分区和段空间管理方式

select tablespace_name          "表空间名",
       extent_management        "表空间管理方式", --默认LOCAL
       allocation_type          "分区管理方式", --默认SYSTEM，自动。
       segment_space_management "段空间管理方式" --默认AUTO，自动。
  from dba_tablespaces;

3、表空间文件位置

SELECT TABLESPACE_NAME "表空间名", BYTES/1024/1024 "表空间大小(M)", FILE_NAME "文件路径",FILE_ID "文件ID" FROM DBA_DATA_FILES order by TABLESPACE_NAME,FILE_NAME;

表空间文件使用率

 select b.file_id 物理文件号, b.file_name 物理文件名, b.tablespace_name 表空间, 
        b.bytes/1024/1024 大小M, (b.bytes-sum(nvl(a.bytes,0)))/1024/1024  已使用M,
        substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5)  利用率 
from dba_free_space a,dba_data_files b 
where a.file_id=b.file_id 
group by b.tablespace_name,b.file_id,b.file_name,b.bytes 
order by b.tablespace_name

临时表空间文件位置

SELECT TABLESPACE_NAME "临时表空间名",BYTES/1024/1024 "表空间大小(M)",FILE_NAME "文件路径" FROM DBA_TEMP_FILES order by TABLESPACE_NAME,FILE_NAME;

4、创建临时表空间

临时表空间：主要用途是在数据库进行，排序运算[如创建索引、order by及group by、distinct、union/intersect/minus/、sort-merge及join、analyze命令]、

管理索引[如创建索引、IMP进行数据导入]、

访问视图等操作时提供临时的运算空间，当运算完成之后系统会自动清理。

create temporary tablespace IRFS_TEMP
  tempfile '/data/oracle/oradata/orcl/irfs_temp01.dbf' 
  size 10240m --初始的文件大小。
  autoextend on --自动增长
  next 1024m --每次自动增长大小
  maxsize 20480m --最大文件大小
  extent management local;--本地管理方式

5、创建表空间

一般建N个表空间和一个索引空间

--创建永久表空间：IRFS 
create tablespace IRFS
  datafile '/data/oracle/oradata/orcl/irfs01.dbf' 
  size 30G --表空间初始的文件大小。
  reuse autoextend off --对成熟的系统部署移植工作而言，通常是可以确定文件的固定大小。避免经常性的文件膨胀，引起性能变化；
  extent management local autoallocate --本地管理方式，区分配方式为自动分配
  segment space management auto;--段管理方式为自动管理

6、设置用户的临时表空间和表空间

    alter user MCQHW 
    default tablespace IRFS
    temporary tablespace IRFS_TEMP;

7、表空间手动扩容

表空间超过70%，在新的磁盘上添加数据文件

alter tablespace irfs add datafile '/data/oracle/oradata/orcl/irfs02.dbf' size 30G;

临时表空间扩容

alter tablespace irfs_temp add tempfile '/home/oracle/db/oradata/orcl/temp02.dbf' size 30G;

删除表空间扩容文件

#alter tablespace 表空间名称 drop datafile 文件id;
alter tablespace UNDOTBS1 drop datafile 6;

删除临时表空间扩容文件

#alter tablespace 临时表空间名称 drop tempfile 文件id;
alter tablespace UNDOTBS2 drop tempfile 7;

8、清理临时表空间

alter  tablespace  IRFS_TEMP shrink space;

我的临时表空间有20G，使用率95%，执行这条sql用了大概2-3小时，执行过程中查询临时表空间的sql一直卡住没有结果，2，3小时执行完成后，使用率降低到3%。

9、删除表空间

drop tablespace IRFS including contents and datafiles;

删除临时表空间

drop tablespace IRFS_TEMP including contents and datafiles;

创建各种类型表空间

-- 创建大小为50mb的永久表空间TEST01，禁止自动扩展数据文件
create tablespace TEST01
	logging
	datafile'F:\app\oraclezq\oradata\orcl\TEST01.dbf' size 50m
	reuse autoextend off;
 
-- 创建永久表空间TEST02，允许自动扩展数据文件，本地管理方式
create tablespace TEST02
	logging
	datafile'F:\app\oraclezq\oradata\orcl\TEST02.dbf' size 50m
	reuse autoextend on next 10m maxsize 200m
	extent management local;
 
-- 创建永久表空间TEST03，允许自动扩展数据文件，本地管理方式，区分配方式为自动分配
create tablespace TEST03
	logging
	datafile'F:\app\oraclezq\oradata\orcl\TEST03.dbf' size 50m
	reuse autoextend on next 10m maxsize 200m
	extent management local autoallocate;
 
-- 创建永久表空间TEST04，允许自动扩展数据文件，本地管理方式，区分配方式为定制分配
create tablespace TEST04
	logging
	datafile'F:\app\oraclezq\oradata\orcl\TEST04.dbf' size 50m
	reuse autoextend on next 10m maxsize 200m
	extent management local uniform size 10m;
 
-- 创建永久表空间TEST05，允许自动扩展数据文件，本地管理方式，区分配方式为自动分配，段管理方式为自动管理
create tablespace test05
	logging
	datafile'F:\app\oraclezq\oradata\orcl\TEST05.dbf' size 50m
	reuse autoextend on next 10m maxsize 200M
	extent management local autoallocate
	segment space management auto;
 
-- 创建永久表空间TEST06，允许自动扩展数据文件，本地管理方式，区分配方式为定制分配，段管理方式为手动管理
create tablespace test06
	logging
	datafile'F:\app\oraclezq\oradata\orcl\TEST06.dbf' size 50m
	reuse autoextend on next 10m maxsize 200M
	extent management local uniform size 10m
	segment space management manual;

参考：

表空间(tableSpace) 段(segment) 盘区(extent) 块(block) 关系_数据库技术_Linux公社-Linux系统门户网站

https://www.cnblogs.com/rusking/p/4286102.html

ORACLE表空间创建、管理、删除的基础用法 - 灰信网（软件开发博客聚合）

Oracle表空间创建参数解析_allocation type oracle_reborn_hsc的博客-CSDN博客