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>
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:
Post a Comment