--undo Table Space Summary
--View all table space names
SELECT NAME from V$tablespace;
--Create a new undo table space and set the auto-expand parameters;
CREATE UNDO tablespace UNDOTBS2 datafile ' D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS02. DBF ' SIZE 10M reuse autoextend on NEXT 100M MAXSIZE UNLIMITED;
-Note: In the open state, only one undo tablespace can be used at some point, and if you want to use a new tablespace, you must switch to that tablespace:
ALTER SYSTEM SET undo_tablespace = UNDO2;
--Modify to manage automatically
ALTER SYSTEM SET undo_management = AUTO SCOPE = SPFILE;
Modify the table space management for UNDO for MANUAL ALTER SYSTEM SET undo_management = MANUAL SCOPE = SPFILE;
--Modification
--Wait for the original undo table space all undo SEGMENT OFFLINE;
SELECT USN,
Xacts,
STATUS,
rssize/1024/1024/1024,
hwmsize/1024/1024/1024,
Shrinks
From V$rollstat
ORDER by Rssize;
--Delete Table space
DROP tablespace UNDO1 including CONTENTS and datafiles;
--Change the size of the Uodo table space
ALTER DATABASE datafile '/u2/oradata/spring/undotbs01. DBF ' RESIZE 1024M;
SELECT Tablespace_name, Segment_type, OWNER, segment_name
From Dba_extents
WHERE file_id = 8
and 565129 between block_id and block_id + BLOCKS-1;
1, the primary function of the temporal tablespace: index create or rebuild; ORDER by or GROUP by; DISTINCT operation; UNION or INTERSECT or minus; Sort-merge JOINS; ANALYZE.
SELECT USERNAME, temporary_tablespace from Dba_users;
To view the default temporary table space
SELECT *
From Database_properties
WHERE property_name = ' default_temp_tablespace ';
--Create a temporary table space
CREATE temporary tablespace temp_data tempfile '/oracle/oradata/db/temp_data. DBF ' SIZE 50M
CREATE temporary tablespace TEMP tempfile '/u02/oradata/orcl/orcl/temp01. DBF ' SIZE 6144M, '/u02/oradata/orcl/orcl/temp02. DBF ' SIZE 6144M;
--Modify the size of the temporary table space
ALTER DATABASE tempfile '/u2/oradata/spring/temp_data. DBF ' RESIZE 1024M;
CREATE temporary tablespace TEMP1 tempfile '/u02/oradata/orcl/orcl/temp101. DBF ' SIZE 4056M;
--Modify the default temp table space for the database
ALTER DATABASE DEFAULT temporary tablespace TEMP1;
--Delete temporary table space
DROP tablespace TEMP including CONTENTS and datafiles;
--Clean up temporary table space
ALTER tablespace TEMP SHRINK SPACE KEEP 20M;
--Automatically reduce the tablespace's temporary files to the smallest possible size
ALTER tablespace TEMP SHRINK tempfile '/U02/ORACLE/DATA/LMTEMP02. DBF ';
2, the temporary table space is too large, the specific steps to re-temporary table space are summarized:
1. Create Transit temporary tablespace CREATETEMPORARYTABLESPACETEMP1 tempfile '/oracle/oradata/secooler/temp02. DBF ' SIZE 512M reuse autoextend on NEXT 1M MAXSIZE UNLIMITED;
2. Change the default temporary tablespace to the new temporary tablespace that you just created TEMP1 ALTER DATABASE default temporary tablespace TEMP1;
Verify that the user's temporary tablespace is TEMP1
SELECT USERNAME, temporary_tablespace from Dba_users;
3. Delete the original temporary table space drop tablespace TEMP including CONTENTS and datafiles;
4. Recreate the temporary tablespace CREATE temporary tablespace TEMP tempfile '/oracle/oradata/secooler/temp01. DBF ' SIZE 512M reuse autoextend on NEXT 1M MAXSIZE UNLIMITED;
5. Reset the default temporary tablespace to the new temp table space ALTER DATABASE default temporary tablespace temp;
Verify that the user's temp table space is temp
SELECT USERNAME, temporary_tablespace from Dba_users;
3. See who is using temporary table space
SELECT SE. USERNAME,
SE. Sid
SE. serial#,
SE. Sql_address,
SE. Machine,
SE. Program,
Su. Tablespace,
Su. Segtype,
Su. CONTENTS from V$session SE,
V$sort_usage SU WHERE SE. SADDR = SU. SESSION_ADDR;
4. View Temporary tablespace temp idle condition
SELECT Tablespace_name,
FILE_ID,
bytes_used/1024/1024,
bytes_free/1024/1024
From V$temp_space_header;
5, specific to a SID temporary tablespace usage
SELECT B.tablespace,
b.segfile#,
b.segblk#,
B.blocks,
B.blocks * 32/1024/1024,
A.sid,
a.serial#,
A.username,
A.osuser,
A.status,
C.sql_text,
B.contents
From V$session A, V$sort_usage B, V$sql C
WHERE a.saddr = b.session_addr
and a.sql_address = c.address (+)
ORDER by B.blocks DESC
When you create a user,
There is a default table space for the parameter. You can see the corresponding information by looking at the view database_properties.
SELECT A.property_name, A.property_value
From Database_properties A
WHERE a.property_name like '%default% ';
6,/* View the overall usage of the temporary table space */
SELECT Tmp_tbs. Tablespace_name,
SUM (Tmp_tbs. TOTAL_MB) TOTAL_MB,
SUM (Used_tot. USED_MB) USED_MB,
SUM (Used_tot. USED_MB)/SUM (Tmp_tbs. TOTAL_MB) * Used_persent
From (SELECT tablespace_name, SUM (BYTES)/1024/1024 TOTAL_MB
From Dba_temp_files
GROUP by Tablespace_name) Tmp_tbs,
(SELECT tmp_used. Tablespace,
SUM (tmp_used. BLOCKS * PARA. db_block_size)/1024/1024 USED_MB
From V$sort_usage tmp_used,
(SELECT VALUE Db_block_size
From V$parameter
WHERE NAME = ' db_block_size ') PARA
GROUP by tmp_used. Tablespace) Used_tot
WHERE Tmp_tbs. Tablespace_name = Used_tot. Tablespace (+)
GROUP by Tmp_tbs. Tablespace_name;
7,/* View the usage of sort segments and data segments in the staging tablespace */
SELECT Tmp_tbs. Tablespace_name,
Used_tot. Segtype Temp_seg_type,
SUM (Tmp_tbs. TOTAL_MB) TOTAL_MB,
SUM (Used_tot. USED_MB) USED_MB,
SUM (Used_tot. USED_MB)/SUM (Tmp_tbs. TOTAL_MB) * Used_persent
From (SELECT tablespace_name, SUM (BYTES)/1024/1024 TOTAL_MB
From Dba_temp_files
GROUP by Tablespace_name) Tmp_tbs,
(SELECT tmp_used. Tablespace,
Tmp_used. Segtype,
SUM (tmp_used. BLOCKS * PARA. db_block_size)/1024/1024 USED_MB
From V$sort_usage tmp_used,
(SELECT VALUE Db_block_size
From V$parameter
WHERE NAME = ' db_block_size ') PARA
GROUP by tmp_used. Tablespace, tmp_used. Segtype) Used_tot
WHERE Tmp_tbs. Tablespace_name = Used_tot. Tablespace (+)
GROUP by Tmp_tbs. Tablespace_name, Used_tot. Segtype;
Oracle Table Space Query Maintenance command Daquan III (temporary tablespace) the most complete in history
