首页 > 代码库 > oracle数据库表空间扩容方法

oracle数据库表空间扩容方法

2024-08-12 14:52:09   218人阅读

 

1. 先查询表空间在物理磁盘上存放的位置,注意使用sysdba的账号登陆。

SELECT tablespace_name, file_id, file_name, round(bytes / (1024 * 1024), 0) total_space FROM dba_data_files ORDER BY tablespace_name;

技术分享

 

2. 需要扩容的表空间是DW_STG_TBS,目前的文件分配序号是DW_STG_TBS20.dbf，

    所以在接下来的要增加的文件的名称从21开始，我们一次行增加20个文件，脚本如下。

    其中设置的每个文件初始分配空间为7g, autoextend on为自动增长大小，oracle单个文件大小最大不超过32g.

--增加Stage层表空间alter tablespace DW_STG_TBSadd datafile  ‘/u01/app/oracle/oradata/crm001/DW_STG_TBS21.dbf‘ size 7167M autoextend on ;alter tablespace DW_STG_TBSadd datafile  ‘/u01/app/oracle/oradata/crm001/DW_STG_TBS22.dbf‘ size 7167M autoextend on ;alter tablespace DW_STG_TBSadd datafile  ‘/u01/app/oracle/oradata/crm001/DW_STG_TBS23.dbf‘ size 7167M autoextend on ;alter tablespace DW_STG_TBSadd datafile  ‘/u01/app/oracle/oradata/crm001/DW_STG_TBS24.dbf‘ size 7167M autoextend on ;alter tablespace DW_STG_TBSadd datafile  ‘/u01/app/oracle/oradata/crm001/DW_STG_TBS25.dbf‘ size 7167M autoextend on ;alter tablespace DW_STG_TBSadd datafile  ‘/u01/app/oracle/oradata/crm001/DW_STG_TBS26.dbf‘ size 7167M autoextend on ;alter tablespace DW_STG_TBSadd datafile  ‘/u01/app/oracle/oradata/crm001/DW_STG_TBS27.dbf‘ size 7167M autoextend on ;alter tablespace DW_STG_TBSadd datafile  ‘/u01/app/oracle/oradata/crm001/DW_STG_TBS28.dbf‘ size 7167M autoextend on ;alter tablespace DW_STG_TBSadd datafile  ‘/u01/app/oracle/oradata/crm001/DW_STG_TBS29.dbf‘ size 7167M autoextend on ;alter tablespace DW_STG_TBSadd datafile  ‘/u01/app/oracle/oradata/crm001/DW_STG_TBS30.dbf‘ size 7167M autoextend on ;alter tablespace DW_STG_TBSadd datafile  ‘/u01/app/oracle/oradata/crm001/DW_STG_TBS31.dbf‘ size 7167M autoextend on ;alter tablespace DW_STG_TBSadd datafile  ‘/u01/app/oracle/oradata/crm001/DW_STG_TBS32.dbf‘ size 7167M autoextend on ;alter tablespace DW_STG_TBSadd datafile  ‘/u01/app/oracle/oradata/crm001/DW_STG_TBS33.dbf‘ size 7167M autoextend on ;alter tablespace DW_STG_TBSadd datafile  ‘/u01/app/oracle/oradata/crm001/DW_STG_TBS34.dbf‘ size 7167M autoextend on ;alter tablespace DW_STG_TBSadd datafile  ‘/u01/app/oracle/oradata/crm001/DW_STG_TBS35.dbf‘ size 7167M autoextend on ;alter tablespace DW_STG_TBSadd datafile  ‘/u01/app/oracle/oradata/crm001/DW_STG_TBS36.dbf‘ size 7167M autoextend on ;alter tablespace DW_STG_TBSadd datafile  ‘/u01/app/oracle/oradata/crm001/DW_STG_TBS37.dbf‘ size 7167M autoextend on ;alter tablespace DW_STG_TBSadd datafile  ‘/u01/app/oracle/oradata/crm001/DW_STG_TBS38.dbf‘ size 7167M autoextend on ;alter tablespace DW_STG_TBSadd datafile  ‘/u01/app/oracle/oradata/crm001/DW_STG_TBS39.dbf‘ size 7167M autoextend on ;alter tablespace DW_STG_TBSadd datafile  ‘/u01/app/oracle/oradata/crm001/DW_STG_TBS40.dbf‘ size 7167M autoextend on ;

3. 将以上SQL在PL/SQL中执行，完成后查询结果如下：

技术分享

4. 使用本博客中另外一篇文章[ORACLE数据库存储空间使用情况查询]中的SQL语句查询表空间大小

技术分享

 

oracle数据库表空间扩容方法


联系
我们