Oracle 导入数据报错ora-01653无法通过1024扩展

柔情只为你懂 2022-12-26 15:26 262阅读 0赞

# 1.查询表空间使用情况 #

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
    D.TOT_GROOTTE_MB "表空间大小(M)",
    D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
    TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') "使用比",
    F.TOTAL_BYTES "空闲空间(M)",
    F.MAX_BYTES "最大块(M)"
    FROM (SELECT TABLESPACE_NAME,
    ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
    ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
    FROM SYS.DBA_FREE_SPACE
    GROUP BY TABLESPACE_NAME) F,
    (SELECT DD.TABLESPACE_NAME,
    ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
    FROM SYS.DBA_DATA_FILES DD
    GROUP BY DD.TABLESPACE_NAME) D
    WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
    ORDER BY 4 DESC;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podWltZW5nX2J5_size_16_color_FFFFFF_t_70]

# 2.查看表空间是否具有自动扩展的能力 #

SELECT T.TABLESPACE_NAME,
    D.FILE_NAME,
    D.AUTOEXTENSIBLE,
    D.BYTES,
    D.MAXBYTES,
    D.STATUS
    FROM DBA_TABLESPACES T, DBA_DATA_FILES D
    WHERE T.TABLESPACE_NAME = D.TABLESPACE_NAME
    ORDER BY TABLESPACE_NAME, FILE_NAME;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podWltZW5nX2J5_size_16_color_FFFFFF_t_70 1]

# 3.查询当前数据库中表空间JT\_BBMS\_DATA是否为自动扩展 #

select tablespace_name,file_name,autoextensible from dba_data_files where tablespace_name = 'JT_BBMS_DATA';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podWltZW5nX2J5_size_16_color_FFFFFF_t_70 2]  
AUTOEXTENSIBLE 值为YES表示支持自动扩展

# 4.扩大表空间的四种方法： #

## 1、增加数据文件 ##

ALTER TABLESPACE JT_BBMS_DATA ADD DATAFILE 
    '/ora/app/jtbbms/oradata/resjt/JT_BBMS_DATA01.dbf' SIZE 1024M;

## 2、增加数据文件并允许自动增长 ##

ALTER TABLESPACE JT_BBMS_DATA ADD DATAFILE 
    '/ora/app/jtbbms/oradata/resjt/JT_BBMS_DATA04.dbf' SIZE 1024M AUTOEXTEND ON NEXT 8M MAXSIZE 20480M;

## 3、允许已存在的数据文件自动增长 ##

ALTER DATABASE DATAFILE '/ora/app/jtbbms/oradata/resjt/JT_BBMS_DATA01.dbf' 
    AUTOEXTEND ON NEXT 8M MAXSIZE 10240M;

## 4、手工改变已存在数据文件的大小 ##

ALTER DATABASE DATAFILE '/ora/app/jtbbms/oradata/resjt/JT_BBMS_DATA01.dbf' 
    RESIZE 10240M;

> 注意：1、2、3、4中的DATAFILE（’/ora/app/jtbbms/oradata/resjt/JT\_BBMS\_DATA01.dbf’）需要自己替换成自己需要操作的数据文件名；1、2中的“JT\_BBMS\_DATA ”需要替换成自己要操作的TABLESPACE 名

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podWltZW5nX2J5_size_16_color_FFFFFF_t_70]: /images/20221120/7e54206d67c0470a9f7de9caa00b3427.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podWltZW5nX2J5_size_16_color_FFFFFF_t_70 1]: /images/20221120/521fdf743d0f44db857561713c25d90d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podWltZW5nX2J5_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20201206120946555.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podWltZW5nX2J5,size_16,color_FFFFFF,t_70