Problem Description
In the physical standby database alert log file shows following errors.
Errors in file /u01/app/oracle/diag/rdbms/bdafisdrs/bdafisdc1/trace/bdafisdc1_m001_19676.trc:
ORA-01187: cannot read from file because it failed verification tests
ORA-01110: data file 201: '+DATA/bdafisdrs/tempfile/temp'
Following is the contents found in the generated trace file.
Trace file /u01/app/oracle/diag/rdbms/bdafisdrs/bdafisdc1/trace/bdafisdc1_m001_19676.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing option
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name: Linux
Node name: DRS-DB-01
Release: 2.6.18-92.el5
Version: #1 SMP Tue Apr 29 13:16:15 EDT 2008
Machine: x86_64
Instance name: bdafisdc1
Redo thread mounted by this instance: 1
Oracle process number: 77
Unix process pid: 19676, image: oracle@DRS-DB-01 (M001)
*** 2010-09-19 11:35:51.595
*** SESSION ID:(103.167) 2010-09-19 11:35:51.595
*** CLIENT ID:() 2010-09-19 11:35:51.595
*** SERVICE NAME:(SYS$BACKGROUND) 2010-09-19 11:35:51.595
*** MODULE NAME:(MMON_SLAVE) 2010-09-19 11:35:51.595
*** ACTION NAME:(Autotask Slave Action) 2010-09-19 11:35:51.595
DDE: Problem Key 'ORA 1110' was flood controlled (0x5) (no incident)
ORA-01110: data file 201: '+DATA/bdafisdrs/tempfile/temp'
ORA-01187: cannot read from file because it failed verification tests
ORA-01110: data file 201: '+DATA/bdafisdrs/tempfile/temp'
Dump of memory from 0x0000000363A3B078 to 0x0000000363A3B3EA
...
...
...
ket_get_active: error 1187
ket_aba_main[2] : error 1187
ket_aba_slave: clearing error 1187
Cause of the Problem
Scenario 01: If your Oracle database version is 8i and you are able to activate the standby database with no errors, however, each time you try to open the standby database in read only mode it fails with the above errors, then possibly it is due to READ_ONLY_OPEN_DELAYED parameter setting.
Scenario 02:
If database files reside on an ocfs and datafiles appear to corrupt to nodes other than "node1" (instance1) in a RAC then problem happened because filesystem was mounted with the "reclaimid" option.
Solution of the Problem
Solution for Scenario 01:
Set READ_ONLY_OPEN_DELAYED parameter to false and then you will be able to open the database in read only mode.
SQL> Alter system set READ_ONLY_OPEN_DELAYED = FALSE scope=spfile;
SQL> shut immediate
SQL> startup
Solution for Scenario 02:
While mounting do not use the reclaimid mount option for "normal" mounts. This option should only be used once after having to regenerate the guid of a node after ip address change.
Showing posts with label Temp. Show all posts
Showing posts with label Temp. Show all posts
Monday, September 27, 2010
Saturday, September 13, 2008
ORA-12906 cannot drop default temporary tablespace
In this post I have shown how to solve the error ORA-12906: cannot drop default temporary tablespace.
Let's have a look about temporary tablespace assigned to users ARJU, PROD and SCOTT.
So These users are assigned to TEMP temporary tablespace.
Now have a look at database default tablespace.
SQL> select PROPERTY_VALUE from database_properties
where property_name = 'DEFAULT_TEMP_TABLESPACE';
PROPERTY_VALUE
--------------------------------------------------------------------------------
TEMP
Whenever you try to drop database default tablespace it fails with error ORA-12906.
SQL> drop tablespace temp;
drop tablespace temp
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace
In order to solve this problem you must assign database default tenporary tablespace to a new one.
To do this create a new tempoary tablespace temp2 and then assign it to database default tablespace.
To create a new one,
SQL> create temporary tablespace temp2 tempfile '/oradata2/temp2.dbf' size 10M;
Tablespace created.
To make this new one to database default temporary tablespace,
SQL> alter database default temporary tablespace temp2;
Database altered.
After chaging database default temporary tablespace you will notice all user's temporary tablespace are also changed.
You can easily drop by,
SQL> drop tablespace temp;
Tablespace dropped.
Related Documents
Free space in Temporary Tablespace
The operation that require sort area or Temporary Tablespace
Information about Temporary Segments.
Let's have a look about temporary tablespace assigned to users ARJU, PROD and SCOTT.
SQL> select username, temporary_tablespace from dba_users where username in ('ARJU','SCOTT','PROD');
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
ARJU TEMP
PROD TEMP
SCOTT TEMP
So These users are assigned to TEMP temporary tablespace.
Now have a look at database default tablespace.
SQL> select PROPERTY_VALUE from database_properties
where property_name = 'DEFAULT_TEMP_TABLESPACE';
PROPERTY_VALUE
--------------------------------------------------------------------------------
TEMP
Whenever you try to drop database default tablespace it fails with error ORA-12906.
SQL> drop tablespace temp;
drop tablespace temp
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace
In order to solve this problem you must assign database default tenporary tablespace to a new one.
To do this create a new tempoary tablespace temp2 and then assign it to database default tablespace.
To create a new one,
SQL> create temporary tablespace temp2 tempfile '/oradata2/temp2.dbf' size 10M;
Tablespace created.
To make this new one to database default temporary tablespace,
SQL> alter database default temporary tablespace temp2;
Database altered.
After chaging database default temporary tablespace you will notice all user's temporary tablespace are also changed.
SQL> select username, temporary_tablespace from dba_users where username in ('ARJU','SCOTT','PROD');
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
ARJU TEMP2
PROD TEMP2
SCOTT TEMP2
You can easily drop by,
SQL> drop tablespace temp;
Tablespace dropped.
Related Documents
Free space in Temporary Tablespace
The operation that require sort area or Temporary Tablespace
Information about Temporary Segments.
Wednesday, September 10, 2008
How to recover or recreate temporary tablespace in 10g
In database you may discover that your temporary tablespace is deleted from OS or it might got corrupted. In order to get it back you might think about recover it. The recovery process is simply recover the temporary file from backup and roll the file forward using archived log files if you are in archivelog mode.
Another solution is simply drop the temporary tablespace and then create a new one and assign new one to the database users.
In order to do that follow the steps here.
1)Find out the temporary datafiles.
2)Make the affected temporary files offline.
SQL> Alter database tempfile 1,2,4 offline;
Database altered.
3)Create a new temporary tablespace and make it database default tablespace.
SQL> create temporary tablespace temp01 tempfile '/oradata2/temp.dbf' size 10M;
Tablespace created.
SQL> alter database default temporary tablespace temp01;
Database altered.
3)Check for users who are not pointed to this default temporary tablespaces. Make this default for those users also.
4)Explicitly assign temporary tablespace for users TEST2 and ARJU.
SQL> alter user arju temporary tablespace temp01;
User altered.
SQL> alter user test2 temporary tablespace temp01;
User altered.
3)Drop the old temporary tablespace.
SQL> drop tablespace temp;
Tablespace dropped.
SQL> drop tablespace temp2;
Tablespace dropped.
SQL> drop tablespace temp3;
Tablespace dropped.
Related Documents
Drop Temporary Tablespace Hangs
ORA-12906 cannot drop default temporary tablespace
Free space in Temporary Tablespace
Implicitly Assigned temp tablespace changes after database default tablespace change
Information about Temporary Segments.
The operation that require sort area or Temporary Tablespace
Another solution is simply drop the temporary tablespace and then create a new one and assign new one to the database users.
In order to do that follow the steps here.
1)Find out the temporary datafiles.
SQL> col file_name format a50
SQL> set linesize 200
SQL> select file_name,file_id, tablespace_name from dba_temp_files;
FILE_NAME FILE_ID TABLESPACE_NAME
-------------------------------------------------- ---------- ------------------------------
/oradata2/temp2.dbf 1 TEMP2
/oradata2/temp.dbf 2 TEMP
/oradata2/temp3.dbf 4 TEMP3
2)Make the affected temporary files offline.
SQL> Alter database tempfile 1,2,4 offline;
Database altered.
3)Create a new temporary tablespace and make it database default tablespace.
SQL> create temporary tablespace temp01 tempfile '/oradata2/temp.dbf' size 10M;
Tablespace created.
SQL> alter database default temporary tablespace temp01;
Database altered.
3)Check for users who are not pointed to this default temporary tablespaces. Make this default for those users also.
SQL> select temporary_tablespace , username from dba_users where temporary_tablespace<>'TEMP01';
TEMPORARY_TABLESPACE USERNAME
------------------------------ ------------------------------
TEMP TEST2
TEMP2 ARJU
4)Explicitly assign temporary tablespace for users TEST2 and ARJU.
SQL> alter user arju temporary tablespace temp01;
User altered.
SQL> alter user test2 temporary tablespace temp01;
User altered.
3)Drop the old temporary tablespace.
SQL> drop tablespace temp;
Tablespace dropped.
SQL> drop tablespace temp2;
Tablespace dropped.
SQL> drop tablespace temp3;
Tablespace dropped.
Related Documents
Drop Temporary Tablespace Hangs
ORA-12906 cannot drop default temporary tablespace
Free space in Temporary Tablespace
Implicitly Assigned temp tablespace changes after database default tablespace change
Information about Temporary Segments.
The operation that require sort area or Temporary Tablespace
Monday, June 9, 2008
Feature and Restriction of Temporary Table
Feature of Temporary Table
-----------------------------------
•Indexes can be created on temporary tables. They are also temporary and the data in the index has the same session or transaction scope as the data in the underlying table.
•Unlike permanent tables, temporary tables and their indexes do not automatically allocate a segment when they are created. Instead, segments are allocated when the first INSERT (or CREATE TABLE AS SELECT) is performed. This means that if a SELECT, UPDATE, or DELETE is performed before the first INSERT, the table appears to be empty.
•DDL operations (except TRUNCATE) are allowed on an existing temporary table only if no session is currently bound to that temporary table.
•If you rollback a transaction, the data you entered is lost, although the table definition persists.
•A transaction-specific temporary table allows only one transaction at a time. If there are several autonomous transactions in a single transaction scope, each autonomous transaction can use the table only as soon as the previous one commits.
•Because the data in a temporary table is, by definition, temporary, backup and recovery of temporary table data is not available in the event of a system failure.
•It is good to know about that temporary table itself is not temporary, the data within it is temporary.
Restriction of Temporary Table
-----------------------------------------------
•Temporary tables cannot be partitioned, clustered, or index organized.
•You cannot specify any foreign key constraints on temporary tables.
•Temporary tables cannot contain columns of nested table.
•You cannot specify the following clauses of the LOB_storage_clause: TABLESPACE, storage_clause, or logging_clause.
•Parallel DML and parallel queries are not supported for temporary tables. Parallel hints are ignored. Specification of the parallel_clause returns an error.
•You cannot specify the segment_attributes_clause, nested_table_col_properties, or parallel_clause.
•Distributed transactions are not supported for temporary tables.
Related Documents
-----------------------------
Create Temporary Table in Oracle
-----------------------------------
•Indexes can be created on temporary tables. They are also temporary and the data in the index has the same session or transaction scope as the data in the underlying table.
•Unlike permanent tables, temporary tables and their indexes do not automatically allocate a segment when they are created. Instead, segments are allocated when the first INSERT (or CREATE TABLE AS SELECT) is performed. This means that if a SELECT, UPDATE, or DELETE is performed before the first INSERT, the table appears to be empty.
•DDL operations (except TRUNCATE) are allowed on an existing temporary table only if no session is currently bound to that temporary table.
•If you rollback a transaction, the data you entered is lost, although the table definition persists.
•A transaction-specific temporary table allows only one transaction at a time. If there are several autonomous transactions in a single transaction scope, each autonomous transaction can use the table only as soon as the previous one commits.
•Because the data in a temporary table is, by definition, temporary, backup and recovery of temporary table data is not available in the event of a system failure.
•It is good to know about that temporary table itself is not temporary, the data within it is temporary.
Restriction of Temporary Table
-----------------------------------------------
•Temporary tables cannot be partitioned, clustered, or index organized.
•You cannot specify any foreign key constraints on temporary tables.
•Temporary tables cannot contain columns of nested table.
•You cannot specify the following clauses of the LOB_storage_clause: TABLESPACE, storage_clause, or logging_clause.
•Parallel DML and parallel queries are not supported for temporary tables. Parallel hints are ignored. Specification of the parallel_clause returns an error.
•You cannot specify the segment_attributes_clause, nested_table_col_properties, or parallel_clause.
•Distributed transactions are not supported for temporary tables.
Related Documents
-----------------------------
Create Temporary Table in Oracle
Monday, May 12, 2008
Free space in Temporary Tablespace
In database there may be permanent and temporary tablespace. The view DBA_FREE_SPACE allows us to show about how much free space in a tablespace have but DBA_FREE_SPACE shows information only about permanent tablespace. It does not show information about temporary tablespace.
In order to know about temporary tablespace free space usage we have to query V$TEMP_SPACE_HEADER view. With an example it is described.
1)Create one temporary and Permanent Tablespace.
I used OMF file system. To know about it search about OMF in my blog.
SQL> ALTER SYSTEM SET db_create_file_dest='/oradata2';
System altered.
SQL> CREATE TABLESPACE PERMANENT_T;
Tablespace created.
SQL> CREATE TEMPORARY TABLESPACE TEMP_T;
Tablespace created.
2)Query DBA_FREE_SPACE and we will see PERMANENT_T is listed but TEMP_T is not listed.
SQL> SELECT DISTINCT TABLESPACE_NAME FROM DBA_FREE_SPACE;
TABLESPACE_NAME
------------------------------
UNDOTBS1
SYSAUX
USERS
DATA
SYSTEM
PERMANENT_T
6 rows selected.
3)To know information about free space of temporary tablespace query V$TEMP_SPACE_HEADER.
SQL> SELECT TABLESPACE_NAME, FILE_ID, BYTES_USED, BYTES_FREE FROM V$TEMP_SPACE_HEADER;
TABLESPACE_NAME FILE_ID BYTES_USED BYTES_FREE
------------------------------ ---------- ---------- ----------
TEMP 1 6291456 14680064
TEMP_T 2 1048576 103809024
In order to know about temporary tablespace free space usage we have to query V$TEMP_SPACE_HEADER view. With an example it is described.
1)Create one temporary and Permanent Tablespace.
I used OMF file system. To know about it search about OMF in my blog.
SQL> ALTER SYSTEM SET db_create_file_dest='/oradata2';
System altered.
SQL> CREATE TABLESPACE PERMANENT_T;
Tablespace created.
SQL> CREATE TEMPORARY TABLESPACE TEMP_T;
Tablespace created.
2)Query DBA_FREE_SPACE and we will see PERMANENT_T is listed but TEMP_T is not listed.
SQL> SELECT DISTINCT TABLESPACE_NAME FROM DBA_FREE_SPACE;
TABLESPACE_NAME
------------------------------
UNDOTBS1
SYSAUX
USERS
DATA
SYSTEM
PERMANENT_T
6 rows selected.
3)To know information about free space of temporary tablespace query V$TEMP_SPACE_HEADER.
SQL> SELECT TABLESPACE_NAME, FILE_ID, BYTES_USED, BYTES_FREE FROM V$TEMP_SPACE_HEADER;
TABLESPACE_NAME FILE_ID BYTES_USED BYTES_FREE
------------------------------ ---------- ---------- ----------
TEMP 1 6291456 14680064
TEMP_T 2 1048576 103809024
Sunday, May 11, 2008
The operation that require sort area or Temporary Tablespace
Whenever a sort occurs within a database it needs sort area. Primarily memory area is used to sort. If there is not sufficient memory then it is needed temporary segments where database writes data in order to sort. There are several operation which needs sort space. They are,
1)Index creation.
The CREATE INDEX statement causes the server process to sort the index values before building the tree. After the sort a final index is built in the tablespaces by using a temporary segment.
2)ORDER BY or GROUP BY clauses of SELECT statements.
The server process must sort on the values in the ORDER BY or GROUP BY clauses.
3)DISTINCT values of SELECT statements.
For the DISTINCT keyword, the data is at first sorted in order to eliminate duplicates.
4)UNION, INTERSECT or MINUS operations.
Servers need to sort the tables they are working on to eliminate duplicates.
5)Sort-Merge joins.
If no index is available, an equivalent-join request needs to perform full table scans and sort each row source separately. After that, the sorted sources are merged together, combining each row from one source with each matching row of the other source.
6)Analyze command execution.
The Analyze command sorts the data to provide summarized information.
7)Various SQL Statements.
The CREATE PRIMARY KEY CONSTRAINT, ENABLE CONSTRAINT, and CREATE TABLE statements require sort segment.
8)CREATE TABLE AS SELECT.
The creation of a new table can start as a temporary segment if MINEXTENTS is larger than 1 or when using the statement CREATE TABLE AS SELECT.
1)Index creation.
The CREATE INDEX statement causes the server process to sort the index values before building the tree. After the sort a final index is built in the tablespaces by using a temporary segment.
2)ORDER BY or GROUP BY clauses of SELECT statements.
The server process must sort on the values in the ORDER BY or GROUP BY clauses.
3)DISTINCT values of SELECT statements.
For the DISTINCT keyword, the data is at first sorted in order to eliminate duplicates.
4)UNION, INTERSECT or MINUS operations.
Servers need to sort the tables they are working on to eliminate duplicates.
5)Sort-Merge joins.
If no index is available, an equivalent-join request needs to perform full table scans and sort each row source separately. After that, the sorted sources are merged together, combining each row from one source with each matching row of the other source.
6)Analyze command execution.
The Analyze command sorts the data to provide summarized information.
7)Various SQL Statements.
The CREATE PRIMARY KEY CONSTRAINT, ENABLE CONSTRAINT, and CREATE TABLE statements require sort segment.
8)CREATE TABLE AS SELECT.
The creation of a new table can start as a temporary segment if MINEXTENTS is larger than 1 or when using the statement CREATE TABLE AS SELECT.
Tuesday, May 6, 2008
Information about Temporary Segments.
A)The users who is Performing Sort operation in Temp Segments:
--------------------------------------------------------------------
SQL> SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,a.username,a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;
TABLESPACE SEGFILE# SEGBLK# BLOCKS SID SERIAL# USERNAME OSUSER STATUS
----------- ------------- ---------- ---------- ---------- ---------- ------ ------ -------
TEMP 201 217865 748928 277 1185 PROD7 oracle ACTIVE
B)Information about Tablespace Containing sort Segments.
SQL> SELECT tablespace_name, extent_size, total_extents, used_extents,
free_extents, max_used_size
FROM v$sort_segment;
TABLESPACE_NAME EXTENT_SIZE TOTAL_EXTENTS USED_EXTENTS FREE_EXTENTS MAX_USED_SIZE
------------------------------- ----------- ------------- ------------ ------------ -------------
TEMP 128 11896 5851 6045 11322
Here,
Extent_size : size of one extent, in number of Oracle blocks
Total_extents: total number of extents in the segment (free or in use)
Used_extents : total number of extents currently in use
Free_extents : total number of extents currently marked as free
Max_used_size: maximum number of extents ever needed by an operation
C)If you want to keep interest of how much space is used in temporary segments then query as,
SELECT EXTENT_SIZE*DB_BLOCK_SIZE*USED_EXTENTS/1024/1024/1024 "Space used in GigaByte" FROM v$sort_segment ;
If your database block size is 8192 (Query from select value from v$parameter where NAME='db_block_size';) then the space used by temp segments is ,
SQL>SELECT EXTENT_SIZE*8192*USED_EXTENTS/1024/1024/1024 "Space used in GigaByte" FROM v$sort_segment;
Space used in GigaByte
----------------------
11.6181641
From V$SORT_SEGMENT you can also query like,
SQL> SELECT TABLESPACE_NAME,EXTENT_SIZE,TOTAL_EXTENTS,USED_EXTENTS,FREE_EXTENTS,MAX_USED_BLOCKS,TOTAL_BLOCKS from V$SORT_SEGMENT;
TABLESPACE EXTENT_SIZE TOTAL_EXTENTS USED_EXTENTS FREE_EXTENTS MAX_USED_BLOCKS TOTAL_BLOCKS
---------- ----------- ------------- ------------ ------------ --------------- ------------
TEMP 128 11896 4526 7370 1449216 1522688
D)In order to know SQL ID and which type of Sort Segment issue,
SQL>SELECT USERNAME, SQLADDR,SQL_ID,TABLESPACE,SEGTYPE,EXTENTS,BLOCKS FROM V$TEMPSEG_USAGE;
USERNAME SQLADDR SQL_ID TABLESP SEGTYPE EXTENTS BLOCKS
------------ ---------------- ------------- ------- --------- ---------- ----------
SYSTEM 00000004129E28C0 b058ymxj1rvkg TEMP LOB_DATA 1 128
PROD7 0000000412E1C9F8 1t1v0wvyzwzuj TEMP SORT 5193 664704
E)To know which SQL is is using Temporary Segments query,
SQL> SELECT s.SQL_TEXT, t.USERNAME,t.TABLESPACE,t.SEGTYPE,t.BLOCKS,t.EXTENTS from V$SQL s, V$TEMPSEG_USAGE t WHERE t.SQL_ID=s.SQL_ID;
SQL_TEXT
------------------------------------------------------------------------------------
-------------------------------------------------------------
USERNAME TABLESP SEGTYPE BLOCKS EXTENTS
------------------------------ ------- --------- ---------- ----------
SELECT sql_id,sql_text from v$sql WHERE sql_id in (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10)
SYSTEM TEMP LOB_DATA 128 1
select * from user_activity order by ACTION,COOKIE_INFO
PROD7 TEMP SORT 36736 287
--------------------------------------------------------------------
SQL> SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,a.username,a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;
TABLESPACE SEGFILE# SEGBLK# BLOCKS SID SERIAL# USERNAME OSUSER STATUS
----------- ------------- ---------- ---------- ---------- ---------- ------ ------ -------
TEMP 201 217865 748928 277 1185 PROD7 oracle ACTIVE
B)Information about Tablespace Containing sort Segments.
SQL> SELECT tablespace_name, extent_size, total_extents, used_extents,
free_extents, max_used_size
FROM v$sort_segment;
TABLESPACE_NAME EXTENT_SIZE TOTAL_EXTENTS USED_EXTENTS FREE_EXTENTS MAX_USED_SIZE
------------------------------- ----------- ------------- ------------ ------------ -------------
TEMP 128 11896 5851 6045 11322
Here,
Extent_size : size of one extent, in number of Oracle blocks
Total_extents: total number of extents in the segment (free or in use)
Used_extents : total number of extents currently in use
Free_extents : total number of extents currently marked as free
Max_used_size: maximum number of extents ever needed by an operation
C)If you want to keep interest of how much space is used in temporary segments then query as,
SELECT EXTENT_SIZE*DB_BLOCK_SIZE*USED_EXTENTS/1024/1024/1024 "Space used in GigaByte" FROM v$sort_segment ;
If your database block size is 8192 (Query from select value from v$parameter where NAME='db_block_size';) then the space used by temp segments is ,
SQL>SELECT EXTENT_SIZE*8192*USED_EXTENTS/1024/1024/1024 "Space used in GigaByte" FROM v$sort_segment;
Space used in GigaByte
----------------------
11.6181641
From V$SORT_SEGMENT you can also query like,
SQL> SELECT TABLESPACE_NAME,EXTENT_SIZE,TOTAL_EXTENTS,USED_EXTENTS,FREE_EXTENTS,MAX_USED_BLOCKS,TOTAL_BLOCKS from V$SORT_SEGMENT;
TABLESPACE EXTENT_SIZE TOTAL_EXTENTS USED_EXTENTS FREE_EXTENTS MAX_USED_BLOCKS TOTAL_BLOCKS
---------- ----------- ------------- ------------ ------------ --------------- ------------
TEMP 128 11896 4526 7370 1449216 1522688
D)In order to know SQL ID and which type of Sort Segment issue,
SQL>SELECT USERNAME, SQLADDR,SQL_ID,TABLESPACE,SEGTYPE,EXTENTS,BLOCKS FROM V$TEMPSEG_USAGE;
USERNAME SQLADDR SQL_ID TABLESP SEGTYPE EXTENTS BLOCKS
------------ ---------------- ------------- ------- --------- ---------- ----------
SYSTEM 00000004129E28C0 b058ymxj1rvkg TEMP LOB_DATA 1 128
PROD7 0000000412E1C9F8 1t1v0wvyzwzuj TEMP SORT 5193 664704
E)To know which SQL is is using Temporary Segments query,
SQL> SELECT s.SQL_TEXT, t.USERNAME,t.TABLESPACE,t.SEGTYPE,t.BLOCKS,t.EXTENTS from V$SQL s, V$TEMPSEG_USAGE t WHERE t.SQL_ID=s.SQL_ID;
SQL_TEXT
------------------------------------------------------------------------------------
-------------------------------------------------------------
USERNAME TABLESP SEGTYPE BLOCKS EXTENTS
------------------------------ ------- --------- ---------- ----------
SELECT sql_id,sql_text from v$sql WHERE sql_id in (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10)
SYSTEM TEMP LOB_DATA 128 1
select * from user_activity order by ACTION,COOKIE_INFO
PROD7 TEMP SORT 36736 287
Subscribe to:
Posts (Atom)