Oracle扩展表空间

参考文档

https://blog.csdn.net/qq_41040771/article/details/117409661

发现DQYWPT_DEV 表空间使用比为99.99%（>90%），查看有几个数据文件
SELECT file_name, tablespace_name, bytes / 1024 / 1024 "bytes MB", maxbytes / 1024 / 1024 "maxbytes MB" FROM dba_data_files WHERE tablespace_name = 'TBS_LOG_DAT_TDM';

是否是自动扩展
SELECT file_id, file_name, tablespace_name, autoextensible, increment_by FROM dba_data_files WHERE tablespace_name = 'TBS_LOG_DAT_TDM' ORDER BY file_id desc;

首先找出该表空间对应的数据文件及全路径，该路径对应FILE_NAME字段
SELECT * FROM dba_data_files t WHERE t.tablespace_name='TBS_LOG_DAT_TDM';

获取创建表空间的语句
SELECT dbms_metadata.get_ddl('TABLESPACE', 'TBS_LOG_DAT_TDM ') FROM dual;

新增一个数据文件，全路径的数据文件名称为该新增数据文件的全路径文件名称。大小为30720M,自动扩展功能打开，且该数据文件的最大扩展值为30G
alter tablespace TBS_LOG_DAT_TDM add datafile '/oradata/data/ora06dg/dat_tdm_log_14.dbf' size 30720M autoextend on maxsize 30G;

查询表空间下使用率
select a.tablespace_name, total, free, total-free as used, substr(free/total * 100, 1, 5) as "FREE%", substr((total - free)/total * 100, 1, 5) as "USED%" from
(select tablespace_name, sum(bytes)/1024/1024 as total from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as free from dba_free_space group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by a.tablespace_name;

删除表空间文件
alter tablespace TBS_LOG_DAT_TDM DROP datafile '/oradata/data/ora06dg/agg_auh_data_02.dbf'

SELECT file_name, tablespace_name, bytes / 1024 / 1024 "bytes MB", maxbytes / 1024 / 1024 "maxbytes MB" FROM dba_data_files WHERE tablespace_name = 'TBS_DATA_AGG_TRM';
SELECT * FROM dba_data_files t WHERE t.tablespace_name='TBS_DATA_AGG_TRM';
alter tablespace TBS_DATA_AGG_TRM add datafile '/oradata/data/ora06dg/agg_trm_data_02.dbf' size 22M autoextend on maxsize 30G;

SELECT file_name, tablespace_name, bytes / 1024 / 1024 "bytes MB", maxbytes / 1024 / 1024 "maxbytes MB" FROM dba_data_files WHERE tablespace_name = 'TBS_IDX_DAT_TDM';
SELECT * FROM dba_data_files t WHERE t.tablespace_name='TBS_IDX_DAT_TDM';
alter tablespace TBS_IDX_DAT_TDM add datafile '/oradata/data/ora06dg/dat_tdm_idx_06.dbf' size 30720M autoextend on maxsize 30G;

SELECT file_name, tablespace_name, bytes / 1024 / 1024 "bytes MB", maxbytes / 1024 / 1024 "maxbytes MB" FROM dba_data_files WHERE tablespace_name = 'TBS_IDX_DAT_MDM';
SELECT * FROM dba_data_files t WHERE t.tablespace_name='TBS_IDX_DAT_MDM';
alter tablespace TBS_IDX_DAT_MDM add datafile '/oradata/data/ora06dg/dat_mdm_idx_02.dbf' size 3060.5M autoextend on maxsize 30G;

SELECT file_name, tablespace_name, bytes / 1024 / 1024 "bytes MB", maxbytes / 1024 / 1024 "maxbytes MB" FROM dba_data_files WHERE tablespace_name = 'TBS_DATA_UNDO';
SELECT * FROM dba_data_files t WHERE t.tablespace_name='TBS_DATA_UNDO';
alter tablespace TBS_DATA_UNDO add datafile '/oradata/data/ora02dg/undo_da05.dbf' size 30720M autoextend on maxsize 30G;