everythingOracle.bizhat.com

 

'Everything you wanted
to know about Oracle'

Training References Syntax Tell A Friend Contact Us

 

 

DBA Fundamentals

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57
<< Previous

Chapter # 12

Next >>


 

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@yourhost 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 ORACLE user and then do the following statements.
SQL> CONNECT oracle/learning
SQL> SET SQLPROMP 'oracle > '
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:_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:_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.

 

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
/

     Reviews and Templates for FrontPage
     

Copyright � everythingOracle.bizhat.com 2006 All Rights Reserved.