Hands-On 11 (Maintaining a TEMPORARY tablespace)

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 11 (Maintaining a TEMPORARY tablespace)
-- Preparation
set echo on
connect system/manager@school as sysdba
SET linesize 1000 pagesize 55
COL name FORMAT a50
col file_name format a50
col extent_management format a20
col username format a10
col member format a50

pause

--Start

CLEAR SCR
-- 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.

pause

CONNECT system/manager@school AS SYSDBA

pause

CLEAR SCR
-- Let's first make sure that the DB_CREATE_FILE_DEST value is
-- set to a valid sub-directory.

pause


ALTER SYSTEM SET db_create_file_dest='c:\newfolder'
/

pause

CLEAR SCR
-- 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.

pause

CREATE TEMPORARY TABLESPACE mytemp
/


pause

CLEAR SCR
-- Query the DBA_TABLESPACES view to display the tablespace
-- name, initial extent, max extents, contents, logging, 
-- and tablespace status.

pause


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.

pause

CLEAR SCR
-- Query the DBA_DATA_FILES directory view.

pause


SELECT file_id, file_name, tablespace_name, status
FROM dba_data_files
/

-- Notice that the temporary tablespace is not there.

pause

CLEAR SCR
pause


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.

--\/-- CONNECT iself/schooling
--\/-- SET SQLPROMP 'iself > '
--\/-- SELECT e1.ename 
--\/-- FROM emp e1, emp e2, emp e3, emp e4
--\/-- ORDER BY 1
--\/-- /

pause

CLEAR SCR
-- While the other session is running, query the following 
-- statement.

pause


SET SQLPROMPT 'dba > '

SELECT s.username, tablespace, contents, extents, blocks
FROM v$session s, v$sort_usage
WHERE s.saddr = session_addr
/

-- >>>>>>>>>>>>>>>>> OR <<<<<<<<<<<<<<<<<<<<<

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.

pause

pause

CLEAR SCR
-- Drop the mytemp tablespace and create it again with the old 
-- method.

pause


DROP TABLESPACE mytemp
/


CREATE TABLESPACE mytemp
DATAFILE 'c:\newfolder\mytemp_01.tmp' SIZE 20M
TEMPORARY
/

pause

CLEAR SCR
-- Drop the mytemp tablespace and delete the datafile
-- from the system.

-- We need to delete the datafile since we did not create
-- the temporary tablespace using Oracle-Managed Files (OMF).

pause

DROP TABLESPACE mytemp
/

HOST ERASE c:\newfolder\mytemp_01.tmp


CLEAR SCR
-- Now, create a temporary tablespace using the TEMPFILE and
-- UNIFORM options. 

-- Use Oracle-Managed Files.

pause


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.

pause

CLEAR SCR
-- Query the DBA_TEMP_FILES view.

pause

SELECT * FROM dba_temp_files
/

-- Here, you see the MYTEMP tablespace. Notice that the 
-- tablespace is located in %ORACLE_HOME%\database sub-directory.
-- Remember that all tablespaces in the database will use 
-- the standard block size defined for the database.

pause

CLEAR SCR
-- Now, you should practice this Hands-On exercise.

-- For more information about the subject, you are encouraged
-- to read from a wide selection of available books.

-- Good luck.
--
pause
pause