Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 Oracle PL/SQL: TABLESPACE: Tempfile operations Jump to:  
Category: >> Oracle PL/SQL >> TABLESPACE: Tempfile operations Bookmark and Share

<< lastnext >>

Snippet Name: TABLESPACE: Tempfile operations

Description: Various management functions of tempfiles for temporary tablespaces.

Unlike normal data files, TEMPFILEs are not fully initialized (sparse). When you create a TEMPFILE, Oracle only writes to the header and last block of the file. This is why it is much quicker to create a TEMPFILE than to create a normal database file.

TEMPFILEs are not recorded in the database's control file. This implies that one can just recreate them whenever you restore the database, or after deleting them by accident. This opens interesting possibilities like having different TEMPFILE configurations between permanent and standby databases, or configure TEMPFILEs to be local instead of shared in a RAC environment.

Note that you cannot remove datafiles from a tablespace until you drop the entire tablespace.

Also see:
» TABLESPACE: Show contiguous space
» TABLESPACE: SYSAUX tablespace
» TABLESPACE: Tablespace management
» TABLESPACE: List tablespaces, files, ...
» TABLESPACE: Dropping Tablespaces
» TABLESPACE: Alter Permanent Tablespace
» TABLESPACE: Transportable tablespaces
» TABLESPACE: Tempfile operations
» TABLESPACE: Create temp tablespace
» TABLESPACE: Change UNDO tablespace
» TABLESPACE: Undo Tablespace
» TABLESPACE: SYSAUX Tablespace
» TABLESPACE: Set default tablespace type
» TABLESPACE: Oracle Managed Auto-extend...
» TABLESPACE: Permanent Tablespace Using...
» TABLESPACE: Using Raw Devices
» TABLESPACE: permanent tablespace
» USER tablespace quota
» USER temp tablespace
» USER tablespace
» USERS: Grant tablespace access
» Nested Loops: loop through tables
» SEQUENCE: Autonumber into two tables
» Count rows in all tables
» List tables with more than 'X' rows
» List analyzed tables with un-analyzed ...
» Compressed Tablespace
» Compressed Tablespace
» DBA Tablespaces
» SELECT from multiple tables.

Comment: (none)

Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 12th, 2009

-- Add Tempfile:
ALTER TABLESPACE <tablespace_name>
ADD TEMPFILE '<path_and_temp_file_name>' SIZE <n>M;
ALTER TABLESPACE sandbox
ADD TEMPFILE '/u02/oradata/sandbox.dbf' SIZE 200M;
 
 
-- Resize Tempfile:      
ALTER DATABASE TEMPFILE '<temp_file_name>'
RESIZE <mega_bytes_integer>M;
ALTER DATABASE TEMPFILE '/u02/oradata/sandbox.dbf' RESIZE 250M;
 
-- Drop Tempfile:
ALTER DATABASE TEMPFILE '<temp_file_name>' DROP;
ALTER DATABASE TEMPFILE '/u02/oradata/sandbox.dbf' DROP;
 
-- Take Temporary Tablespace Offline:     
ALTER DATABASE TEMPFILE '<path_and_temp_file_name>' OFFLINE;
ALTER DATABASE TEMPFILE '/u02/oradata/sandbox.dbf' OFFLINE;
 
-- Place Temporary Tablespace Online:     
ALTER DATABASE TEMPFILE '<path_and_temp_file_name>' ONLINE;
ALTER DATABASE TEMPFILE '/u02/oradata/sandbox.dbf' ONLINE;
 
-- Changing the Default Temporary Tablespace: 
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE <tablespace_name>;
col property_value format a30
col description format a55
 
SELECT *
FROM database_properties
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';
 
SELECT file_name, tablespace_name
FROM dba_temp_files;
 
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE sandbox;
 


Free
Oracle Magazine
Subscriptions
and Oracle White Papers


SQL University.net courses meet the most demanding needs of the business world for advanced education in a cost-effective manner. SQL University.net courses are available immediately for IT professionals and can be taken without disruption of your workplace schedule or processes.

Compared to traditional travel-based training, SQL University.net saves time and valuable corporate resources, allowing companies to do more with less. That's our mission, and that's what we deliver.

Click here to find out more
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 72 users online    © 2009 psoug.org

PSOUG LOGIN
Username: 
Password: 
Forgot your password?