PLSQL--表空间管理

查看表空间信息
  --查看表空间信息
  SELECT TABLESPACE_NAME,
         STATUS,
         CONTENTS,
         LOGGING
    FROM DBA_TABLESPACES;
  --查看数据文件信息
  SELECT TABLESPACE_NAME,
         'PERMANENT' TABLESPACE_KIND,
         FILE_NAME,
         STATUS,
         AUTOEXTENSIBLE,
         BYTES/1024/1024 CURR_BYTES_M,
         ONLINE_STATUS
    FROM DBA_DATA_FILES --永久表空间（含撤销表空间）
   UNION ALL
  SELECT TABLESPACE_NAME,
         'TEMPORARY' TABLESPACE_KIND,
         FILE_NAME,
         STATUS,
         AUTOEXTENSIBLE,
         BYTES/1024/1024 CURR_BYTES_M,
         'ONLINE' ONLINE_STATUS
    FROM DBA_TEMP_FILES; --临时表空间
  查看用户默认表空间信息
  SELECT USERNAME 用户名,
         DEFAULT_TABLESPACE 默认表空间,
         TEMPORARY_TABLESPACE 默认临时表空间
    FROM DBA_USERS;
  
创建表空间
CREATE TABLESPACE tbs_name --指定新表空间的名字
DATAFILE '/path/filename' SIZE num[K/M] --映射的数据文件路径及初始大小
[AUTOEXTEND [ON|OFF] NEXT num[K/M] MAXSIZE num[K/M]] --是否自动扩展及自动扩展时每次扩展的大小和最大值
[ONLINE|OFFLINE] --表空间在线或离线
[LOGGING|NOLOGGING]; --该空间下对象加载时是否记录日志


修改表空间
  增加数据文件
    --非自动扩展
  ALTER TABLESPACE tbs_name ADD DATAFILE '/path/filename' SIZE 10M AUTOEXTEND OFF ;
    --自动扩展
  ALTER TABLESPACE tbs_name ADD DATAFILE '/path/filename' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
  修改数据文件
    扩展和缩小
    ALTER DATABASE DATAFILE '/path/filename' RESIZE num[K/M];
    修改自动扩展属性
    ALTER DATABASE DATAFILE '/path/filename' AUTOEXTEND ON NEXT num[K/M] MAXSIZE UNLIMITED;
    ALTER DATABASE DATAFILE '/path/filename' AUTOEXTEND OFF;
  删除数据文件
  ALTER TABLESPACE tbs_name DROP DATAFILE '/path/filename';
  重命名表空间
  ALTER TABLESPACE tbs_name_old RENAME TO tbs_name_new;
  SYSTEM、SYSAUX表空间及处于OFFLINE状态的表空间不能重命名。
删除表空间
DROP TABLESPACE tbs_name INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

修改表空间状态
  修改为只读状态--禁止DML操作，小部分数据库对象允许删除，基本该表空间只读禁写
  ALTER TABLESPACE tbs_name READ ONLY;
  修改为读写状态（默认）--即ONLINE状态，允许正常操作
  ALTER TABLESPACE tbs_name READ WRITE;
  
创建用户、表和索引时指定表空间
  用户
  CREATE USER user_name IDENTIFIED BY pwd 
  DEFAULT TABLESPACE tbs_name TEMPORARY TABLESPACE tmp_tbs_name;
  表
  CREATE TABLE tb_name(
  col_name col_type
  ...)
  TABLESPACE tbs_name;
  ALTER TABLE tb_name MOVE TABLESPACE tbs_name;
  索引
  CREATE INDEX ind_name on tb_name(col_name) TABLESPACE tbs_name;
  ALTER INDEX ind_name REBUILD TABLESPACE tbs_name;
  --出于性能和安全考虑，表和索引建议分开放至不同的表空间内。
  
调整用户的默认表空间
  调整默认的永久表空间
  ALTER USER user_name DEFAULT TABLESPACE tbs_name;
  调整默认的临时表空间
  ALTER USER user_name TEMPORARY TABLESPACE tbs_name;
  
设置数据库的默认表空间
  设置默认永久表空间
  ALTER DATABASE DEFAULT TABLESPACE tbs_name;
  设置默认临时表空间
  ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tbs_anme;

临时表空间迁移
  1）创建新的临时表空间
  CREATE TEMPORARY TABLESPACE tmp_tbs_name_new
  TEMPFILE '/path/filename' SIZE num[K/M]
  [AUTOEXTEND ON|OFF NEXT num[K/M] MAXSIZE UNLIMITED];
  2）修改数据库的临时表空间为新的临时表空间
  ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tmp_tbs_name_new;
  3）删掉旧的临时表空间
  DROP TABLESPACE tmp_tbs_name_old INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

表空间预警方案
  eg：表空间使用率超过95%或剩余表空间少于30M时，存储过程不能开始进行。
SELECT A.TABLESPACE_NAME 表空间名称,
       ROUND(TOTAL/1024/1024,4) 表空间大小M,
       ROUND(FREE/1024/1024,4) 表空间剩余大小M,
       ROUND((TOTAL-FREE)/1024/1024,4) 表空间使用大小M,
       ROUND((TOTAL-FREE)/TOTAL,4)*100 使用率
  FROM (SELECT TABLESPACE_NAME,SUM(BYTES) FREE
          FROM DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) A,
       (SELECT TABLESPACE_NAME,SUM(BYTES) TOTAL
          FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME) B
    ON A.TABLESPACE_NAME=B.TABLESPACE_NAME
 WHERE /*A.TABLESPACE_NAME=UPPER('tbs_name')*/
 ORDER BY ROUND((TOTAL-FREE)/TOTAL,4)*100 DESC;