Shrinking a Locally Managed Temporary Tablespace

in 11g database, You can shrink the temporary tablespace by command
alter tablespace temp shrink space;


Earlier, large sort operations by Oracle database were causing the temporary table space to grow to a higher value and space once allocated was not being released. in 11g we can shrink the temporary tablespace.

Also, we can limit the size of temporary tablespace by KEEP command.

ALTER TABLESPACE temp SHRINK SPACE KEEP 200M;

OR shrink the tempfile.
ALTER TABLESPACE temp SHRINK TEMPFILE '/sandeep/dbfs/temp.dbf';

No comments:

Post a Comment