DBA Fundamentals

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:

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.

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.

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
/