Thanks for the question, oracle.
Asked: August 08, 2017 - 8:46 am UTC
Last updated: August 08, 2017 - 2:19 pm UTC
Version: 11.2.0
Viewed 1000+ times
You Asked
Hi team,
We have given the system,sysaux,undo,temp tablespace huge size allocated now the space issue on Os level So, can i drop and re-create a tablespace in Oracle which is oracle tablespace It is downtime activity ? How can we do this ?
Note-
All Users data stored in separate tablespace it is not in these system,sysaux,undo tablespace.
Can i drop and re-create temp tablespace in production environment without down-time ?
Thanks
and Chris said...
From the docs:
You cannot drop the SYSTEM tablespace. You can drop the SYSAUX tablespace only if you have the SYSDBA system privilege and you have started the database in UPGRADE mode.And
You cannot drop an undo tablespace if it is being used by any instance or if it contains any undo data needed to roll back uncommitted transactions.
You cannot drop a tablespace that has been designated as the default tablespace for the database. You must first reassign another tablespace as the default tablespace and then drop the old default tablespace.
You cannot drop a temporary tablespace if it is part of the database default temporary tablespace group. You must first remove the tablespace from the database default temporary tablespace group and then drop it.
You cannot drop a temporary tablespace if it contains segments that are in use by existing sessions. In this case, no error is raised. The database waits until there are no segments in use by existing sessions and then drops the tablespace. http://docs.oracle.com/database/122/SQLRF/DROP-TABLESPACE.htm#SQLRF01807 You can change an undo tablespace with:
ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_02;
Though you'll get an error if any of the following are true:
The tablespace does not exist
The tablespace is not an undo tablespace
The tablespace is already being used by another instance (in a RAC environment only) https://docs.oracle.com/cd/B28359_01/server.111/b28310/undo005.htm#ADMIN11477 You can add a new temporary tablespace whenever you want. And remove the old one subject to the above restrictions. But be aware that this will ultimately grow again depending on how much temp space your SQL uses.
https://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces002.htm#ADMIN11366