Create Oracle tablespace

Tablespace is an important concept in Oracle databases. The following describes the syntax for creating tablespaces in Oracle. We hope that you can have a better understanding of creating tablespaces in Oracle.

Syntax description for creating a tablespace in Oracle:

Create [undo] tablespace tablespace_name

[Datafile datefile_spec1 [, datefile_spec2] ......

[{Mininum extent integer [k | M]

| Blocksize integer [k]

| Logging clause

| Force Logging

| Default {data_segment_compression} storage_clause

| [Online | offline]

| [Permanent | temporary]

| Extent_manager_clause

| Segment_manager_clause}]

1. Undo

The system creates a tablespace for rollback.

In 9i, the database administrator can manage rollback segments without having to create undo tablespaces. The system automatically manages rollback segment allocation and recovery. Of course, you can also create a general tablespace and create a rollback segment on it. however, for users, system management is much better than self-management. if you need to manage it by yourself, see the detailed command for rollback segment management.

When the system does not specify a tablespace for rollback, the system uses the system rollback segment for transaction management.

2. tablespace

Name of the tablespace.

3. datafile datefile_spec1

Specifies the space file contained in the tablespace. Datefile_spec1 is like ['filename'] [size integer [k | M] [reuse] [autoextend_clause]

[Autoextend_clause] is an example: autoextend {off | on [next integer [k | M] [maxsize_clause]}

Filename indicates the full path name of the data file, size indicates the file size, and reuse indicates whether the file is reused.

Autoextend indicates whether automatic expansion. Off | on indicates whether automatic expansion is disabled. Next indicates the size of the expansion after the data file is full.

Maxsize_clause indicates the maximum size of the data file. For example, maxsize {unlimited | integer [k | M]}. Unlimited indicates an infinite tablespace. Integer indicates the maximum size of the data file.

Datafile 'd:/Oracle/oradata/imagedata01.dbf' size 2000 m,

'D:/Oracle/oradata/imagedata02.dbf 'size 2000 m

4. mininum extent integer [k | M]

Specifies the minimum value of the range in the tablespace. This parameter can reduce space fragments and ensure that the range in the tablespace is an integer multiple of this value.

5. blocksize integer [k]

This parameter can be used to set the size of an unstandard block. If you want to set this parameter, you must set db_block_size, at least one db_nk_block_size, and the declared integer value must be equal to db_nk_block_size.

Note: This parameter cannot be set in the temporary tablespace.

6. Logging clause

This clause declares the log attributes of all user objects in the tablespace (logging by default), including tables, indexes, partitions, materialized views, and indexes and partitions in materialized views.

7. Force Logging

Use this clause to indicate that the tablespace enters the forced log mode. At this time, the system records all changes to objects in the table space, except for changes to temporary segments. This parameter is higher than the object's nologging option.

Note: The database cannot be opened and is in read/write mode. This option cannot be used in temporary tablespace and rollback tablespace.

8. Default storage_clause

Declare the default storage clause.

9. Online | offline

Changes the status of a tablespace. Online: The tablespace is valid immediately after being created. This is the default value. Offline: Invalid after being created. This value can be obtained from dba_tablespace.

10. Permanent | temporary

Specifies whether a tablespace is a permanent tablespace or a temporary tablespace. Permanent tablespace stores permanent objects, while temporary tablespace stores temporary objects existing in the session lifecycle. The temporary tablespace generated by this parameter is always in dictionary management after it is created, and the extent management local option cannot be used. To create a local tablespace, you must use the create temporary tablespace

Note: block size cannot be declared after this parameter is declared.

11. extent_management_clause

This is the most important clause that describes how to manage the range of a tablespace. Once you declare this clause, you can only change these parameters by porting them.

If you want to manage the tablespace locally, declare the local option. Local tablespace management is implemented through bitmap management. Autoallocate indicates that the tablespace is automatically allocated with a range. You cannot specify the range. Only Versions later than 9.0 have this function. Uniform indicates the fixed size of the tablespace range. The default value is 1 MB.

You cannot set the system tablespace of a locally managed database to dictionary management.

Oracle recommends using local tablespace management.

If extent_management_clause is not set, Oracle sets a default value for it. If the initialization parameter compatible is smaller than 9.0.0, the system creates a dictionary management tablespace. If the value is greater than 9.0.0, set the value as follows:

If default storage_clause is not specified, Oracle creates an automatically assigned local tablespace.

Otherwise, if the mininum extent is specified, Oracle determines whether the mininum extent, initial, and next are equal, and whether the pctincrease is = 0. if the preceding conditions are met, Oracle creates a local tablespace for management, and the extent size is initial. if the preceding conditions are not met, Oracle creates an automatically assigned local tablespace.

If the mininum extent is not specified. Initial, then oracle determines whether next is equal, and whether pctincrease is = 0. If you want Oracle to create a local tablespace and specify the uniform. Otherwise, Oracle creates an automatically allocated local tablespace.

Note: Local tablespace management can only store permanent objects. If you declare local, you cannot declare default storage_clause, mininum extent, temporary.

Extent management local

12. segment_management_clause

Segment space management auto

Oracle segment space management:

 

 

 

 

 

 

 

 

 

 

Supplement: Oracle tablespace-large file tablespace and small file tablespace

Database -- instance -- tablespace -- segment -- data expansion -- data block data file is the concept of physical structure

Large file tablespace

In Oracle, users can create big file tablespaces (bigfile tablespace ). In this way, the tablespace used by the Oracle database can be composed of a single large file, rather than several small data files. This allows Oracle to take advantage of the 64-bit system to create,ManagementLarge files. In a 64-bit system, the storage capacity of the Oracle database is extended to 8 EB (1eb = 1024pb, 1pb = 1024 TB, 1 TB = 1024 GB ).

When database files are managed by Oracle (Oracle-managed files) and bigfile tablespace is used, the data files are completely transparent to users. In other words, you only need to perform management operations on the table space, instead of the underlying data file ). Use a large file tablespace to manage the tablespace as a disk space,BackupAnd recovery. Using large file tablespaces, combined with Oracle-managed files technology and automatic storage management technology, the Administrator no longer needs to manually create new data files and maintain a large number of database files, which simplifies the management of database files. By default, the database creates a small file table space (smallfile tablespace), that is, the traditional table space type in Oracle. In the database, system and sysaux tablespaces are always created using traditional types.

Bigfiletablespace can only be used for the tablespace (tablespace) of locally managed (locally managed) and automatically managed (automatic segment space ). However, there are two exceptions: Undo tablespace and temporary tablespace managed locally, even if the segment is manually managed ), you can also use large file tablespaces.

An Oracle database can contain big files/small file tablespaces (bigfile/smallfiletablespace) at the same time ).SQLWhen executing a statement, you do not need to consider the table space type unless the datafile name is explicitly referenced in the statement. The administrator can create a set of temporary tablespaces (temporary tablespace). When necessary, you can use the temporary spaces provided by each tablespace (tablespace) in the group. The administrator can also specify a tablespace group as the default temporary tablespace of the database. When you need a large amount of temporary space for sorting, you can use large file tablespaces and tablespace groups.

Advantages of using large file tablespaces

● Using bigfile tablespace can significantly enhance the storage capability of Oracle databases. A small file table space (smallfile tablespace) can contain up to 1024 data files, while a large file table space contains only one file.
The maximum size of data files is 1024 times that of small data files. In this case, the maximum capacity of the large and small file tablespaces is the same. However, since each database can use a maximum of 64 K data files, the maximum number of tablespaces in the database when large file tablespaces are used is 1024 times that of small file tablespaces, the total database capacity when using large file tablespaces is three orders of magnitude higher than when using small file tablespaces. In other words, when an Oracle database uses a large file tablespace and uses the maximum data block capacity (32 K), its total capacity can reach 8 EB.
● Using large file tablespaces in super-large databases reduces the number of data files and simplifies the management of data files. Due to the decrease in data files, the information about the data files in SGA and the capacity of the control file are also reduced.
● Data files are transparent to users, which simplifies database management.

Factors to consider when using large file tablespaces

● Bigfile tablespace should be used with automatic storage management or other logical volume management tools (logical volume manager, these tools should support dynamic expansion of logical volumes, as well as striping (data cross-disk distribution) or raid.
● Avoid using large file tablespaces on systems that do not support striping, because this will not facilitate parallel execution (parallel execution) and parallel backup (backupparallation) of RMAN ).
● When the disk group in use in the tablespace may not have enough space, and the only way to expand the tablespace is to add data files to another disk group, avoid using large file tablespaces.
● It is not recommended to use large file tablespaces on platforms that do not support large files, which limits the capacity of tablespaces. Refer to the relevant operating system documentation to learn about the maximum supported file capacity.
● If large file tablespaces are used to replace traditional tablespaces, the performance of the database open, checkpoints, and dbwr processes will be improved. However, increasing the data file capacity may increase the backup and recovery time.

 

Tip:
No data expansion is specified when you create a table space.
(Extent). By default, local management is used ).

Auto: Auto is the recommended option for Oracle. The management of parameters such as pctfree and pctused is reduced, making table space management more efficient. The partition size is automatically determined by the system. Because oracle can determine the optimal size of each partition, the size of each partition changes. If you need to specify a fixed size, you need to specify the uniform parameter: 22: 55: 44sys @ orcl> Create tablespace ttbs001 datafile '/Oracle/oradata/Lyon/ttbs001.dbf 'size 5 m uniform. size 1 M Segment space management auto; tablespace created.
When the tablespace is created according to the default conditions, the segment space management is already Auto: 23: 07: 01sys @ orcl> Create tablespace ttbs001 datafile '/Oracle/oradata/Lyon/comment 'size 5 m; tablespace created.23: 07: 24sys @ orcl> select tablespace_name, comment from dba_tablespaces; tablespace_name comment
Certificate ------------------------------------------------------------------------------------------------------------
System Manual
Undotbs1 Manual
Sysaux auto
Temp Manual
Users auto
Example auto
Indx auto
Lyontbs auto
Perfstat auto
Ttbs001 auto