Sample Exam Questions

From the objective of OSS-DB Exam Silver
S3.1 SQL commands (Tablespace)

Sample Question


Choose one incorrect use of the tablespace.

  1. A directory to be used as a tablespace is specified in the CREATE TABLESPACE command, but the directory must already exist and be empty.
  2. If a table is created by executing 'CREATE TABLE (... table_definition ...) TABLESPACE tablespace_name;', then the specified tablespace is used.
  3. If you specify a tablespace name with the default_tablespace parameter, objects created such as by CREATE TABLE use the specified tablespace.
  4. To create objects in a tablespace, you must have CREATE privilege on the tablespace.
  5. When a tablespace is dropped with the DROP TABLESPACE command, the objects in it are also automatically dropped.

※This sample exam is different from those that appear in the actual OSS-DB Exam.

Answer and Explanation

Tablespaces are physical places for tables, indexes, and so on. By default, all objects are placed under the directories that make up the database cluster, but you can create tablespaces to place objects elsewhere.
For example, you might want to use tablespaces when you run out of disk space and want to add another partition to store your data, or when you want to place frequently used tables or indexes in faster storage.
In PostgreSQL, you can specify a directory as a tablespace. For example, if the tablespace to be created is named my_tablespace and the directory to be used is /path/to/my_tablespace, then you would create it with the command CREATE TABLESPACE my_tablespace LOCATION ‘/path/to/my_tablespace’. However, CREATE TABLESPACE does not execute mkdir internally, so you must create this directory beforehand, and it must be empty. On Linux, you would execute the following commands as a root user:

mkdir /path/to/my_tablespace
chown postgres /path/to/my_tablespace
chmod 700 /path/to/my_tablespace

When executing CREATE TABLE, the default tablespace is used unless otherwise specified. However, if you execute it as CREATE TABLE my_table(...) TABLESPACE my_tablespace, my_tablespace will be used.
There is also a parameter called default_tablespace, so if you do:

SET default_tablespace = my_tablespace;
CREATE TABLE my_table(...);

my_tablespace will be used.

Superuser privileges are required to create a tablespace, but just creating it does not allow regular users to create objects in the tablespace. To create an object, you need to grant CREATE privileges on the tablespace, as in:

GRANT CREATE ON TABLESPACE my_tablespace TO somebody;

To drop a tablespace, use the DROP TABLESPACE command, but the tablespace must be empty to execute. If any objects are present, they must first be deleted or moved to another tablespace.

This is a question where you need to select the incorrect option, so the correct answer is E.