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

女爷i 2023-10-06 17:18 2阅读 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系统门户网站][tableSpace_ _segment_ _extent_ _block_ _Linux_-Linux]

[https://www.cnblogs.com/rusking/p/4286102.html][https_www.cnblogs.com_rusking_p_4286102.html]

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

[Oracle表空间创建参数解析\_allocation type oracle\_reborn\_hsc的博客-CSDN博客][Oracle_allocation type oracle_reborn_hsc_-CSDN]

[tableSpace_ _segment_ _extent_ _block_ _Linux_-Linux]: https://www.linuxidc.com/Linux/2011-07/38016.htm
[https_www.cnblogs.com_rusking_p_4286102.html]: https://www.cnblogs.com/rusking/p/4286102.html
[ORACLE_ -]: https://www.freesion.com/article/7574693950/
[Oracle_allocation type oracle_reborn_hsc_-CSDN]: https://blog.csdn.net/mufenghsc/article/details/38532887