Oracle数据库之表空间(一)_常用操作

1 查看数据库中存在的表空间

select t.TABLESPACE_NAME from dba_tablespaces t;

2 创建表空间

create tablespace mmis_dbs 
    datafile 'D:\oracle\product\10.2.0\mmis_dbs.dbf' size 1024M --存储地址 初始大小1G
    autoextend on next 100M maxsize unlimited   --每次扩展100M，无限制扩展
    EXTENT MANAGEMENT local  autoallocate --本地管理方式，区分配方式为自动分配
    segment space management auto; --空间自动管理

3 查看表空间存储参数

select 
    T.TABLESPACE_NAME, --表空间名称
    T.INITIAL_EXTENT,-- 表空间初始大小
    T.NEXT_EXTENT, -- 下一分区大小
    T.MIN_EXTENTS,--分区数量
    T.MAX_EXTENTS  -- 最大分区数量
from DBA_TABLESPACES T;

4 查看表空间使用情况

select 
    T1.FILE_ID as 文件ID,
    T1.FILE_NAME as 文件名称,
    T1.TABLESPACE_NAME as 表空间名称,
    T1.BYTES/1024/1024 as 表空间总大小M,
    (T1.BYTES-SUM(NVL(T.BYTES,0)))/1024/1024 as 表空间已使用大小,
    sum(nvl(T.BYTES,0))/1024/1024 as 表空间剩余大小,
    sum(nvl(T.BYTES,0))/(T1.BYTES)*100 as 表空间剩余百分比，
    (100-(SUM(NVL(T.BYTES,0))/(T1.BYTES)*100)) as 表空间已使用百分比
from dba_free_space T,dba_data_files T1
where T.FILE_ID=T1.FILE_ID  
group by T1.TABLESPACE_NAME,T1.FILE_NAME,T1.FILE_ID,T1.BYTES
ORDER BY T1.TABLESPACE_NAME;

5 扩展表空间

• 手动增加表空间数据文件大小,ALTER DATABASE DATAFILE '数据文件路径'  RESIZE '设置大小' 

ALTER DATABASE DATAFILE 'F:\APP\ORADATA\TABLESPACE\MMIS_DBS.DBF' RESIZE '1024M';

•  将表空间数据文件设置为自动扩展    ALTER DATABASE DATAFILE   '指定数据文件路径'    AUTOEXTEND ON NEXT  '指定每次自动扩展大小'   MAXIMIZE '指定最大可扩展数量'

ALTER DATABASE DATAFILE  'F:\APP\ORADATA\TABLESPACE\MMIS_DBS.DBF' 
AUTOEXTEND ON NEXT 500M MAXIMIZE 2048M;

• 增加表空间数据文件: ALTER DATABASE 表空间名称  ‘增加的表空间文件路径 ’ SIZE '指定文件大小'

ALTER DATABASE MMIS_DBS 
      add DATAFILE 'F:\APP\ORADATA\TABLESPACE\MMIS_DBS01.DBF' SIZE '10240M';

6 收缩表空间

      ALTER DATABASE DATAFILE '表空间文件路径' resize '收缩大小M'

 第一步： :查看表空间使用情况

select 
    T1.FILE_ID as 文件ID,
    T1.FILE_NAME as 文件名称,
    T1.TABLESPACE_NAME as 表空间名称,
    T1.BYTES/1024/1024 as 表空间总大小M,
    (T1.BYTES-SUM(NVL(T.BYTES,0)))/1024/1024 as 表空间已使用大小,
    sum(nvl(T.BYTES,0))/1024/1024 as 表空间剩余大小,
    sum(nvl(T.BYTES,0))/(T1.BYTES)*100 as 表空间剩余百分比，
    (100-(SUM(NVL(T.BYTES,0))/(T1.BYTES)*100)) as 表空间已使用百分比
from dba_free_space T,dba_data_files T1
where T.FILE_ID=T1.FILE_ID  
group by T1.TABLESPACE_NAME,T1.FILE_NAME,T1.FILE_ID,T1.BYTES
ORDER BY T1.TABLESPACE_NAME;

第二步：收缩表空间：ALTER DATABASE DATAFILE '表空间文件路径' resize  重置大小M

ALTER DATABASE DATAFILE 'F:\APP\ORADATA\TABLESPACE\MMIS_DBS.DBF' RESIZE 32500M;

第三步：查看收缩后的表空间，是否收缩成功

7 删除表空间

        including contents and datafiles：表空间内容及文件
        cascade constraints ：参照完整性约束

drop tablespace MMIS_DBS
     including contents and datafiles 
     cascade constraints;

8  查看指定表空间的数据文件列表

select file_name from dba_data_files where tablespace_name='MMIS_DBS';

9 设置表空间在线

alter tablespace MMIS_DBS online;

10  设置表空间离线 

alter tablespace MMIS_DBS offline;

11  重命名表空间

alter tablespace MMIS_DBS rename to MMIS_DBS01;