Tuesday, July 18, 2017

ORA-03297: file contains used data beyond requested RESIZE value

SQL> alter database tempfile '+DATA/testp/tempfile/temp.276.799247671' resize 5000M;
alter database tempfile '+DATA/testp/tempfile/temp.276.799247671' resize 5000M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value


SQL>

SQL> col file_name format a70
SQL> select file_name, tablespace_name, status, BYTES/1024/1024 "F Size MB", maxbytes/1024/1024 "Max Size MB", INCREMENT_BY, AUTOEXTENSIBLE from dba_temp_files;

FILE_NAME                                                              TABLESPACE_NAME                STATUS   F Size MB Max Size MB INCREMENT_BY AUT
---------------------------------------------------------------------- ------------------------------ ------- ---------- ----------- ------------ ---
+DATA/testp/tempfile/temp.276.799247671                              TEMP                           ONLINE       11000       11000           80 YES

SQL>

SQL> ALTER TABLESPACE temp SHRINK SPACE KEEP 5000M;

Tablespace altered.

SQL> col file_name format a70
SQL> select file_name, tablespace_name, status, BYTES/1024/1024 "F Size MB", maxbytes/1024/1024 "Max Size MB", INCREMENT_BY, AUTOEXTENSIBLE from dba_temp_files;

FILE_NAME                                                              TABLESPACE_NAME                STATUS   F Size MB Max Size MB INCREMENT_BY AUT
---------------------------------------------------------------------- ------------------------------ ------- ---------- ----------- ------------ ---
+DATA/testp/tempfile/temp.276.799247671                              TEMP                           ONLINE  10999.9922       11000           80 YES

SQL>


SQL> ALTER TABLESPACE temp SHRINK SPACE KEEP 5000M;

Tablespace altered.

SQL> select file_name, tablespace_name, status, BYTES/1024/1024 "F Size MB", maxbytes/1024/1024 "Max Size MB", INCREMENT_BY, AUTOEXTENSIBLE from dba_temp_files;

FILE_NAME                                                              TABLESPACE_NAME                STATUS   F Size MB Max Size MB INCREMENT_BY AUT
---------------------------------------------------------------------- ------------------------------ ------- ---------- ----------- ------------ ---
+DATA/testp/tempfile/temp.276.799247671                              TEMP                           ONLINE  6231.99219       11000           80 YES

SQL>
SQL>
SQL>
SQL> alter database tempfile '+DATA/testp/tempfile/temp.276.799247671' AUTOEXTEND on maxsize 5000M;

Database altered.

SQL> select file_name, tablespace_name, status, BYTES/1024/1024 "F Size MB", maxbytes/1024/1024 "Max Size MB", INCREMENT_BY, AUTOEXTENSIBLE from dba_temp_files;

FILE_NAME                                                              TABLESPACE_NAME                STATUS   F Size MB Max Size MB INCREMENT_BY AUT
---------------------------------------------------------------------- ------------------------------ ------- ---------- ----------- ------------ ---
+DATA/testp/tempfile/temp.276.799247671                              TEMP                           ONLINE  6231.99219  6231.99219           80 YES

SQL>

No comments: