Shulou(Shulou.com)05/31 Report--

This article mainly shows you "how to shrink temporary tablespaces in the database and deal with ORA-03297 errors", the content is easy to understand, well-organized, hope to help you solve your doubts, the following let the editor lead you to study and learn "how to shrink temporary tablespaces in the database and deal with ORA-03297 errors" this article.

The following error is reported when we do manual resize

SQL > alter database tempfile'/ u01 resize

Alter database tempfile'/ u01 resize resize 16G

*

ERROR at line 1:

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

SQL > alter database tempfile'/ u01 resize

Alter database tempfile'/ u01 resize resize 20G

*

ERROR at line 1:

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

We use the exchange of temporary tablespaces to shrink temporary tablespaces

New temporary tablespace

SQL > CREATE TEMPORARY TABLESPACE temp2 tempfile'/ u01 size size autoextend on next 20m oradata 8G

Tablespace created.

SQL > ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2

Database altered.

SQL > select * from (select username,temporary_tablespace from dba_users) where rownum DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES

Query is using temporary tablespace transactions We are test environment direct kill (formal environment is used with caution, wait a while before drop)

SQL > SELECT 'ALTER SYSTEM KILL SESSION''| | sid | |','| | serial# | |''immediate;' FROM v$session where saddr in (SELECT session_addr FROM v$sort_usage WHERE tablespace='TEMP2')

SQL > DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES

Tablespace dropped.

Delete temporary tablespace completed

The above is all the contents of the article "how to shrink temporary tablespaces and deal with ORA-03297 errors in the database". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.