iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

DBAs - Fundamentals

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 | Lesson 22 | Lesson 23 | Lesson 24 | Lesson 25 | Lesson 26 |

 

Lesson 12

"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
/