What Does This Oracle SQL Statement Do?

Question

I am migrating a MSSQL script to Oracle SQL and I can't figure out what one line in the script is doing.

I am very new to SQL.

CREATE TABLE HA_BACKUP_PROCESSES 
(
    ID numeric (10, 0) NOT NULL , 
    PROCESS_ID numeric (10, 0) NOT NULL , 
    BACKUP_PROCESS_ID numeric (10, 0) NOT NULL , 
    CONSTRAINT HA_BCK_PROC_PK PRIMARY KEY (ID) 
        USING INDEX TABLESPACE userdata001 
)

In the above code, what is the 'USING INDEX TABLESPACE userdata001' statement doing?

Answer 1

This clause allows selection of the tablespace in which the index associated with a UNIQUE or PRIMARY KEY constraint will be created. If not specified, default_tablespace is used, or the database's default tablespace if default_tablespace is an empty string

Answer 2

Tablespaces are nothing more than logical containers for datafiles and indexes.

When setting up an Oracle instance, you must define your tablespaces before you can create datafiles. Then, when you create a table or index you must specify the tablespace you want to create the datafile in, or accept the default tablespace.

Answer 3

It instructs the database to store index information in the tablespace named "userdata001".

A helpful explanation can be found here:

A tablespace is a logical storage unit within an Oracle database. It is logical because a tablespace is not visible in the file system of the machine on which the database resides. A tablespace, in turn, consists of at least one datafile which, in turn, are physically located in the file system of the server. Btw, a datafile belongs to exactly one tablespace. Each table, index and so on that is stored in an Oracle database belongs to a tablespace. The tablespace builds the bridge between the Oracle database and the filesystem in which the table's or index' data is stored.