Q
Problem solve Get help with specific problems with your technologies, process and projects.

Adding capacity to existing tablespace

Many of our company's primary projects on an Oracle Server are all pointing to the USR table which is at 99.96% capacity. How difficult is it to create a new table space, and then repoint the table from the USR tablespace to the new tablespace? Is there a way to list all of the databases that reside on a system?


It sounds like what you are wanting to do is just add additional capacity to the tablespace in which the USR table resides. One way to add more space is to issue a command similar to the following, which will add another datafile to the tablespace in which your table resides:

ALTER TABLESPACE your_ts_name
ADD DATAFILE '/directory_path/file_name.dbf'
SIZE 200M
AUTOEXTEND ON
MAXSIZE 500M;
Note that this example also shows turning on AUTOEXTEND, which will enable autoextend capabilities for the tablespace.

If you just want to increase the size of an existing datafile, you could use:

ALTER DATABASE
DATAFILE '/directory/path/file_name.dbf'
MAXSIZE 500M ;
If you really want to move the current tablespace elsewhere, there are several methods for doing this with either an ALTER DATABASE command or an ALTER TABLESPACE command.

To move a single table is a bit different. You can "move" a table using a CREATE TABLE AS or INSERT INTO...SELECT or via the SQL*Plus COPY command or via EXPORT/IMPORT. Pick your method and execute it, then delete/drop the table from it's original source is one way of doing it.

Bottom-line, you need to make sure that what you really want to do is "move" the table. If you can add additional datafiles to eliminate your space problem, that's the best choice.

For More Information

  • What do you think about this answer? E-mail the editors at editor@searchDatabase.com with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close