Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

SQL > alter database temporary tablespace temp;

SQL > alter tablespace temp; alter tablespace temp;

Create temporary tablespace tempData
create temporary tablespace tempdata tempfile '/oradata/orcl/tempdata01.dbf' size 30g autoextend off;
Add temporary tablespace datafiles
alter tablespace tempdata add tempfile '/oradata/orcl/tempdata02.dbf' size 30g autoextend off;
Delete temporary tablespace data files
alter tablespace tempdata drop tempfile '/oradata/orcl/tempdata02.dbf' including datafiles;
-- Resize temporary tablespace data files
alter database tempfile '/oradata/orcl/tempdata01.dbf' resize 2G;
-- Set automatic extension
alter database tempfile '/oradata/orcl/tempdata01.dbf' autoextend on;
-- Toggle default temporary tablespaces
alter database default temporary tablespace tempdata;
Delete temporary tablespace temp
drop tablespace temp including contents and datafiles cascade constraints;
-- Shrink temporary tablespace temp
alter tablespace temp shrink space keep 8G;
alter tablespace temp shrink tempfile '/oradata/orcl/tempdata01.dbf';
Copy the code

To view the current default temporary tablespace:

SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
Copy the code

Query the temp tablespace usage:

select  df.tablespace_name "Tablespace",
       df.totalspace "Total(MB)",
       nvl(FS.UsedSpace, 0)  "Used(MB)",
       (df.totalspace - nvl(FS.UsedSpace, 0)) "Free(MB)",
       round(100 * (1-( nvl(fs.UsedSpace, 0) / df.totalspace)), 2) "Pct. Free(%)"
FROM  (SELECT tablespace_name, round(SUM(bytes) / 1048576) TotalSpace
        FROM   dba_TEMP_files
        GROUP  BY tablespace_name) df,
       (SELECT tablespace_name, ROUND(SUM(bytes_used) / 1024 / 1024)  UsedSpace
        FROM   gV$temp_extent_pool
        GROUP  BY tablespace_name) fs  WHERE  df.tablespace_name = fs.tablespace_name(+)
Copy the code

Query the usage of temporary files in a temporary tablespace:

SELECT TABLESPACE_NAME         AS TABLESPACE_NAME    ,
    BYTES_USED/1024/1024/1024    AS TABLESAPCE_USED  ,
    BYTES_FREE/1024/1024/1024  AS TABLESAPCE_FREE
FROM V$TEMP_SPACE_HEADER
ORDER BY 1 DESC;
Copy the code

SQL > alter tablespace temp; alter tablespace temp;

set linesize 260 pagesize 1000
col machine for a40
col program for a40
SELECT se.username,
       sid,
       serial#,
       se.sql_id
       machine,
       program,
       tablespace,
       segtype,
       (su.BLOCKS*8/1024/1024) GB
  FROM v$session se, v$sort_usage su
 WHERE se.saddr = su.session_addr
 order by su.BLOCKS desc;

SQL > select * from v$sort_usage; SQL > select * from v$sort_usage; SQL > select * from v$sort_usage
Copy the code

SQL > select * from temp tablespace temp;

select a.SQL_ID,
       a.SAMPLE_TIME,
       a.program,
       sum(trunc(a.TEMP_SPACE_ALLOCATED / 1024 / 1024)) MB
  from v$active_session_history a
 where TEMP_SPACE_ALLOCATED is not null 
 and sample_time between
 to_date('&date1'.'yyyy-mm-dd hh24:mi:ss') and
 to_date('&date2'.'yyyy-mm-dd hh24:mi:ss')
 group by a.sql_id,a.SAMPLE_TIME,a.PROGRAM
 order by 2 asc.4 desc;
Copy the code

This is the end of sharing ~

If you think the article is helpful to you, please like it, favorites it, pay attention to it, comment on it, and support it four times with one button. Your support is the biggest motivation for my creation.

❤️ technical exchange can follow the public number: Lucifer think twice before you do ❤️