Oracle development: create and manage tablespaces and data files 1

Oracle logically stores data in tablespaces, but actually stores data in data files.
Table space and data files
Table space: A table space belongs to only one database at a time. It is composed of one or more data files. Logical storage units can be divided in the near future.
Data File: Only one tablespace and one Database can be created.
Tablespace type
System tablespace:
-Create with database
-Include data dictionary
-Contains System Restoration segments.

Non-system tablespace:
-Separated storage segments
-Easy space management
-Control the amount of space allocated to the user

Create a tablespace
Create tablespace
Create tablespace
[Datafile clause]
[Minimum extent integer [k | M]
[Blocksize integer [k]
[Logging | nologging]
[Default storage_clause]
[Online | offline]
[Permanent | temporary]
[Extent_management_clause]
[Segment_management_clause]

Where:
Tablespace: the name of the tablespace to be created.
Datafile: one or more data files that constitute the tablespace
Minimum extent: Make sure that the size of each occupied area in the tablespace is a multiple of integer. Use K or m to specify the size in kilobytes or megabytes.
Blocksize: blocksize specifies the non-standard block size of the tablespace. To specify this clause, it must have db_cache_size and set at least one db_nk_cache_size parameter. The specified integer in this clause must correspond to the setting of a db_nk_cache_size parameter.
Logging: by default, all changes to all tables, indexes, and partitions in the tablespace are written into the redo log file. Logging is the default setting.
Nologging: by default, all changes to all tables, indexes, and partitions in the tablespace are not written into the redo log file.
Nologging only affects some DML and DDL commands, such as direct loading.
Default: Default specifies the default storage parameters for all objects created in the tablespace.
Offline: the specified tablespace is not available after it is created.
Permanent: Specifies the tablespace that can be used to retain permanent objects.
Temporary: the specified tablespace is only used to retain temporary objects, for example, the segments used for implicit sorting caused by the order by clause. The extent management local blocksize clause cannot be specified.

Extent_management_clause: This clause specifies how to manage partitions in a tablespace.

Segment_management_clause: This is only related to permanent and locally managed tablespaces. It can be used to specify whether Oracle should use the idle list or bitmap to track occupied space and free space in the tablespace segment.

Datafile_clause: = filename [size integer [k | M] [reuse] [autoextend_clause]

Filename: the name of the data file in the tablespace.

Size: Specifies the file size. Use K or m to specify the size in kilobytes or megabytes.

Reuse: allows the Oracle server to reuse existing files.

Autoextend_clause: This clause enables or disables automatic expansion of data files.

Next: Specify the next increment size of the disk space automatically allocated when more partitions are required in bytes.

Where:

Maxsize: specifies the maximum disk space that data files can automatically expand.

Unlimited: the disk space that can be allocated to data files or tempfiles is unrestricted.

Verify the preceding parameters. The example is as follows::
SQL> Create tablespace userdata datafile '/disk4/ora10/oradata/ora1/userdata01.dbf' size 20 m
2 autoextend on next 500 k maxsize 100 m
3 Logging online
4 extent management local uniform size 128 K;

Tablespace created.

Tablespace space management
The tablespace is allocated in the unit of partition (extent. You can use the following two methods to track the free space and occupied space in the created tablespace:
Local tablespace: Use the bitmap management area in the tablespace. Each bit in the bitmap corresponds to a block or a group of blocks. When a zone is allocated or released for reuse, the Oracle server changes the bitmap value to display the new status of the block. From Oracle9i, local management has become the default setting.
Table space managed by the dictionary: it is managed by the data dictionary. The Oracle server will update the corresponding table in the data dictionary when allocating or recycling the partition.
Local table space management: reduces competition for data dictionary tables. No restored data is generated when space is allocated or recycled.

Example:
Create tablespace userdata
Datafile '/u01/oradata/userdata01.dbf' size 500 m
Extent management local uniform size 128 K;
The local option of the extent management clause specifies that the tablespace is managed locally. By default, the tablespace is managed locally.
Extent_management_clause:
[Extent management [dictionary | Local
[Autoallocate | uniform [size integer [k | M]
Where:
Dictionary: Specifies the use of dictionary tables to manage tablespaces.
Local: specifies that the tablespace is managed locally through a bitmap. If local is specified, default cannot be specified.
Storage_clause, minimum extent, or temporary.
Autoallocate: Specifies the tablespace to be managed by the system. You cannot specify the partition size. This is the default setting.
Uniform: specifies that the tablespace is managed in each area of the size in bytes. Use K or m to specify the partition size in kilobytes or megabytes. The default size is 1 MB.

Advantages of local tablespace management:
1. The local tablespace has the following advantages over the dictionary-managed tablespace:
2. Local Management can avoid cyclic space management operations, but such operations may occur in the tablespace of dictionary management. Once the space in a zone is consumed or released
3. When another consumption or release operation is generated (consuming or releasing the space in the restoration segment or data dictionary table), it will happen.
4. Because the local tablespace does not record idle space in the data dictionary table, the competition for these tables is reduced.
5. The local management of the zone can automatically track the neighboring idle space, so there is no need to merge the idle areas.
6. The local management zone size can be automatically determined by the system.
7. Changes to the bitmap of a partition do not generate restoration information because they do not update the tables in the data dictionary (except in special cases such as the tablespace quota information ).