Practice 8: Managing Tablespaces and Data Files
1 Create permanent tablespaces with the following names and storage:
a DATA01 for tables with default storage
b DATA02 for large objects with default storage (Ensure that every used extent
size in the tablespace is multiple of 100K.)
c INDX01 for indexes with the default storage (Enable automatic extension of
500K when more extents are required.)
d RONLY for read only tables with the default storage
Display the information from the data dictionary
2 Allocate 500K more to the tablespace DATA02 and verify the result.
3 Relocate the INDX01 tablespace and move it to DISK6.
4 Turn RONLY tablespace to read only after creating a table in this tablespace.
Attempt to create an additional table and drop the table. What happens and why?
5 Drop the tablespace RONLY and verify it.
6 Without shutting down the instance, change the SORT_AREA_SIZE to 2
kilobytes.
7 Open two connections to the database as user SYSTEM. Run srt_dd.sql from one
session and monitor the sort activity from another session. Query sort statistics and
temporary segment information both during and after the completion of the script.
Note the findings.
8 From one of the sessions, run asn_tts.sql to prepare for the next part of the lab.
This script ensures that TEMP tablespace will be used for the sorts made by the
user SYSTEM, and will be covered under the lesson “Managing Users.” Connect as
SYSTEM from one of the sessions and run srt_dd.sql. From the other session
monitor sort activity and statistics, as was done in question 3. Do you notice any
difference?
9 Reset SORT_AREA_SIZE.
1 Create permanent tablespaces with the following names and storage:
a DATA01 for tables with default storage
b DATA02 for large objects with default storage (Ensure that every used extent
size in the tablespace is multiple of 100K.)
c INDX01 for indexes with the default storage (Enable automatic extension of
500K when more extents are required.)
d RONLY for read only tables with the default storage
Display the information from the data dictionary.
Hint
– Execute the CREATE TABLESPACE command to create the permanent
tablespaces.
– Display the information from the data dictionary.
– Query the dynamic performance view DBA_DATA_FILES to verify the result.
2 Allocate 500K more to the tablespace DATA02 and verify the result.
Hint
– Use the ALTER DATABASE DATAFILE... RESIZE command to allocate
another 500K.
– Query the dynamic performance view V$DATAFILE to verify the result.
3 Relocate the INDX01 tablespace and move it to DISK6.
Hint
– Take the tablespace INDX01 OFFLINE.
– Query the dynamic performance view V$DATAFILE to verify the result.
– Execute the ALTER TABLESPACE RENAME command to rename the files.
– Take the tablespace INDX01 ONLINE.
– Query the dynamic performance views V$DATAFILE to verify the result.
4 Turn RONLY tablespace to read only after creating a table in this tablespace.
Attempt to create an additional table and drop the table. What happens and why?
Hint: There is no hint for this question.
5 Drop the tablespace RONLY and verify it.
Hint
– Execute the DROP TABLESPACE ... to remove the tablespace.
– Delete the operating-system files.
– Query the dynamic performance view V$TABLESPACE to verify the result.
6 Without shutting down the instance, change the SORT_AREA_SIZE to 2
kilobytes.
Hint: SORT_AREA_SIZE is a dynamic parameter needing the
DEFERRED option.
7 Open two connections to the database as user SYSTEM. Run srt_dd.sql from one
session and monitor the sort activity from another session. Query sort statistics and
temporary segment information both during and after the completion of the script.
Note the findings.
Hint: Sort segment information is available from V$SORT_SEGMENT
and current sort activity from V$SORT_USAGE.
8 From one of the sessions, run asn_tts.sql to prepare for the next part of the lab.
This script ensures that TEMP tablespace will be used for the sorts made by the
user SYSTEM, and will be covered under the lesson “Managing Users.” Connect as
SYSTEM from one of the sessions and run srt_dd.sql. From the other session
monitor sort activity and statistics, as was done in question 3. Do you notice any
difference?
9 Reset SORT_AREA_SIZE.