Oracle永久和临时表空间管理

一、永久表空间创建以及参数说明
#创建一个名为OPS的永久表空间设置自动扩展，每次扩展增量1MB，支持自动最大扩展值为32GB，并设置自动段空间管理。
CREATE TABLESPACE OPS  
DATAFILE '/oracle/oradata/testdb/ops.dbf' 
SIZE 100MB 
AUTOEXTEND ON NEXT 1M MAXSIZE 32G   #启动表空间自动扩展，每次扩展增量1M，支持自动最大扩展值为32GB。
EXTENT MANAGEMENT LOCAL                     #本地管理表空间(现代Oracle默认方式)
SEGMENT SPACE MANAGEMENT AUTO;      #自动段空间管理(ASSM)，优于手工管理

#新建一个名为DEV的永久表空间不设置自动扩展，但设置自动段空间管理。
CREATE TABLESPACE DEV 
DATAFILE '/oracle/oradata/testdb/dev.dbf' 
SIZE 30G 
AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

#查询表空间OPS表空间的管理方式(LOCAL/DICTIONARY)、段空间管理(AUTO/MANUAL)等核心参数
SELECT 
    tablespace_name,
    block_size,
    status,
    contents,
    logging,
    extent_management,
    allocation_type,
    segment_space_management
FROM dba_tablespaces
WHERE tablespace_name = 'OPS';

#查询表空间显示物理文件的初始大小、自动扩展设置及增长参数
SELECT 
    file_name,
    bytes/1024/1024 "SIZE_MB",
    autoextensible,
    increment_by*8/1024 "NEXT_EXTEND_MB",
    maxbytes/1024/1024 "MAXSIZE_MB",
    status
FROM dba_data_files
WHERE tablespace_name = 'OPS';

二、永久表空间扩展
#查看所有永久表空间利用率
set line 200 pagesize 9999
select b.tablespace_name, round(sum(b.bytes)/1024/1024,0) sum_MB, round(sum(nvl(a.bytes,0))/1024/1024,0) free_MB, round((sum(b.bytes)-sum(nvl(a.bytes,0)))/sum(b.bytes),4)*100 use_precent
from (select tablespace_name,file_id,sum(bytes) bytes from dba_free_space group by tablespace_name,file_id ) a,
           dba_data_files b
where a.file_id(+)=b.file_id 
and a.tablespace_name(+)=b.tablespace_name
group by b.tablespace_name
order by use_precent;

#查看所有永久表空间的数据文件
set linesize 150 pagesize 999
col file_name for a60
select tablespace_name, file_name, bytes/1024/1024/1024 GB, autoextensible, increment_by from dba_data_files order by 1,2;

#查看操作系统文件系统的剩余空间
df -hT

#查看ASM磁盘组的剩余空间
set lines 200
set pages 99
select group_number,name,state,total_mb,free_mb from v$asm_diskgroup;

#存储管理方式使用操作系统文件系统,永久表空间添加数据文件扩展空间
alter tablespace DEV add datafile '/oracle/oradata/testdb/dev1.dbf' size 5G autoextend off;
 
#储管理方式使用Oracle ASM(自动存储管理),永久表空间添加数据文件扩展空间
alter tablespace DEV add datafile '+DATA/testdb/dev1.dbf' size 5G autoextend off;

三、临时表空间
#查看用户对应的TEMP表空间
col username for a15
SELECT username, temporary_tablespace FROM dba_users where account_status='OPEN';

#查看TEMP表空间的使用率
set line 200 pagesize 9999
col TABLESPACE_NAME for a15
select TABLESPACE_NAME, 
 MAX_SIZE, 
 TOTAL_BLOCKS*8192/1024/1024 "TOTAL_MB" , 
 USED_BLOCKS*8192/1024/1024 "USED_MB" , 
 FREE_BLOCKS*8192/1024/1024 "FREE_MB",
 round(USED_BLOCKS/TOTAL_BLOCKS*100,2) "USED_%"
from v$sort_segment
where TABLESPACE_NAME = 'TEMP';

#查看TEMP表空间的的数据文件
select tablespace_name,file_name,bytes/1024/1024/1024 GB,autoextensible,increment_by from dba_temp_files order by 1,2;

#查看使用TEMP表空间的用户及SQL
column username   format a10 
column user  format a10 
column tablespace format a20
select username,user,tablespace,blocks*8192/1024/1024 "Blocks USed",sql_id,session_addr,sqladdr from V$TEMPSEG_USAGE;

#查看操作系统文件系统的剩余空间
df -hT

#查看ASM磁盘组的剩余空间
set lines 200
set pages 99
select group_number,name,state,total_mb,free_mb from v$asm_diskgroup;
 
#存储管理方式使用操作系统文件系统,临时表空间添加数据文件扩展空间
alter tablespace TEMP add tempfile '/oracle/oradata/testdb/temp02.dbf' size 5G autoextend off;
 
#储管理方式使用Oracle ASM(自动存储管理),临时表空间添加数据文件扩展空间
alter tablespace TEMP add tempfile '+DATA/testdb/temp02.dbf' size 5G autoextend off;