|
Step 6 - Creating a Tablespace
|
Learn Oracle - Creating a Tablespace
A particular user, groups of users, or an application may require its own tablespace for
creating schema objects, rather than using the default USER tablespace. To create a
tablespace, follow the steps listed in this section. This exercise will also familiarize Oracle DBA
with the various attributes that Oracle DBA can set, and possibly later modify, for a tablespace.
1. From the Administration page, click Tablespaces under Storage. This takes Oracle DBA to
the Tablespaces page.
2. Click Create. Or, if Oracle DBA want to create a tablespace that is like an existing
tablespace, select an existing tablespace. Then from the Actions menu, select
Create Like.
The Create Tablespace General page appears.
Did you notice the other options that are available from the Actions pull down
menu? These are shortcuts for various tablespace operations.
3. Enter a name for the tablespace. For example: TB1
4. Under the Extent Management heading, select Locally Managed.
A locally managed tablespace’s extents are managed locally and efficiently within
the tablespace by Oracle. For a dictionary managed tablespace, you must more
actively manage extents and data dictionary access is required for tracking them.
Dictionary managed tablespaces are being deprecated. Oracle does not
recommend their use.
5. Under the Type heading, select appropriate type.
Permanent tablespaces store permanent database objects created by the system or
users.
Temporary tablespaces are for storing temporary segments, as would be created
when SQL statements perform sorts. The preconfigured database already has a
temporary tablespace. Oracle DBA would create another temporary tablespace if Oracle DBA were
creating a temporary tablespace group. Under normal circumstances, Oracle DBA should
not need to create additional temporary tablespaces.
Undo tablespaces are used by the system to store undo information. Oracle DBA might
want to create another undo tablespace, but only one undo tablespace can be the
active one.
Again, Oracle DBA should not need to create additional undo tablespaces. However, if you
want to switch the undo tablespace used by the database instance, then you can
create a new undo tablespace and instruct the database to use it instead of the
undo tablespace currently in use. The undo tablespace no longer in use can be
dropped later.
6. Under Status, select Read Write.
Read-write status means users can read and write to the tablespace after it is
created. This is the default.
If the tablespace was created read-only, then the tablespace could not be written to
until its status is changed to read/write. You would probably not create a
tablespace as read-only, but rather change it to that status after Oracle DBA have written
data to it that Oracle DBA do not want modified.
If the tablespace was created offline, then no users could have access to it. You
would probably not create a tablespace as offline, but rather you would change its
status to offline when, for example, you wanted to perform some maintenance on
its underlying files.
7. In the Datafiles section of the page, Oracle DBA can add one or more datafiles to the
tablespace. However, if Oracle DBA select Use bigfile tablespace, then the tablespace can
have only one datafile. Click Add.
Bigfile tablespaces are used with ultralarge databases where Oracle’s Automatic
Storage Management or other logical volume managers support striping or RAID,
and dynamically extensible logical volumes.
8. In the Add Datafiles page, enter a file name. For example: TS101.dbf. Use the File
Directory and File Size defaults.
At least one datafile is required for the SYSTEM and SYSAUX tablespaces of a
database. Your database should contain several other tablespaces with their
associated datafiles or tempfiles. The number of datafiles that you anticipate
creating for your database can affect the settings of initialization parameters and
the specification of CREATE DATABASE statement clauses. Be aware that your
operating system might impose limits on the number of datafiles contained in
your Oracle database. Also consider that the number of datafiles, and how and
where they are allocated can affect the performance of your database.
9. To allow Oracle to automatically grow the datafile, under Storage, select
Automatically extend datafile when full (AUTOEXTEND) and specify an
amount in the Increment field by which you want to extend the datafile each time
it fills. Leave the Maximum File Size set to Unlimited. Click Continue.
You are returned to the Create Tablespace General page.
10. Click the Storage property page.
The Create Tablespace Storage page appears.
Oracle DBA can manually resize a datafile with the ALTER DATABASE statement. This lets
Oracle DBA add more space to your database without adding more datafiles, if Oracle DBA are
concerned about reaching the maximum number of datafiles allowed in your
database. For a bigfile tablespace, use the ALTER TABLESPACE statement. You
cannot add a datafile to a bigfile tablespace. Manually reducing the sizes of
datafiles lets you reclaim unused space in the database. This is useful for
correcting errors in estimates of space requirements.
11. Leave all of the defaults set on the Storage page. See online help for more
information on these settings.
12. Click the Thresholds property page.
The Thresholds page appears. This page lets Oracle DBA set monitored thresholds for
space usage. Oracle DBA receive a warning when the threshold is reached. You can accept
the default, specify different thresholds, or disable threshold warnings. See
Chapter 10, "Monitoring and Tuning the Database" for more information about
monitoring the database.
13. After determining thresholds, click OK to add the tablespace. The Tablespaces
page appears where Oracle DBA can see your new tablespace in the Results section.
|
|
|
