Oracle tablespace query and maintenance command Book 3 (temporary tablespace) the most comprehensive history, oracle command book

Oracle tablespace query and maintenance command Book 3 (temporary tablespace) the most comprehensive history, oracle command book
-- 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;


How to add data files to ORACLE or temporary tablespaces?

Background: when using the create table... as select statement to create a new table, the new table data volume is relatively large, such as 1 billion, then SQL * Plus is likely to prompt "ORA-01653 :... "error message. This error message indicates that the tablespace size is insufficient. You need to add data files to the tablespace. Analysis: 1. query the remaining BYTES of the table SPACE. SELECT TABLESPACE_NAME, SUM (BYTES)/1024/1024 as free space (M) FROM DBA_FREE_SPACEWHERE TABLESPACE_NAME = '& tablespace_name' group by TABLESPACE_NAME; Note: if it is a temporary tablespace, query DBA_TEMP_FREE_SPACESELECT TABLESPACE_NAME, FREE_SPACE/1024/1024 as free space (M) FROM DBA_TEMP_FREE_SPACEWHERE TABLESPACE_NAME = '& tablespace_name'; 2. if you do not know the table space data file directory planning, you can first query all the table space data files SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, BYTES/1024/1024 as bytes (M) FROM DBA_DATA_FILESWHERE TABLESPACE_NAME = '& tablespace_name'; Note: For temporary tablespace, query DBA_TEMP_FILESSELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, BYTES/1024/1024 as space (M) FROM DBA_TEMP_FILESWHERE TABLESPACE_NAME = '& tablespace_name'; 3. ADD the data file alter tablespace & tablespace_name add datafile & datafile_name 'size 2G for a TABLESPACE with insufficient space. Note: to resize a temporary TABLESPACE, use the following statement.

Oracle UNDO tablespace and temporary tablespace

First, it is certainly not a concept. The UNDO tablespace is used to store UNDO data. When a DML operation is executed, oracle will write the old data of these operations to the UNDO segment, to ensure rollback or consistent reading, the temporary tablespace is mainly used for querying and storing some buffer data. You have heard that UNDO is also a temporary table, probably because neither of the two tablespaces will store data permanently.