Relocate or Restructure Database Table

The three main reasons to relocate or restructure a table are:

1. Reading or writing to the table conflicts with other database activities, and moving it to a different physical disk would alleviate the problem.
2. A table has outgrown its current location and would acquire more free space if it were moved to a different location.
3. Moving the table would eliminate gaps of wasted space or chained rows, which cause slow response times for queries and other database operations involving the table.

Prior to Oracle, the only method of relocating or restructuring a table was to create a copy of the table, then drop the table, recreate the table structure (including changing the tablespace name if you wanted to relocate the table), and then copying all the data into the table. This series of steps was complex and time-consuming. It also required you to create enough free space in the database to hold two copies of the data while you shuffled things around. In addition, all the issues involved in dropping a table came into play (such as lost grants, indexes, and other related objects).
Oracle solves these problems by allowing you to relocate or restructure a table without dropping it.
The diagram below shows you the new syntax of the ALTER TABLE command with the new MOVE keyword.

Scenario: I want to use the

alter table move tablespace

command to reorganize tables to remove fragmentation.
Question: Does the "alter table move tablespace" command preserve my 1) index and 2) constraint definitions?

alter table sales move tablespace sales_font;

The alter table move tablespace syntax preserves the index and constraint definitions: The table move will change the ROWIDs of the table rows, and as such the indexes, which are based on ROWIDs, will become invalid. Therefore, the need to execute step two immediately after step one, which is to rebuild the invalid indexes on the current table. At the same time an advantage of using the table move procedure is

1. all constraints are preserved and
2. index definitions are also saved,

so that re-indexing is possible using the fast index REBUILD method, rather than the slower index DROP and CREATE method.

The ALTER TABLE… MOVE command allows you to relocate or reorganize a table much more simply.

A file is in stream record format when the records are not specified by size; instead SQL*Loader forms records by scanning for the record terminator. Stream record format is the most flexible format, but there can be a negative effect on performance. The specification of a datafile to be interpreted as being in stream record format looks similar to the following:

INFILE datafile_name ["str terminator_string"]

The terminator_string is specified as either 'char_string' or X'hex_string' where:

1. 'char_string' is a string of characters enclosed in single or double quotation marks
2. X'hex_string' is a byte string in hexadecimal format

When the terminator_string contains special (nonprintable) characters, it should be specified as a X'hex_string'. However, some nonprintable characters can be specified as ('char_string') by using a backslash. For example:

1. \n indicates a line feed
2. \t indicates a horizontal tab
3. \f indicates a form feed
4. \v indicates a vertical tab
5. \r indicates a carriage return

If the character set specified with the NLS_LANG parameter for your session is different from the character set of the datafile, character strings are converted to the character set of the datafile. This is done before SQL*Loader checks for the default record terminator.

Hexadecimal strings are assumed to be in the character set of the datafile, so no conversion is performed. On UNIX-based platforms, if no terminator_string is specified, SQL*Loader defaults to the line feed character, \n. On Windows-based platforms, if no terminator_string is specified, then SQL*Loader uses either \n or \r\n as the record terminator, depending on which one it finds first in the data file. This means that if you know that one or more records in your data file has \n embedded in a field, but you want \r\n to be used as the record terminator, then you must specify it. Example 5-5 illustrates loading data in stream record format where the terminator string is specified using a character string, '|\n'. The use of the backslash character allows the character string to specify the nonprintable line feed character.
Example 5-5: Loading Data in Stream Record Format

load data
infile 'example.dat' "str '|\n'"
into table example
fields terminated by ',' optionally enclosed by '"'
(col1 char(5),
col2 char(7))
example.dat:
hello,world,|
james,bond,|

Relocate Table - Exercise