表空间相关命令

查询表空间创建的时间
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
select t.name tablespace_name, min(d.creation_time) tablespace_create_time from v$datafile d, v$tablespace t where d.ts# = t.ts# group by t.name order by 1;

检查有关表空间的信息
select * from v$tablespace;

检查有关表空间的信息，很多输出 
select * from DBA_TABLESPACES;

所有的表空间
SELECT tablespace_name
FROM   dba_tablespaces
ORDER BY 1;

查看数据文件和表空间（好用的）
set line 999 pages 999
col FILE_NAME format a50
col tablespace_name format a15
Select tablespace_name, file_name, autoextensible, bytes/1024/1024/1024 "USEDSPACE GB", maxbytes/1024/1024/1024 "MAXSIZE GB" from dba_data_files order by tablespace_name;

表空间使用    包括临时表空间。
set linesize 200;
column "Tablespace" format a15;
column "Used GB" format 999,999.99
column "Free GB" format 999,999.99
column "Allocated GB" format 999,999.99
column "Used % of Allocated" format 99.99;
column "Maximum GB" format 999,999.99
column "Used % of Maximum" format 99.99;

SELECT NVL (b.tablespace_name, NVL (a.tablespace_name, 'UNKNOWN')) "Tablespace",
  gbytes_alloc - NVL (gbytes_free, 0) "Used GB",
  NVL (gbytes_free, 0) "Free GB",
  gbytes_alloc "Allocated GB",
  ( (gbytes_alloc - NVL (gbytes_free, 0)) / gbytes_alloc) * 100 "Used % of Allocated",
  gbytes_max "Maximum GB",
  CASE
    WHEN gbytes_max > 0
    THEN ( (gbytes_alloc - NVL (gbytes_free, 0)) / gbytes_max) * 100
  END "Used % of Maximum"
FROM
  (SELECT SUM (bytes) / 1024 / 1024 / 1024 gbytes_free,
    tablespace_name
  FROM sys.dba_free_space
  GROUP BY tablespace_name
  UNION
  SELECT SUM (free_space) / 1024 / 1024 / 1024 gbytes_free,
    tablespace_name
  FROM sys.dba_temp_free_space
  GROUP BY tablespace_name
  ) a,
  (SELECT SUM (bytes) / 1024 / 1024 / 1024 gbytes_alloc,
    SUM (maxbytes)    / 1024 / 1024 / 1024 gbytes_max,
    tablespace_name,
    COUNT (*) data_files
  FROM sys.dba_data_files
  GROUP BY tablespace_name
  UNION
  SELECT SUM (bytes) / 1024 / 1024 / 1024 gbytes_alloc,
    SUM (maxbytes)   / 1024 / 1024 / 1024 gbytes_max,
    tablespace_name,
    COUNT (*) data_files
  FROM sys.dba_temp_files
  GROUP BY tablespace_name
  ) b
WHERE a.tablespace_name(+) = b.tablespace_name
AND ( b.tablespace_name IS NULL
OR INSTR (LOWER (b.tablespace_name), LOWER (b.tablespace_name)) > 0)
ORDER BY 7 DESC NULLS LAST;

只监视超过特定最大容量阈值（例如 80%）的表空间
SELECT *
FROM
  (SELECT NVL (b.tablespace_name, NVL (a.tablespace_name, 'UNKNOWN')) "Tablespace",
    gbytes_alloc - NVL (gbytes_free, 0) "Used GB",
    NVL (gbytes_free, 0) "Free GB",
    gbytes_alloc "Allocated GB",
    ( (gbytes_alloc - NVL (gbytes_free, 0)) / gbytes_alloc) * 100 "Used % of Allocated",
    gbytes_max "Maximum GB",
    CASE
      WHEN gbytes_max > 0
      THEN ( (gbytes_alloc - NVL (gbytes_free, 0)) / gbytes_max) * 100
    END "Used % of Maximum"
  FROM
    (SELECT SUM (bytes) / 1024 / 1024 / 1024 gbytes_free,
      tablespace_name
    FROM sys.dba_free_space
    GROUP BY tablespace_name
    UNION
    SELECT SUM (free_space) / 1024 / 1024 / 1024 gbytes_free,
      tablespace_name
    FROM sys.dba_temp_free_space
    GROUP BY tablespace_name
    ) a,
    (SELECT SUM (bytes) / 1024 / 1024 / 1024 gbytes_alloc,
      SUM (maxbytes)    / 1024 / 1024 / 1024 gbytes_max,
      tablespace_name,
      COUNT (*) data_files
    FROM sys.dba_data_files
    GROUP BY tablespace_name
    UNION
    SELECT SUM (bytes) / 1024 / 1024 / 1024 gbytes_alloc,
      SUM (maxbytes)   / 1024 / 1024 / 1024 gbytes_max,
      tablespace_name,
      COUNT (*) data_files
    FROM sys.dba_temp_files
    GROUP BY tablespace_name
    ) b
  WHERE a.tablespace_name(+)                                      = b.tablespace_name
  AND ( b.tablespace_name                                        IS NULL
  OR INSTR (LOWER (b.tablespace_name), LOWER (b.tablespace_name)) > 0)
  ORDER BY 7 DESC NULLS LAST
  )
WHERE "Used % of Maximum" > 80;

兆字节的显示
set linesize 200;
column "Tablespace" format a15;
column "Used MB" format 999,999.99
column "Free MB" format 999,999.99
column "Allocated MB" format 999,999.99
column "Used % of Allocated" format 99.99;
column "Maximum MB" format 999,999.99
column "Used % of Maximum" format 99.99;

--Check Tablespace Usage
SELECT NVL (b.tablespace_name, NVL (a.tablespace_name, 'UNKNOWN')) "Tablespace",
  gbytes_alloc - NVL (gbytes_free, 0) "Used MB",
  NVL (gbytes_free, 0) "Free MB",
  gbytes_alloc "Allocated MB",
  ( (gbytes_alloc - NVL (gbytes_free, 0)) / gbytes_alloc) * 100 "Used % of Allocated",
  gbytes_max "Maximum MB",
  CASE
    WHEN gbytes_max > 0
    THEN ( (gbytes_alloc - NVL (gbytes_free, 0)) / gbytes_max) * 100
  END "Used % of Maximum"
FROM
  (SELECT SUM (bytes) / 1024 / 1024 gbytes_free,
    tablespace_name
  FROM sys.dba_free_space
  GROUP BY tablespace_name
  UNION
  SELECT SUM (free_space) / 1024 / 1024 gbytes_free,
    tablespace_name
  FROM sys.dba_temp_free_space
  GROUP BY tablespace_name
  ) a,
  (SELECT SUM (bytes) / 1024 / 1024 gbytes_alloc,
    SUM (maxbytes)    / 1024 / 1024 gbytes_max,
    tablespace_name,
    COUNT (*) data_files
  FROM sys.dba_data_files
  GROUP BY tablespace_name
  UNION
  SELECT SUM (bytes) / 1024 / 1024 gbytes_alloc,
    SUM (maxbytes)   / 1024 / 1024 gbytes_max,
    tablespace_name,
    COUNT (*) data_files
  FROM sys.dba_temp_files
  GROUP BY tablespace_name
  ) b
WHERE a.tablespace_name(+) = b.tablespace_name
AND ( b.tablespace_name IS NULL
OR INSTR (LOWER (b.tablespace_name), LOWER (b.tablespace_name)) > 0)
ORDER BY 7 DESC NULLS LAST;

创建表空间，单个数据文件
create tablespace test datafile '/u02/oradata/CDB1/pdb1/test_01.dbf' size 10m autoextend on next 10m maxsize unlimited;

创建表空间，多个数据文件
create tablespace test_lh datafile '/u02/oradata/CDB1/pdb1/test_lh_01.dbf' size 10m autoextend on next 10m maxsize unlimited, '/u02/oradata/CDB1/pdb1/test_lh_02.dbf' size 10m autoextend on next 10m maxsize unlimited, '/u02/oradata/CDB1/pdb1/test_lh_03.dbf' size 10m autoextend on next 10m maxsize unlimited;

OMF创建表空间，单个数据文件
CREATE TABLESPACE test_ts DATAFILE SIZE 100M AUTOEXTEND ON NEXT 10M;

OMF创建表空间，多个数据文件
create tablespace test datafile size 10m autoextend on next 10m maxsize unlimited,  size 10m autoextend on next 10m maxsize unlimited,  size 10m autoextend on next 10m maxsize unlimited;

检查当前默认永久表空间
COLUMN PROPERTY_VALUE FORMAT A10
select property_value from database_properties where property_name = 'DEFAULT_PERMANENT_TABLESPACE';

检查用户的默认表空间
select default_tablespace from dba_users where username = 'LH_YONGHU';

设置用户默认表空间
alter user LH_YONGHU default tablespace TEST_BIAOKONGJIAN;

表空间重命名
alter tablespace TEST_BIAOKONGJIAN rename to TEST_BKJ;

指定用户使用表空间大小
alter user LH_YONGHU quota 10m on TEST_BKJ;

查询用户表空间配额
SELECT * FROM DBA_TS_QUOTAS where username = 'LH_YONGHU';

查询用户表空间配额
select tablespace_name, case max_bytes when -1 then 'UNLIMITED' else to_char(max_bytes/1024/1024/1024) end "QUOTA(GB)" from dba_ts_quotas where username = 'LH_YONGHU';

查询用户表空间配额，还剩下多少空间（列出所有有配额的用户）
set pagesize 200
set lines 200
col ownr format a20 justify c heading 'Owner'
col name format a20 justify c heading 'Tablespace' trunc
col qota format a12 justify c heading 'Quota (KB)'
col used format 999,999,990 justify c heading 'Used (KB)'
set colsep '|'
select
username ownr,
tablespace_name name,
decode(greatest(max_bytes, -1),
-1, 'UNLIMITED',
to_char(max_bytes/1024, '999,999,990')
) qota,
bytes/1024 used
from
dba_ts_quotas
where
max_bytes!=0
or
bytes!=0
order by
1,2
/

输入用户查询表空间配额详细信息
set colsep '|'
select
username ownr,
tablespace_name name,
decode(greatest(max_bytes, -1),
-1, 'UNLIMITED',
to_char(max_bytes/1024, '999,999,990')
) qota,
bytes/1024 used
from
dba_ts_quotas
where
( max_bytes!=0
or
bytes!=0) and username='&USERNAME'
order by
1,2
/

指定表空间无限配额
alter user LH_YONGHU quota unlimited on TEST_BKJ;

关闭配额
alter user LH_YONGHU quota 0 on TEST_BKJ;

表空间的状态
select status from dba_tablespaces where tablespace_name = 'TEST_BKJ';

表空间脱机
alter tablespace TEST_BKJ offline;

表空间联机
alter tablespace TEST_BKJ online;

表空间切换到只读模
alter tablespace TEST_BKJ read only;

表空间切换到读写模
alter tablespace TEST_BKJ read write;

删除表空间    同时删除数据文件
alter tablespace TEST_TS offline;
drop tablespace TEST_TS including contents and datafiles;

允许的数据文件数
show parameter db_files

数据库的默认块大小
show parameter db_block_size;

数据库的默认表空间类型
select property_value from database_properties where property_name = 'DEFAULT_TBS_TYPE';

数据文件创建日期
set linesize 200;
column name format a60;
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select name, creation_time from v$datafile order by 1;

查看表空间中的数据文件
column file_name format a60;
select file_id, file_name from dba_data_files where tablespace_name = 'TEST_BKJ';

数据文件是否在Oracle中自动扩展，查看指定表空间
SELECT autoextensible FROM dba_data_files WHERE tablespace_name = 'TEST_BKJ';

数据文件是否在Oracle中自动扩展（所有的）
SELECT file_name,autoextensible FROM dba_Data_files;

数据文件是否自动扩展，指定表空间
是否扩展和最大数据文件大小
SELECT file_name,bytes/1024/1024/1024 "curentsize GB", maxbytes/1024/1024/1024 "MaxSize GB", 
autoextensible FROM dba_data_files where tablespace_name = 'TEST_BKJ';

禁用数据文件的自动扩展
alter database datafile '/oradata/CDB1/pdb1/test_01.dbf' autoextend off;

启用数据文件的自动扩展
alter database datafile '/oradata/CDB1/pdb1/test_01.dbf' autoextend on;

设置数据文件的最大大小
alter database datafile '/oradata/CDB1/pdb1/test_01.dbf' autoextend on maxsize 5G;


通过数据文件查找表空间
select t.name tablespace_name from v$datafile d inner join v$tablespace t on d.ts# = t.ts# where d.name in ('/u02/oradata/CDB1/pdb1/test_01.dbf');

重命名数据文件
alter tablespace TEST offline;
select status from dba_tablespaces where tablespace_name = 'TEST';
cd /u02/oradata/CDB1/pdb1/
mv test_01.dbf test_a.dbf
alter database rename file '/u02/oradata/CDB1/pdb1/test_01.dbf' to '/u02/oradata/CDB1/pdb1/test_a.dbf';
alter tablespace TEST online;
select status from dba_tablespaces where tablespace_name = 'TEST';

通过数据文件查找表空间    可以是不同表空间
column datafile_name format a40;
column tablespace_name format a20;
select d.name datafile_name, t.name tablespace_name from v$datafile d inner join v$tablespace t on d.ts# = t.ts# where d.name in ('/u02/oradata/CDB1/pdb1/test_a.dbf', '/u02/oradata/CDB1/pdb1/test_lh_01.dbf');

离线移动数据文件
通过数据文件查找表空间
select d.name datafile_name, t.name tablespace_name from v$datafile d inner join v$tablespace t on d.ts# = t.ts# where d.name in ('/u02/oradata/CDB1/pdb1/test_a.dbf', '/u02/oradata/CDB1/pdb1/test_lh_01.dbf');
ARCHIVELOG模式，您可以使数据文件而不是表空间脱机。
alter database datafile '/u02/oradata/CDB1/pdb1/test_a.dbf' offline;
alter database datafile '/u02/oradata/CDB1/pdb1/test_lh_01.dbf' offline;
将数据文件复制到新位
cp -ip /u02/oradata/CDB1/pdb1/test_a.dbf /u03/oradata/CDB1/pdb1/test_a.dbf
cp -ip /u02/oradata/CDB1/pdb1/test_lh_01.dbf /u03/oradata/CDB1/pdb1/test_lh_01.dbf
重命名文件
alter database rename file '/u02/oradata/CDB1/pdb1/test_a.dbf' to '/u03/oradata/CDB1/pdb1/test_a.dbf';
alter database rename file '/u02/oradata/CDB1/pdb1/test_lh_01.dbf'to '/u03/oradata/CDB1/pdb1/test_lh_01.dbf';
联机之前恢复了这两个数据文件。别忘了，这仅适用于ARCHIVELOG模式
recover datafile '/u03/oradata/CDB1/pdb1/test_a.dbf';
recover datafile '/u03/oradata/CDB1/pdb1/test_lh_01.dbf'
alter database datafile '/u03/oradata/CDB1/pdb1/test_a.dbf' online;
alter database datafile '/u03/oradata/CDB1/pdb1/test_lh_01.dbf' online;
删除未使用的数据文件
rm -i /u02/oradata/CDB1/pdb1/test_a.dbf
rm -i /u02/oradata/CDB1/pdb1/test_lh_01.dbf

离线移动数据文件        离线表空间方法
select d.name datafile_name, t.name tablespace_name from v$datafile d inner join v$tablespace t on d.ts# = t.ts# where d.name in ('/u03/oradata/CDB1/pdb1/test_a.dbf', '/u03/oradata/CDB1/pdb1/test_lh_01.dbf');
离线表空间
alter tablespace TEST offline;
alter tablespace TEST_LH offline;
将数据文件复制到新位置
cp -ip /u03/oradata/CDB1/pdb1/test_a.dbf /u02/oradata/CDB1/pdb1/test_a.dbf
cp -ip /u03/oradata/CDB1/pdb1/test_lh_01.dbf /u02/oradata/CDB1/pdb1/test_lh_01.dbf
在表空间级别进行重命名
alter tablespace TEST rename datafile '/u03/oradata/CDB1/pdb1/test_a.dbf' to '/u02/oradata/CDB1/pdb1/test_a.dbf';
alter tablespace TEST_LH rename datafile '/u03/oradata/CDB1/pdb1/test_lh_01.dbf' to '/u02/oradata/CDB1/pdb1/test_lh_01.dbf';
在线表空间
alter tablespace TEST online;
alter tablespace TEST_LH online;
删除未使用的数据文件
rm -i /u03/oradata/CDB1/pdb1/test_a.dbf
rm -i /u03/oradata/CDB1/pdb1/test_lh_01.dbf

RMAN移动PDB表空间（所有数据文件）
查询表空间里的数据文件
select file_id, file_name from dba_data_files where tablespace_name = 'TEST_LH';
使表空间脱机
alter tablespace TEST_LH offline;
备份为副本
rman target sys/AAbb1234@PDB1
backup as copy db_file_name_convert ('/u02/oradata/CDB1/pdb1/','/u03/oradata/CDB1/pdb1/') tablespace pdb1:test_lh;
切换表空间进行复制
switch tablespace pdb1:test_lh to copy;
在线表空间
alter tablespace TEST_LH online;

联机移动数据文件        使用文件号移动数据文件
alter database move datafile '/u02/oradata/CDB1/pdb1/test_a.dbf' to '/u03/oradata/CDB1/pdb1/test_a.dbf' reuse;
alter database move datafile 13 to '/u02/oradata/CDB1/pdb1/test_a.dbf' reuse;

查询文件号
select file# from v$datafile where name = '/u03/oradata/CDB1/pdb1/test_a.dbf';

查表空间中的表
column segment_name format a10;
column owner format a10;
select owner, segment_type, segment_name from dba_segments where tablespace_name = 'TEST_BKJ' order by 1,2,3;

查看表空间中的表，显示大小
select owner, segment_type, segment_name, bytes/1024/1024 "SIZE(MB)" from dba_segments where tablespace_name = 'TEST_BKJ' order by 4 desc;

输入用户和表，查询所在的表空间
select distinct tablespace_name from dba_segments where owner = 'LH_YONGHU' and segment_name = 'EMP' and segment_type like '%TABLE%';

查看临时表空间文件详细信息
包含临时表空间组
col db_name for a10
col tablespace_name for a10
col file_name for a25
SELECT vc2.name "db_name",tf.file_name, tf.tablespace_name,
autoextensible, maxbytes/1024/1024 "Max_MB", SUM(tf.bytes)/1024/1024 "MB_SIZE"
FROM v$containers vc2, cdb_temp_files tf
WHERE vc2.con_id = tf.con_id
GROUP BY vc2.name,tf.file_name, tf.tablespace_name, autoextensible, maxbytes
ORDER BY 1, 2;

临时表空间的总体大小
显示组里的总大小
Col name for a10
col tablespace_name for a15
SELECT  vc2.name, tf.tablespace_name, sum(decode(autoextensible,'NO',bytes,'YES',maxbytes))/1024/1024 "Max Bytes", SUM(tf.bytes)/1024/1024
FROM v$containers vc2, cdb_temp_files tf
WHERE vc2.con_id = tf.con_id
GROUP BY vc2.name, tf.tablespace_name
ORDER BY 1, 2;

查看临时表空间和数据文件
select file_id,file_name,tablespace_name from dba_temp_files;

查看临时表空间的数据文件
select file#,name,bytes/1024/1024 from v$tempfile;

创建临时表空间，包含一个临时文件
create temporary tablespace temp1 tempfile '/u02/oradata/CDB1/pdb1/temp1_01.dbf' size 10m autoextend on next 10m maxsize unlimited;

omf创建临时表空间，包含一个临时文件
create temporary tablespace temp1 tempfile  size 10m autoextend on next 10m maxsize unlimited;

创建临时表空间，包含多个临时文件
create temporary tablespace temp2 tempfile '/u02/oradata/CDB1/pdb1/temp2_01.dbf' size 10m autoextend on next 10m maxsize unlimited, '/u02/oradata/CDB1/pdb1/temp2_02.dbf' size 10m autoextend on next 10m maxsize unlimited, '/u02/oradata/CDB1/pdb1/temp2_03.dbf' size 10m autoextend on next 10m maxsize unlimited;

omf创建临时表空间，包含多个临时文件
create temporary tablespace temp2 tempfile  size 10m autoextend on next 10m maxsize unlimited, 
size 10m autoextend on next 10m maxsize unlimited,  size 10m autoextend on next 10m maxsize unlimited;

添加临时文件
alter tablespace temp1 add tempfile '/u02/oradata/CDB1/pdb1/temp1_02.dbf' size 100m autoextend on next 10m maxsize unlimited;

omf添加临时文件
alter tablespace TEMP1 add tempfile size 1G autoextend on next 200M;

调整临时文件大小
alter database tempfile '/u02/oradata/CDB1/pdb1/temp1_01.dbf' resize 24g;

OMF调整临时文件大小
alter database tempfile '/u02/oradata/CDB1/F87B8DE671A61501E0530B011FAC99E8/datafile/o1_mf_temp1_l34mtkng_.tmp' resize 2G;

删除现有的临时表空间数据文件
alter database tempfile '/u02/oradata/CDB1/F87B8DE671A61501E0530B011FAC99E8/datafile/o1_mf_temp1_l34mtkng_.tmp'DROP INCLUDING DATAFILES;

删除临时表空间，包括物理文件
DROP TABLESPACE temp1 INCLUDING CONTENTS AND DATAFILES;

收缩临时文件
查看临时表空间大小
select sum(bytes)/1024/1024/1024 gb from dba_temp_files where tablespace_name = 'TEMP1';
方法一：缩小临时文件
alter database tempfile '/u02/oradata/CDB1/pdb1/temp1_01.dbf' resize 20g;
方法二：尽可能地缩小整个表空间
alter tablespace temp1 shrink space;
方法三：删除临时文件
alter database tempfile '/u02/oradata/CDB1/pdb1/temp1_02.dbf' offline;
alter database tempfile '/u02/oradata/CDB1/pdb1/temp1_02.dbf' drop including datafiles;

重命名临时文件
哪个表空间拥有这个临时文件
select t.name tablespace_name from v$tempfile d inner 
join v$tablespace t on d.ts# = t.ts# where d.name in ('/u02/oradata/CDB1/pdb1/temp1_01.dbf');
离线临时文件
alter database tempfile '/u02/oradata/CDB1/pdb1/temp1_01.dbf' offline;
重命名物理文件
cd /u02/oradata/CDB1/pdb1/
mv temp1_01.dbf temp1_aa.dbf
重命名临时文件
alter database rename file '/u02/oradata/CDB1/pdb1/temp1_01.dbf' to '/u02/oradata/CDB1/pdb1/temp1_aa.dbf';
在线临时文件
alter database tempfile '/u02/oradata/CDB1/pdb1/temp1_aa.dbf' online;

移动临时文件位置
检查当前的临时文件
column name format a60;
select name, status from v$tempfile;
添加一个新的临时文件(在不同位置)
alter tablespace temp add tempfile '/oradata/CDB1/pdb1/temp01.dbf' size 10m autoextend on next 10m maxsize unlimited;
检查临时文件的状态
select name, status from v$tempfile;
脱机原来的临时文件
alter database tempfile '/u02/oradata/CDB1/pdb1/temp01.dbf' offline;
删除原来的临时文件
alter database tempfile '/u02/oradata/CDB1/pdb1/temp01.dbf' drop including datafiles;
再次检查当前的临时文件。
SELECT vc2.name "db_name",tf.file_name, tf.tablespace_name,
autoextensible, maxbytes/1024/1024 "Max_MB", SUM(tf.bytes)/1024/1024 "MB_SIZE"
FROM v$containers vc2, cdb_temp_files tf
WHERE vc2.con_id = tf.con_id
GROUP BY vc2.name,tf.file_name, tf.tablespace_name, autoextensible, maxbytes
ORDER BY 1, 2;

临时表空间文件脱机
alter database tempfile '/u02/oradata/CDB1/F87B8DE671A61501E0530B011FAC99E8/datafile/o1_mf_temp1_l34mtkng_.tmp'offline;

临时表空间文件联机
alter database tempfile
'/u02/oradata/CDB1/F87B8DE671A61501E0530B011FAC99E8/datafile/o1_mf_temp1_l34mtkng_.tmp'online;

查看临时表空间状态
select tablespace_name, status from dba_tablespaces where tablespace_name in ('TEMP1');

检查当前默认临时表空间
select property_value from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE';

设置默认临时表空间
alter database default temporary tablespace temp1;

检查用户当前的临时表空间
select temporary_tablespace from dba_users where username = 'LH_YONGHU';

设置用户默认临时表空间
alter user LH_YONGHU temporary tablespace temp1;

查看临时表空间组
select * from dba_tablespace_groups;

创建临时表空间组
create temporary tablespace temp_zu01 tempfile size 50m tablespace group tempzu;

将组设置为默认临时表空间
create temporary tablespace temp_zu01 tempfile size 50m tablespace group tempzu;

将组设置为默认临时表空间
alter database default temporary tablespace tempzu;

查找使用临时表空间的SQL
SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c
WHERE a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
ORDER BY b.tablespace, b.blocks;


显示当前的撤消模式
column property_name format a30
column property_value format a30

select property_name, property_value
from   database_properties
where  property_name = 'LOCAL_UNDO_ENABLED';

根容器查看（可以看到PDB的UNDO） 
select con_id, tablespace_name
from   cdb_tablespaces
where  contents = 'UNDO'
order by con_id;

切换到共享撤消模式
shutdown immediate;
startup upgrade;
alter database local undo off;
shutdown immediate;
startup;

切换到共享撤消模式后应该删除UPDO表空间
select con_id, tablespace_name
from   cdb_tablespaces
where  contents = 'UNDO'
order by con_id;
切换到PDB
alter session set container = pdb1;
删除
drop tablespace UNDOTBS1;

切换到本地撤消模式
shutdown immediate;
startup upgrade;
alter database local undo on;
shutdown immediate;
startup;

创建PDB，查看里面的UNDO表空间
CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb_adm IDENTIFIED BY Password1
  FILE_NAME_CONVERT=('/u02/oradata/CDB1/pdbseed/','/u02/oradata/CDB1/pdb2/');
ALTER PLUGGABLE DATABASE pdb2 OPEN;
select con_id, tablespace_name
from   cdb_tablespaces
where  contents = 'UNDO'
order by con_id;

撤消表空间使用情况
撤消表空间的总空间、可用空间和已用空间
SELECT a.tablespace_name,
SIZEMB,
USAGEMB,
(SIZEMB - USAGEMB) FREEMB
FROM ( SELECT SUM (bytes) / 1024 / 1024 SIZEMB, b.tablespace_name
FROM dba_data_files a, dba_tablespaces b
WHERE a.tablespace_name = b.tablespace_name AND b.contents like 'UNDO'
GROUP BY b.tablespace_name) a,
( SELECT c.tablespace_name, SUM (bytes) / 1024 / 1024 USAGEMB
FROM DBA_UNDO_EXTENTS c
WHERE status <> 'EXPIRED'
GROUP BY c.tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name;

检查活动、过期和未过期的事务空间使用情况
select tablespace_name tablespace, status, sum(bytes)/1024/1024 sum_in_mb, count(*) counts
from dba_undo_extents
group by tablespace_name, status order by 1,2;

显示UNDO的配置
show parameter undo