Oracle 11g Release 1 (11.1) tablespace -- create and expand (permanent) tablespaces

Oracle 11g Release 1 (11.1) tablespace -- create and expand (permanent) tablespaces this article content create (permanent) tablespaces view tablespaces extended tablespaces www.2cto.com creation (permanent) tablespace Oracle manages tablespaces by region 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 support segment management. The following example shows how to create a tablespace using local management. This is the recommended method for Oracle.
Example 1: Use the AUTOALLOCATE area allocation method. Create the tablespace mytbs01. The data file is D: \ oracledata \ mytbs01_1.dbf. The size is 2 MB, and AUTOALLOCATE is used to specify the partition allocation mode. Create tablespace mytbs01datafile 'd: \ oracledata \ mytbs01_1.dbf 'size 2 Mautoallocate; Example 2: Use the UNIFORM partition allocation method. Create the tablespace mytbs02. The data files are D: \ oracledata \ mytbs02_1.dbf and D: \ oracledata \ mytbs02_2.dbf. There are two files in size: 1 MB and 2 MB, respectively, and specify the area allocation mode as UNIFORM. Create tablespace mytbs02 www.2cto.com datafile 'd: \ oracledata \ mytbs02_1.dbf 'size 1 M, 'd: \ oracledata \ mytbs02_2.dbf' size 2 Muniform size 128 k;
Example 3: Use the data file extension method. Create the tablespace mytbs03 and specify the data file expansion mode to automatically increase. Each time the table is 1 MB, the maximum size is 11 Mb. 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. The partition allocation management mode is UNIFORM, and the size is the default value. Use segment space management 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", 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 www.2cto.com 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. 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; the extended tablespace is physically composed of data files stored on the disk. For example, the tablespace created in Example 1 is mybtb01, the maximum size is 2 MB. Suppose there is a table in it. When the data occupies 2 MB space, if you continue to insert data into the table, Oralce will not have enough free space on the mytbs01 tablespace, the error message is displayed. The data file size is specified during creation. To provide larger tablespaces and increase data, you must expand the tablespaces. There are three ways to expand a tablespace: Add a data file www.2cto.com to change the size of the data file. Allow automatic expansion of the data file. Example 7: Add a data file. Add a new data file D: \ ORACLEDATA \ MYTBS01_2.DBF for the tablespace in Example 1. SQL> column tablespace_name format a15SQL> column file_name format a28SQL> column total format a5SQL> select t. tablespace_name, 2 t. file_name, 3 t. bytes/1024/1024 | 'M' as total 4 from dba_data_files t 5 where t. tablespace_name = 'mytbs01 '; TABLESPACE_NAM FILE_NAME TOTAL -------------------------------------- ----- MYTBS01 D: \ ORACLEDATA \ MYTBS01_1.DBF 2 m SQL> alter tablespace MYTBS01 2 add d Atafile 'd: \ oracledata \ mytbs01_2.dbf 'size 3 M; the database has been changed. Www.2cto.com SQL> select t. tablespace_name, 2 t. file_name, 3 t. bytes/1024/1024 | 'M' as total 4 from dba_data_files t 5 where t. tablespace_name = 'mytbs01 '; TABLESPACE_NAM FILE_NAME TOTAL -------------- tables ----- MYTBS01 D: \ ORACLEDATA \ tables 2 M MYTBS01 D: \ ORACLEDATA \ tables 3 M before expansion, first view the table space MYTBS01 data files. Add a new data file for the tablespace mytbs01. Example 8: change the data file size. Based on Example 7, expand the size of the tablespace mytbs01 data file D: \ ORACLEDATA \ MYTBS01_2.DBF. SQL> column tablespace_name format a15SQL> column file_name format a28SQL> column total format a5SQL> select t. tablespace_name, 2 t. file_name, 3 t. bytes/1024/1024 | 'M' as TOTAL 4 from dba_data_files t 5 where t. tablespace_name = 'mytbs01 '; TABLESPACE_NAM FILE_NAME TOTAL -------------- certificate ----- MYTBS01 D: \ ORACLEDATA \ MYTBS01_1.DBF 2 M MYTBS01 D: \ ORACLEDATA \ MYTBS01_2.DBF 3 M SQL> alter database 2 datafile 'd: \ oracledata \ mytbs01_2.dbf '3 resize 4 M; the database www.2cto.com has been changed. SQL> select t. tablespace_name, 2 t. file_name, 3 t. bytes/1024/1024 | 'M' as TOTAL 4 from dba_data_files t 5 where t. tablespace_name = 'mytbs01 '; TABLESPACE_NAM FILE_NAME TOTAL -------------- certificate ----- MYTBS01 D: \ ORACLEDATA \ MYTBS01_1.DBF 2 M MYTBS01 D: \ ORACLEDATA \ MYTBS01_2.DBF 4 M
Example 9: Allow automatic expansion of data files. On the basis of Example 8, modify the tablespace mytbs01 data file D: \ ORACLEDATA \ MYTBS01_2.DBF to automatically expand. SQL> column tablespace_name format a15SQL> column file_name format a28SQL> column autoextensible format a15SQL> column total format a5SQL> column maxsize format a7SQL> select t. tablespace_name, 2 t. file_name, 3 t. bytes/1024/1024 | 'M' as TOTAL, 4 t. autoextensible, 5 t. maxbytes/1024/1024 | 'M' as MAXSIZE 6 from dba_data_files t 7 where t. tablespace_name = 'mytbs01 '; TABLESPACE_NAM FILE_NAM E total autoextensible maxsize -------------- hour ----- ------------- MYTBS01 D: \ ORACLEDATA \ hour 2 m no 0MMYTBS01 D: \ ORACLEDATA \ minute 4 m no 0 m SQL> alter database 2 datafile 'd: \ oracledata \ mytbs01_2.dbf '3 autoextend on next 1 M maxsize 20 M; the database has been changed. Www.2cto.com SQL> select t. tablespace_name, 2 t. file_name, 3 t. bytes/1024/1024 | 'M' as TOTAL, 4 t. autoextensible, 5 t. maxbytes/1024/1024 | 'M' as MAXSIZE 6 from dba_data_files t 7 where t. tablespace_name = 'mytbs01 '; TABLESPACE_NAM FILE_NAME total autoextensible maxsize -------------- bytes ----- --------------- ------- MYTBS01 D: \ ORACLEDATA \ MYTBS01_1.DBF 2 m no 0MMYTBS01 D: \ ORACLEDATA \ MYTBS01_2.DBF 4 m yes 20 M author IGod Interface