Altering and Maintaining Tablespaces

1.Increasing the Size of a Tablespace

You can increase the size of a tablespace by either increasing the size of a datafile in the tablespace or adding one. See "Creating Datafiles and Adding Datafiles to a Tablespace" for more information.

Additionally, you can enable automatic file extension (AUTOEXTEND) to datafiles and bigfile tablespaces. See "Enabling and Disabling Automatic Extension for a Datafile".

2.Altering a Locally Managed Tablespace

You cannot alter a locally managed tablespace to a locally managed temporary tablespace, nor can you change its method of segment space management. Coalescing free extents is unnecessary for locally managed tablespaces. However, you can use the ALTER TABLESPACE statement on locally managed tablespaces for some operations, including the following:

• 1.Adding a datafile. For example:

  ALTER TABLESPACE lmtbsb
     ADD DATAFILE '/u02/oracle/data/lmtbsb02.dbf' SIZE 1M;
  

• Altering tablespace availability (ONLINE/OFFLINE). See "Altering Tablespace Availability".

• Making a tablespace read-only or read/write. See "Using Read-Only Tablespaces".

• Renaming a datafile, or enabling or disabling the autoextension of the size of a datafile in the tablespace. See Chapter 15, "Managing Datafiles and Tempfiles".

3.Altering a Bigfile Tablespace

Two clauses of the ALTER TABLESPACE statement support datafile transparency when you are using bigfile tablespaces:

• 1.RESIZE: The RESIZE clause lets you resize the single datafile in a bigfile tablespace to an absolute size, without referring to the datafile. For example:

  ALTER TABLESPACE bigtbs RESIZE 80G;
  

• 2.AUTOEXTEND (used outside of the ADD DATAFILE clause):

  With a bigfile tablespace, you can use the AUTOEXTEND clause outside of the ADD DATAFILE clause. For example:

  ALTER TABLESPACE bigtbs AUTOEXTEND ON NEXT 20G;
  

An error is raised if you specify an ADD DATAFILE clause for a bigfile tablespace.

4.Altering a Locally Managed Temporary Tablespace

Note:

You cannot use the ALTER TABLESPACE statement, with the TEMPORARY keyword, to change a locally managed permanent tablespace into a locally managed temporary tablespace. You must use the CREATE TEMPORARY TABLESPACE statement to create a locally managed temporary tablespace.

You can use ALTER TABLESPACE to add a tempfile, take a tempfile offline, or bring a tempfile online, as illustrated in the following examples:

ALTER TABLESPACE lmtemp
   ADD TEMPFILE '/u02/oracle/data/lmtemp02.dbf' SIZE 18M REUSE;

ALTER TABLESPACE lmtemp TEMPFILE OFFLINE;
ALTER TABLESPACE lmtemp TEMPFILE ONLINE;

Note:

You cannot take a temporary tablespace offline. Instead, you take its tempfile offline. The view V$TEMPFILE displays online status for a tempfile.

The ALTER DATABASE statement can be used to alter tempfiles.

The following statements take offline and bring online tempfiles. They behave identically to the last two ALTER TABLESPACE statements in the previous example.

ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' OFFLINE;
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' ONLINE;

The following statement resizes a tempfile:

ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' RESIZE 18M;

The following statement drops a tempfile and deletes its operating system file:

ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' DROP
    INCLUDING DATAFILES;

The tablespace to which this tempfile belonged remains. A message is written to the alert log for the tempfile that was deleted. If an operating system error prevents the deletion of the file, the statement still succeeds, but a message describing the error is written to the alert log.

It is also possible to use the ALTER DATABASE statement to enable or disable the automatic extension of an existing tempfile, and to rename a tempfile. See Oracle Database SQL Language Reference for the required syntax.

Note:

To rename a tempfile, you take the tempfile offline, use operating system commands to rename or relocate the tempfile, and then use the ALTER DATABASE RENAME FILE command to update the database controlfiles.

5.Shrinking a Locally Managed Temporary Tablespace

You use the SHRINK SPACE clause of the ALTER TABLESPACE statement to shrink a temporary tablespace, or the SHRINK TEMPFILE clause of the ALTER TABLESPACE statement to shrink a specific tempfile of a temporary tablespace. Shrinking frees as much space as possible while maintaining the other attributes of the tablespace or tempfile. The optional KEEP clause defines a minimum size for the tablespace or tempfile.

Shrinking is an online operation, which means that user sessions can continue to allocate sort extents if needed, and already-running queries are not affected.

The following example shrinks the locally managed temporary tablespace lmtmp1 to a size of 20M.

ALTER TABLESPACE lmtemp1 SHRINK SPACE KEEP 20M;

The following example shrinks the tempfile lmtemp02.dbf of the locally managed temporary tablespace lmtmp2. Because the KEEP clause is omitted, the database attempts to shrink the tempfile to the minimum possible size.

ALTER TABLESPACE lmtemp2 SHRINK TEMPFILE '/u02/oracle/data/lmtemp02.dbf';   
	

6.Dropping Tablespaces

You cannot drop a tablespace that contains any active segments. For example, if a table in the tablespace is currently being used or the tablespace contains undo data needed to roll back uncommitted transactions, you cannot drop the tablespace. The tablespace can be online or offline, but it is best to take the tablespace offline before dropping it. To drop a tablespace, use the DROP TABLESPACE statement. The following statement drops the users tablespace, including the segments in the tablespace: DROP TABLESPACE users INCLUDING CONTENTS; If the tablespace is empty (does not contain any tables, views, or other structures), you do not need to specify the INCLUDING CONTENTS clause. Use the CASCADE CONSTRAINTS clause to drop all referential integrity constraints from tables outside the tablespace that refer to primary and unique keys of tables inside the tablespace. To delete the datafiles associated with a tablespace at the same time that the tablespace is dropped, use the INCLUDING CONTENTS AND DATAFILES clause. The following statement drops the users tablespace and its associated datafiles: DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES; A message is written to the alert log for each datafile that is deleted. If an operating system error prevents the deletion of a file, the DROP TABLESPACE statement still succeeds, but a message describing the error is written to the alert log. more details: http://docs.oracle.com/cd/E11882_01/server.112/e25494/tspaces.htm#ADMIN12490

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26844646/viewspace-1132681/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26844646/viewspace-1132681/