"He who
hesitates is a damned fool." - Mae West (1892-1980) |
Read
first then play the video:
DBA-VIDEO -Maintaining
a TEMPORARY tablespace
Maintaining a TEMPORARY
tablespace
Introduction
As a DBA, you are
responsible for maintaining a TEMPORARY tablespace due to a user"s
big transaction sorting. Your job"s responsibilities dictate that
you should at least be informed of the following basic fundamental
subjects:
Maintaining a TEMPORARY
tablespace with OMF
Maintaining a TEMPORARY
tablespace without OMF
Using the
DB_CREATE_FILE_DEST parameter
Creating a TEMPORARY
tablespace with OMF
Using the DBA_TABLESPACES
view
TABLESPACE_NAME
INITIAL_EXTENT
MAX_EXTENTS
CONTENTS
LOGGING
STATUS
Using the DBA_DATA_FILES
directory view
Using the DBA_TEMP_FILES
directory view
Using the V$SESSION
dictionary view
Using the V$SORT_SEGMENT
view
Dropping a TEMPORARY
tablespace
Creating a TEMPORARY
tablespace using original syntax
Advantages of using OMF
Creating a TEMPORARY
tablespace using the TEMPFILE clause
Using Oracle-Managed Files
Using the UNIFORM option
Using the DBA_TEMP_FILES
view
Commands:
ALTER SYSTEM SET
CREATE TEMPORARY TABLESPACE
DROP TABLESPACE
CREATE TABLESPACE TEMPORARY
Hands-on
In this exercise you will learn how to maintain a temporary
tablespace with or without using Oracle-Managed Files (OMF) and more.
First, connect to SQL*Plus as the system/manager user.
SQL> CONNECT system/manager@school AS
SYSDBA
Define a create file
destination
Let's first make sure that the DB_CREATE_FILE_DEST value is set to
a valid sub-directory.
SQL> ALTER SYSTEM SET db_create_file_dest='c:'
/
Create a temporary tablespace (OMF)
Now, create a temporary
tablespace with Oracle-Managed Files (OMF). Users create temporary
segments in a tablespace when a disk sort is required to support their
use of select statements containing the GROUP BY, ORDER BY, DISTINCT,
or UNION, or the CREATE INDEX statements.
SQL> CREATE TEMPORARY TABLESPACE mytemp
/
Query the DBA_TABLESPACES view to display the tablespace name, initial
extent, max extents, contents, logging, and tablespace status.
SQL> SELECT tablespace_name,
initial_extent, max_extents,
contents, logging, status
FROM dba_tablespaces
/
Notice that the CONTENTS column is set to TEMPORARY. The
default init size is 1 Megabytes and the maximum extend is unlimited.
Query the DBA_DATA_FILES directory view.
SQL> SELECT file_id, file_name,
tablespace_name, status
FROM dba_data_files
/
Notice that the temporary tablespace is not there.
Query the DBA_TEMP_FILE directory view.
SQL> SELECT * FROM dba_temp_files
/
Note the naming convention.
Open a new session and connect to SQLPlus as the ISELF user and then
do the following statements.
SQL> CONNECT iself/schooling
SQL> SET SQLPROMP 'iself > '
SQL> SELECT e1.ename
FROM emp e1, emp e2, emp e3, emp e4
ORDER BY 1
/
While the other session is running, come back and query the following
statement.
SQL> SET SQLPROMPT 'dba > '
SQL> SELECT s.username, tablespace, contents, extents, blocks
FROM v$session s, v$sort_usage
WHERE s.saddr = session_addr
/
Or you can query the V$SORT_SEGMENT table.
SQL> SELECT
tablespace_name, extent_size,
total_extents,
max_sort_blocks
FROM v$sort_segment
/
The sort segment high-water mark information is exist for
duration of the instance. Starting the instance will clean this table.
This a good way to find out how big the users sort segments has
become.
Drop a temporary tablespace
Drop the mytemp tablespace and create it again with the old
method.
SQL> DROP TABLESPACE mytemp
/
Notice that your datafile will be deleted.
Create a temporary
tablespace using User-Managed file
SQL> CREATE TABLESPACE
mytemp
DATAFILE 'c:\mytemp_01.tmp' SIZE 20M
TEMPORARY
/
Drop a temporary tablespace
(UMF)
Drop the mytemp tablespace and delete the datafile from the
system. Note that we need to delete the datafile since we did not
create the temporary tablespace using Oracle-Managed Files (OMF).
SQL> DROP TABLESPACE mytemp
/
SQL> HOST ERASE c:\mytemp_01.tmp
TEMPIFLE and UNIFORM
options
Now, create a temporary tablespace using the TEMPFILE and UNIFORM
options. Make sure to use Oracle-Managed Files.
SQL> CREATE TEMPORARY TABLESPACE mytemp
TEMPFILE 'mytemp_01.tmp' SIZE 20M
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 10M
/
The uniform extent sizing is used to simplify how extents
are allocated to objects.
Query the DBA_TEMP_FILES view.
SQL> SELECT * FROM dba_temp_files
/
Here, you see the MYTEMP tablespace. Notice that the
tablespace is located in %ORACLE_HOME%sub-directory. Remember that all
tablespaces in the database will use the standard block size defined
for the database.
"If women didn't
exist, all the money in the world would have no meaning." -
Aristotle Onassis (1906-1975) |
Questions:
Q: How do you create a
TEMPORARY tablespace using Oracle-Managed File (OMF) technique?
Q: How do you create a
TEMPORARY tablespace using User-Managed File (UMF) technique?
Q: What is the
DB_CREATE_FILE_DEST parameter?
Q: Describe the
DBA_TABLESPACES view.
Q: Describe the
DBA_DATA_FILES view.
Q: Describe the following
views.
DBA_DATA_FILES
directory view
DBA_TEMP_FILES
directory view
V$SESSION dictionary
view
V$SORT_SEGMENT view
Q: How do you drop a
temporary tablespace?
Q: Create a temporary
tablespace using an Oracle original syntax.
Q: Create a temporary
tablespace using the TEMPFILE clause.
Q: Describe the UNIFORM
option.
Q: What do the following
SQL statements do?
SQL> SELECT
tablespace_name, initial_extent, max_extents,
contents, logging, status
FROM dba_tablespaces
/
SQL> SELECT s.username, tablespace, contents, extents, blocks
FROM v$session s, v$sort_usage
WHERE s.saddr = session_addr
/
SQL> SELECT tablespace_name, extent_size,
total_extents,
max_sort_blocks
FROM v$sort_segment
/
SQL> CREATE TEMPORARY TABLESPACE mytemp
TEMPFILE 'mytemp_01.tmp' SIZE 20M
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 10M
/
|