Oracle Database Administration – Lab #3
Tablespace and Datafile Management

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.

 


System Object   DATA01 Tablespace            INDEX01 Tablespace                READONLY01 Tablespace

File Name            USERXXXdata01.dbf         USERXXXindex01.dbf                 USERXXXreadonly01.dbf

Size                      1M                                                2M                                                500K




steps :
 

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


2.  Create permanent tablespaces as follows

DATA01 as a locally managed tablespace with uniform sized extents of 32K. 
INDEX01 as a locally managed tablespace that allows extents to be different sizes such that extent size is automatically allocated by the DBMS.
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;

 

COLUMN name FORMAT A55;

SELECT name, ts# FROM v$datafile;

 

3.  Alter the DBA user

Alter the DBA user account for your database such that the DBA is assigned a default tablespace of DATA01.
Verify the result by querying DBA_USERS as shown here:
SELECT username, default_tablespace FROM dba_users;

 

4.  Alter tablespaces

Allocate 104K additional space to tablespace DATA01. 
Verify the result by querying DBA_DATA_FILES as shown here:
SELECT tablespace_name, bytes/1024 FROM dba_data_files;

 

5.       Relocate a tablespace

Create a new directory inside your Oradata directory where your DATA01 tablespace datafile is located.  Name the new directory application_data.  Set appropriate permissions (775) for the application_data directory.
Take the action necessary to relocate DATA01 to the new directory. 
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

a. Create a tablespace named READONLY01 on disk drive /a01 (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.
 CREATE TABLE test1 (

      test1_column_1  CHAR(20) )

 TABLESPACE readonly01;

 

INSERT INTO test1 VALUES (‘Row 1’);

COMMIT;

 

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

Set the DB_CREATE_FILE_DEST parameter to $HOME/Oradata in memory only. 
Create a tablespace named DATA02 with size 100K.   Do not specify a file location. 
Verify the result by querying DBA_DATA_FILES:
SELECT tablespace_name, file_name, status FROM dba_data_files;

Drop the tablespace including the contents and datafile.
 

8.  Shut down the database