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

This article mainly explains "Oracle how to view and modify temporary tablespaces", the content of the explanation is simple and clear, easy to learn and understand, now please follow the editor's ideas slowly in depth, together to study and learn "Oracle how to view and modify temporary tablespaces"!

-check tablespace usage (including temporary tablespaces)

SELECT d.tablespace_name "Name", d.status "Status"

TO_CHAR (NVL / 1024 / 1024, 0), '99999990.90') "Size (M)"

TO_CHAR (NVL (a.BYTES-NVL (f.BYTES, 0), 0) / 1024 / 1024

'99999999.99'

) USE

TO_CHAR (NVL ((a.BYTES-NVL (f.BYTES, 0)) / a.BYTES * 100,0)

'990.00'

) "Used%"

FROM SYS.dba_tablespaces d

(SELECT tablespace_name, SUM (BYTES) BYTES

FROM dba_data_files

GROUP BY tablespace_name) a

(SELECT tablespace_name, SUM (BYTES) BYTES

FROM dba_free_space

GROUP BY tablespace_name) f

WHERE d.tablespace_name = a.tablespace_name (+)

AND d.tablespace_name = f.tablespace_name (+)

AND NOT (d.extent_management LIKE 'LOCAL' AND d.CONTENTS LIKE' TEMPORARY')

UNION ALL

SELECT d.tablespace_name "Name", d.status "Status"

TO_CHAR (NVL / 1024 / 1024, 0), '99999990.90') "Size (M)"

TO_CHAR (NVL (t.BYTES, 0) / 1024 / 1024, '99999999.99') USE

TO_CHAR (NVL (t.BYTES / a.BYTES * 100,0), '990.00') "Used%"

FROM SYS.dba_tablespaces d

(SELECT tablespace_name, SUM (BYTES) BYTES

FROM dba_temp_files

GROUP BY tablespace_name) a

(SELECT tablespace_name, SUM (bytes_cached) BYTES

FROM v$temp_extent_pool

GROUP BY tablespace_name) t

WHERE d.tablespace_name = a.tablespace_name (+)

AND d.tablespace_name = t.tablespace_name (+)

AND d.extent_management LIKE 'LOCAL'

AND d.CONTENTS LIKE 'TEMPORARY'

1. Query tablespace remaining byte size

SELECT TABLESPACE_NAME, SUM (BYTES) / 1024 plus 1024 AS "FREE SPACE (M)"

FROM DBA_FREE_SPACE

WHERE TABLESPACE_NAME ='& tablespace_name'

GROUP BY TABLESPACE_NAME

Note: if it is a temporary tablespace, please query DBA_TEMP_FREE_SPACE

SELECT TABLESPACE_NAME, FREE_SPACE/1024/1024 AS "FREE SPACE (M)"

FROM DBA_TEMP_FREE_SPACE

WHERE TABLESPACE_NAME ='& tablespace_name'

two。 Query all data file paths in the tablespace

SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, BYTES/1024/1024 AS "BYTES (M)"

FROM DBA_DATA_FILES

WHERE TABLESPACE_NAME ='& tablespace_name'

Note: if it is a temporary tablespace, please query DBA_TEMP_FILES

SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, BYTES/1024/1024 AS "SPACE (M)"

FROM DBA_TEMP_FILES

WHERE TABLESPACE_NAME ='& tablespace_name'

3. Add data files for insufficient tablespaces

ALTER TABLESPACE & tablespace_name ADD DATAFILE'& datafile_name' SIZE 2G

Note: if you want to expand the temporary tablespace, use the following statement

ALTER TABLESPACE & tablespace_name ADD TEMPFILE'& datafile_name' SIZE 2G

4. View the size of the temporary tablespace and the data file path

SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, BYTES/1024/1024 AS "SPACE (M)"

FROM DBA_TEMP_FILES

WHERE TABLESPACE_NAME = 'TEMP'

Or

Select name, bytes/1024/1024 as "size (M)" from v$tempfile order by bytes

5. Rebuild and modify the default temporary tablespace approach:

-- query the default temporary table space name of the current database

Select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE'

Create a new temporary tablespace

Create temporary tablespace temp02 tempfile'E:\ oracle\ oradata\ lims\ TEMP02.DBF' size 1024m autoextend on

-- change the default tablespace to the temporary tablespace just created

Alter database default temporary tablespace temp02

-- View the temporary tablespaces used by the user

SELECT USERNAME,TEMPORARY_TABLESPACE FROM DBA_USERS

-- delete the original temporary tablespace

Drop tablespace temp including contents and datafiles

-- check all tablespace names to confirm that temporary tablespaces have been deleted

Select tablespace_name from dba_tablespaces

Thank you for your reading, the above is the content of "how to view and modify temporary tablespaces by Oracle". After the study of this article, I believe you have a deeper understanding of how to view and modify temporary tablespaces by Oracle. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.