Oracle tablespace query and maintenance command Daquan 3 (temporary tablespace) the most comprehensive history

Oracle tablespace query and maintenance command Daquan 3 (temporary tablespace) the most comprehensive history
-- UNDO tablespace Summary
-- View All tablespace names
Select name from v $ TABLESPACE;
-- Create a new UNDO tablespace and set automatic expansion parameters;
Create undo tablespace UNDOTBS2 DATAFILE 'd: \ ORACLE \ PRODUCT \ 10.1.0 \ ORADATA \ ORCL \ UNDOTBS02.DBF 'size 10 m reuse autoextend on next 100 m maxsize unlimited;
-- Note: In the OPEN state, only one UNDO tablespace can be used at some time. To use a new tablespace, you must switch to the tablespace:
Alter system set UNDO_TABLESPACE = UNDO2;
-- Change to automatic management
Alter system set UNDO_MANAGEMENT = auto scope = SPFILE;

Modify the tablespace Management Mode of UNDO to manual alter system set UNDO_MANAGEMENT = manual scope = SPFILE;
-- Modify

-- Wait for all undo segment offlines of the original UNDO tablespace;
Select usn,
XACTS,
STATUS,
RSSIZE/1024/1024/1024,
HWMSIZE/1024/1024/1024,
SHRINKS
From v $ ROLLSTAT
Order by rssize;
-- Delete a tablespace
Drop tablespace UNDO1 including contents and datafiles;
-- Change the size of the UODO tablespace
Alter database datafile '/U2/ORADATA/SPRING/undotbs01.dbf' RESIZE 1024 M;

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. Temporary 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;
View default temporary tablespace
SELECT *
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'default _ TEMP_TABLESPACE ';
-- Create a temporary tablespace

Create temporary tablespace TEMP_DATA TEMPFILE '/ORACLE/ORADATA/DB/temp_data.dbf' SIZE 50 M

Create temporary tablespace temp tempfile '/U02/ORADATA/ORCL/TEMP01.DBF 'size 6144 M,'/U02/ORADATA/ORCL/TEMP02.DBF 'SIZE 6144 M;

-- Modify the size of the temporary tablespace
Alter database tempfile '/U2/ORADATA/SPRING/temp_data.dbf' RESIZE 1024 M;

Create temporary tablespace TEMP1 TEMPFILE '/U02/ORADATA/ORCL/temp101.dbf' SIZE 4056 M;

-- Modify the default temporary tablespace of the database
Alter database default temporary tablespace TEMP1;

-- Delete temporary tablespace
Drop tablespace temp including contents and datafiles;

-- Clear temporary tablespace
Alter tablespace temp shrink space keep 20 M;
-- Automatically reduces the temporary file size of the tablespace to the minimum possible size.
Alter tablespace temp shrink tempfile '/U02/ORACLE/DATA/LMTEMP02.DBF ';

2. The temporary tablespace is too large. The specific steps for restarting the temporary tablespace are summarized as follows:

1. create temporary tablespace CREATETEMPORARYTABLESPACETEMP1 TEMPFILE '/ORACLE/ORADATA/SECOOLER/TEMP02.DBF' SIZE 512 m reuse autoextend on next 1 m maxsize unlimited;

2. Change the default temporary tablespace to the newly created temporary tablespace 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 tablespace drop tablespace temp including contents and datafiles;

4. Recreate the temporary tablespace create temporary tablespace temp tempfile '/ORACLE/ORADATA/SECOOLER/temp01.dbf' SIZE 512 m reuse autoextend on next 1 m maxsize unlimited;

5. Reset the default temporary tablespace to the new temp tablespace alter database default temporary tablespace temp;
Verify that the user's temporary tablespace is TEMP

Select username, TEMPORARY_TABLESPACE FROM DBA_USERS;

3. check who is using the temporary tablespace.
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 the TEMP idle state of the temporary tablespace
SELECT TABLESPACE_NAME,
FILE_ID,
BYTES_USED/1024/1024,
BYTES_FREE/1024/1024
From v $ TEMP_SPACE_HEADER;

5. Specific usage of a SID temporary tablespace
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 creating a user,
There is a default tablespace parameter. You can view the view DATABASE_PROPERTIES to see the corresponding information.

Select a. PROPERTY_NAME, A. PROPERTY_VALUE
FROM DATABASE_PROPERTIES
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) * 100 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 sorting and data segments 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) * 100 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;