Create basic tablespace

Table space management type:
1. tablespace managed by data dictionary.
2. Local tablespace.
Understand the tablespace information: DESC dba_tablespaces;
Create a basic tablespace:
Create [temporary | undo] tablespace tablespace_name
[Datafile datafile_tempfile_spacification]
[Blocksize number K]
[Online | offline]
[Logging | nologging]
[Force logging]
[Default Storage]
[Compress | nocompress]
[Permanent | temporary]
[Extent management dictionary | Local [autoallocate | uniform size number k | M]
[Segment space managment auto | manual];
[Temporary | undo]: indicates the purpose of the created tablespace. The temporary tablespace stores data generated during sorting and other operations. The undo tablespace is used to store the "before" image of the modified data, so that the original data can be restored when the delete operation is revoked.
[Blocksize number K]: Indicates creating a non-standard data block tablespace.
[Logging | nologging]: specifies all default log options stored in the tablespace. When logging is used, the table space logging option is generated to record any operations on the Data Objects in the table space. When nologging is used, no log logging option is generated.
[Force logging]: This clause forces Oracle to generate the log record items of the tablespace without considering the logging or nologging settings. This log record records the creation or modification of objects in the database.
[Compress | nocompress]: The compress option indicates that the data in the data block will be compressed, and the result of compression is to remove duplicate values in the column. When retrieving data, Oracle automatically decompress the data. Nocompress indicates that no compression is performed.
[Default Storage]: used to set the default storage parameters for database objects stored in the tablespace. If you specify a storage parameter when creating a database object, this parameter is only valid in the tablespace managed by the data dictionary. This option is not valid in the tablespace managed by localization.
[Online | offline]: the online option indicates that the tablespace is available immediately. If the offline option is used, the created tablespace is unavailable.
[Permanent | temporary]: Permanent indicates the database object that will permanently Save the tablespace. The temporary option indicates temporary saving of the database object.
[Datafile datafile_tempfile_spacification]: The complete Syntax of this clause is as follows:
Datafile | tempfile file_name size k | M Reuse
[Autoextend off | on
[Next number k | M
Maxsize unlimited | number k | M];
Reuse: if the file already exists, the file is cleared and re-created. If this keyword is not used, an error occurs when the data file already exists.
Autoextend: Specifies whether the data file is automatically extended.
Next: if the specified data file is automatically extended, use this parameter to specify the size of each extension of the data file.
Maxsize: when the data file is automatically extended, this parameter is used to specify the minimum extended data file.
[Extent management dictionary | Local [autoallocate | uniform size number k | M]: This clause determines whether the created tablespace is a data dictionary or a local management method. For localized tablespace management, you can use the uniform and autoallocate keywords.
Uniform: indicates that all disk partitions in the tablespace are of the same size.
Autoallocate: indicates that the disk size is automatically allocated by Oracle. This option is the default value.
[Segment space managment auto | manual]: This clause indicates whether the middle section of the tablespace is managed automatically or manually. The default value is auto. It is automatically managed.

 

 

 

 

 

Example:

/* Step 2: create a temporary tablespace */
Create temporary tablespace cjq_temp
Tempfile 'e: \ app \ Administrator \ oradata \ orcl \ cjq_temp.dbf'
Size 50 m
Autoextend on
Next 50 m maxsize 20480 m
Extent management local;

/* Step 2: create a data table space */
Create tablespace cjq
Logging
Datafile 'e: \ app \ Administrator \ oradata \ orcl \ cjq. dbf'
Size 50 m
Autoextend on
Next 50 m maxsize 20480 m
Extent management local;

/* Step 2: create a user and specify the tablespace */
Create user cjq identified by cjq
Default tablespace cjq
Temporary tablespace cjq_temp;

 
/* Step 4: grant permissions to users */
Grant connect, resource, DBA to cjq;