Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Monday, 1 October 2012

TEMPORARY Tablespaces & TEMPFILES

Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables. Numerous SQL operations that require disk sorting such as: CREATE INDEX, ANALYZE, Select DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS, Sort-Merge joins, etc. Thus assigning a temporary tablespace to each user in the database to prevent them from allocating sort space in the SYSTEM tablespace.
SQL>CREATE USER HRMS DEFAULT TABLESPACE Main_DB TEMPORARY TABLESPACE Temp;
SQL> ALTER USER HRMS TEMPORARY TABLESPACE temp;
TEMPFILES?
Unlike normal data files, TEMPFILE are not fully initialized. When you create a TEMPFILE, Oracle only writes to the header and last block of the file. TEMPFILEs are not recorded in the database's control file so that one can simply re-create it after accidental delete. Temp files cannot contain permanent objects and therefore doesn't need to be backed up.
One cannot remove datafiles from a tablespace until you drop the entire tablespace. However, one can remove a TEMPFILE from a database.
SQL> ALTER DATABASE TEMPFILE 'C:\oracle1\oradata\temp03.dbf' DROP INCLUDING DATAFILES;
If you remove all tempfiles from a temporary tablespace, you may get error: ORA-25153: Temporary Tablespace is Empty. To Add Tempfile:
SQL> ALTER TABLESPACE temp ADD TEMPFILE 'C:\oracle1\oradata\temp03.dbf' SIZE 100M;
Except for adding a tempfile you cannot use the other ALTER TABLESPACE statement for a locally managed temporary tablespace such as operations like rename, set to read only, recover, etc. will fail with tempfile.
How does one create Temporary Tablespaces?
Oracle 9i example:
SQL> CREATE TEMPORARY TABLESPACE temp
      TEMPFILE '/oradata/mytemp_01.tmp' SIZE 20M
      EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;
For best performance, the UNIFORM SIZE must be a multiple of the SORT_AREA_SIZE parameter. For Example: To create temporary tablespace.
SQL> CREATE TEMPORARY TABLESPACE temp;
Default Temporary Tablespaces:
In Oracle 9i and above, one can define a Default Temporary Tablespace at database creation time:
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
The default Default Temporary Tablespace is SYSTEM. Each database can be assigned one and only one Default Temporary Tablespace. Using this feature, a Temporary Tablespace is automatically assigned to users. The following restrictions apply to default temporary tablespaces:
         The Default Temporary Tablespace must be of type TEMPORARY
         The DEFAULT TEMPORARY TABLESPACE cannot be taken off-line
         The DEFAULT TEMPORARY TABLESPACE cannot be dropped until you create another one.
To check the default temporary tablespace for a database:
SQL>SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
All new users that are not explicitly assigned a TEMP TABLESPACE will get the Default Temp Tablespace as its TABLESPACE. Also, when you assign a TEMPORARY tablespace to a user, Oracle will not change this value next time you change the Default Temporary Tablespace for the database.
Other performance Considerations for Temp Tablespaces:
         Always use temporary tablespaces instead of permanent content tablespaces for sorting operation. By using nologging and large sort segment to reduce recursive SQL and ST space management enqueue contention.
         Always create your temporary tablespaces as locally managed instead of dictionary managed. Thus use sort space bitmap instead of sys.fet$ and sys.uet$ for allocating space.
         Always use TEMPFILEs instead of DATAFILEs will reduce backup and recovery time as well as Prevent allocating sort space in SYSTEM tablepace.
         Stripe your Temp tablespaces over multiple disks to minimize disk contention and to speed-up sorting operations.
Monitoring TEMP Tablespace:
Use V$TEMPFILE and DBA_TEMP_FILES to check the Tempfile.
Monitor temporary segments from V$SORT_SEGMENT and V$SORT_USAGE
Use V$TEMP_SPACE_HEADER to check free space of temporary tablespace.

1 comments:

  1. QUANTUM BINARY SIGNALS

    Get professional trading signals delivered to your mobile phone every day.

    Start following our signals NOW & profit up to 270% per day.

    ReplyDelete