Shulou(Shulou.com)05/31 Report--

This article shares information about oracle 11g querying temporary tablespaces for usage and users who are using temporary tablespaces. Xiaobian thinks it is quite practical, so share it with everyone for reference. Let's follow Xiaobian and have a look.

1. Query usage of temporary tablespaces:

select c.tablespace_name,

to_char(c.bytes/1024/1024/1024,'99,999.999') total_gb,

to_char( (c.bytes-d.bytes_used)/1024/1024/1024,'99,999.999') free_gb,

to_char(d.bytes_used/1024/1024/1024,'99,999.999') use_gb,

to_char(d.bytes_used*100/c.bytes,'99.99') || '%'use

from (select tablespace_name,sum(bytes) bytes

from dba_temp_files GROUP by tablespace_name) c,

(select tablespace_name,sum(bytes_cached) bytes_used

from v$temp_extent_pool GROUP by tablespace_name) d

where c.tablespace_name = d.tablespace_name;

2. Query which users are using

select a.username,

a.sql_id,

a.SEGTYPE,

b.BYTES_USED/1024/1024/1024||'G',

b.BYTES_FREE/1024/1024/1024 from V$TEMPSEG_USAGE a join V$TEMP_SPACE_HEADER b on a.TABLESPACE=b.tablespace_name;

explain username The username of the user executing sql

sql_id sql_id of sql being executed

segtype What is the SQL statement being executed doing?

BYTES_USED Size of temporary table space used by executing sql statement

BYTES_FREE How many temporary tablespaces remain

You can see that there are 6 data files in this temporary table space. The query results show how many temporary data files are used and how many are left. Another problem is that the first user DBSNMP users have occupied the temporary table space. Then the second user can still use the temporary table space of the six data files. Personal understanding is that among the six data files, there is the first user in use and the second user in use.

Query whether there are large fields in the instance using temporary tablespaces:

select * from V$TEMPORARY_LOBS;

Explanation of the official documentation of the data dictionary V$TEMP_SPACE_HEADER:

V$TEMP_SPACE_HEADER displays aggregate information for each file in each LOCALLY MANAGEMENT temporary table space, including the amount of space currently in use and the amount of free space identified in the space header.

ColumnDatatypeDescriptionTABLESPACE_NAMEVARCHAR2(30)Name of the temporary tablespaceFILE_IDNUMBERAbsolute file numberBYTES_USEDNUMBERHow many bytes are in useBLOCKS_USEDNUMBERHow many blocks are in useBYTES_FREENUMBERHow many bytes are freeBLOCKS_FREENUMBERHow many blocks are freeRELATIVE_FNONUMBERThe relative file number for the file

Explanation of the official documentation for V$TEMPSEG_USAGE:

V$TEMPSEG_USAGE describes temporary segment usage.

Column data type description USERNAMEVARCHAR2(30) User requesting temporary space USERVARCHAR2(30) This column is obsolete and maintained for backward compatibility. The value of this column is always equal to the value USERNAME in. SESSION_ADDRRAW(4 |8) Session Address SESSION_NUMNUMBER Session Sequence Number SQLADDRRAW(4)| 8) Address of SQL statement SQLHASHNUMBERHash value of SQL statement SQL_IDVARCHAR2(13)SQL identifier of SQL statement TABLESPACEVARCH2 (31) Tablespace of allocated space CONTENTSVARCHAR2(9) Indicates whether the table is TEMPORARY or PERMANTSEGTYPEVARCHAR 2(9) Type of sort type:

SORT

HASH

DATA

INDEX

LOB_DATA

LOB_INDEX

SEGFILE#NUMBER file number of initial range SEGBLK#NUMBER block number of initial range EXTENTS NUMBER range BLOCKS NUMBER range allocated to sorted block SEGRFNO#NUMBER relative file number of initial range

Explanation in the official documentation for V$TEMP_EXTENT_POOL

V$TEMP_EXTENT_POOL Displays the status of temporary space cached and used for instances. Note that temporary space cache loading is lazy, and instances can be dormant.

Column data type description TABLESPACE_NAMEVARCHAR2(30) Name of table space FILE_IDNUMBER Absolute file number EXTENTS_CACHEDNUMBER Number of cached ranges EXTENTS_USEDNUMBER Number of ranges actually used BLOCKS_CACHEDNUMBER Number of blocks cached BLOCKS_USEDNUMBER Number of bytes cached BYTES_USEDNUMBER Number of bytes used RELATIVE_FNONUMBER Relative file number

About V$TEMPORARY_LOBS Official Documentation Explanation

V$TEMPORARY_LOBS Displays temporary LOBs.

Column data type description SIDNUMBER Session IDCACHE_LOBSNUMBER Number of temporary LOBs cached NOCACHE_LOBSNUMBERnocache Number of temporary LOBs ABSTRACT_LOBSNUMBER Number of abstract LOBs Thank you for reading! About "oracle 11g how to query the usage of temporary tablespaces and users who are using temporary tablespaces" This article is shared here, I hope the above content can be of some help to everyone, so that everyone can learn more knowledge, if you think the article is good, you can share it to let more people see it!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.