Managing Tablespaces. Guidelines for Managing Tablespaces. A tablespace is a database storage unit that groups related logical structures together. ![]() Vol.7, No.3, May, 2004. Mathematical and Natural Sciences. Study on Bilinear Scheme and Application to Three-dimensional Convective Equation (Itaru Hataue and Yosuke.![]() SAP Adaptive Server Enterprise 16.0 Release Bulletin SAP Adaptive Server Enterprise 16.0 for HP-UX Release Bulletin SAP Adaptive Server Enterprise 16.0 for IBM AIX. View and Download HP Integrity rx2620 user's & service manual online. HP Integrity RX2620 Server User Service Guide. Integrity rx2620 Server pdf manual download. The database data files are stored in tablespaces. Before working with tablespaces of an Oracle Database, familiarize yourself with the guidelines provided in the following sections: Using Multiple Tablespaces. Using multiple tablespaces allows you more flexibility in performing database operations. When a database has multiple tablespaces, you can: Separate user data from data dictionary data to reduce I/O contention. Separate data of one application from the data of another to prevent multiple applications from being affected if a tablespace must be taken offline. Store the data files of different tablespaces on different disk drives to reduce I/O contention. Take individual tablespaces offline while others remain online, providing better overall availability. Optimizing tablespace use by reserving a tablespace for a particular type of database use, such as high update activity, read- only activity, or temporary segment storage. Back up individual tablespaces. Some operating systems set a limit on the number of files that can be open simultaneously. Such limits can affect the number of tablespaces that can be simultaneously online. To avoid exceeding your operating system limit, plan your tablespaces efficiently. Create only enough tablespaces to fulfill your needs, and create these tablespaces with as few files as possible. If you must increase the size of a tablespace, then add one or two large data files, or create data files with autoextension enabled, rather than creating many small data files. Review your data in light of these factors and decide how many tablespaces you need for your database design. Assigning Tablespace Quotas to Users. Grant to users who will be creating tables, clusters, materialized views, indexes, and other objects the privilege to create the object and a quota (space allowance or limit) in the tablespace intended to hold the object segment. Note. For PL/SQL objects such as packages, procedures, and functions, users only need the privileges to create the objects. No explicit tablespace quota is required to create these PL/SQL objects. Creating Tablespaces. Before you can create a tablespace, you must create a database to contain it. The primary tablespace in any database is the SYSTEM tablespace, which contains information basic to the functioning of the database server, such as the data dictionary and the system rollback segment. The SYSTEM tablespace is the first tablespace created at database creation. It is managed as any other tablespace, but requires a higher level of privilege and is restricted in some ways. For example, you cannot rename or drop the SYSTEM tablespace or take it offline. The SYSAUX tablespace, which acts as an auxiliary tablespace to the SYSTEM tablespace, is also always created when you create a database. It contains the schemas used by various Oracle products and features, so that those products do not require their own tablespaces. As for the SYSTEM tablespace, management of the SYSAUX tablespace requires a higher level of security and you cannot rename or drop it. The management of the SYSAUX tablespace is discussed separately in "Managing the SYSAUX Tablespace". The steps for creating tablespaces vary by operating system, but the first step is always to use your operating system to create a directory structure in which your data files will be allocated. On most operating systems, you specify the size and fully specified filenames of data files when you create a new tablespace or alter an existing tablespace by adding data files. Whether you are creating a new tablespace or modifying an existing one, the database automatically allocates and formats the data files as specified. To create a new tablespace, use the SQL statement CREATE TABLESPACE or CREATE TEMPORARY TABLESPACE. You must have the CREATE TABLESPACE system privilege to create a tablespace. Later, you can use the ALTER TABLESPACE or ALTER DATABASE statements to alter the tablespace. You must have the ALTER TABLESPACE or ALTER DATABASE system privilege, correspondingly. You can also use the CREATE UNDO TABLESPACE statement to create a special type of tablespace called an undo tablespace, which is specifically designed to contain undo records. These are records generated by the database that are used to roll back, or undo, changes to the database for recovery, read consistency, or as requested by a ROLLBACK statement. Creating and managing undo tablespaces is the subject of Chapter 1. Managing Undo". The creation and maintenance of permanent and temporary tablespaces are discussed in the following sections: Locally Managed Tablespaces. Locally managed tablespaces track all extent information in the tablespace itself by using bitmaps, resulting in the following benefits: Fast, concurrent space operations. Space allocations and deallocations modify locally managed resources (bitmaps stored in header files). Enhanced performance. Readable standby databases are allowed, because locally managed temporary tablespaces do not generate any undo or redo. Space allocation is simplified, because when the AUTOALLOCATE clause is specified, the database automatically selects the appropriate extent size. User reliance on the data dictionary is reduced, because the necessary information is stored in file headers and bitmap blocks. Coalescing free extents is unnecessary for locally managed tablespaces. All tablespaces, including the SYSTEM tablespace, can be locally managed. The DBMS_SPACE_ADMIN package provides maintenance procedures for locally managed tablespaces. Creating a Locally Managed Tablespace. Create a locally managed tablespace by specifying LOCAL in the EXTENT MANAGEMENT clause of the CREATE TABLESPACE statement. This is the default for new permanent tablespaces, but you must specify the EXTENTMANAGEMENTLOCAL clause to specify either the AUTOALLOCATE clause or the UNIFORM clause. You can have the database manage extents for you automatically with the AUTOALLOCATE clause (the default), or you can specify that the tablespace is managed with uniform extents of a specific size (UNIFORM). If you expect the tablespace to contain objects of varying sizes requiring many extents with different extent sizes, then AUTOALLOCATE is the best choice. AUTOALLOCATE is also a good choice if it is not important for you to have a lot of control over space allocation and deallocation, because it simplifies tablespace management. Some space may be wasted with this setting, but the benefit of having Oracle Database manage your space most likely outweighs this drawback. If you want exact control over unused space, and you can predict exactly the space to be allocated for an object or objects and the number and size of extents, then UNIFORM is a good choice. This setting ensures that you will never have unusable space in your tablespace. When you do not explicitly specify the type of extent management, Oracle Database determines extent management as follows: If the CREATE TABLESPACE statement omits the DEFAULT storage clause, then the database creates a locally managed autoallocated tablespace. If the CREATE TABLESPACE statement includes a DEFAULT storage clause, then the database considers the following: If you specified the MINIMUM EXTENT clause, the database evaluates whether the values of MINIMUM EXTENT, INITIAL, and NEXT are equal and the value of PCTINCREASE is 0. If so, the database creates a locally managed uniform tablespace with extent size = INITIAL. If the MINIMUM EXTENT, INITIAL, and NEXT parameters are not equal, or if PCTINCREASE is not 0, the database ignores any extent storage parameters you may specify and creates a locally managed, autoallocated tablespace. If you did not specify MINIMUM EXTENT clause, the database evaluates only whether the storage values of INITIAL and NEXT are equal and PCTINCREASE is 0. If so, the tablespace is locally managed and uniform. Otherwise, the tablespace is locally managed and autoallocated. The following statement creates a locally managed tablespace named lmtbsb and specifies AUTOALLOCATE. CREATE TABLESPACE lmtbsb DATAFILE '/u. SIZE 5. 0M. EXTENT MANAGEMENT LOCAL AUTOALLOCATE. AUTOALLOCATE causes the tablespace to be system managed with a minimum extent size of 6. K. The alternative to AUTOALLOCATE is UNIFORM. You can specify that size in the SIZE clause of UNIFORM. If you omit SIZE, then the default size is 1.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
November 2017
Categories |