Create a tablespace in Oracle

In Oracle, table space is the basic method for data management. All user objects must be stored in the table space, that is, the user has the right to use the space to create user objects. otherwise, you do not need to create objects because you want to create objects, such as tables and indexes, and there is no place to store them. Oracle will prompt that there is no storage quota.
Therefore, before creating an object, you must first allocate a bucket.
To allocate storage space, create a tablespace:
Example of creating a tablespace:
Create tablespace "sample"
Logging
Datafile 'd: \ oracle \ oradata \ ora92 \ luntan. ora size 5 m
Extent management local segment space management auto
The preceding statement consists of the following parts:
1. Create tablespace "sample" to create a tablespace named "sample.
To name a table space, follow the naming rules of oracle.
There are three types of tablespaces that can be created in Oracle:
(1) Temporary: Temporary tablespace for storing temporary data;
The syntax for creating a temporary tablespace is as follows:
Create temporary tablespace "sample "......
(2) undo: Restore the tablespace. used to store the redo log file.
The syntax for creating a restored tablespace is as follows:
Create undo tablespace "sample "......
(3) User tablespace: it is the most important and used to store user data table space.
You can directly write it as: Create tablespace "sample"
Temporary and undo tablespaces are special tablespaces managed by Oracle. They are only used to store system-related data.
Second: Logging
There are two options: nologging and logging,
Nologging: when creating a tablespace, no redo logs are created.
Logging is the opposite of nologging, that is, the redo log is generated when the tablespace is created.
When using nologging, the advantage is that logs are not generated during creation, so that the tablespace can be created quickly but cannot be logged. After data is lost, it cannot be recovered. However, when creating a tablespace, there is no data. Generally, after creating a tablespace and importing data, you need to back up the data. Therefore, you do not need to create a tablespace. Therefore, when creating a tablespace, select nologging to accelerate the creation of the tablespace.
Third, datafile is used to specify the location and size of the data file.
For example, datafile 'd: \ oracle \ oradata \ ora92 \ luntan. ora size 5 m
The storage location of the file is D: \ oracle \ oradata \ ora92 \ luntan. ora, and the file size is 5 MB.
If multiple files exist, separate them with commas:
Datafile 'd: \ oracle \ oradata \ ora92 \ luntan. ora size 5 m,
'D: \ oracle \ oradata \ ora92 \ dd. ora 'size 5 m
However, the size of each file must be specified. The unit is subject to the specified unit, for example, 5 m or 500 K.
Specific files can be stored on different media, such as disk arrays, based on different needs to reduce Io competition.
When the file name is specified, it must be an absolute address and cannot be a relative address.
Fourth: extent management Local Storage Area Management Method
Before Oracle 8i, there are two options: one is to manage the dictionary and the other is local, starting from 9i, only Local Management is supported. because local management has many advantages.
Dictionary management: each storage unit in the data file is used as a record. Therefore, when performing the DM operation, A large number of Delete and update operations will be performed on the management table. when doing a large amount of data management, it will produce a lot of DM operations, strictly affecting performance. At the same time, operations on table data for a long time will produce a lot of disk fragments, this is why disk sorting is required.
Local: You can manage disks in binary mode with high efficiency. at the same time, the system can automatically track and record the situation of near-idle space to avoid merging idle areas.
Fifth: Segment space management
Disk extension management method:
Segment space management: the time zone size of this option is automatically determined by the system. Since oracle can determine the optimal size of each region, the partition size is variable.
Uniform segment space management: Specifies the partition size. You can also use the default value (1 MB ).
Sixth: section space management:
Auto: it can only be used in locally managed tablespaces. When local is used to manage tablespaces, when the free space in the data block is increased or decreased, its new status will be reflected in the in-place diagram. Bitmap makes Oracle's management of free space more automated and provides better performance for management of free space. However, it cannot be automatically managed for tables with lob fields.
Manual: It is currently not used, mainly for backward compatibility.
7. Specify the block size. You can specify the size of the tablespace data block.
Example:
1 create tablespace "sample"
2 Logging
3 datafile 'd: \ oracle \ oradata \ ora92 \ sample. ora size 5 m,
4 'd: \ oracle \ oradata \ ora92 \ dd. ora size 5 m
5 extent management local
6 uniform segment space management
7 * auto
SQL>/
The tablespace has been created.
To delete the tablespace, you can
SQL> drop tablespace sample;
The tablespace is discarded.