Manipulating Tablespaces and Data Files with OEM


As always, when maintaining anything with OEM, you have to attach to a launched console, expand the databases folder, and find the database from the list that you will be working with.

Because we are working with tablespaces and data files, and both are considered to be storage, you need to find and expand the storage section.

Changing the Storage Settings

To change the storage settings, you need to expand the tablespaces folder, find the tablespace to which you need to make alterations, and right-click the tablespace. From the resulting pop-up menu, choose View/Edit Details, click the storage tab in the popped-up window, make any changes that you need to make, and click the Apply button; then click OK. Alternatively, you can simply select the tablespace to which you need to make changes, select the storage tab, make the changes, click Apply, and then click OK. Figure 10.2 shows the screen details where this is accomplished.

Figure 10.2. Altering storage settings via OEM.


Creating a Temporary Tablespace

Right-click the Tablespaces folder and select Create from the pop-up menu. Supply the details for your new tablespace on the General tab (name of the new tablespace, filename, or tempfile namein this case, statusand type for the new tablespace). Because we are creating a temporary tablespace, select the Temporary radio button from the Type region of the window.

Select the Storage tab and enter the relevant storage information. This includes locally managed or managed in the dictionary and extent allocation (Uniform for locally managed or with override settings for dictionary managed).

Click the Create button to complete the creation of the temporary tablespace.

Figure 10.3 shows the screen through which you create a tablespace. The only difference in OEM between creating an undo tablespace, a temporary tablespace, and a regular tablespace is which radio button at the bottom of the screen you select.

Figure 10.3. Tablespace creation screen.


Bringing a Tablespace Offline or Online

Expand the Tablespaces folder and select the tablespace that you want to bring online or offline. In the General tab, select the Online or Offline radio button or the Read-Only block. If you are taking the tablespace offline, you need to select, from the drop-down list next to Offline, the mode you want to use to bring it offline (Normal, Temporary, Immediate, For Recover). Click Apply.

Dropping a Tablespace

Expand the Tablespaces folder and select the tablespace that you want to drop. From the Main menu at the top of the console, select Object and from the drop-down menu select Remove. A splash screen appears from which you verify that you really do want to drop the tablespace in question. The screen tells you that when you drop the tablespace, the stuff in it goes too. You can also select a check box that allows you to delete the data files associated with the tablespace from the operating system. Your choices are Yes (you really do want to drop the tablespace), No (you don't really want to drop it), or Help. Figure 10.4 shows the warning screen instructing you on Oracle's suggestions when dropping tablespaces. Notice that you can choose to drop the data files at the same time, or to not drop them.

Figure 10.4. Drop Tablespace OEM screen.


Enabling or Disabling AUTOEXTEND

Expand the Datafiles folder and select the data file that you want to alter. In the storage tab of the data file, you can select, or deselect, the Automatically Extend Datafile When Full (AUTOEXTEND) check box. If you are enabling AUTOEXTEND, you can set the increment value for the file (how many kilobytes or megabytes you want to be added to the file with each automatic extend) as well as the Maximum Size. Click Apply, and the data file's automatic extensibility is altered. Figure 10.5 shows the screen on which you can select, or deselect, the Autoextend option for an individual data file. When choosing to set a data file to automatically extend, you can give it the parameters of what increments you want it to increment in (kilobytes or megabytes) and what the maximum size you want it to be able to grow to (again, in kilobytes or megabytes).

Figure 10.5. OEMs Autoextend screen.


Be aware that if you don't change the default behavior that OEM provides, Unlimited will be set as the file's maximum size.


Adding a Data File

Expand the Tablespaces folder, find the tablespace that you need to add a data file to, and right-click on the tablespace (or just select the tablespace). If you right-click on the tablespace, you need to select Add Datafile from the resulting menu. In the General tab, enter the information on the new file that you want to add to your tablespace and click Create (if you are using the right-click method) or Apply (if you are just using the click on the tablespace method). Figure 10.6 shows the screen on which you can specify the location of data files that you want to add to a tablespace and the location of the tab on which you can specify the storage parameters connected to the tablespace and its associated data files. Figure 10.7 shows you the options available on the storage parameters tab.

Figure 10.6. OEM Add Datafile screen.


Figure 10.7. OEM Add Datafile storage parameters.


Creating a Tablespace

You can create a tablespace using OEM as well as create tablespaces from the command line.

From a launched OEM console, expand the database that you are going to work on from the Database folder. Expand the storage folder and select the Tablespaces folder from within. Right-click on Tablespaces and from the right mouse menu, select Create.

In the General tab of the property sheet, enter the tablespace name that you are trying to create. In the Datafile region, specify the fully qualified data file or data files that you want to have associated with your new tablespace.

On the Storage tab of the property sheet, enter any relevant storage information that you want to have associated with your new tablespace, and click Create. (Refer to Figure 10.3 to see the tablespace creation screen.)

OEM is the first tool that Oracle provides to help with the administration of tablespaces; Oracle Managed Files (OMF) is the other. The next section looks at OMF and the role it plays in tablespace and data file maintenance.

We have come to the end of the historically standard way of manipulating tablespaces and their associated data files. But with OMF, there are interesting new ways of accomplishing this maintenance and management.



    Oracle 9i Fundamentals I Exam Cram 2
    Oracle 9i Fundamentals I Exam Cram 2
    ISBN: 0789732653
    EAN: 2147483647
    Year: 2004
    Pages: 244
    Authors: April Wells

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net