ALTER TABLESPACE

Function

ALTER TABLESPACE modifies the attributes of a tablespace.

Precautions

• Only the tablespace owner or a user granted with the ALTER permission can run the ALTER TABLESPACE command. The system administrator has this permission by default. To modify a tablespace owner, you must be the tablespace owner or system administrator and a member of the new owner role.

• To change the owner, you must also be a direct or indirect member of the new owning role.

  NOTE: If new_owner is the same as old_owner, the current user will not be verified. A message indicating successful ALTER execution is displayed.

Syntax

• The syntax of renaming a tablespace is as follows:

  ALTER TABLESPACE tablespace_name 
      RENAME TO new_tablespace_name;
  

• The syntax of setting the owner of a tablespace is as follows:

  ALTER TABLESPACE tablespace_name 
      OWNER TO new_owner;
  

• The syntax of setting the attributes of a tablespace is as follows:

  ALTER TABLESPACE tablespace_name 
      SET ( {tablespace_option = value} [, ... ] );
  

• The syntax of resetting the attributes of a tablespace is as follows:

  ALTER TABLESPACE tablespace_name 
      RESET ( { tablespace_option } [, ...] );
  

• The syntax of setting the quota of a tablespace is as follows:

  ALTER TABLESPACE tablespace_name 
      RESIZE MAXSIZE { UNLIMITED | 'space_size'};
  

Parameter Description

• tablespace_name

  Specifies the tablespace to be modified.

  Value range: an existing table name

• new_tablespace_name

  Specifies the new name of a tablespace.

  The new name cannot start with PG_.

  Value range: a string. It must comply with the naming convention.

• new_owner

  Specifies the new owner of the tablespace.

  Value range: an existing username

• tablespace_option

  Sets or resets the parameters of a tablespace.

  Value range:

  • seq_page_cost: sets the optimizer to calculate the cost of obtaining disk pages in sequence. The default value is 1.0.

  • random_page_cost: sets the optimizer to calculate the cost of obtaining disk pages in a non-sequential manner. The default value is 4.0.

    NOTE:

    • The value of random_page_cost is relative to that of seq_page_cost. It is meaningless when the value is equal to or less than the value of seq_page_cost.
    • The prerequisite for the default value 4.0 is that the optimizer uses indexes to scan table data and the hit ratio of table data in the cache is about 90%.
    • If the size of the table data space is smaller than that of the physical memory, decrease the value to a proper level. On the contrary, if the hit ratio of table data in the cache is lower than 90%, increase the value.
    • If random-access memory like SSD is adopted, the value can be decreased to a certain degree to reflect the cost of true random scan.

  Value range: a positive floating point number

• RESIZE MAXSIZE

  Resets the maximum size of tablespace.

  Value range:

  • UNLIMITED: No limit is set for the tablespace.

  • Determined by space_size. For details about the format, see CREATE TABLESPACE.

    NOTE:

    • If the adjusted quota is smaller than the current tablespace usage, the adjustment is successful. You need to decrease the tablespace usage to a value less than the new quota before writing data to the tablespace.
    • You can also use the following statement to change the value of MAXSIZE:

    ALTER TABLESPACE tablespace_name RESIZE MAXSIZE
    { 'UNLIMITED' | 'space_size'};
    

Examples

See Examples in CREATE TABLESPACE.

Helpful Links

CREATE TABLESPACE and DROP TABLESPACE