Optimizing Sort Operations - Oracle Database 11gR2 Performance Tuning - page 243

3. Examine statistics about temporary tablespace blocks:

COL TABLESPACE_NAME FOR A16
SELECT
TABLESPACE_NAME, CURRENT_USERS,
TOTAL_BLOCKS, USED_BLOCKS, FREE_BLOCKS,
MAX_BLOCKS, MAX_USED_BLOCKS, MAX_SORT_BLOCKS
FROM V$SORT_SEGMENT
ORDER BY TABLESPACE_NAME;

4. Examine statistics about temporary tablespace extents:

SELECT
TABLESPACE_NAME, CURRENT_USERS, EXTENT_SIZE,
TOTAL_EXTENTS, USED_EXTENTS, FREE_EXTENTS,
EXTENT_HITS
FROM V$SORT_SEGMENT
ORDER BY TABLESPACE_NAME;

5. Execute a query to retrieve the temporary files:
COL NAME FOR A32
SELECT
NAME, STATUS, ENABLED,
BYTES, BLOCKS, BLOCK_SIZE
FROM V$TEMPFILE;

6. Create a temporary tablespace TEMP_TEST :

CREATE TEMPORARY TABLESPACE TEMP_TEST
TEMPFILE '/u01/oradata/TESTDB/temp_test.dbf' SIZE 160M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

7. Assign a temporary tablespace TEMP_TEST to a user:

ALTER USER sh TEMPORARY TABLESPACE TEMP_TEST;

8. Drop temporary tablespace TEMP_TEST and clean up the database:

ALTER USER sh TEMPORARY TABLESPACE TEMP;
DROP TABLESPACE TEMP_TEST INCLUDING CONTENTS AND DATAFILES;