Learn Oracle


I have created this learn oracle "The step 10 process" Oracle Tutorial to help Database aspirants to accomplish their dream of working as Oracle Database administrator. I have tried to keep it simple and User friendly. Use NEXT and PREVIOUS Button to navaigate away and to a chapter. Since i myself had been a Database adminstrator trainer and has got real life experience of working as a Oracle DBA, I would welcome any sort of queries and doubts or modifications to webmaster@oracleonline.info.

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.

Oracle Tutorial - BLOGS

STEP 6 - Next Topics
This Step discusses management of the database's storage structure. Click on Oracle tuorial links below:
The Control FileControl file
Redo Log FilesRedo log file
Archive Log FilesArchive log file
Initialization Parameter FileParameter file
Password FilePassword file
Backup FilesBackup file
Viewing Tablespaces in your DatabaseTablespace
Creating a tablespacecreating tablespace
Modifying a Tablespace
Dropping a Tablespace
Managing Undo for Your Database
Oracle Database - PODCASTS
More Tutorials on Oracle dba ...

Liked it ? Want to share it ? Social Bookmarking
Add to: Mr. Wong Add to: BoniTrust Add to: Newsider Add to: Digg Add to: Del.icio.us Add to: Reddit Add to: Jumptags Add to: StumbleUpon Add to: Slashdot Add to: Netscape Add to: Furl Add to: Yahoo Add to: Spurl Add to: Google Add to: Blinklist Add to: Technorati Add to: Newsvine Information

Source : Oracle Documentation | Return to Learn Oracle

Want to share or request Oracle Tutorial articles to become a Oracle DBA. Direct your requests to webmaster@oracleonline.info