Home » RDBMS Server » Server Administration » space recovery (11g)
space recovery [message #605285] Wed, 08 January 2014 07:15 Go to next message
kekanap
Messages: 18
Registered: January 2008
Location: Centurion
Junior Member

Hallo Team,

I messed up. I created and added files to a wrong table space (user_data) instead PERFSTAT and i would like to claim it back if possible.

This is the command that i ran and it ran successfully but it was not supposed to be user_data :

ALTER TABLESPACE USER_DATA ADD DATAFILE '/u05/oracle/oradata/bill/dbf/data1/PERFSTAT_BILL_08.DBF' SIZE 5G;
ALTER TABLESPACE USER_DATA ADD DATAFILE '/u05/oracle/oradata/bill/dbf/data1/PERFSTAT_BILL_09.DBF' SIZE 5G;
ALTER TABLESPACE USER_DATA ADD DATAFILE '/u05/oracle/oradata/bill/dbf/data1/PERFSTAT_BILL_10.DBF' SIZE 5G;
ALTER TABLESPACE USER_DATA ADD DATAFILE '/u05/oracle/oradata/bill/dbf/data1/PERFSTAT_BILL_11.DBF' SIZE 5G;
ALTER TABLESPACE USER_DATA ADD DATAFILE '/u05/oracle/oradata/bill/dbf/data1/PERFSTAT_BILL_12.DBF' SIZE 5G;
ALTER TABLESPACE USER_DATA ADD DATAFILE '/u05/oracle/oradata/bill/dbf/data1/PERFSTAT_BILL_13.DBF' SIZE 5G;
ALTER TABLESPACE USER_DATA ADD DATAFILE '/u05/oracle/oradata/bill/dbf/data2/PERFSTAT_BILL_14.DBF' SIZE 5G;


i would like to reverse and claim back the space then run like below:

ALTER TABLESPACE PERFSTAT ADD DATAFILE '/u05/oracle/oradata/bill/dbf/data1/PERFSTAT_BILL_08.DBF' SIZE 5G;
ALTER TABLESPACE PERFSTAT ADD DATAFILE '/u05/oracle/oradata/bill/dbf/data1/PERFSTAT_BILL_09.DBF' SIZE 5G;
ALTER TABLESPACE PERFSTAT ADD DATAFILE '/u05/oracle/oradata/bill/dbf/data1/PERFSTAT_BILL_10.DBF' SIZE 5G;
ALTER TABLESPACE PERFSTAT ADD DATAFILE '/u05/oracle/oradata/bill/dbf/data1/PERFSTAT_BILL_11.DBF' SIZE 5G;
ALTER TABLESPACE PERFSTAT ADD DATAFILE '/u05/oracle/oradata/bill/dbf/data1/PERFSTAT_BILL_12.DBF' SIZE 5G;
ALTER TABLESPACE PERFSTAT ADD DATAFILE '/u05/oracle/oradata/bill/dbf/data1/PERFSTAT_BILL_13.DBF' SIZE 5G;
ALTER TABLESPACE PERFSTAT ADD DATAFILE '/u05/oracle/oradata/bill/dbf/data2/PERFSTAT_BILL_14.DBF' SIZE 5G;

Re: space recovery [message #605288 is a reply to message #605285] Wed, 08 January 2014 07:21 Go to previous messageGo to next message
John Watson
Messages: 4677
Registered: January 2010
Location: Global Village
Senior Member
alter tablespace user_data drop datafile '/u05/oracle/oradata/bill/dbf/data2/PERFSTAT_BILL_14.DBF';
but you'll have to be quick.
Re: space recovery [message #605295 is a reply to message #605288] Wed, 08 January 2014 08:15 Go to previous messageGo to next message
kekanap
Messages: 18
Registered: January 2008
Location: Centurion
Junior Member

awesome. it worked. thank you very much
Re: space recovery [message #605434 is a reply to message #605295] Thu, 09 January 2014 06:58 Go to previous messageGo to next message
kekanap
Messages: 18
Registered: January 2008
Location: Centurion
Junior Member

James thank you for yesterday's response. How would i have checked if the space was already being used ?
Re: space recovery [message #605437 is a reply to message #605434] Thu, 09 January 2014 07:02 Go to previous message
John Watson
Messages: 4677
Registered: January 2010
Location: Global Village
Senior Member
Now that your emergency is over, it is time you did some work: read the docs, and you will find the appropriate views. Hint: "extents".
Previous Topic: Instance with ReadOnly and ReadWrite Access
Next Topic: Table data changed
Goto Forum:
  


Current Time: Wed Oct 22 09:55:20 CDT 2014

Total time taken to generate the page: 0.05984 seconds