OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » space recovery (11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

space recovery [message #605285]

Wed, 08 January 2014 07:15

kekanap
Messages: 19
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;

Report message to a moderator

Re: space recovery [message #605288 is a reply to message #605285]

Wed, 08 January 2014 07:21

John Watson
Messages: 9003
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.

Report message to a moderator

Re: space recovery [message #605295 is a reply to message #605288]

Wed, 08 January 2014 08:15

kekanap
Messages: 19
Registered: January 2008
Location: Centurion

Junior Member

awesome. it worked. thank you very much

Report message to a moderator

Re: space recovery [message #605434 is a reply to message #605295]

Thu, 09 January 2014 06:58

kekanap
Messages: 19
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 ?

Report message to a moderator

Re: space recovery [message #605437 is a reply to message #605434]

Thu, 09 January 2014 07:02

John Watson
Messages: 9003
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".

Report message to a moderator

Previous Topic:	Instance with ReadOnly and ReadWrite Access
Next Topic:	Table data changed

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Aug 07 22:27:00 CDT 2026