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