Oracle 表空间和数据文件的管理

Oracle 表空间和数据文件的管理

一些常用SQL语句汇总

创建表空间

-- 创建表空间
create tablespace ts_name
  datafile '/opt/oracle/oradata/orcl/ts_name01.dbf'
  size 100M
  autoextend on maxsize 1000M
  extent management local
  uniform size 128K
  segment space management auto;

-- 创建临时表空间
create temporary tablespace ts_name
  tempfile '/opt/oracle/oradata/orcl/ts_name01.dbf'
  size 100M
  autoextend on next 5M maxsize unlimited
  extent management local
  uniform size 128K;
  
-- 使用变量
-- 替换值为&1可以外部传参数
define tbsp_large=5G;
define tbsp_med=500M;
-- 
create tablespace school_data
  datafile '/opt/oracle/oradata/orcl/school_data01.dbf'
  size &&tbsp_large
  extent management local
  uniform size 128K
  segment space management auto;
-- 
create tablespace school_index
  datafile '/opt/oracle/oradata/orcl/school_index01.dbf'
  size &&tbsp_med
  extent management local
  uniform size 128K
  segment space management auto;

-- 查看表空间创建语句
set long 1000000;
set pagesize 500;
select dbms_metadata.get_ddl('TABLESPACE', tablespace_name)
from dba_tablespaces;

重命名表空间

-- 重命名表空间
-- Oracle 会在数据字典、控制文件和数据文件头部更新表空间的名称
alter tablespace tools rename to tools_dev;

表空间控制重做日志

-- 表空间控制重做日志
-- 只需要加上nologging子句
create tablespace school_data
  datafile '/opt/oracle/oradata/orcl/school_data01.dbf'
  size 100M
  extent management local
  uniform size 128K
  segment space management auto
  nologging;

-- 修改
alter tablespace school_data nologging;
-- 查看
select tablespace_name, logging from dba_tablespaces;

更改表空间写入模式

-- 更改表空间写入模式
alter tablespace school_data read only;
alter tablespace school_data read write;

-- Oracle 11g及以上， 可对单个表修改
alter table my_tab read only;
alter table my_tab read write;

删除表空间

-- 删除表空间
-- 删除表空间前， 应先使其脱机（这是一个好习惯）
alter tablespace tbsp_name offline;
-- 等待一段时间，看是否有用户报错； 确定了不需要表空间后，再删除
drop tablespace tbsp_name including contents and datafiles;

-- 查询外键约束
column owner format a20;
column table_name format a20;
column constraint_name format a20;
column referencing_table format a20;
column foreign_key_name format a30;
column fk_status format a20;
set linesize 150;
select p.owner, p.table_name, p.constraint_name, 
       f.table_name referencing_table, f.constraint_name foreign_key_name, f.status fk_status
from dba_constraints p, dba_constraints f, dba_tables t
where p.constraint_name = f.r_constraint_name
  and f.constraint_type = 'R'
  and p.table_name = t.table_name
  and t.tablespace_name = UPPER('&tablespace_name')
order by 1, 2, 3, 4, 5;
-- 删除表空间带外键约束
drop tablespace tbsp_name including contents and datafiles cascade constraints;

-- 查询表空间中是否有表和索引
select owner, segment_name, segment_type
from dba_segments
where tablespace_name = upper('&tablespace_name');

查看表空间的尺寸

-- 查看表空间的尺寸
set pagesize 100 lines 132 echo off verify off feedback off space 1 trimsp on
compute sum of a_byt t_byt f_byt on report
break on report on tablespace_name on pf
column tablespace_name format a17 tru head 'Tablespace|Name'
column file_name       format a45 tru head 'Filename'
column a_byt           format 9,990.999 head 'Allocated|GB'
column t_byt           format 9,990.999 head 'Current|Used GB'
column f_byt           format 9,990.999 head 'Current|Free GB'
column pct_free        format 990.0     head 'File %|Free'
column pf              format 990.0     head 'Tbsp %|Free'
column seq noprint
define b_div=1073741824
-- 
SELECT 1 seq, b.tablespace_name, nvl(x.fs, 0)/y.ap*100 pf, b.file_name file_name, b.bytes/&&b_div a_byt, nvl((b.bytes-sum(f.bytes))/&&b_div, b.bytes/&&b_div) t_byt, nvl(sum(f.bytes)/&&b_div, 0) f_byt, nvl(sum(f.bytes)/b.bytes*100, 0) pct_free
FROM dba_free_space f, dba_data_files b, (SELECT y.tablespace_name, sum(y.bytes) fs FROM dba_free_space y GROUP BY y.tablespace_name) x, (SELECT x.tablespace_name, sum(x.bytes) ap FROM dba_data_files x GROUP BY x.tablespace_name) y
WHERE f.file_id(+) = b.file_id
  AND x.tablespace_name(+) = y.tablespace_name
  AND y.tablespace_name = b.tablespace_name
  AND f.tablespace_name(+) = b.tablespace_name
GROUP BY b.tablespace_name, nvl(x.fs, 0)/y.ap*100, b.file_name, b.bytes
UNION
SELECT 2 seq, tablespace_name, j.bf/k.bb*100 pf, b.name file_name, b.bytes/&&b_div a_byt, a.bytes_used/&&b_div t_byt, a.bytes_free/&&b_div f_byt, a.bytes_free/b.bytes*100 pct_free
FROM v$temp_space_header a, v$tempfile b, (SELECT sum(bytes_free) bf FROM v$temp_space_header) j, (SELECT sum(bytes) bb FROM v$tempfile) k
WHERE a.file_id = b.file#
ORDER BY 1, 2, 4, 3;

-- 查询表空间使用情况
SELECT UPPER(F.TABLESPACE_NAME) "Tablespace Name",     
D.TOT_GROOTTE_MB "Size(M)",     
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "Used(M)",     
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') "Used %",     
F.TOTAL_BYTES "Free(M)",     
F.MAX_BYTES "Max Block(M)"    
FROM (SELECT TABLESPACE_NAME,     
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,     
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES     
FROM SYS.DBA_FREE_SPACE     
GROUP BY TABLESPACE_NAME) F,     
(SELECT DD.TABLESPACE_NAME,     
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB     
FROM SYS.DBA_DATA_FILES DD     
GROUP BY DD.TABLESPACE_NAME) D     
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME     
ORDER BY 4 DESC;

-- 查看表空间是否具有自动扩展的能力     
SELECT T.TABLESPACE_NAME,D.FILE_NAME, D.AUTOEXTENSIBLE, D.BYTES, D.MAXBYTES, D.STATUS
FROM DBA_TABLESPACES T, DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME = D.TABLESPACE_NAME
ORDER BY TABLESPACE_NAME, FILE_NAME;

更改表空间的尺寸

-- 更改表空间的尺寸
-- resize数据文件大小
ALTER DATABASE datafile '/opt/oracle/oradata/orcl/school_data01.dbf' RESIZE 100M;
-- resize临时文件
ALTER DATABASE tempfile '/opt/oracle/oradata/orcl/school_temp01.dbf' RESIZE 100M;

-- 给表空间增加数据文件
ALTER TABLESPACE school_data ADD datafile '/opt/oracle/oradata/orcl/school_data01.dbf' SIZE 50M;
-- 给表空间增加临时文件
ALTER TABLESPACE school_temp ADD tempfile '/opt/oracle/oradata/orcl/school_temp01.dbf' SIZE 50M;

-- 给表空间增加数据文件, 并且允许数据文件自动增长
ALTER TABLESPACE school_data ADD datafile '/opt/oracle/oradata/orcl/school_data01.dbf' SIZE 50M 
AUTOEXTEND ON NEXT 5M MAXSIZE 100M;

-- 允许已存在的数据文件自动增长
ALTER DATABASE datafile '/opt/oracle/oradata/orcl/school_data01.dbf' 
AUTOEXTEND ON NEXT 5M MAXSIZE 100M;

切换数据文件的联机状态

-- 切换数据文件的联机状态
alter tablespace users offline normal;
alter tablespace users offline temporary;
alter tablespace users offline immediate;

alter database datafile 4 offline for drop;
recover datafile 4;
alter database datafile 4 online;

命令	是否要求数据库处于 归档日志模式	在切换联机状态时是否 需要执行介质回复	是否在mount模式 中有效
alter tablespace … offline normal	否	否	否
alter tablespace … offline temporary	否	在某些条件下，取决于数据文件 是否已经处于联机状态	否
alter tablespace … offline immediate	否	是	否
alter database datafile … offline	是	是	是
alter database datafile … offline for drop	否	是	是

重命名和移动数据文件

-- 重命名和移动数据文件
-- Oracle 12c 新增命令， 需要数据文件联机状态才可操作生效
alter database move datafile '/opt/oracle/oradata/orcl/user01.dbf' to '/opt/oracle/oradata/orcl/user01_dev.dbf';
-- 保留源文件
alter database move datafile '/opt/oracle/oradata/orcl/user01.dbf' to '/opt/oracle/oradata/orcl/user01_dev.dbf' keep;
-- 覆盖现存文件
alter database move datafile '/opt/oracle/oradata/orcl/user01.dbf' to '/opt/oracle/oradata/orcl/user01_dev.dbf' reuse;

-- Oracle 11g及以下，需要先使数据文件脱机
-- 方法1： 组合使用SQL命令和OS命令
(1)select name from v$datafile;
(2)alter tablespace ... offline 或 alter database datafile ... offline; 可以先关闭数据库， 重启进入mount模式
(3)使用OS命令（如mv或cp），移动数据文件到新位置
(4)使用alter tablespace ... rename datafile ... to ...语句或alter database rename file ... to ...语句， 通过新的数据文件名称更新控制文件
(5)使数据文件联机
-- 方法2： 组合使用重新创建控制文件和OS命令
(1)创建含有CREATE CONTROLFILE语句的跟踪文件
(2)修改这个跟踪文件，显示数据文件的新位置
(3)关闭数据库
(4)使用OS命令移动数据文件
(5)将数据库启动到nomount模式
(6)运行CREATE CONTROLFILE命令
-- 相关语句
alter database backup controlfile to trace as '/tmp/cf.sql' noresetlogs;