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

2024-01-20 09:51| 来源: 网络整理| 查看: 265

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博客

【本文地址】

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

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

今日新闻

推荐新闻