Oracle 11g Release 1 (11.1) tablespace-simple management of permanent tablespace

Oracle 11g Release 1 (11.1) tablespace -- simple management permanent tablespace this article content create tablespace view tablespace modify tablespace availability/read/write Delete tablespace www.2cto.com suppose database is Oracle 11g, log on to SQL * PLUS with the SYS user. 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. Among them, the LMT area allocation method: UNIFORM (UNIFORM)-Unified 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 free list to manage used data blocks and free data blocks of segments. 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. Create tablespace mytbs01 www.2cto.com datafile 'd: \ oracledata \ mytbs01_1.dbf 'size 2 Mautoallocate; Example 2: create a tablespace mytbs02 using the UNIFORM area allocation method. The data file is D: \ oracledata \ mytbs02_1.dbf and D: \ oracledata \ mytbs02_2.dbf. There are two files in size: 1 MB and 2 MB. Create tablespace mytbs02datafile 'd: \ oracledata \ mytbs02_1.dbf 'size 1 M, 'd: \ oracledata \ mytbs02_2.dbf' size 2 Muniform size 128 k; Example 3: create Table space mytbs03, and specify the data file expansion mode to automatically increase, each time 1 M, up to 11 M. Create tablespace mytbs03datafile 'd: \ oracledata \ mytbs03_1.dbf 'size 1 Mautoextend on next 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. Create tablespace mytbs04datafile 'd: \ oracledata \ mytbs04_1.dbf 'size 3 M reuseuniformsegment space management auto; view the tablespace you created in the Oracle system table below. Dba_tablespaces-database tablespace management information. Dba_data_files-database tablespace file management information. Dba_free_space-database tablespace File Usage information. Example 5: Table space usage, including the table space name, number of DBF Files, total size, and remaining/Occupied size. Select. tablespace_name as "tablespace name", www.2cto.com c. pieces as "Number of DBF Files",. totalspace | 'M' as "total size", B. freespace | 'M' as "remaining size",. totalspace-nvl (B. freespace, 0) | 'M' as "occupied size", c. max_blocks as "maximum block", c. min_blocks as "minimum block", c. avg_blocks as "average block", c. sum_blocks as "Total number of blocks" from (select t1.tablespace _ name, sum (t1.bytes)/1024/1024 as totalspace from dba_data_files t1 group by t1.tablespace _ name) a, (select t2.tablespace _ name, sum (t2.bytes)/1024/1024 as freespace from dba_free_space t2 group by t2.tablespace _ name) B, (select t. tablespace_name, count (*) as pieces, max (t. blocks) as max_blocks, min (t. blocks) as min_blocks, avg (t. blocks) as avg_blocks, sum (t. blocks) as sum_blocks from dba_free_space t group by t. tablespace_name) c where. tablespace_name = B. tablespace_name and B. tablespace_name = c. tablespace_name

Example 6: see table space information, including table space name, path, type, management mode, zone management mode, segment management mode, and automatic growth. Www.2cto.com select t1.tablespace _ name as "tablespace name", t2.file _ name as "file name", t1.contents as "type", t1.extent _ management as "management mode ", t1.allocation _ type as "region management mode", t1.segment _ space_management as "segment management mode", t2.autoextensible as "auto scaling", t1.status as "online" from dba_tablespaces t1, dba_data_files t2 where t1.tablespace _ name = t2.tablespace _ name
Example 7: Modify Table space availability-online/offline. SQL> alter tablespace mytbs01 offline; The tablespace has been changed. SQL> alter tablespace mytbs01 online; The tablespace has been changed. Whether the table space is online indicates whether the table space is available. Taking tablespaces offline is to improve the I/O performance of data files. You may need to move the data files in a specific tablespace. To ensure the consistency of the data files in the tablespace, you must change the tablespace to offline before moving the data file to prevent the data files from being modified. After the table space maintenance is completed, the tablespace should be immediately changed to the ONLINE State so that the tablespace can be accessed. Example 8: Modify the tablespaces read/write-only. SQL> alter tablespace mytbs01 read only; the tablespace has been changed. SQL> alter tablespace mytbs01 read write; The tablespace has been changed. SQL> If the tablespace is only used to store static data, or you want to move the tablespace to another database, you should first change the tablespace to read-only. Example 9: delete a tablespace. SQL> CREATE TABLESPACE mytbs05 2 DATAFILE 'd: \ oracledata \ mytbs05_1.dbf 'size 1 M REUSE 3 AUTOALLOCATE; www.2cto.com TABLESPACE created. SQL> ALTER TABLESPACE mytbs05 offline; The TABLESPACE has been changed. SQL> DROP TABLESPACE mytbs05 INCLUDING CONTENTS AND DATAFILES; The TABLESPACE has been deleted. SQL> If the tablespace is not empty, the INCLUDING clause must be added. Including contents and datafiles indicates that the tablespace content AND data files are deleted. It can also be including contents and datafiles cascade constraints, indicating that in addition to deleting table space content AND data files, you also need to delete its constraints. Author IGod Interface