Clear Temporary Tablespace

[carlaestevao80]

How can I clean my temporary tablespace?

Thanks
CE

[gilbertoca]

How can I clean my temporary tablespace?

I have read in some discussions that we have two ways to do it: one is doing a normal shutdown and the other is to delete and re-create temporary tablespace.
But rather do some tests:

1) Check the size:

Select all

SELECT * FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'TEMP';

or

Select all

SELECT * FROM DBA_TEMP_FILES

2) Check the storage configuration:

Select all

SELECT * FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = 'TEMP';

3) Check for fragmentation:

Select all

SELECT * FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME = 'TEMP';

If there are many "Free Space", then you can use the COALESCE clause To release continuous spaces:

Select all

ALTER TABLESPACE TEMP COALESCE;

[carlaestevao80]

The Tablespace Temp is 100% free, however it is with too much space assigned and I can not do Resize


ORA-03297: The file contains data used beyond the value of Resize required

[gilbertoca]

Maybe have something in the lycheira (recyclebin). Try to clean it:

Select all

purge recyclebin;

Gilberto

[carlaestevao80]

Hello,
I have already cleaned the Recycle Bin and the problem remains ...

CE

[gilbertoca]

See if you can perform this command:

Select all

 
alter database database_name 
tempfile 'directory/of/tempfile' resize new_size;  

otherwise you will have to recreate it: http://www.idevelopment.info/data/Oracl ... BS_3.shtml

Gilberto

[cigano]

Temporary Tablespace will always work with 100% use, so there is no need to make a cleaning. However, it should be verified if the Datafile has a limit to grow, as if the size of the Datafile causes it to be busy 100% of the HD will generate problems for Oracle.
If this is not your case Leave it, Oracle himself is responsible for re-allocating the required space within Datafile.