oracle临时表空间(2)

--8、查找消耗资源比较的sql语句
  Select se.username,
         se.sid,
         su.extents,
         su.blocks * to_number(rtrim(p.value)) as Space,
         tablespace,
         segtype,
         sql_text
    from v$sort_usage su, v$parameter p, v$session se, v$sql s
   where p.name = 'db_block_size'
     and su.session_addr = se.saddr
     and s.hash_value = su.sqlhash
     and s.address = su.sqladdr
   order by se.username, se.sid
  
--9、查看当前临时表空间使用大小与正在占用临时表空间的sql语句
  select sess.SID, segtype, blocks * 8 / 1000 "MB", sql_text
    from v$sort_usage sort, v$session sess, v$sql sql
   where sort.SESSION_ADDR = sess.SADDR
     and sql.ADDRESS = sess.SQL_ADDRESS
   order by blocks desc;

--10、临时表空间组介绍
    --1）创建临时表空间组：
    create temporary tablespace tempts1 tempfile 'C:\ORADATA\ORCL\TEMP1_01.DBF' size 2M tablespace group group1;
    create temporary tablespace tempts2 tempfile 'C:\ORADATA\ORCL\TEMP1_02.DBF' size 2M tablespace group group2;
    
    --2）查询临时表空间组：dba_tablespace_groups视图
    select * from dba_tablespace_groups;
    GROUP_NAME                     TABLESPACE_NAME
    ------------------------------ ------------------------------
    GROUP1                         TEMPTS1
    GROUP2                         TEMPTS2
    --3）将表空间从一个临时表空间组移动到另外一个临时表空间组：
    alter tablespace tempts1 tablespace group GROUP2 ;
    select * from dba_tablespace_groups;

    GROUP_NAME                     TABLESPACE_NAME