Assume that the database is Oracle11g. Log On with the SYS user under SQL * PLUS. This article demonstrates creating a (permanent) tablespace. After that, you can view the tablespace information and then modify the tablespace.
Assume that the database is Oracle 11 GB. log on to the database with the SYS user under SQL * PLUS. This article demonstrates creating a (permanent) tablespace. After that, you can view the tablespace information and then modify the tablespace.
Content
Create a tablespace
View tablespaces
Modify Table space availability/read/write
Delete a tablespace
Assume that the database is Oracle 11 GB. log on to the database with the SYS user under SQL * PLUS. This article demonstrates creating a (permanent) tablespace. After that, you can view the tablespace information, modify the tablespace configuration, and delete the tablespace.
Create a tablespace
Oracle manages tablespaces by zone and segment space.
Zone management methods-different zones are allocated in two ways: dictionary management (dictionary-managed tablespace, DMT) and local management (local-managed tablespace, LMT ). We strongly recommend that you use LMT for Oracle 10 Gb. From Oracle 9i, LMT is used by default when creating a tablespace.
The allocation method of the LMT area is as follows:
UNIFORM (UNIFORM)-UNIFORM allocation. The size of all partitions in the specified tablespace is the same. The default value is 1 MB.
Automatic (AUTOALLOCATE or SYSTEM)-automatic allocation. Specifies the size of the zone automatically managed by the Oracle system. This is the default setting.
Segment management method-LMT method. In addition to specifying the partition allocation method, you can also specify the segment management method. The section space management mode is mainly used by Oracle to manage used data blocks and idle data blocks in the Section. There are two types:
MANUAL-Oracle uses the used data blocks and idle data blocks of the free list Management segment. This is a traditional section space management method to be compatible with previous versions.
AUTO-Oracle uses bitmap to manage used data blocks and idle data blocks of segments. Determine whether the data block in the segment is available through the value of the unit in the bitmap.
The dictionary management method does not contain segment management.
The following example shows how to create a tablespace using local management.
Example 1: Use AUTOALLOCATE to create the tablespace mytbs01. The data file is D: \ oracledata \ mytbs01_1.dbf. The size is 2 MB.
CreateTablespace mytbs01datafile'D: \ oracledata \ mytbs01_1.dbf' Size2 Mautoallocate;
Example 2: Create the tablespace mytbs02 using the UNIFORM partition allocation method. The data files are D: \ oracledata \ mytbs02_1.dbf and D: \ oracledata \ mytbs02_2.dbf. There are two files ,, the size is 1 M and 2 M respectively.
2 MuniformSize128 k;
Example 3: Create Table space mytbs03 and specify the data file expansion mode to automatically increase. Each time the table space is 1 MB, the maximum value is 11 Mb.
2 M maxsize 11 M;
In this case, you cannot specify UNIFORM.
Example 4: Use AUTO segment management. Create the tablespace mytbs04, which adopts the UNIFORM area allocation management mode. The size is the default value. Use the segment space management mode to specify the segment management mode.
CreateTablespace mytbs04datafile'D: \ oracledata \ mytbs04_1.dbf' Size3 M reuseuniformsegmentSpaceManagement auto;