Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • Disbaling DBA_SCHEDULER_JOBS Oracle
  • Changing the Global Database Name Oracle
  • Creating never expiring DB user accounts in Oracle Oracle
  • Adding addidional hard drive and attach it to a linux box. Linux/Unix
  • move_arch_files.ksh Linux/Unix
  • Useful Solaris Commands on 28-SEP-2005 Linux/Unix
  • How does one SELECT a value from a table into a Unix variable? From SQL to Shell Linux/Unix
  • Removing first line Linux/Unix
  • How to set Processes Parameter Oracle
  • Reclaim temp tablespace for oracle 8, 8i Oracle
  • Reading parameter file and printing Linux/Unix
  • Explain Plan Output 2 Oracle
  • moving lob object to other tablespace lob_mvmt.sql Oracle
  • upload.html PHP/MYSQL/Wordpress
  • chk_space_SID.ksh Linux/Unix

Temporary tablespace explaination

Posted on 10-Apr-2008 By Admin No Comments on Temporary tablespace explaination

A new mechanism to control the allocation of temporary storage space was

introduced in 7.3, initially as support for OPS, but it is equally applicable

to non-OPS environments. This requires the creation of a designated temporary

tablespace, that is one created with the designation TEMPORARY. The effect of

this is twofold:

1. No permanent objects (tables, indexes, etc.) can be stored in such a

tablespace.

2. A temporary segment created in this tablespace will not be de-allocated

at the end of the sort. Instead the extents are MARKED as free, are not

placed back on the freelist, and are retained for reuse. An element called

the Sort Extent Pool (SEP) is created in the SGA to describe them.

Subsequent sort operations will then be allocated extents from this pool

via a memory lookup, thereby avoiding most of the space management tasks

described above.

The temporary segment containing this “pool” of extents is referred to as an

“instance” and there will be one for each OPS instance opening the database.

Each instance will maintain its own SEP. The view V$SORT_SEGMENT makes these

visible. It contains information about every sort segment created in the

LOCAL instance. So, if node 1 executed a statement which created a sort

segment, node 2 will not see the entry in V$SORT_SEGMENT unless it has

already executed some statements which created a sort segment in node 2’s

instance.

The view DBA_SEGMENTS contains information about segments in the entire

database. So, if all nodes create sort segments, there will be multiple

entries in DBA_SEGMENTS, one for each node.

A single instance configuration will have just the one segment, hence one

pool of extents.

Another advantage for an OPS environment occurs when a sort requires an extent

and there are none free in the pool. The segments may grow in the usual way,

but if another instance does have a free extent, this can be re-assigned from

one pool to the other.

During a sort operation, the number of sort segment(s) created should

eventually grow to a stable point where no new extents are allocated. If

large sorts are occurring, which take up all of the available space, then

the operation may receive an ORA-1652. The solution here is to add additional

files to the tablespace.

It has also been observed that in an OPS environmemt ORA-1652 messages occur

in the alert.log while the re-assignment from one instance’s SEP free extents

to the requesting one seems to be working fine because client sessions

do not get the ORA-1652. This suggests that the re-allocation is internally

triggered by event 1652, and the ORA-1652 is not surpressed from being

written to the alert.log. This can be quite confusing for DBA’s.

SQL commands

============

1. CREATE TABLESPACE

DATAFILE …

DEFAULT STORAGE (…)

TEMPORARY;

or

ALTER TABLESPACE

TEMPORARY;

2. ALTER USER TEMPORARY TABLESPACE ;

N.B. The DEFAULT STORAGE clause governs the size of the sort extents created

within a tablespace. This should always be a multiple of the SORT_AREA_SIZE

specified in the init.ora file for each instance.

Oracle, SQL scripts

Post navigation

Previous Post: restarting network in linux
Next Post: changing kernel parameter in Oracle Enterpise Linux

Related Posts

  • good note for shared pool tunnig Oracle
  • Adding Datafile on Primary Server and Impact on Standby Server Oracle
  • Export With Query Another Example. Oracle
  • Caching sequence in Memory Oracle
  • sid_wise_sql.sql Oracle
  • TOP-N Sql to find Nth max or Top N rows Oracle

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Categories

  • AWS (2)
  • Azure (1)
  • Linux/Unix (149)
  • Oracle (392)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (341)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • load SPM baseline from cursor cache05-Jun-2025
  • Drop all SPM baselines for SQL handle05-Jun-2025
  • Load SPM baseline from AWR05-Jun-2025
  • Drop specific SQL plan baseline – spm05-Jun-2025
  • findinfo.sql (SQL for getting CPU and Active session info)27-May-2025
  • SQL Tracker by SID sqltrackerbysid.sql22-Apr-2025
  • How to connect to Oracle Database with Wallet with Python.21-Mar-2025
  • JSON/XML Types in Oracle18-Mar-2025
  • CPU Core related projections12-Mar-2025
  • Exadata Basics10-Dec-2024

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • changing kernel parameter in Oracle Enterpise Linux Linux/Unix
  • find_log_switch.sql Find log switches in graphical manner Oracle
  • avail.sh ( find filesystem spae usage) Linux/Unix
  • My Minimum Tuning Programs Oracle
  • CPU speed on solaris Linux/Unix
  • Jai Shree Ram Oracle
  • PHP code to add WordPress posts in bulk programmatically PHP/MYSQL/Wordpress
  • Physical Standby switchover with session active Oracle

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme