Instructor's Notes

(If applicable)

• Lesson 12: Creating and Managing Tablespaces
• In this lesson you will learn a lot of things about tablespaces. First, we will review the basic concepts (you already learned a little bit about tablespaces in the Architecture, Part I lesson). Then, you will learn how to create a locally managed tablespace (and what the term “locally managed” means). From there, we will discuss a new feature called Oracle Managed Files and lots of other necessities about tablespaces.
• Let’s get started…

Instructor's Notes

(If applicable)

• In the lesson Architecture, Part I, you were introduced to the concept of tablespaces. Let’s take a moment to review the basics….
• A tablespace is a logical database object. It is an association to one or more database files (datafiles).
• Earlier, also in the Architecture, Part I lesson, we learned that tablespaces contain objects called a segment. A segment is the storage used by one database object such as a table or index. (There are exceptions, e.g. clusters can combine rows of two or more tables into the same segment.) The segment name is the same as the object name.
• The data dictionary view USER_SEGMENTS records the relationship between tablespace and segment:
• SQL> select tablespace_name, segment_name, segment_type
• 2 from user_segments;

Instructor's Notes

(If applicable)

• As always, the data dictionary records information about all the objects within the database. DBA_TABLESPACES contains one row for every tablespace. Here is a description of the columns shown in this example:
• STATUS – Current status of the tablespace. Can be one of the following values:
• ONLINE – objects in the tablespace can be read and updated.
• READ ONLY – objects in the tablespace can only be read (selected).
• OFFLINE – objects in the tablespace cannot be accessed. Can be useful, for example, when recovering the tablespace.
• CONTENTS can be:
• UNDO – Used only for undo (rollback) segments. You’ll learn more about UNDO segments in the Managing UNDO lesson.
• PERMANENT – Used for permanent system and user objects.
• TEMPORARY – Can only contain temporary segments for sorting and external tables.
• DBA_DATA_FILES shows the relationship between tablespace and datafile (one tablespace can contain many datafiles). In this example, we see that tablespace EXAMPLE contains two datafiles.

Instructor's Notes

(If applicable)

• All database objects that require disk storage are assigned to a tablespace. This includes tables, clusters, indexes, materialized views, temporary segments and undo segments. Objects such as views, synonyms, sequences do not require disk storage (other than for a small definition in the data dictionary) and therefore are not assigned to a tablespace.
• Why does Oracle provide the tablespace object? Well, by assigning objects to a tablespace (rather than each object have it’s own datafile), we can group related objects together. Thus we can manage the related objects together. For example, you can export a tablespace, backup and recover a tablespace, even take a tablespace offline (making all objects in the table inaccessible). Tablespaces also provide control over which disk device is used for an object. However, we do not have exact control over placement within the tablespace (e.g. within a 100MB datafile).

Instructor's Notes

(If applicable)

• Locally managed tablespaces were introduced with Oracle8i and are a great leap forward in tablespace technology. They are a replacement for dictionary managed tablespaces. They differ in the way they managed used and free extents. Why are they better? Well, lets compare them:
• Dictionary managed tablespaces use dictionary tables (SYS.UET$ and SYS.FET$) to track used extents and free extents. These dictionary tables need to be updated when extents are allocated or freed. This is an inefficient bottleneck. Another inefficiency comes from the SMON background process periodically scanning dictionary managed tablespaces to look for coalesce opportunities; i.e. if SMON sees two adjacent free extents it combines them into one larger free extent.
• Locally managed tablespace creates a 64k bitmap in each datafile header. The bits represent used and free extents. Changing bits from 0 to 1 to allocate an extent or 1 to 0 to free an extent is significantly more efficient than the dictionary technique. Additionally, the SMON coalesce is not performed.
• Note that, if the SYSTEM tablespace is locally managed, all other tablespaces must be locally managed; Oracle will not allow creation of a dictionary managed tablespace. DBA_TABLESPACES records the type of tablespace, “DICTIONARY” or “LOCAL”.

Instructor's Notes

(If applicable)

• Tablespace Basics Workshop
  1. What tablespaces currently exist on your database and what type of data do they contain?
  2. What database files exist on your database and what tablespace do they belong to?
  3. What type of tablespace is your system tablespace, dictionary managed or locally managed?
  4. Question: If the system tablespace is an LMT, what type of tablespace will all other tablespaces have to be?

Instructor's Notes

(If applicable)

• When you create a database object that requires disk space (e.g. tables and indexes), you should specify a tablespace parameter to control what tablespace the object should be created in. If the tablespace parameter is not specified, the object is created in the user’s default tablespace.
• To create an object in a tablespace, a user needs quota on the tablespace or the UNLIMITED TABLESPACE system privilege. If necessary, review the lessons Creating and Managing Users and Managing Security for more information on quota and system privileges.
• Supplemental Notes - Review
  • You learned in the lesson Creating and Managing Users that the DBA_USERS dictionary view shows the default tablespace for a user.
  • SQL> select default_tablespace
  • 2 from dba_users
  • 3 where username = 'DAVE';
  • DEFAULT_TABLESPACE
  • ------------------------------
  • USERS

Instructor's Notes

(If applicable)

• Creating tablespaces is done with the CREATE TABLESPACE statement. This example creates a “system-managed LMT”. Let’s discuss each parameter in turn (the SEGMENT SPACE MANAGEMENT clause will be discussed later in this lesson):
• The DATAFILE parameter specifies the path, name and size of the operating system file. This can be omitted if the Oracle Managed Files (OMF) feature is enabled. You will learn more about OMF later in this lesson. Size is specified in bytes, kilobytes or megabytes.
• The AUTOEXTEND ON feature (the default is OFF) causes Oracle to allocate additional space to the datafile if needed. The NEXT parameter defines how much.
• The MAXSIZE parameter sets an upper limit on the datafile size. The default MAXSIZE is UNLIMITED which I recommend you avoid because the file could gobble up all the available space on a disk or mount point.
• The clause EXTENT MANAGEMENT LOCAL clause creates a locally managed tablespace, as opposed to a dictionary managed tablespace. Remember, if the SYSTEM tablespace is an LMT, all other tablespaces have to be an LMT and EXTENT MANAGEMENT LOCAL is the default.

Instructor's Notes

(If applicable)

• When creating a locally managed tablespace, you can choose to have the system determine the extent sizes for all objects subsequently created in the tablespace (called system-managed extents). This is accomplished with the AUTOALLOCATE parameter. (AUTOALLOCATE is the default for locally-managed tablespaces and need not be specified.) AUTOALLOCATE is generally best when you do not know how big the tablespace will become.
• Oracle usually* uses the following extent sizes:
• Extent sizes for first and next extents is 64K until the table reaches 1Mb in size.
• Oracle will then create extents 1M in size until the table reaches a total size of 64Mb.
• Extents of 8Mb in size are then allocated until the table grows to 1G in size, after which Oracle will allocates 64Mb extents.
• *Oracle claims that these extent sizes are not always used, and that there is an undocumented internal formula, but I have not seen it vary from this. Admittedly, I have not tested enough scenarios to doubt that it can vary.
• Refer to the supplied script TS1.SQL for sample code illustrated here.

Instructor's Notes

(If applicable)

• A UNIFORM SIZE locally managed tablespace allocates the exact same extent size for all objects created within the tablespace . Use this option if you want total control over sizes of objects within the tablespace. UNIFORM SIZE is generally best when you DO know how big the tablespace will become.
• You can query the USER_EXTENTS view to see the affect of the UNIFORM SIZE parameter:
• SQL> create table t2 (c1 number )
• 2 tablespace ts2;


• Table created.


• SQL> select bytes, blocks from user_extents
• 2 where segment_name = 'T2';

• Notes continue on the next page…

Instructor's Notes

(If applicable)

• SQL> alter table t2 allocate extent;


• Table altered.


• SQL> select bytes, blocks from user_extents
• 2 where segment_name = 'T2';


• BYTES BLOCKS
• ---------- ----------
• 10485760 1280
• 10485760 1280


• See the supplied script TS2.SQL for a working example of this code.

Instructor's Notes

(If applicable)

• Oracle9i Release 1 introduced a new feature called “automatic segment space management” (ASSM), which use bitmaps to track the amount of free space on the blocks within a segment. ASSM is enabled for a tablespace with the “SEGMENT SPACE MANAGEMENT AUTO” clause.
• ASSM eliminates the need to code and tune parameters PCTFREE, PCTUSED, FREELIST and FREELIST GROUPS. While we will not cover the “old” way of managing segment storage in detail in this course, here’s a quick overview:
• A “freelist” is a list of blocks in an table (i.e. segment) that will accept new rows. Each table has one or more freelists. When an INSERT statement is executed, the transaction will access the freelist to find blocks that are accepting rows. If more than one transaction is inserting rows at the same time, contention for the freelist will occur. In this situation, allocating more than one freelist might be advisable. However, this can cause the object to grow unnecessarily (why this is so is outside the scope of this course; refer to Chapter 4 of Tom Kyte’s, Effective Oracle by Design text book for a discussion of this.)


• Notes continue on the next page…

Instructor's Notes

(If applicable)

• PCTUSED controls when a block is put on the freelist; when the used space on a block falls below the PCTUSED value, the block is put on the freelist.
• PCTFREE controls when a block is removed from the freelist; when the space available falls below the PCTFREE value, the block is removed from the freelist.
• Freelist groups (i.e. FREELIST GROUPS parameter) were usually used in Oracle Parallel Server (now RAC) environments, so that each instance could have its own set of freelists.

Instructor's Notes

(If applicable)

• Tuning the FREELIST, PCTFREE and PCTUSED parameters can be a difficult task (many DBA’s just ignored FREELIST and PCTUSED). We can see from these examples that creating heap tables is much simpler when ASSM is used. We eliminate the need to code the FREELIST, FREELIST GROUPS and PCTUSED parameters. In fact, they are ignored when a table is created. However, PCTFREE may still have some value at initial creation time – to save some amount of space for row expansion and thus reduce row migration. More research on this is warranted.
• See the Oracle notes on this here:
  • http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/tables.htm#246
• CREATE TABLE parameters (including STORAGE parameters) will be discussed in detail in the Tables lesson later in this course.

Instructor's Notes

(If applicable)

• The Oracle9i Oracle Managed Files (OMF) feature provides a default name, location and size for OS files related to the database, thereby eliminating the need for datafile specification on the CREATE TABLESPACE command.
• The OMF feature is enabled with the DB_CREATE_FILE_DEST initialization parameter (also see DB_CREATE_ONLINE_LOG_DEST_1 for redo log files). In the first example, I use the ALTER SYSTEM command to set the default location for datafiles to a desired OS path. The next example executes CREATE TABLESPACE without the DATAFILE clause, so the datafile is created in the default OS location, with a size of 100MB. The DATAFILE clause is only necessary if:
• the DB_CREATE_FILE_DEST initialization parameter has not been set.
• you want to override the default size and explicitly define a size for the datafile – as shown in the second CREATE TABLESPACE example.
• you want to override the default location.



• Notes for this slide continue on the next page…

Instructor's Notes

(If applicable)

• Supplemental Notes
• OMF Defaults
• File size: 100M
• Auto extensible: ON
• Max size: UNLIMITED
• File location: Defined by DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n parameters. The DB_CREATE_ONLINE_LOG_DEST_n parameter will be discussed in detail in the Managing Redo Logs lesson.
• All OMF file names begin with the prefix “ORA_” All file names, regardless of file type and operating system, contain a unique character string generated by Oracle. The rest of an OMF file name depends on the type of file and the operating system.
• Assume a datafile with this name:


• SQL> select file_name from dba_data_files
• 2 where tablespace_name = 'PRODUCT_TBS';
• FILE_NAME
• --------------------------------------------------
• C:\ORACLE\ORADATA\ORA91\ORA_PRODUCT__YG20QZ00.DBF


• The name can be broken down as follows:
• ORA_ generated by Oracle.
• PRODUCT_ tablespace name, up to 8 characters, including special characters such as _
• YG20QZ00 Unique character string generated by Oracle.
• .DBF file extension for a permanent or temporary tablespace. .LOG is used for log files, .CTL for control files.

Instructor's Notes

(If applicable)

• OMF can be used for tablespace files (datafiles), online redo log files, and control files. OMF is used when executing a command that creates or drops a file (e.g. CREATE DATABASE, CREATE TABLESPACE, DROP TABLESPACE, ALTER TABLESPACE, and more). When the DROP TABLESPACE command is executed, the OS file is also deleted. We’ll look at this in more depth later in this lesson.
• OMF can be configured for the entire database with initialization parameters or the ALTER SYSTEM command. OMF can also be configured at the session level with the ALTER SESSION command.
• OMF is optional; “normal” database file creation techniques are still supported. Additionally, the OMF feature can be used in conjunction with the normal file techniques.
• This feature has a limitation – the ability to have multiple default locations for spreading OS files across the disk array. Therefore, OMF is probably best suited for environments with just a few mount points (e..g striped RAID disk system), test platforms and perhaps small production databases. It might also be useful if you need to create Oracle installation scripts that will be run in remote environments.

Instructor's Notes

(If applicable)

• Oracle Flexible Architecture (OFA) is a suggested method of separating data by object type and object use. This aids administration and I/O load balancing. If you used the Database Configuration Assistant to help create your database, you probably are already using standard OFA tablespace layout.
• OFA suggests creating the following tablespaces:
• SYSTEM - This should contain &