File=cmis565_lab3.htm; updated May 27, 2013


Oracle Database Administration – Lab #3

Tablespace and Datafile Management (10 points)


Situation:  You have identified the need to alter the structure of the database with regard to tablespaces and datafiles.  In this laboratory you will practice managing tablespaces and datafiles.


Use Notepad, Wordpad, or Microsoft Word to document your solution to the tasks in the laboratory. 

·         Format your output as necessary to make it easy to read.  Do not submit for grading anything you would not submit to your boss for review in an actual work environment, i.e. in other words, do not turn in sloppy work that is difficult for me to grade.   

Note: -1 if you do not use the correct font and format your output professionally.


Tablespace and Datafile:  The modifications that you make to your database for this lab should be OFA-compliant.  You must decide which disk drive (/u01 through /u03) to use for these tablespaces.


System Object 

File Name


DATA01 Tablespace



INDEX01 Tablespace



READONLY01 Tablespace



COMP_DATA Tablespace





Tasks for this Lab


1.  Startup your instance.   Connect as the DBA user for your database.

.  Create permanent tablespaces as follows (2 points – see the above table of information on tablespace and datafile names and sizes). 

a.    DATA01 as a locally managed tablespace with uniform sized extents of 48K. 

b.    INDEX01 as a locally managed tablespace that allows extents to be different sizes such that extent size is automatically allocated by the DBMS.

c.    Query the DBA_TABLESPACES and V$DATAFILE view to display information about the tablespaces you just created as shown here:

COLUMN tablespace_name FORMAT A10;

SELECT tablespace_name, extent_management, initial_extent,

    next_extent, pct_increase

FROM dba_tablespaces;



SELECT name, ts# FROM v$datafile;


3.  Alter the DBA user (1 point).

a.    Alter the DBA user accounts for your database such that each DBA is assigned a default tablespace of DATA01.

b.    Verify the result by querying DBA_USERS as shown here:

SELECT username, default_tablespace FROM dba_users;


4.  Alter tablespaces (1 point).

a.    Allocate 104K additional space to tablespace DATA01.  

b.    Verify the result by querying DBA_DATA_FILES as shown here:

SELECT tablespace_name, bytes/1024 FROM dba_data_files;


5.     Relocate a tablespace (1 point).  

a.    Create a new directory inside your oradata directory for the disk drive where you decided to create the DATA01 tablespace datafile.  Name the new directory application_data.  Set appropriate permissions (775) for the application_data directory.

b.    Take the action necessary to relocate DATA01 to the new directory. 

c.    Verify the result by querying DBA_DATA_FILES as shown here:

COLUMN file_name FORMAT A50;

COLUMN tablespace_name FORMAT A15;

SELECT tablespace_name, file_name, status FROM dba_data_files;


6.  Read-Only Tablespace (2 points).

a.    Create a tablespace named READONLY01 on disk drive /u01 (but do not make the tablespace read-only at this time).  The READONLY01 tablespace should inherit the Oracle default storage options.  Verify the result by querying DBA_TABLESPACES as shown here:

SELECT tablespace_name FROM dba_tablespaces;


b.    Create a table named TEST1 and insert one data row into the table.


      test1_column_1  CHAR(20) )

 TABLESPACE readonly01;


 INSERT INTO test1 VALUES (‘Row 1’);



c.  Make the READONLY01 tablespace read-only.  Verify the result by querying V$DATAFILE:

SELECT name, enabled FROM v$datafile WHERE enabled = ‘READ ONLY’;


d.    Attempt to create a new test table named TEST2.  What happens?  Drop the first table, TEST1.  What happens?

e.    Drop the READONLY01 tablespace and the associated datafile.  Verify that the tablespace has been dropped by querying DBA_TABLESPACES and DBA_DATA_FILES:

SELECT tablespace_name FROM dba_tablespaces;

SELECT file_name FROM dba_data_files;


7.  OMF Tablespace Creation (1 point).

a.    Set the DB_CREATE_FILE_DEST parameter to $HOME/oradata in memory only. 

b.    Create a tablespace named DATA02 with size 100K.   Do not specify a file location. 

c.    Verify the result by querying DBA_DATA_FILES:

SELECT tablespace_name, file_name, status FROM dba_data_files;


d.    Drop the tablespace that you just created including the contents and datafile.


8.  Compressed Tablespace Creation (1 point).

a.    Create a compressed tablespace of size 1M named COMP_DATA that compresses data for OLTP.  Store the tablespace on disk drive /u02.

b.    Verify the result by querying DBA_TABLESPACES as shown here:

SELECT tablespace_name FROM dba_tablespaces;


9.  Shut down the database (-1 point if you forget to shutdown). 



Do not leave your database running between laboratory sessions.