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 only the data dictionary, the SYSTEM rollback segment, and a second rollback segment, called SYSROL by default, that can be taken off-line or dropped after additional rollback segments have been created. No other object or activity should occur here. By default, a user has no quota on a tablespace. Check by query DBA_TS_QUOTAS. To eliminate quota on a tablespace from a user, execute the following:
• ALTER USER x QUOTA 0 ON system QUOTA 0 ON rbs;
• DATA - Create any number of DATA tablespaces (each must have a unique name). Each DATA tablespace will typically hold the tables for a specific application. However, usage patterns may dictate that you create multiple DATA tablespaces for a single application to further segregate your data.


• Notes continue on the next page…

Instructor's Notes

(If applicable)

• INDEXES - Create any number of INDEX tablespaces (each must have a unique name). Each INDEX tablespace will typically hold the indexes for a specific application. However, usage patterns may dictate that you create multiple INDEXES tablespaces for a single application to further segregate your data.
• TOOLS - Use this tablespace(s) as a repository for the objects created by Oracle or other vendor tools.
• RBS - Use this tablespace(s) for rollback segments. See the lesson on rollback segments for more information.
• TEMP - Many SQL and other database operations require sort operations. If the sort cannot be performed in memory (see database parameter SORT_AREA_SIZE), temporary disk storage is required. Insure that all users have their TEMPORARY TABLESPACE set to this tablespace:
• CREATE USER x IDENTIFIED BY y TEMPORARY TABLESPACE temp;
• USERS - In a development (i.e. test) database, users will often have the need to create test tables. Insure that these test tables are segregated by providing quota on a “USERS” tablespace, revoking quota if necessary on other tablespaces, and making USERS their default tablespace.
• CREATE USER x IDENTIFIED BY y
• DEFAULT TABLESPACE users
• QUOTA UNLIMITED ON users;

Instructor's Notes

(If applicable)

• Striping is a technique of distributing data across more than one disk device. This technique is used to distribute the I/O load over many disks and to take advantage of parallel processing (specifically parallel full table scans).
• Operating system software, vendor software or hardware often supplies striping technology. However, if you are on a very tight budget, you can use Oracle to do the striping for you. As shown in the slide, create a tablespace using the disk devices you want to stripe on. Oracle will automatically allocate extents in a round-robin fashion, as this example demonstrates:
• SQL> create table t (c1 number) tablespace test;


• Table created.


• SQL> alter table t allocate extent;


• Table altered.


• SQL> /


• This example continues on the next page…

Instructor's Notes

(If applicable)

• Table altered.


• SQL> /


• Table altered.
• SQL> select extent_id, blocks, file_id from dba_extents
• 2 where segment_name = 'T'
• 3 and owner=user
• 4 /


• Note that the FILE_ID changes for each extent allocated to the table. Use the DBA_DATA_FILES view to confirm the file names relating to the file id’s:
• SQL> select file_id, file_name from dba_data_files
• 2 where tablespace_name = 'TEST';


• Refer to the supplied script STRIPING.SQL for the code demonstrated here.

Instructor's Notes

(If applicable)

• In addition to the standard block size (defined by DB_BLOCK_SIZE), Oracle9i supports a maximum of 4 additional non-standard block sizes within a single database. You can choose from the following block sizes: 2k, 4k, 8k, 16k and, on some platforms, 32k.
• A non-standard database block size is assigned to a tablespace with the CREATE TABLESPACE statement BLOCKSIZE parameter. Tablespace blocksize definitions can be monitored with the DBA_TABLESPACES and V$DATAFILE views. A block size column has been added to both views to show the assigned block sizes for each tablespace.
• Non-standard block sizes of the same size as the standard block size are not permitted. Query parameter DB_BLOCK_SIZE to determine the default blocksize for your database:
• SQL> show parameter db_block_size


• Notes continue on the next page…

Instructor's Notes

(If applicable)

• Before using a non-standard database block size, a subcache must be set up in shared memory. Use the DB_xK_CACHE_SIZE initialization parameters. (Remember that the total size of the SGA must remain within SGA_MAX_SIZE value. Review the lesson Architecture, Part 3 for more information on subcaches. )
• The demonstration shown in this slide creates a table with a 16k (non-standard) blocksize. It performs the following steps:
• Uses the SQL*Plus SHOW PARAMETER command to determine if a 16k cache exists. It does not (see VALUE 0), so it then:
• Backs up the SPFILE by writing it to a text parameter file (PFILE). The next step will change the SPFILE, so I recommend backing it up first.
• Creates a 40M 16k buffer cache with the ALTER SYSTEM statement.
• Creates a tablespace called “WAREHOUSE”. I expect the table size to be fairly static and slightly less than 40M, so I used a UNIFORM LMT.
• Creates a table called “SALES_HISTORY” in the WAREHOUSE tablespace. Blocks in the SALES_HISTORY segment will be 16k.

Instructor's Notes

(If applicable)

• Support for non-standard database block sizes may be helpful in two different areas; transportable tablespaces and database performance.
• Transportable tablespaces have been a part of Oracle since Oracle8i. They allow you to move a data subset of one database to another. This is done by copying the tablespace’s data files and integrating the tablespace’s structural information into the target database. This can be much faster than other data migration alternatives such as export/import. This feature is useful for archiving data, cloning a tablespace, and for moving data between data warehouse and data mart staging areas to the actual data warehouse or data mart. Transportable tablespaces can also be used to move index data, avoiding index rebuilds when loading table data.
• One potential problem in moving a tablespace from one database to another is that the two databases will have different block sizes. Oracle9i solves this problem by allowing non-standard database block sizes.



• Notes for this slide continue on the next page…

Instructor's Notes

(If applicable)

• Choosing a database block size can prove to be an important factor in a database’s performance. (One of your performance goals as a DBA is to minimize the number of blocks that have to be read in order to satisfy a query’s request.) For query-intensive applications that touch a lot of records, a larger block size will increase performance over using a smaller block size. But a frequent tuning problem is that many databases support two or more types of applications that have different access patterns, for example OLTP and Decision Support. OLTP applications tend to contain short transactions that touch few records and perform better with a small block size. DSS applications tend to query large amounts of data and perform better with a large block size. Oracle9i provides the flexibility to support both in one database – as long as the objects supporting the different applications can be segregated into separate tablespaces.

Instructor's Notes

(If applicable)

• You can help prevent out of space errors by regularly monitoring space used and space available (free space). If possible, consider keeping 25% free for tablespaces that are subject to inserts or updates.
• To check on the amount of free space remaining in a tablespace, you can query the DBA_FREE_SPACE data dictionary view or use a useful monitoring script I found on the Tom Kyte web site (http://asktom.oracle.com). The script is supplied as part of this course in the file FREE_SPACE.SQL. The output (shown in the slide) is described here by Tom Kyte:
• "Kbytes = space allocated to the tablespace currently.
• Used = space allocated within the tablespace to specific objects
• Free = space not yet allocated to any objects
• Used = % of space allocated to objects in tablespace
• Largest= Largest free contiguous extent available (NEXT_EXTENTS request larger then this will FAIL)"

Instructor's Notes

(If applicable)

• The datafile for a tablespace may eventually fill up as existing objects expand (this can happen when the INITIAL allocation is exhausted and additional INSERT operations are performed) or additional objects are created within the tablespace.
• Space can be added automatically via the use of the AUTOEXTEND feature, introduced with Oracle release 7.3. In the CREATE TABLESPACE example above, an additional 8 megabyte extent will be added when the initial 64 megabyte extent is used and more space is required. Additional 8 megabyte extents will be added until the datafile reaches 128MB in size, the specified limit in this example. After that, Oracle errors will be returned when space requests are invoked.
• AUTOEXTEND can also be used on the ALTER DATABASE statement, as shown in the second example.
• AUTOEXTEND is part of the FILE_SPECIFICATION clause. Refer to the Oracle9i SQL Reference, Chapter 7, FILE_SPECIFICATION, for more information.

Instructor's Notes

(If applicable)

• As the first example in this slide demonstrates, another method of adding space to a tablespace is to add more datafiles with the ALTER TABLESPACE statement. The ALTER TABLESPACE system privilege is required to execute the ALTER TABLESPACE command.
• Supplemental Notes
  • You can also use the ALTER DATABASE RESIZE statement to shrink datafiles (a datafile cannot be shrunk to a size less than what is currently used in the datafile).

Instructor's Notes

(If applicable)

• Yet another way of adding space is to resize the datafile. This is done with the ALTER DATABASE command as shown above. I also wanted to increase the MAXSIZE, so I executed a 2nd ALTER DATABASE with the AUTOEXTEND clause.

Instructor's Notes

(If applicable)

• Besides adding additional space to a tablespace, the ALTER TABLESPACE statement has additional uses. This includes taking tablespaces offline, perhaps for maintenance or restore and recovery operations, as shown in this example. You will learn more about RMAN operations in the Introduction to RMAN lesson later in this course.
• OFFLINE makes the datafiles within the tablespace unavailable to database users. OFFLINE supports the following sub-parameters:
• NORMAL flushes all dirty (i.e. updated) blocks in all datafiles in the tablespace out of the SGA. You do not need to perform media recovery on this tablespace before bringing it back online. NORMAL is the default.
• TEMPORARY Tries to write dirty blocks but does not guarantee it. Some datafiles may require media recovery before you bring the tablespace back online.
• IMMEDIATE Does not write dirty blocks. Perform media recovery on the tablespace before bringing it back online.
• FOR RECOVER takes the production database tablespaces in the recovery set offline for tablespace point-in-time recovery. For additional information see Oracle9i Backup and Recovery Guide.
• Use the ALTER TABLESPACE ONLINE statement to bring the tablespace back online.

Instructor's Notes

(If applicable)

• READ ONLY mode prevents write operations to any object in the tablespace. This includes creating new or extending existing objects and insert, update and delete operations.
• Supplemental Notes
  • The DBA_TABLESPACES STATUS column contains ‘READ ONLY’ in this mode.
  • Note that the tablespace cannot be created in this mode - you must ALTER it into this mode.

Instructor's Notes

(If applicable)

• Dropping a tablespaces will remove information about the tablespace from both the Oracle data dictionary and the control file so that the next time the instance starts up it does not attempt to open the file.
• If the datafile is an OMF file, it will also be removed from the operating system's file system. NOTE: The file(s) being physically deleted in the above command do not show up in your recycle bin on Windows servers.
• The tablespace can be online; however, it is recommended to take OFFLINE first to insure that no one is using the tablespace.
• CAUTION: All DDL statements, including DROP TABLESPACE, issue an implicit COMMIT; this operation cannot be rolled back. You may want to export the tablespace before dropping as a means of backup. You will learn more about the export utility in the Logical Backups lesson.
• Use the clause 'INCLUDING CONTENTS' if the tablespace contains any objects.
• The last example demonstrates the DROP TABLESPACE command with the Oracle9i option "AND DATAFILES".


• Notes continue on the next page…

Instructor's Notes

(If applicable)

• The AND DATAFILES option will physically delete the operating system files that are associated with the tablespace being dropped – even if the tablespace was created without OMF. The “AND DATAFILES” clause can be used with OMF objects, but it is not necessary; the default behavior is to delete the OS file for OMF tablespaces.
• Supplemental Notes
  • The DROP TABLESPACE system privilege is required.
  • Users who rely on their default tablespace will receive errors when they attempt to create objects if you drop their default tablespace. I recommend you query the dictionary to determine if the tablespace you’re going to drop is anyone’s default tablespace:
  • SQL> select username from dba_users
  • 2 where default_tablespace = 'TS1';
  • 
    USERNAME
  • ------------------------------
  • DAVE

Instructor's Notes

(If applicable)

• Oracle Discoverer and other tools will create objects owned by user SYSTEM. Before installing these products, change the DEFAULT TABLESPACE to something other than SYSTEM. This can be done with ALTER USER.
• To prevent users from building any object in the SYSTEM tablespace, set the QUOTA on the SYSTEM tablespace to 0 for all users (QUOTA is, by default, 0; so if you do not provide QUOTA in the first place, you will not have to reduce it to 0). This query will reveal if anyone has quota on the SYSTEM tablespace:
• SQL> select username, bytes, max_bytes
• 2 from dba_ts_quotas
• 3 where tablespace_name = 'SYSTEM';


• no rows selected

Instructor's Notes

(If applicable)

• If you run out of space in the SYSTEM tablespace Oracle will not be able to operate properly. Monitor this tablespace often; insure there is ample free space (some DBAs recommend 40% free space). The DBA_FREE_SPACE dictionary view can assist with this or the Tom Kyte script FREE_SPACE.SQL.

Instructor's Notes

(If applicable)

• OK, let’s summarize what you learned in this lesson:
• A tablespace is a logical database object that associates with one or more datafiles. The concept of a tablespace helps with placement of objects on disk and management techniques such as ‘export tablespace’.
• The CREATE TABLESPACE statement is used to create a new tablespace. Remember that locally managed tablespaces are more efficient because it uses a bitmap to control used and free extents.
• Use ALTER TABLESPACE to add space (extend), take a tablespace offline or change the mode to read only.
• Monitor available free space regularly to prevent out-of-space errors.
• Make sure the SYSTEM tablespace is dedicated to system objects. Change the default tablespace of all users to something other than SYSTEM and do not provide quota on SYSTEM.

Instructor's Notes

(If applicable)

• Tablespaces Workshop
  1. If OMF is not enabled, enable it. Then create an OMF managed tablespace called “ts1” with the following attributes:
• Datafile size 10m
• Automatic Segment Space Management = AUTO
• Locally managed with Uniform extent size = 1M
• Autoextend off
2. Query the DBA_DATA_FILES view to determine the name of the physical datafile created.
3. Create a test table called "T" in tablespace TS1.
4. Query the DBA_SEGMENTS view to determine the amount of space used by table T.
5. Run the FREE_SPACE.SQL script and review the report to determine if some tablespaces should have space added.
6. Add 1M of disk space to one of the tablespaces.
7. Run the FREE_SPACE.SQL script and review the report to see the 1M of additional space added.