Oracle Tablespace Exercises for Newbie

One of the more confusing aspects of the Oracle database is the fact that you musts deal with both a logical and a physical side to some of the more important database objects. Both the Redo Log and Tablespace in the Oracle database have a logical side and physical side in referencing the object. The logicalreference is the notation in the data dictionary that defines the object and its storage components, and the physical side consists of the actual data file associated with the object.
A tablespace, the database object in Oracle that is responsible for the actual table objects (as well as other types of objects) and resulting data storage, is made up of both the logical and physical aspects. The logical actually defines how storage of objects in the datafile will be handled, while the physical is made up of the actual data file created in the OS. Oracle has several ways of handling these datafiles--either as dictionary managed or as Oracle managed files. For the purposes of this lab, you will be dealing with dictionary-managed files.
In this lab you will be creating a series of tablespaces and related files, and then looking at the database to see how the storage settings you defined are accommodating changes to the database.
Use the following information as a guide for creating your new tablespaces:
Tablespace   Subdirectory   Data File     (Size)    Initial/Next   PctIncrease
Name           Name
------------ ------------- -----------   ------- ------------ ----------
INDX01        DISK3            indx01.DBF   (1M)     16K             0
USERS01      DISK1            users01.DBF (2M)     16K             0
RONLY         DISK1            ronly.DBF     (1M)     16K             0
TEMP01       DISK3            temp01.DBF  (1M)     local -
Creating a SPOOL session: Be sure that you start a spool session before you start executing your SQL code when working on the lab. Your SPOOL file name should be DATABASE_lab4. If you do the lab in several sessions, be sure that you use a different file name, so not to overwrite the existing file (for example DATABASE_lab4, DATABASE_lab4a, DATABASE_lab4b etc.)

L A B   S T E P S

Step 1: Create a series of Tablespaces

First,set up the CREATE TABLESPACE commands to create the permanent DICTIONARY MANAGED tablespaces and the single TEMPORARY tablespace, based on the names and configurations shown above. Keep in mind that the TEMP01 tablespace is to be a TEMPORARY tablespace and use EXTENT MANAGEMENT LOCAL with a uniform extent size of 512K. You also want to allow the reuse of extents.
Once you have your CREATE TABLESPACE statements complete, start an SQL*Plus session and connect to your database instance as the SYS AS SYSDBA users. Execute your statements one at a time to create the tablespaces, correcting any errors as you go. Remember that once a tablespace is successfully created, you should move on to the next tablespace. Trying to recreate a single tablespace more than once will cause an error.
Once you have successfully created all 4 tablespaces, query the dynamic performance view DBA_DATA_FILES twice to verify the results of your work. Each query will provide a little different view of your work.
Your response will look similar to this:

FILE_NAME                                                                                               FILE_ID TABLESPACE_NAME
--------------------------------------------------------------------------- ---------- ------------------
F:\DBM\SPRINGB\DB1000\ORACLE\ADMIN\ORADATA\DISK1\SYSTEM01.DBF                    1 SYSTEM
F:\DBM\SPRINGB\DB1000\ORACLE\ADMIN\ORADATA\DISK3\INDX01.DBF                         3 INDX01
F:\DBM\SPRINGB\DB1000\ORACLE\ADMIN\ORADATA\DISK1\USERS01DV001.DBF              4 USERS01
F:\DBM\SPRINGB\DB1000\ORACLE\ADMIN\ORADATA\DISK1\RONLYDV001.DBF                 5 RONLY
F:\DBM\SPRINGB\DB1000\ORACLE\ADMIN\ORADATA\DISK1\SYSAUX01.DBF                    2 SYSAUX
5 rows selected.


TABLESPACE_NAME                      BYTES      BLOCKS  STATUS
------------------------------ ----------- ---------- ---------
SYSTEM                                198025216        24173 AVAILABLE
INDX01                                     1048576           128 AVAILABLE
USERS01                                   2097152           256 AVAILABLE
RONLY                                      1048576           128 AVAILABLE
SYSAUX                                 167772160       20480 AVAILABLE
5 rows selected.

Step 2: Relocating a Tablespace and datafile

In this step you are going to relocate the INDX01 tablespace from DISK 3 to DISK 5. Keep in mind that when renaming your datafile, Oracle looks at the entire path for the datafile as the datafile name. In this sense, you are not actually changing the name of the files, but rather the location. To Oracle that is the same thing.

Step by Step Hints:

1. Start an SQL*Plus session and execute the ALTER TABLESPACE command to take the tablespace INDX01 OFFLINE.
2. Query the dynamic performance view V$DATAFILE to verify the result.
3. Physically copy the INDX01.DBF datafile from the DISK3 FOLDER TO THE DISK5 FOLDER within the subdirectories within you instance directories that you mapped to the network drive.
4. Back in your SQL*Plus session, execute the ALTER TABLESPACE RENAME DATAFILE command to rename the files. In this command you want to use the full path to the datafile that starts with the F:\ as you see it in step one. This process is identical to renaming a LOGFILE as shown on page 160 in your book.
5. Execute the ALTER TABLESPACE command to take the tablespace INDX01 ONLINE.
6. Verify the results using the same query as in step 2.
7. Remove the old file from the subdirectory.

Your response will look similar to this after hint #2:

NAME                                                                                                   STATUS
--------------------------------------------------------------------------- ---------
F:\DBM\SPRINGB\DB1000\ORACLE\ADMIN\ORADATA\DISK1\SYSTEM01.DBF        SYSTEM
F:\DBM\SPRINGB\DB1000\ORACLE\ADMIN\ORADATA\DISK1\SYSAUX01.DBF        ONLINE
F:\DBM\SPRINGB\DB1000\ORACLE\ADMIN\ORADATA\DISK3\INDX01.DBF            OFFLINE
F:\DBM\SPRINGB\DB1000\ORACLE\ADMIN\ORADATA\DISK1\USERS01DV001.DBF  ONLINE
F:\DBM\SPRINGB\DB1000\ORACLE\ADMIN\ORADATA\DISK1\RONLYDV001.DBF     ONLINE
5 rows selected.

Your response will look similar to this after hint #6:

NAME                                                                                                   STATUS
--------------------------------------------------------------------------- ---------
F:\DBM\SPRINGB\DB1000\ORACLE\ADMIN\ORADATA\DISK1\SYSTEM01.DBF        SYSTEM
F:\DBM\SPRINGB\DB1000\ORACLE\ADMIN\ORADATA\DISK1\SYSAUX01.DBF        ONLINE
F:\DBM\SPRINGB\DB1000\ORACLE\ADMIN\ORADATA\DISK5\INDX01.DBF            ONLINE
F:\DBM\SPRINGB\DB1000\ORACLE\ADMIN\ORADATA\DISK1\USERS01DV001.DBF  ONLINE
F:\DBM\SPRINGB\DB1000\ORACLE\ADMIN\ORADATA\DISK1\RONLYDV001.DBF     ONLINE
5 rows selected.

Step 3: Verify Existing Tablespaces in your instance

Query the dynamic performance view V$TABLESPACE to verify the tablespaces you currently have in your database. Notice the difference in the results from what you have been seeing.
Your response will look similar to this:

       TS#   NAME
---------- ----------
         0    SYSTEM
         1    SYSAUX
         2    INDX01
         3    USERS01
         4    RONLY
         5    TEMP01
6 rows selected.

Step 4: Working with a Read-Only Tablespace

First, create a table name T1 with one number data type column in the RONLY tablespace.
Your response will look similar to this:

Table created.

Next,using an ALTER TABLESPACE command setthe RONLY tablespace to read only.
Your response will look similar to this:

Tablespace altered.

Query the v$datafile view to verify that the tablespace has been altered.
Your response will look similar to this:

NAME                                                                                                   ENABLED       STATUS
--------------------------------------------------------------------------- ------------- -------
F:\DBM\SPRINGB\DB1000\ORACLE\ADMIN\ORADATA\DISK1\SYSTEM01.DBF        READ WRITE  SYSTEM
F:\DBM\SPRINGB\DB1000\ORACLE\ADMIN\ORADATA\DISK1\SYSAUX01.DBF        READ WRITE  ONLINE
F:\DBM\SPRINGB\DB1000\ORACLE\ADMIN\ORADATA\DISK3\INDX01.DBF            DISABLED      OFFLINE
F:\DBM\SPRINGB\DB1000\ORACLE\ADMIN\ORADATA\DISK1\USERS01DV001.DBF  READ WRITE  ONLINE
F:\DBM\SPRINGB\DB1000\ORACLE\ADMIN\ORADATA\DISK1\RONLYDV001.DBF     READ ONLY    ONLINE
5 rows selected.

Now attempt to create an additional table named T2.
Your response will look similar to this:

CREATE TABLE t2 (t2 number) TABLESPACE ronly
*
ORA-01647: tablespace 'RONLY' is read only, cannot allocate space in it

Now drop the original table (T1) that you first crated.
Your response will look similar to this:

Table dropped.

Create a text document to be turned in with the lab files, to explain why you could not create a new table after the RONLY tablespace status was changed, yet you could drop an existing table when the tablespace was in READ ONLY status. Save your file as yourname_LAB04_step4.txt.

Step 5: Dropping a Tablespace

Create and execute the DROP TABLESPACE command to drop the RONLY tablespace. Be sure that you delete the associated data file from the directory structure after you have successfully dropped the tablespace from the DBMS.

Hints:

• Execute the DROP TABLESPACE. Since there is a table still in the tablespace, you will need to use the INCLUDING CONTENTS constraint to remove the tablespace.
• Delete the operating-system files.

Now query the dynamic performance view V$TABLESPACE to verify the result.
Your response will look similar to this:

       TS#   NAME     
---------- ----------
            0 SYSTEM   
            1 SYSAUX   
            2 INDX01   
            3 USERS01  
            5 TEMP01      
5 rows selected.

Step 6: Addressing UNDO Management

Prior to Oracle9i, only manual undo management was available through the creation and use of multiple Rollback Segments. Rollback segments are involved in every insert, update and delete transaction that happen in the database. As of Oracle9i, undo management can be handled through a special tablespace called an "undo tablespace." In this section of the lab we will add and set up another tablespace named UNDOTBS to act as our undo management tablespace.
First, use the SHOW PARAMETER UNDO command in your SQL*Plus session to find out the current condition of undo management for your instance. You should find that the instance is in MANUAL undo management.

Your response will look similar to this:
NAME                                            TYPE          VALUE
------------------------------------ ----------- -------
undo_management                           string         MANUAL
undo_retention                                integer       900
undo_tablespace                             string

The database needs to be in AUTO undo management, so you will need to do a little manipulation here.

• Shutdown you instance using SHUTDOWN IMMEDIATE.
• On your mapped network drive, go into your INITDB###.ora file and uncomment the line UNDO_MANAGEMENT=AUTO.
• Now start your instance up again using the INITDB###.ora pfile.
• Look at the parameters for UNDO again.

Your response will look similar to this:

NAME                                            TYPE         VALUE
------------------------------------ ----------- ----------
undo_management                           string         AUTO
undo_retention                                integer       900
undo_tablespace                             string

Now we can create an undo tablespace. Using a CREATE statement similar to the one you will find in the textbook, create an undo tablespace named UNDOTBS with a 2M datafile and place the datafile in the Disk4 directory of your OFA structure. Remember that you must use the complete path to the F:\ drive for the datafile, just as you did for the tablespaces you created in step 1.

• Now, once again, shutdown you instance, using SHUTDOWN IMMEDIATE.
• On your mapped network drive, go into your INITDB###.ora file and uncomment the line undo_tablespace=UNDOTBS parameter.
• Now start your instance up again, using the INITDB###.ora pfile.
• Once again, look at the parameters for UNDO. This time you should see your new tablespace listed.

Your response will look similar to this:

NAME                                            TYPE         VALUE
------------------------------------ ----------- ----------
undo_management                           string         AUTO
undo_retention                                integer       900
undo_tablespace                             string         UNDOTBS

This concludes the Lab for Week 4. Be sure to enter a SPOOL OFF command to end your spool session and save your output file.