| You create tablespaces with the CREATE TABLESPACE command. 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 is the name of the tablespace that you want to create. MINIMUM EXTENT allows you to ensure that every extent used in the tablespace is at least this size and is always a multiple of this size. You can specify this in either K for kilobytes or M for megabytes. LOGGING, which is the default if not specified, tells Oracle that all tables, indexes, and partitions in this tablespace should have all changes written to the online redo log files. NOLOGGING specifies that all tables, partitions, and indexes in this tablespace should not have all their changes written to the online redo log files. NOLOGGING affects only certain DML and DDL commandsfor example, direct loads. This parameter can be overridden after the tablespace is created. DEFAULT provides the default storage parameters for all the objects created in the tablespace. These defaults can be overridden. ONLINE, the default, 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, again the default, specifies that the tablespace can be used to hold permanent objects (tables, indexes, clusters). TEMPORARY signifies that the tablespace can be used only to hold temporary data and temporary objects. TEMPORARY tablespaces hold things such as the segments used by implicit sorts (ORDER BY, GROUP BY). DATAFILE_clause is the fully qualified filename including the data filename, the location of the data file, the size that you want to have assigned to the data file, and the optional REUSE and AUTOEXTEND clauses to determine whether you want to reuse (REUSE clause) an existing data file or you want the data file to have the capability to automatically extend its storage (AUTOEXTEND) in order to not fill up.
We have now created tablespaces, but what if we want to get rid of a tablespace? What do we do then? The following section covers dropping tablespaces. |
