Changing the Size of the Tablespace


You use the ALTER TABLESPACE command to alter the default storage definition of a tablespace or to add a data file to it. The following is the generic command to alter the storage settings for a tablespace:

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

Only dictionary managed tablespaces can have their storage settings altered.


Resizing a Tablespace

To resize a tablespace, you can either add a new data file to that tablespace, or you can change the size of an existing data file (either automatically or manually).

Adding a data file can be accomplished with the following command:

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

Manually adjusting the size for a data file associated with a tablespace, you use the following ALTER DATABASE command:

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

This command can be used to either grow or shrink tablespace data files. However, if any objects have storage above the specified size, the data file can only be decreased in size to the last block of the last object in the data file.

To allow the data file to extend itself automatically, you can simply turn on AUTOEXTEND and set a maximum size for the data file (this allows it to grow without allowing it to run the file system or the entire drive out of space):

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

AUTOEXTEND either enables (on) or disables (off) the automatic extension of data files.

Optionally, you can set MAXSIZE to UNLIMITED if you are willing to keep an eye on the remaining space on the storage device on which the file exists to make sure that it does not completely run out of space to grow.

Moving Data Files with ALTER TABLESPACE

Often, instead of simply adjusting the size of a tablespace, you want to change the location for that tablespace's data files. This can be done to move a more active tablespace onto its own disk, or to spread I/O out across different disks.

To move a tablespace's data files, the tablespace must be offline, and the target data file or files must exist prior to the execution of the ALTER TABLESPACE command.

You can take the tablespace offline, copy the data files to the new location (then check to make sure that they are in the new location), execute the ALTER TABLESPACE command to use the new location, bring the tablespace back online, and then delete the original set of data files:

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

You cannot use this method to move the data files for the SYSTEM tablespace, the undo tablespace, and the temporary tablespace because the these tablespaces can never be taken offline.


Moving Data Files with ALTER DATABASE

An alternative method to move a tablespace's data files, and a method that can be used to move the data files associated with the SYSTEM tablespace, involves using the ALTER DATABASE command. Because the undo tablespaces and temporary tablespaces can also not be taken offline, this is the means that needs to be used to move the files associated with those tablespaces, as well.

To use this method, you need to shut down the database NORMAL or IMMEDIATE. After the database is shut down, you can use the operating system's commands to either copy or move the files associated with the tablespace involved. I usually copy the files; that way they are available if something weird happens in the process. After the files are in the new location, mount the database and issue the ALTER DATABASE RENAME FILE command as follows, and open the database after it is successful:

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

Because Oracle Enterprise Manager (OEM) is a powerful tool, it is possible to maintain tablespaces and their data files using this tool. The next section gives you information on how this can be accomplished.



    Oracle 9i Fundamentals I Exam Cram 2
    Oracle 9i Fundamentals I Exam Cram 2
    ISBN: 0789732653
    EAN: 2147483647
    Year: 2004
    Pages: 244
    Authors: April Wells

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net