Changing the Size of the Tablespace

Posted by thakuranupam on November 8, 2009

Alter the STORAGE settings of a Tablespace

ALTER TABLESPACE tablespacename
[MINIMUM EXTENT integer [K|M]
[DEFAULT storage clause]

Resizing a Tablespace

ALTER TABLESPACE mydb1data
ADD DATAFILE ‘\mydatabases\mydb1\mydb1data02.dbf’
SIZE 100M;

ALTER DATABASE
DATAFILE ‘\mydatabases\mydb1\mydb1data01.dbf’
RESIZE 500M;

To allow the data file to extend itself automatically;

ALTER DATABASE
DATAFILE ‘\mydatabases\mydb1\mydb1data01.dbf’
SIZE 250M
AUTOEXTEND ON NEXT 25M MAXSIZE 500M;

Moving Data Files with ALTER TABLESPACE

ALTER TABLESPACE mydb1data
RENAME DATAFILE ‘/mydatabases/mydb1/mydb1data01.dbf’
TO ‘/mydatabases3/mydb1/mydb1data01.dbf’;

Moving Data Files with ALTER DATABASE

ALTER DATABASE mydb1
RENAME FILE ‘/mydatabases/mydb1/system01.dbf’,'/mydatabases/mydb1/system02.dbf’
TO ‘/mydatabases3/mydb1/system01.dbf’,'/mydatabases3/mydb1/system02.dbf’;

Like this:

Be the first to like this post.

This entry was posted on November 8, 2009 at 11:02 am and is filed under Tablespaces. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.