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;
|
