Creating Tablespaces

The following is the generic form of the command with an explanation of the different components:

CREATE TABLESPACE tablespace_name
[DATAFILE clause]
[MINIMUM EXTENT integer [K]]
[BLOCKSIZE integer [K]]
[LOGGING | NOLOGGING]
[DEFAULT storage_clause]
[ONLINE | OFFLINE]
[PERMANENT | TEMPORARY]

• tablespace_name – Name of the tablespace to be created
• DATAFILE – to assign the location of data file associated with the tablespace. AUTOEXTEND is an optional clause to signify the auto extension of data files. REUSE is another optional clause to use an existing data file for the tablespace to be created
• MINIMUM EXTENT - ensures that every extent used in the tablespace is at least this size and is always a multiple of this size
• BLOCKSIZE – ensures that each block in the extents allocated is of this size
• LOGGING – All changes in this tablespace should be written to the online redo log files
• NOLOGING – Changes in this tablespace should not be written to the online redo log files
• ONLINE - creates the tablespace online and available for users to start using immediately
• OFFLINE - makes the tablespace unavailable immediately, and users are not able to use it for storage until the DBA has brought it online
• PERMANENT - specifies that the tablespace can be used to hold permanent objects
• TEMPORARY – signifies that the tablespace can be used only to hold temporary data and temporary objects

Advertisement