Creating Tablespaces using Enterprise Manager

1.  Open Internet explorer and enter the URL for Enterprise manager (http ://<server ip address > :5500/em)

2.  Click on the Administration tab as shown in the figure 9 below.

Figure -9 Oracle Enterprise Manager Window

3.  Click on Tablespaces as shown in figure 10.

Figure -10 Administration Window

4.  The Tablespaces window appears as in figure 11. Click on Create button.

Figure -11 Tablespaces Window

5.  Enter the new tablespace name in the ‘Name’ field.

   Other controls on this form are explained below:

· Extent Management: This control allows the user to select the method used for extent management.

i. In ‘Locally Managed’ tablespaces, free space is managed in a bitmap at the head of the file. Also, the process of finding free space is faster.

ii. In ‘Dictionary Managed’ tablespaces, the segments can be of any size. This can (and frequently does) lead to free space fragmentation. This leads to slower free space allocation.

Figure -12 Create Tablespace

Type: Select whether you want this to be your permanent tablespace, temporary tablespace or undo tablespace. A short explanation on the these is below:

i. In case off a ‘Permanent’ tablespace, the extents are allocated and de-allocated using the data dictionary. We must make expensive, serialized recursive SQL calls to allocate extents in this tablespace and to free them when we are done.

ii. In case of a ‘Temporary’ tablespace, we will allocate an extent only once and then keep it. These extents will be managed not via the data dictionary but they are managed in memory. This avoids the expensive recursive SQL calls for allocating and de-allocating extents. You will never see an extent freed in this tablespace until you shutdown (or alter the tablespace in some way).

iii. This page allows you to create an ‘Undo’ tablespace and assign it to an individual instance. Under the ‘Undo’ option, you choose the instance from the ‘Assign It To Instance’ drop-down list. This option is part of the automatic management of rollback segments. 

Status: Select whether you want this tablespace to be ‘Read Only’, ‘Read Write’ or ‘Offline’. The read write table spaces allow both read and write operations while the read only tablespaces allow only read operation.

When you offline the tablespace, Oracle does not allow you to initiate a DML on any of the tables in that tablespace. However it allows for the transaction to be complete (i.e. commit or rollback). It stores the undo entries related to the active transaction in the deferred rollback segment in the SYSTEM tablespace when the tablespace goes offline. When the tablespace is back online, it applies those entries to the data blocks, hence completing the recovery. 

Datafiles: This region allows you to define the datafiles in this tablespace.

6. Click on the ‘Add’ button in the ‘Datafiles’ region (figure 13).

Figure -13 Create Tablespace (Add datafiles button)

7. Enter a ‘File Name’ for the new data file.

Other controls on this form are explained below: 

Storage: The ‘Automatically extend datafile when full’ option indicates whether you want your datafile to extend its size when it is full. If you select this option, you must fill up a value in the ‘Increment’ text field (how much the size of datafile should be extended every time it is full). Also, the ‘Maximum File Size’ can be specified. If you select the option ‘Unlimited’, then there is no limit on the max size of this file. If you select ‘Value’, then you need to fill in the max size for this datafile.

8.  Click ‘Continue’. The create tablespace window will be displayed again.

Figure -14 Create Tablespace (Add datafile window)

9. Click ‘OK’. The new tablespace is now created.

In the next tutorial we will learn about Managing Tablespaces, Altering Tablespace , Creating an ‘Undo’ tablespace and Renaming a Tablespace using SQL Plus, Using iSQL Plus and Enterprise Manager.

TrackBack URI for this entry

Comments (0)

Subscribe to this comment's feed

Show/Hide comments

Show/Hide comment form