oracle表空间管理

2023年 12月 9日 34.5k 0

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如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论