Managing Tablespaces and Data Files Learning Objectives To learn about database structure relationships. To present the types of temporary segments. To learn how to change the tablespace status. To introduce the methods used to manage tablespaces and data files. To illustrate how the division of the database into logical and physical structures. To learn the concepts associated with storage structure components. To illustrate the methods by which the storage structure information is retrieved.
Database Structure: Relationships The performance of a database depends largely upon the effective management of system resources, such as the hard disk space. The logical and physical structures that compose the Oracle database are made up of other components. The physical structure consists of control files, online redo log files, and data files. The components of the logical structure of a database are tablespaces, segments, extents, and data blocks. When the components of the logical structure are created, space is allocated in the database according to predefined parameters. The DBA can override or configure these parameters to use the space efficiently. The separation of logical and physical components of an Oracle database facilitates a fine-grained control of disk space management. As a DBA, you can configure the parameters that allocate space for the physical and logical components of the database. To use the hard disk space efficiently, you should be familiar with the relationships between the physical and the logical components of the database. You should also understand how space is allocated in the database. The first entity is the Oracle database. It is a logical component of the database structure. The database is divided into smaller logical areas of space called tablespaces. Each tablespace in an Oracle database consists of one or more files called data files. The data files are physical components that conform to the operating system on which the Oracle server is running. A tablespace is made up of segments. A segment is the space allocated for a specific type of logical storage component in a tablespace. Some examples of segments are index, temporary, rollback, and data segments. A segment such as a data segment may span multiple files that belong to the same tablespace. The next level of the logical database structure is called an extent. An extent is a set of contiguous blocks. Each segment is made up of one or more extents. An extent must exist in one data file. Data blocks are at the lowest level of the logical database structure. The data in an Oracle database is stored in data blocks. One data block corresponds to one or more physical O/S blocks allocated from an existing data file.                               Tablespace: Features To facilitate effective disk space management, it is important to review how the database is divided into logical and physical storage structures. The largest logical storage structure of a database is a tablespace. In this topic, you learn about the features of a tablespace. A tablespace can belong to only one database. Another feature of a tablespace is that it consists of one or more operating system files. Next, a tablespace can be brought online when the database is running. This feature enables a DBA to control data without shutting down the database. Another feature is that the status of tablespaces can be switched between read-write and read-only. This feature enables you to prevent users from changing stored data. Finally, all tablespaces, except for the SYSTEM tablespace or a tablespace with an active rollback segment, can be taken offline. This leaves the database running. This helps a DBA in maintaining data without making the database unavailable. Tablespace: uses In this topic, you learn about the different uses of tablespaces. An Oracle database is divided into logical and physical storage structures. The largest logical storage structure is a tablespace. You can use the tablespace to help in managing disk space efficiently. One of the uses of a tablespace is that it helps the Oracle server in controlling space allocation and assigning space quotas to users. You size a tablespace appropriately, then assign it to a user to control space allocation. Next, a tablespace is used for controlling the availability of data. This is done by taking individual tablespaces online or offline. Proper use of tablespaces improves I/O performance. It also reduces I/O contention by distributing data storage across devices. A tablespace is also used to perform partial backup and partial recovery operations. Finally, a tablespace is used to store large amounts of static data on read-only devices. Data Files: Features ·         A tablespace is the largest logical storage structure in a database. The data in the tablespace is stored in the physical storage structures called data files. In this topic, you learn about the features of a data file. ·         Each tablespace in an Oracle database consists of one or more data files. Data files are the physical structures that conform to the operating system on which the Oracle server is installed. ·         The size of a data file can be changed by the DBA after its creation. Otherwise, the DBA can specify that the size of a data file should dynamically increase along with an increase in the number of objects in the tablespace. ·         This feature enables the DBA to circumvent the MAXDATAFILES limitation, and makes it possible for the database to consist of fewer data files for each tablespace. It also prevents users and applications from getting errors due to the lack of free space in a tablespace. ·         One feature of a data file is that it can belong to only one tablespace. This enables a DBA to backup the data file without affecting the availability of any other tablespace. ·         Another feature is that the data file for a tablespace is created by the Oracle server. However, the amount of disk space to be occupied by the data file is specified by the DBA. The Oracle server creates the data file with the specified amount of disk space along with a small overhead.
Tablespace: Types ·         In this topic, you learn about the differences between the SYSTEM and Non-SYSTEM tablespaces. When you create a database, a tablespace called the SYSTEM tablespace is created by default. All other tablespaces, called Non-SYSTEM tablespaces, are created by the DBA. ·         You create a Non-SYSTEM tablespace to manage the database easily and enable the users to use the database effectively. ·         The Non-SYSTEM tablespace is not a mandatory requirement. The SYSTEM tablespace is required in all databases for database operation. ·         The Non-SYSTEM tablespace may contain data segments, index segments, temporary segments, and rollback segments. On the other hand, the SYSTEM tablespace contains data dictionary information, definitions of stored procedures, packages, and database triggers. ·         The Non-SYSTEM tablespace may contain any additional rollback segments and application indexes. A SYSTEM tablespace contains the SYSTEM rollback segment. ·         You should not store user data in the SYSTEM tablespace. If the SYSTEM tablespace contains user data, you cannot take offline backups of the data without shutting down the database. Creating a Tablespace ·         In this topic, you learn about creating a tablespace. As the DBA, you are responsible for maintaining the data in the databases. One way that you can effectively manage data is by creating tablespaces for different sets of users, such as creating a marketing tablespace for the marketing personnel. ·         You execute a SQL command or use Oracle Storage Manager to create a tablespace. The syntax of the SQL command used to create a tablespace is following. Alternatively, you can use Oracle Storage Manager to create a tablespace. CREATE TABLESPACE tablespace DATAFILE filespec [autoextend_clause] [, filespec [autoextend_clause] [MINIMUM EXTENT integer [K|M]] [DEFAULT storage_clause] [PERMANENT|TEMPORARY] [ONLINE|OFFLINE] storage_clause :== ([INITIAL integer [K|M]] [NEXT integer [K|M] [MINEXTENDS integer] [MAXEXTENTS {integer|UNLIMITED}] [PCTINCREACE integer])] Ex: Create a tablespace named ACCOUNTS by using Oracle Storage Manager. The name of the data file is accts01.dbf and its size is 10 KB. -          The Oracle Storage Manager window is displayed and the Tablespaces node is selected. To begin the process of creating the ACCOUNTS tablespace, click on the Object menu now. -          To create the tablespace, click on the Create option. -          The Create Tablespace dialog box is displayed. To name the tablespace as ACCOUNTS, type ACCOUNTS and click on the Add button now. -          The Create Datafile dialog box is displayed. To create the accts01.dbf data file for the ACCOUNTS tablespace, type e:\orant\oradata\orcl\accts01.dbf and press Tab now. -          To specify the size of the data file as 10 KB, type 10 and click on the K Bytes box. To accept the values, click on the OK button. -          To complete the process of creating the ACCOUNTS tablespace, click on the Create button now. -          The Oracle Storage Manager window is displayed. You successfully created the ACCOUNTS tablespace. -          The SQL command that was generated during this creation of a tablespace is following. CREATE TABLESPACE ACCOUNTS DATAFILE e:\orant\oradata\orcl\accts01.dbf  SIZE 10K; -          To view the ACCOUNTS tablespace that you created by using Oracle Storage Manager, click on the Tablespaces node now. -          The Tablespaces node displays the ACCOUNTS tablespace that you created. -          When creating tablespaces, the maximum number of tablespaces that you can create for each database is 64000. The maximum number of data files that can be assigned for each tablespace is 1023. Storage Parameters ·         In this topic, you learn about tablespace storage parameters. The efficiency of a tablespace depends on the amount of space used by a segment. Therefore, it is important for you to know the parameters that influence the amount of space used by a segment. ·         There are five parameters that determine and control the amount of space used by a segment. These are INITIAL, NEXT, MAXEXTENTS, MINEXTENTS, and PCTINCREASE. ·         The size of the first extent of any segment is set by the INITIAL parameter. The minimum size of the first extent is two blocks or 2*DB_BLOCK_SIZE. The default size is five blocks or 5*DB_BLOCK_SIZE. ·         The NEXT parameter refers to the size of the second extent. The minimum size of the next extent is one block, and the default size is five blocks or 5*DB_BLOCK_SIZE. ·         The value of the MINEXTENTS parameter determines the number of extents allocated when the segment is created. By default, the minimum value is one. ·         PCTINCREASE is the percent by which the NEXT size increases. ·         For example, NEXT is set to 200 KB, and PCTINCREASE is set to 50%. In such a situation, the second extent will be 200 KB in size, the third extent will be 300 KB, and the fourth extent will be 450 KB. ·         The minimum value that the PCTINCREASE parameter can take is zero, and its default value is 50. The calculated value is rounded up to the next value, which is a multiple of 5*DB_BLOCK_SIZE. ·         The MAXEXTENTS parameter determines the maximum number of extents a segment can contain. The minimum value is one. The default maximum value depends on the data block size. The maximum size can also be specified by the keyword UNLIMITED, equivalent to a value of 2,147,483,645. ·         The values for these parameters should account for a typical object that will be created in the tablespace. However, these storage parameters can be overruled when creating individual segments.
Creating Temporary Tablespace ·         In this topic, you create a temporary tablespace. Oracle performs sort operations in memory. However, when a large amount of data is involved, Oracle needs to use some disk space temporarily to sort the data. The SYSTEM tablespace is used as the default tablespace for that purpose. ·         Once the sorting is over, the space is released. When a large amount of data is involved, the efficiency of the database declines. To avoid the usage of the SYSTEM tablespace for sorting, Oracle enables you to create temporary tablespaces and assign them to database users. ·         The SQL command used to create a temporary tablespace is displayed. In addition to the SQL command, you can create a temporary tablespace by using Oracle Storage Manager. CREATE TABLESPACE tablespace DATAFILE filespec [autoextend_clause] [DEFAULT storage_clause] TEMPORARY Ex: Create a temporary tablespace named Temp by using Oracle Storage Manager. The data file should be named temp01.dbf, and its size should be 50 KB. Assign the value 500 KB to initial and next extents. The percentage increase should be zero, and the maximum number of extents should be 500. -          The Oracle Storage Manager window is displayed and the Tablespaces node is selected. To begin the process of creating the temporary tablespace named Temp, click on the Object menu now. -          To begin the process of creating the temporary tablespace, click on the Create option now. -          The Create Tablespace dialog box is displayed. To specify the name of the temporary tablespace as Temp, type Temp and click on the Add button now. -          The Create Datafile dialog box is displayed. To specify the name of the data file for the temporary tablespace as temp01.dbf, type temp01.dbf and press Tab now. -          To specify the size of the data file as 50 K, type 50 and click on the K Bytes box. To accept the values, click on the OK button. -          To specify that the new tablespace is temporary, click on the Temporary option button. -          The Create Tablespace dialog box is displayed. To specify the storage parameters, click on the Extents tab now. -          To override the default values, click on the Override Default Values check box. -          To specify the initial extent, click on the Initial Size text box. -          To specify the value of the initial extent as 500 KB, type 500 and press Tab. -          To specify the value of the next extent as 500 KB, type 500 and press Tab. -          To specify the percentage increase as zero, type 0 and click on the Value text box. -          To specify the maximum number of extents for a segment as 500, type 500 and press Tab now. -          You specified the value for the maximum number of extents.  To complete the process of creating the temporary tablespace, click on the Create button now. -          You successfully created a temporary tablespace. This enables tasks such as sort operations to use this area as a temporary workspace. -          The SQL command that was generated is displayed. ·         A temporary tablespace can only be used to store sort segments and cannot contain any permanent objects. The existing tablespaces can be altered so that they become temporary. However, this is possible as long as they do not contain any permanent objects, such as a table. Locally Managed Tablespaces ·         Tablespace extents can be managed using data dictionary tables or bitmaps, called locally managed tablespaces. When you create a tablespace, you must select either a data dictionary-managed tablespace or a locally managed tablespace. You cannot alter it at a later time. ·         In this topic, you learn the benefits of locally managed tablespaces. ·         A locally managed tablespace manages its own extents and maintains a bitmap in each data file to keep track of the free or used blocks. ·         Each bit in the bitmap corresponds to a block or a group of blocks. When an extent is allocated or freed for reuse, the Oracle server changes the bitmap values to show the new status of the blocks. ·         Locally managed tablespaces have some advantages over dictionary-managed tablespaces. There is reduced contention on data dictionary tables because locally managed tablespaces do not record free space in data dictionary tables. ·         Locally managed tablespaces avoid recursive space management operations that can occur in dictionary-managed tablespaces. When this happens, consuming or releasing space in an extent results in another operation that consumes or releases space in a rollback segment or data dictionary table. ·         Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents. Also, the sizes of extents that are managed locally can be automatically determined by the system. Alternatively, all extents can have the same size in a locally managed tablespace. ·         To specify local tablespace management, the LOCAL command option of the EXTENT MANAGEMENT clause is used. ·         In this topic, you learn to add tablespace data files. When tablespaces get filled with objects over a period of time, the space available in the data files is exhausted. To overcome this situation, you can add data files to tablespaces. This provides more disk space for allocation to database objects. ·         You can add space to a tablespace by either using a SQL command or Oracle Storage Manager. The SQL command used to add a data file to a tablespace is displayed. Ex: In addition to the SQL command, you can use a graphic interface called Oracle Storage Manager to add a data file to a tablespace. Add a data file named accts02.dbf of 100 KB located in the c:\orant\database directory to the ACCOUNTS tablespace by using Oracle Storage Manager. -          The Oracle Storage Manager window is open. To add a data file to the ACCOUNTS tablespace, it must be selected. To begin the process of selecting the ACCOUNTS tablespace, click on the Tablespaces node now. -          The contents of the Tablespaces node appear. To select the ACCOUNTS tablespace, click on the ACCOUNTS tablespace name now. -          The property sheet for the ACCOUNTS tablespace is displayed in the right pane of the Oracle Storage Manager window. To add a data file to the selected tablespace, click on the Add button now. -          The Create Datafile dialog box is displayed. To specify the name of the data file as accts2 type accts02.dbf and press Tab now. -          To specify the size of the data file as 100 KB, type 100 and click on the OK button. -          The Oracle Storage Manager window appears. The accts02.dbf data file that you created is displayed in the Datafiles group box.
Extending Data Files ·         Due to an increase in the number of database objects in a tablespace, the data files may run out of allocated disk space. To overcome this problem, you alter the size of a data file. ·         At the time of creation, you can also specify that the data file should increase dynamically. In this topic, you learn to enable a data file to extend automatically. ·         To specify the automatic file extension command option when creating a data file, you can use the CREATE DATABASE, CREATE TABLESPACE DATAFILE, or ALTER TABLESPACE ADD DATAFILE command. ·         You can alter the size of an existing data file using one of two methods. You can automatically alter the size by using the AUTOEXTEND command option, or you can alter the size manually by using the ALTER DATABASE command. ·         The AUTOEXTEND command option enables or disables the automatic extension of data files. The SQL command to specify the AUTOEXTEND command option for an existing data file is displayed. ·         Another way of enabling the automatic extension is by using Oracle Storage Manager. Enable automatic extension of the accts01.dbf data file in the ACCOUNTS tablespace by using Oracle Storage Manager. The automatic extension should be at the rate of 2 KB. Ex: The Oracle Storage Manager window is open. Since the accts01.dbf data file is in the ACCOUNTS tablespace, you must select the ACCOUNTS tablespace. -          To begin the process of selecting the ACCOUNTS tablespace, click on the Tablespaces node now. -          The contents of the Tablespaces node are displayed. To select the ACCOUNTS tablespace, double-click on the ACCOUNTS tablespace name now. -          The list of folders under the ACCOUNTS tablespace is displayed. The accts01.dbf data file is located in the Datafiles folder. To begin the process of selecting the ACCOUNTS data file, double-click on the Datafiles node now. -          The accts01.dbf data file appears. To select the accts01.dbf data file, click on E:\ORANT\ORADATA\ORCL\ACCTS01.DBF now. -          The property sheet for the accts01.dbf data file appears in the right pane of the Oracle Storage Manager window. To access the section where you specify the automatic extension feature of the data file, click on the Auto Extend tab now. -          The Auto Extend page appears. To specify that the data file should automatically extend, click on the Enable Auto Extend check box now. -          To select the text box for specifying the rate of increment for an automatic extension, click on the Increment text box now. -          To specify that the rate of the automatic extension should be 2 KB at a time, type 2 and press Tab now. -          You specified the value for the rate of the automatic extension. Next, you apply the changes in the data file. -          The right pane of the Oracle Storage Manager window has been scrolled. To apply the changes in the accts01.dbf data file, click on the Apply button now. -          You successfully enabled the automatic extension of the accts01.dbf data file in the ACCOUNTS tablespace by using Oracle Storage Manager. -          The SQL command that was automatically generated when you enabled the automatic extension of the accts01.dbf data file is displayed. You can also display this SQL command by using the Show SQL button in the Oracle Storage Manager. Resizing Data Files ·         As a DBA, you estimate the space requirements of a database. However, when actually using the database, you may realize that there was an error in estimating the space requirement. To overcome this problem, you resize data files. In this topic, you learn to resize a data file. ·         The SQL command used to resize the data files of a tablespace is displayed. ·         Resize the accts01.dbf data file in the e:\orant\database directory to 2 MB by using Oracle SQL*Plus Worksheet. Ex: The Oracle SQL*Plus Worksheet window is open. To complete the command of resizing the data file accts01.dbf to 2 MB, type 'e:\orant\oradata\orcl\accts01.dbf' RESIZE 2M; and press F5 now. SQL> ALTER DATABASE DATAFILE 'e:\orant\oradata\orcl\accts01.dbf' RESIZE 2M; -          The Oracle SQL*Plus Worksheet window is displayed. You successfully resized the accts01.dbf data file. Changing Storage Parameters ·         The values of the segment storage allocation parameters determine the amount of disk space used by a database. These values are determined during the creation of the segment. In this topic, you learn to modify the values of the default storage parameters. ·         Over a period of time, you may need to change the storage parameters for more efficient management of the disk space. To satisfy this requirement, you alter the values of the default storage parameters for a tablespace. ·         The SQL command used to alter the values of the storage parameters is displayed. ·         Ex: In addition to the SQL command, you can use Oracle Storage Manager to alter the values of the storage parameters. Change the value of the storage parameters INITIAL and NEXT of the ACCOUNTS tablespace to 100 KB by using Oracle Storage Manager. -          The Oracle Storage Manager window is open, and the ACCOUNTS tablespace is selected. To access the page containing the storage information, click on the Extents tab now. -          To change the value of the INITIAL parameter to 100 KB, type 100 and press Tab. -          To change the value of the NEXT parameter, type 100 and press Tab. -          You specified the value for the next extent. This enables you to efficiently manage disk space. -          The right pane of the Oracle Storage Manager window has been scrolled. To apply the changes, click on the Apply button now. -          You successfully changed the values of the storage parameters by using Oracle Storage Manager. -          The SQL command that was generated while changing the values of the storage parameters of the tablespace is displayed. You can display the SQL statements by using the Show SQL button in Oracle Storage Manager. Obtaining Temporary Segment Information from Oracle ·         There are several data dictionary views available for obtaining information about temporary segments. ·         The views in the dictionary displaying this information base their content either on temporary segments that exist in the database or on dynamic performance information about temporary segments collected while the instance is running. ·         The views you should remember for viewing temporary segment information include the following: • DBA_SEGMENTS Gives information about the name, tablespace location, and owner of both types of temporary segments in Oracle. Note that you will only see information on temporary segments in permanent tablespaces while those segments are allocated, but you will see information about temporary segments in temporary tablespaces for the life of the instance. • V$SORT_SEGMENT Gives information about size of the temporary tablespaces, current number of extents allocated to sort segments, and sort segment high-water mark information. • V$SORT_USAGE Gives information about sorts that are happening currently on the database. ·         You can obtain the name, segment type, and tablespace storing sort segments using the DBA_SEGMENTS view. ·         Note that this segment will not exist until the first disk sort is executed after the instance starts. The following code block is an example:  SQL> select owner, segment_name, segment_type, tablespace_name from dba_segments; ·         You can get the size of sort segments allocated in temporary tablespaces by issuing queries against V$SORT_SEGMENT, which you will find useful in defining the sizes for your temporary tablespaces on an ongoing basis. ·         The following query illustrates how to obtain this sort segment high-water mark information from V$SORT_SEGMENT: SQL> select tablespace_name, extent_size, total_extents, max_sort_blocks          from v$sort_segment; Finally, you can see information about sorts currently taking place on the instance by joining data from the V$SESSION and V$SORT_USAGE views. The following code block displays an example: SQL> select a.username, b.tablespace, b.contents,b.extents, b.blocks from v$session a, v$sort_usage b  where a.saddr = b.session_addr; Dictionary Views for Temporary Tablespace Management There are a couple of new dictionary views for managing temporary tablespaces: • DBA_TEMP_FILES This dictionary view gives you information about every datafile in your database that is associated with a temporary tablespace. • V$TEMPFILE Similar to DBA_TEMP_FILES, this performance view gives you information about everydatafile in your database that is associated with a temporary tablespace.
Tablespace Offline: Modes ·         As a part of the database maintenance tasks, such as upgrading storage media, a DBA will relocate data files for a particular tablespace. To enable the relocation of data files, the DBA can take individual tablespaces offline. ·         A tablespace can be taken offline in either normal, temporary, or immediate mode. ·         In the normal mode, the Oracle server performs a checkpoint on all the data files before taking the tablespace offline. This is the preferred mode. ·         In the temporary mode, the Oracle server performs a checkpoint on all the possible data files before taking the tablespace offline. In this mode, any offline data files may require media recovery later. ·         Finally, a checkpoint is not performed by the Oracle server in the immediate mode before taking the tablespace offline. You must perform media recovery before bringing the tablespace back online. Note that the SYSTEM tablespace and any tablespace with active rollback segments cannot be taken offline. ·         The Oracle server debars any SQL statements to reference the objects contained in a tablespace that has been taken offline. Therefore, the users trying to access the objects that are offline will receive an error. ·         In addition, when a tablespace is either taken offline or brought back online, the event is recorded in the data dictionary and the control file. ·         Since the events are recorded in the control file, the tablespace will maintain its last recorded status. For instance, if the tablespace was offline at the time of database shutdown, it will not be checked when the database is subsequently mounted and opened. Taking Tablespace Offline ·         In this topic, you place an individual tablespace offline by using Oracle Storage Manager. As part of the database maintenance tasks, such as relocating a data file, you may need to make a tablespace unavailable while allowing normal access to the remainder of the database. ·         The SQL command used to take a tablespace offline is displayed. ·         In addition, you can use Oracle Storage Manager to take a tablespace offline. Take the ACCOUNTS tablespace offline by using Oracle Storage Manager. ·         Ex: The Oracle Storage Manager window is open. The ACCOUNTS tablespace is selected. To begin the process of taking the tablespace offline, click on the Offline option button now. -          You performed the step to take the ACCOUNTS tablespace offline. Notice that the normal mode is selected. -          To complete the process of taking the ACCOUNTS tablespace offline, click on the Apply button now. -          The right pane of the Oracle Storage Manager window displays the status of the tablespace. You successfully took the ACCOUNTS tablespace offline. -          The SQL command that was generated is displayed. SQL> ALTER TABLESPACE “ACCOUNTS” OFFLINE NORMAL;
Moving Data Files: Non-SYSTEM ·         An increase in the number of records in the database causes the data file size to increase. This may lead to a situation where the media may run out of free space. To overcome this problem, Oracle enables you to move the data files to a new location. ·         Ex: Move the accts01.dbf data file of the ACCOUNTS tablespace in the e:\orant\oradata\orcl directory to d:\orant\oradata\orcl directory by using Oracle Storage Manager. -          The first step in the process of moving a data file to a new location is to take the tablespace offline. The Oracle Storage Manager window is open, and the ACCOUNTS tablespace is selected. To take the tablespace offline, click on the Offline option button now. -          To take the ACCOUNTS tablespace offline in the normal mode, click on the Apply button now. -          The next step in moving a data file is to use the operating system command to move or copy the files. The MS-DOS window is open and the source directory path is provided. To copy the data file accounts to the d:\orant\oradata\orcl directory. C:\> copy e:\orant\oradata\orcl\accts01.dbf d:\orant\oradata\orcl\accts01.dbf -          The next step in the process of moving a data file is to change the path of the data file by using Oracle Storage Manager. -          The SQL command to move the data file is displayed. You can use either the SQL command or Oracle Storage Manager to perform the same task. -          The Oracle Storage Manager window is displayed, the ACCOUNTS tablespace is selected, and the Datafiles node has been expanded. To select the data file, click on E:\ORANT\ORADATA\ORCL\ACCTS01.DBF directory path and filename now. -          To edit the path of the accts01.dbf data file, click in the Name field. -          To change the path of the accounts data file, type d:\orant\database\accts01.dbf and click on the Apply button now. -          The next step is to bring the tablespace online. To start the process of bringing the ACCOUNTS tablespace online, click on the Online option button now. -          To bring the ACCOUNTS tablespace online, click on the Apply button. -          You successfully moved the data file accts01.dbf from the e:\orant\oradata\orcl directory to the d:\orant\oradata\orcl directory. You can now remove the original data file in the e:\orant\oradata\orcl directory. -          You use Oracle Storage Manager to move data files that belong only to the Non-SYSTEM tablespaces and that do not contain active rollback or temporary segments. Moving Data Files: SYSTEM ·         In this topic, you learn to move system data files. The SYSTEM tablespace is required for a database to operate successfully. However, in certain situations, you must move the data files of the SYSTEM tablespace to a new location. ·         You can move a data file of the SYSTEM tablespace to a new location. The first step in moving a data file belonging to the SYSTEM tablespace is to shut down the database. ·         The next step is to use an operating system command for moving the data file to the new location. ·         Next, you mount the database. ·         The next step is to execute a SQL command for moving the data file of the SYSTEM tablespace. ·         Ex: The e:\orant\database directory belongs to the SYSTEM tablespace. Use the Oracle SQL*Plus Worksheet to move the SYSTEM01.DBF data file from the e:\orant\database directory to the c:\orclbackup directory. -          The Oracle SQL*Plus Worksheet window is open. The database is mounted, and the file has been copied in the c:\orclbackup directory. To complete the command of moving the data file, type RENAME FILE 'e:\orant\oradata\orcl\system01.dbf' and press Enter now. ·         To complete the code, type TO 'c:\orclbackup\system01.dbf'; and press F5 now. ·         The final step in moving a data file belonging to a SYSTEM tablespace is to open the database. ·         You should always provide complete file names, including their directory paths, to identify the old and the new data files.
Making Tablespace Read_Only Mode ·         In this topic, you learn to change a tablespace to the read-only mode. To meet the organizational requirements, you may have tablespaces that contain data that is static in nature. ·         To eliminate the need to perform backup and recovery of such static data, you can make the tablespaces read-only. ·         Making tablespaces read-only also ensures that no write operations are performed on the data. You can store the data files on read-only media, such as CD-ROMs or WORM drives because the Oracle server never updates the files of a read-only tablespace. Restrictions ·         Several conditions must be met before you can make a tablespace read-only. First, to make a tablespace read-only, it must be online. ·         If you are using Oracle(i), you can make a tablespace read-only while there are active transactions in the database. ·         Once the command is issued, current write commands are completed, and the tablespace becomes read-only. Oracle(i) is the only release that can make a tablespace read-only while active transactions are in the database. ·         Finally, the tablespace must not contain active rollback segments and the tablespace must not be currently involved in an online backup. ·         The recommended way to meet these restrictions is to start the instance in the restricted mode. In addition, making the tablespaces read-only will cause a checkpoint on the data files. ·         The SQL command used to change a tablespace to the read-only mode is displayed. ·         In addition to the SQL command, Oracle provides Oracle Storage Manager, a graphic interface, for changing a tablespace to the read-only status. Make the ACCOUNTS tablespace read-only by using Oracle Storage Manager. ·         The Oracle Storage Manager window is open, and the ACCOUNTS tablespace has been selected. To begin the process of making the tablespace read-only, click on the Read Only check box now. ·         You started the process of making the ACCOUNTS tablespace read-only. ·         To complete the process of making a tablespace read-only, click on the Apply button now. ·         You successfully made the ACCOUNTS tablespace read-only. Read-Only Tablespaces On Read-Only Media ·         As a DBA, you may have an objective to minimize the time you spend on data backups. One way to reduce this time is to store static data on read-only tablespaces and then store it on read-only media. Storing read-only data on read-only media is a three-step process ·         The first step is to make the tablespace read-only. ·         The next step is to copy all the data files of the tablespace to the read-only media. ·         The final step in storing the read-only data on read-only media is to rename the data files to point to the new location.
Dropping a Tablespace ·         In this topic, you drop a tablespace by using Oracle Storage Manager. Due to a change in the user requirements, there may be tablespaces that are no longer required. The presence of these tablespaces is a waste of disk capacity. To solve this problem, you can drop such tablespaces. ·         You can remove tablespaces using two methods, the SQL command and Oracle Storage Manager. The SQL command used to remove such tablespaces is displayed. ·         In addition to the SQL command, Oracle provides a graphic interface, Oracle Storage Manager, to drop a tablespace. Drop the ACCOUNTS tablespace by using Oracle Storage Manager. ·         The Oracle Storage Manager window is open. The ACCOUNTS tablespace has been selected. To start the process of dropping the tablespace, click on the Object menu now. ·         The Object drop-down menu appears. To remove the ACCOUNTS tablespace, click on the Remove option now. ·         The Oracle Storage Manager dialog box appears. To confirm the deletion of the ACCOUNTS tablespace, click on the Yes button now. Guidelines ·         You should follow a set of guidelines when you drop a tablespace. You should ensure that current transactions do not access any of the segments in the tablespace. The best way to guarantee this is taking the tablespace offline first. ·         If you are using the SQL command to drop a tablespace, you must use the INCLUDING CONTENTS clause for a tablespace that still contains data. ·         When the tablespace contains many objects, using the INCLUDING CONTENTS clause might generate a lot of rollback. As an alternative, use a script to drop the objects in the tablespace one by one and then drop the tablespace. Dropping Tablespace: Effects ·         To save disk capacity, you drop tablespaces that are no longer required. In this topic, you learn about the effects of dropping a tablespace. ·         The data of the dropped tablespace is not available anymore. Therefore, you cannot query the data of a dropped tablespace. ·         When you drop a tablespace, only the file pointers in the control file of the associated database are dropped. To reclaim the disk space used by the tablespace, you must delete the data files explicitly at the operating-system level. ·         Finally, if a read-only tablespace is dropped, segments within it are also dropped. Only the data dictionary and the control files need to be updated.
Multiple Tablespace: Benefits ·         Organizing data in tablespaces provides multiple benefits. In this topic, you learn about the benefits of using multiple tablespaces. ·         Using multiple tablespaces ensures that the user data and the data dictionary data can be kept separately. This enables flexible database operations. ·         You can also store the different application data in different tablespaces. This ensures a high availability of data. ·         The reduction in I/O contention is another benefit of using multiple tablespaces. The I/O contention is reduced by storing the data files of different tablespaces on separate disk drives. ·         Using multiple tablespaces ensures that a permanent loss of data is avoided. This is done by separating the rollback segments from the data segments. ·         Using multiple tablespaces provides a high degree of flexibility in data administration because individual tablespaces can be taken offline. This also ensures a high availability of the data. ·         Using multiple tablespace also ensures backing up of individual tablespaces. ·         Finally, using multiple tablespaces ensures that tablespaces can be reserved for a particular type of activity on the database, such as high-update activity, read-only activity, and temporary segment storage. This improves the efficiency of the database. Obtaining Tablespace Information ·         As a DBA, you require information about tablespaces to manage the Oracle database efficiently. In this topic, you learn the database views that access this information. ·         Data dictionary views and dynamic performance views enable you to gather information on tablespaces. The data dictionary views are: DBA_TABLESPACES and DBA_DATA_FILES. The dynamic performance view is V$DATAFILE. ·         The DBA_TABLESPACES view contains information about the names, default storage parameters, types, and availability of all the tablespaces. Ex: For effectively managing the database, you obtain the location and the default storage parameters for all tablespaces. Oracle SQL*Plus Worksheet has been opened. -          To obtain the names and default storage parameters of all the tablespaces in the ORCL database, type SELECT * FROM dba_tablespaces; and press F5 now. ·         The DBA_DATA_FILES view contains information about the data files that belong to a specific tablespace and their AUTOEXTEND settings. Ex: To view the tablespaces to which the data file belongs and the setting of the AUTOEXTEND command option, type SELECT * FROM dba_data_files; and press F5 now. ·         The V$DATAFILE view contains information on the name of the data file, the size, the availability, and the tablespace names to which the data files belong.
Storage Structures Database Storage Hierarchy In this topic, you learn about how an Oracle database stores data. Using efficient data storage methods can maximize the speed that data can be retrieved from the database. The database storage hierarchy consists of various components such as tablespaces, segments, and extents are displayed. The first component in the storage hierarchy is the database, which is logically divided into tablespaces. The tablespace is the second component in the storage hierarchy. The tablespace is used to group related logical structures. For example, tablespaces commonly group all objects of an application to simplify certain administrative operations. The third component in the storage hierarchy is the data file, and each tablespace contains one or more of these. The data files are the physical structures that store data. The combined size of the data files in a tablespace is the total storage capacity of the tablespace. The combined storage capacity of all the tablespaces in a database is the total storage capacity of the database. A tablespace may contain one or more segments. A segment, the fourth component in the storage hierarchy, is the space used by one logical structure, such as a table or an index. When a segment is created, it consists of at least one extent. The fifth component in the storage hierarchy is the extent. It is a contiguous set of blocks used to store a specific type of information. Extents are added when a segment requires more space. The last component in the storage hierarchy is the Oracle block. It is the smallest unit of input/output. When data needs to be retrieved from the disk, the Oracle server requests one or more Oracle blocks. Its size should be a multiple of the operating system block size. A data block corresponds to a specific number of bytes of physical database space on the disk. For each Oracle database, a data block size is specified when the database is created. Segments: Types In this topic, you learn about the uses and characteristics of each type of segment. You create segments in a database to store, retrieve, and maintain data. A segment is a space-occupying object in the database and is made up of one or more extents. The different types of segments are: 1- A table, also known as a non-partitioned table, is the most common means of storing data within a database. Data within a table is stored in a random manner, and the database administrator has very little control over the location of rows within the blocks in a table. Note: All the data in a non-partitioned table must be stored in one tablespace. 2- In a partitioned table, the data of a large table is distributed to different tablespaces in logical units called partitions. If a table is partitioned, each partition is stored in a separate segment. The storage parameters can be specified to control each table partition independently, improving the scalability and availability of data. 3- A cluster contains one or more tables. The rows of these tables are stored on the basis of their key column values. The tables in a cluster are stored in a single segment and share the same storage characteristics. 4- In an index-organized table, data is stored within the index on the basis of data key value. All the data in an index-organized table can be retrieved directly from the index tree. The entire index-organized table is stored in a single segment. 5- One or more columns in a table can be used to store large objects (LOBs), such as images. LOBs are stored in separate segments within a database by using LOB segments or externally by using binary files (BFILES). The location of the LOB data in a database is indicated by a pointer or a locator in the table. 6- A LOB index is created implicitly when a LOB segment is created. The LOB index is used by the Oracle server to retrieve or manipulate LOB data. You can specify the storage characteristics of a LOB index. 7- A nested table is a table within a table. In a nested table, a column can contain another separate table. A nested table is always stored as a separate segment. 8- Each index in an Oracle database is allocated a single segment to hold all the entries for a particular index. For example, if a table has three indexes, three different index segments are used. An index is used to retrieve the location of rows in a table for a given set of key values. 9- An index can be partitioned and spread across several tablespaces. Each partition in an index corresponds to a segment and cannot span multiple tablespaces. Index partitions help minimize contention by spreading the index I/O. Partitions also minimize unavailability during maintenance operations. 10- A rollback segment is used to maintain read consistency, support long-running queries, and process update operations in the database. When a change is made, the old value is stored in the rollback segment before changing the data or index blocks. This process enables a user to undo the changes made to the database, if necessary. 11- A user may execute commands that require sorting. If the data to be sorted is larger than the memory allocated for the sort operation, then the Oracle server allocates a temporary segment in a tablespace specified for the user. The server uses this temporary segment to store the intermediate results while executing a command. 12- A bootstrap segment, also known as the cache segment, is created by the script SQL.BSQ during database creation. This segment helps to initialize the data dictionary cache when the database is opened by an Oracle instance. Note: The bootstrap segment cannot be queried or updated. It does not require any maintenance by a database administrator (DBA).
Extent Allocation Control In this topic, you learn about extent allocation control. The Oracle server allocates an extent to a segment when the existing space in the segment is full. The procedure for controlling extent allocation to a segment is defined by specifying storage parameters at the segment level. The storage parameters used for extent allocation are based on certain precedence rules. Any storage parameter specified at the segment level overrides the corresponding option set at the tablespace level. When storage parameters are not explicitly set at the segment level, they inherit the default values for the storage parameters set at the tablespace level. Note: If the DEFAULT STORAGE clause is not specified for the tablespace, the tablespace inherits the default specified for the Oracle server. If the storage parameters are changed, the new options pertaining to the storage parameters apply to subsequently allocated extents. Some parameters cannot be specified at the tablespace level. These parameters can be specified only at the segment level. The minimum extent size specified for the tablespace applies to all the extents that are subsequently allocated for segments in the tablespace.
Free Space: Coalescing In this topic, you learn about coalescing free space. An extent is a logical unit of database storage space allocation and is made up of a set of contiguous data blocks. One or more extents constitute a segment. Extents are allocated to and deallocated from a segment under certain conditions. The conditions for extent allocation when segments are created, extended or altered, and for extent deallocation when segments are dropped, altered, truncated or automatically resized. As segments are created, space is allocated for the segment from the free extents in a tablespace.Contiguous space used by a segment is known as a used extent. When segments release space, the extents that are released are added to the pool of free extents available in the tablespace. Contiguous space may be released when several extents are deallocated within a tablespace, for example when two tables are dropped. The contiguous extents may be coalesced into one extent under the following conditions: -         When the System Monitor (SMON) initiates a space transaction to merge adjacent free extents. -         When the oracle server needs to allocate an extent that needs space from more than one adjacent free extent. -         When requested from the DBA.  Extents in a tablespace can be coalesced by using Oracle SQL*Plus Worksheet. You can query theDBA_FREE_SPACE_COALESCED view to determine whether you need to coalesce free extents within a tablespace. Use the ALTER TABLESPACE command to coalesce all the available free space in the extents of a tablespace into larger contiguous extents. The ALTER TABLESPACE command is: ALTER TABLESPACE tablespace COALESCE; Ex: Coalesce the free space in the extents of the RBS tablespace by using Oracle SQL*Plus Worksheet. SQL> ALTER TABLESPACE rbs COALESCE; Fragmentation Propensity: Object In this topic, you learn about the different types of segments that have varying propensities for fragmentation. To optimize the use of space and avoid fragmentation, it is recommended that the different types of segments be placed in different tablespaces. The recommended structure for the tablespaces, their objects, and their fragmentation propensity are followed: Organizing tablespace based on fragmentation propensity Tablespace                          Usage                                   Fragmentation SYSTEM                                Data dictionary                      Zero TOOLS                                   Applications                           Very Low DATAn                                   Data segments                      Low INDEXn                                  Index segments                    Low    RBSn                                      Rollback segments               High TEMPn                                   Temporary Segments           Very High Data dictionary objects, with the exception of the audit table, are not usually dropped and have a low propensity for fragmentation. The space used for the repositories of applications, such as Oracle Enterprise Manager and Oracle Designer, is only deallocated while reorganizing these structures. Repositories of applications have a low propensity for fragmentation compared to the reorganization of user tables. Data and index segments used for user-written applications might need to be reorganized more frequently than repositories. The rollback segments are likely to cause fragmentation because they are allocated and deallocated frequently. Temporary segments in permanent tablespaces may be released quite frequently. Therefore, the chance of fragmentation in these tablespaces is very high. To reduce the chances of fragmentation, the temporary segments are required to be located in separate tablespaces.
Data Blocks: Contents In this topic, you learn about the contents of an Oracle data block. The Oracle server stores data in data blocks. A data block is the smallest unit of I/O used by a database. There are various features associated with a data block. It is the minimum unit of I/O and consists of one or more O/S blocks. It is set by the DB_BLOCK_SIZEinitialization parameter upon database creation. A data block is made up of three parts. These include the block header, free space, and data space. The block header contains the data block address, table directory, row directory, and transaction slots. The transaction slots are used when transactions make changes to rows in the block. The free space is located in the middle of the data block. It enables the growth of the block header and the data space. The free space is initially contiguous, but deletions and updates can fragment the free space in the block. The free space is coalesced by the Oracle server when necessary. The data space contains row data. Row data can be table data or index data. Data is inserted into the block from the bottom up. Block Space Utilization Parameters In this topic, you learn about the block space utilization parameters that control the use of space in data and index segments. There are parameters that control concurrency and parameters that control the use of data space. Concurrency means that you have simultaneous access to data. The parameters controlling concurrency are INITTRANS and MAXTRANS. The INITTRANS parameter specifies the initial number of transaction slots that are created in an index or a data block. The transaction slots are used to store information about the transactions that have made changes to the block. The INITTRANS parameter, which has the default value of one for a data segment and two for an index segment, guarantees the minimum level of concurrency. For example, if the value of the INITTRANS parameter is set to three, it ensures that space for three transaction slots is reserved to enable three transactions to concurrently make changes to the block. Additional transaction slots can be allocated from the free space in the block to allow more concurrent changes. The MAXTRANS parameter specifies the maximum number of transaction slots that are created in an index or a data block. The MAXTRANS parameter has a default value of 255. The MAXTRANS parameter sets the limit for the number of concurrent transactions that can make changes to a data or index block. The value of MAXTRANS restricts the use of space for transaction slots. This guarantees that there is sufficient space in the block for use by the row or index data. The block space utilization parameters that control the use of data space within a block are PCTFREE and PCTUSED. The PCTFREE parameter specifies the percentage of space in each data block that is reserved for growth resulting from updates of rows in that data block. This parameter has a default value of 10 percent. For example, the value of the PCTFREE parameter is specified as 20 in a CREATE TABLE statement. This indicates that inserts to the block should stop as soon as free space drops to 20 percent or less. The free space thereafter can only be used for updates. The PCTUSED parameter represents the minimum percentage of the used space that the Oracle server tries to maintain for each data block of the table. This parameter has a default value of 40. When a data block is filled to the limit determined by the value of the PCTFREE parameter, the Oracle server considers the block unavailable for the insertion of new rows. The block remains unavailable for the insertion of new rows until the percentage filled by the data of that block is less than the value of the PCTUSED parameter. Until the percentage of the block falls below the value of the PCTUSED parameter, the Oracle server uses the free space in the data block only for updating the rows contained in the data block. For example, if PCTUSED is defined as 40 percent, the block is reused for inserts as soon as utilization drops to less than 40 percent. Insertions continue until utilization reaches 80 percent and the cycle repeats.
Obtaining Information About Storage Structures You can determine storage information for database objects from many sources in the data dictionary. There are several data dictionary views associated with tracking information about structures for storage in the database, such as tablespaces, extents, and segments. In addition, there are dictionary views for the database objects that offer information about space utilization settings. The names of dictionary views are usually taken from the objects represented by the data in the dictionary view, preceded by classification on the scope of the data. Each segment has its own data dictionary view that displays the storage information. Assuming that the DBA wants to know the storage parameters set for all objects on the database, the DBA may use the following views to determine storage information for the segment types already discussed: • DBA_SEGMENTS This summary view contains all types of segments listed by the data dictionary views and their storage parameters. • DBA_TABLESPACES You can use this view to see the default storage settings for the tablespaces in the database. • DBA_TS_QUOTAS You can use this view to identify the tablespace quotas assigned for users to create objects in their default and temporary tablespaces. • V$TABLESPACE This gives a simple listing of the tablespace number and name. • DBA_EXTENTS You use this view to see the segment name, type, owner, name of tablespace storing the extent, ID for the extent, file ID storing the extent, starting block ID of the extent, total bytes, and blocks of the extent. • DBA_FREE_SPACE This view identifies the location and amount of free space, by tablespace name, file ID, starting block ID, bytes, and blocks. • DBA_FREE_SPACE_COALESCED This view identifies the location of free space in a tablespace that has been coalesced, by tablespace name, total extents, extents coalesced, percent of extents that are coalesced, as well as other information about the space in the tablespace that SMON has coalesced. • DBA_DATA_FILES This view gives information about datafiles for every tablespace. • V$DATAFILE This view gives information about datafiles for every tablespace. Ex: You retrieve the information to check the current number of the extents and blocks allocated to a segment. You query the DBA_SEGMENTS view for the SEGMENT_NAME, TABLESPACE_NAME, EXTENTS, and BLOCKS columns for the segments that are owned by SCOTT by using Oracle SQL*Plus Worksheet. SQL> SELECT segment_name, tablespace_name, extents, blocks FROM dba_segments WHERE owner = 'SCOTT'; Ex: In this exercise, you retrieve the information about the extents for the EMP segment owned by SCOTT. You query the DBA_EXTENTS view for the EXTENT_ID, FILE_ID, BLOCK_ID, and BLOCKS columns. SQL> SELECT extent_id, file_id, block_id, blocks FROM dba_extents WHERE owner = 'SCOTT' AND segment_name = 'EMP'; Free Space Information In this topic, you learn to verify how much free space is available in a tablespace. The availability of free space in each tablespace of the database can be verified by using the DBA_FREE_SPACE view. The DBA_FREE_SPACE view makes use of certain columns to display the location of free blocks in a tablespace. These columns include tablespace_name, relative_fno, file_id, block_id, and blocks. Ex: You check the number of free extents and blocks in each tablespace by querying the DBA_FREE_SPACE view. The columns to be displayed are TABLESPACE_NAME, COUNT(*), and SUM(BLOCKS). You sort the information using the TABLESPACE_NAME column. SQL> SELECT tablespace_name, COUNT(*), SUM(blocks) FROM dba_free_space GROUP BY tablespace_name; SQL> SELECT tablespace_name,  blocks FROM dba_free_space where tablespace_name='RBS'; You retrieved information on the free blocks in all the tablespaces. Notice that the tablespace RBS contains 20 free extents with the total of 6849 blocks.
Summary Understand how tablespaces and datafiles relate to one another. A tablespace can have many datafiles, but each datafilecan associate with only one tablespace.  At database creation, there is one tablespace—SYSTEM. The DBA should not place all database objects into that tablespace, because often their storage needs conflict with each other. Instead, the DBA should create multiple tablespaces for the different segments available on the database and place those objects into those tablespaces. A final tablespace you should create to separate your application data from objects created in support of your Oracle database administrative tools, such as Oracle Enterprise Manager, is the TOOLS tablespace. The different types of segments (and tablespaces you need) are table, index, rollback, and temporary. When a segment containing a database object cannot store any more data for that table, Oracle will obtain an extent to store the data. This adversely affects performance. Understand the discussion of how to weigh segment preallocation against allowing Oracle to acquire new extents. There are two types of temporary segments: temporary segments for permanent tablespaces and temporary segments for temporary tablespaces. Oracle's use of temporary segments was reworked to make disk sorts more efficient. Sort segments in temporary tablespaces are allocated for the first disk sort and then persist for everyone's use for the duration of the instance, The result is less fragmentation than is the case in temporary segments in permanent tablespaces. A new memory area called the sort extent pool manages how user processes allocate extents for disk sorts in temporary tablespaces. You cannot create permanent database objects, such as tables, in temporary tablespaces. You also cannot convert permanent tablespaces into temporary ones unless there are no permanent objects in the permanenttablespace. The size of extents in the temporary tablespace should be set to a multiple of sort_area_size, plus one additional block for the segment header, in order to maximize disk sort performance. Understand the inverse proportional relationship between the lifespan of extents and fragmentation in the tablespace—the shorter the lifespan, the higher potential for fragmentation in the tablespace.
Review Questions 1. What items comprise the logical disk storage resources in Oracle? What items comprise the physical disk storage resources in Oracle? 2. How do physical disk storage resources map to logical disk storage resources in your Oracle database? 3. What are locally managed tablespaces, and what advantage does this feature have over dictionary managed tablespaces? 4. Identify two ways that tablespaces are created in Oracle. 5. Identify some ways that the size of a tablespace can be changed in Oracle. 6. Issuing the alter database datafile filename offline drop statement has what effect on your tablespace size? 7. What are the two types of temporary segments? What are the two types of tablespaces? 8. How do you determine the appropriate size for extents in the temporary tablespace? Why is it important that this size be a multiple of SORT_AREA_SIZE? 9. What is the importance of creating multiple temporary tablespaces for different sorting needs? 10. What views are available for obtaining information about temporary and sort segments? 11. What are some of the statuses a tablespace can have, and how can you change them? 12. Identify several types of segments available for storing database objects. 13. Why is it important not to put all database objects in the SYSTEM tablespace? 14. Should different types of database segments be stored in the same tablespace or in different tablespaces? Why or why not? 15. What must happen in order for Oracle to allocate an extent to a database object? 16. On what parameters does the size of the extent allocated to a database object depend? 17. What statement can be used to change the size of the next extent allocated to a database object? 18. What dictionary view will tell the DBA the default settings for database objects in a tablespace? What dictionary view tells the DBA the quotas for space ontablespaces each user has? 19. What views might be used to see how much space is free in a tablespace?
Practice Test & Answers 1. The Oracle background process that handles periodic coalescence of free space in a tablespace:________________________ 2. The keyword that prevents you from creating a table in a tablespace marked for use when you run select, order by statements on millions of rows of output is which of the following choices? A. lifespan                  B. permanent                  C. online               D. offline             E. temporary        F. read only 3. A high pctused: A. Increases performance costs by forcing Oracle to place the block on freelists frequently B. Increases performance costs by forcing Oracle to place the block on freelists rarely C. Decreases performance costs by forcing Oracle to place the block on freelists frequently D. Decreases performance costs by forcing Oracle to place the block on freelists rarely 4. To control the allocation of additional extents for a table or index, which of the following choices is most appropriate? A. Make the next extent be allocated as high as possible. B. Specify a high pctused. C. Specify a low pctfree. D. Make the initial segment allocated large enough to accommodate all data plus growth. 5. To determine the space allocated for temporary segments, the DBA can access which of the following views? A. DBA_TABLESPACES        B. DBA_TABLES       C.DBA_SEGMENTS      D. DBA_FREE_SPACE 6 . Which two of the following choices will decrease segment lifespan in the Oracle tablespace? (Choose two) A. Frequent truncate table operations B. Frequent insert operations C. Frequent drop table operations D. Frequent alter table operations 7. The process that most directly causes fragmentation in a tablespace storing temporary segments because it deallocates segments used for disk sorts is which of the following choices? A. Server                 B. DBWR                              C. SMON                                D. LGWR Answer: 1. SMON          2. E    3. A   4. D     5. C    6. A and C   7. C