--undo Table Space Summary
--View all tablespace names
SELECT NAME from V$tablespace;
--Create a new undo table space and set your own active extension 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: Only one undo table space can be used at some point in the open state. Assuming that you want to use the newly created tablespace, you must switch to that table space:
ALTER SYSTEM SET undo_tablespace = UNDO2;
--Change the initiative to manage yourself
ALTER SYSTEM SET undo_management = AUTO SCOPE = SPFILE;
Change the table space management of UNDO to MANUAL ALTER SYSTEM SET undo_management = MANUAL SCOPE = SPFILE;
--Change
--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;
--Change 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;
--Change the default temporary 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;
--Take the initiative to narrow the table space'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, another temporary table space detailed steps Summary:
1. Create a 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. Rebuild 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 newly created temp table space ALTER DATABASE default temporary tablespace temp;
To verify that the temporary tablespace for the user is temp
SELECT USERNAME, temporary_tablespace from Dba_users;
3. See who is using the 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 table Space Temp spare situation
SELECT Tablespace_name,
FILE_ID,
bytes_used/1024/1024,
bytes_free/1024/1024
From V$temp_space_header;
5, detailed 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 number of references. You can see the corresponding information by viewing 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 tablespace */
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 the sort segment and data segment in the temporary 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 three (temporary tablespace) History of the most complete
