Oracle表空间和数据文件的常用操作

1. 1. 表空间资料查询
      SELECT tablespace_name, block_size, extent_management, segment_space_management FROM dba_tablespaces;
      配和
      SELECT tablespace_name, initial_extent, next_extent, max_extents, pct_increase, min_extlen FROM dba_tablespaces;
      配合
      SELECT tablespace_name, status, contents FROM dba_tablespaces;
   2. 表空间对应数据文件资料查询
      SELECT file_id, file_name, tablespace_name, autoextensible, bytes FROM dba_data_files;
   3. 创建数据字典管理的表空间(只有SYSTEM表空间为数据字典管理[Dictionary]时才能创建,10g以后的SYSTEM默认都是本地管理[Local].实质上数据字典管理表空间的做法基本不可行了.而且本技术既落后也低效)
      CREATE TABLESPACE xxx DATAFILE 'c:/zzz/yyy.dbf' SIZE 50M, 'c:/mmm/nnn.dbf' SIZE 50M MINIMUM EXTENT 50K EXTENT MANAGEMENT DICTIONARY DEFAULT STORAGE (INITIAL 50K NEXT 50K MAXENTENTS 100 PCTINCREASE 0);
      
      第一个extent为50k,第二个50k.从第三个开始大小为NEXT * ((1 + PCTINCREASE/100)的n-2次方)
   4. 创建本地管理的表空间
      CREATE TABLESPACE xxx DATAFILE 'c:/zzz/yyy.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
      
      每个extent都是1兆大小
   5. 创建还原表空间 (只能使用DATAFILE和EXTENT MANAGEMENT子句)
      CREATE UNDO TABLESPACE xxx_undo DATAFILE 'c:/zzz/yyy_undo.dbf' SIZE 20M;
   6. 查询临时表空间资料
      SELECT f.file#, t.ts# "TableSpace#", f.name "File", t.name "TableSpace" FROM v$tempfile f, v$tablespace t WHERE f.ts# = t.ts#;
   7. 创建临时表空间
      CREATE TEMPORARY TABLESPACE xxx_temp TEMPFILE 'C:/YYY/ZZZ_TEMP.DBF' SIZE 10M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M;
      
      为了提高效率,UNIFORM SIZE最好是SORT_AREA_SIZE(PGA中的排序区大小)的整数倍.
   8. 默认表空间
      a.) 当数据库没有默认临时表空间时,将使用SYSTEM表空间作为排序区,从而使其碎片化.
      b.) 查询当前默认临时表空间
           SELECT * FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';
      c.) 变更默认临时表空间
           ALTER DATABASE DEFAULT TEMPORARY TABLESPACE xxx_temp;
   9. 脱机/联机表空间
      ALTER TABLESPACE xxx OFFLINE|ONLINE
      
      查询表空间在线状况请见上文.查询数据文件在线情况
      SELECT file#, name, status FROM v$datafile;
   10. 变更表空间读写状况
       ALTER TABLESPACE xxx READ WRITE|ONLY;
   11. 变更表空间存储设置
       ALTER TABLESPACE xxx MINIMUM EXTENT 100K;
       或
       ALTER TABLESPACE xxx DEFAULT STORAGE (INITIAL 100K NEXT 100K MAXEXTENTS 200);
   12. 重置表空间的大小
       a.) 自动拓展
             ALTER DATABASE DATAFILE 'c:/aaa/bbb.dbf' AUTOEXTEND ON NEXT 1M;
       b.) 手工重置. 可变大变小,如下变更文件大小到100M
             ALTER DATABASE DATAFILE 'c:/aaa/bbb.dbf' RESIZE 100M;
       c.) 给表空间添加一个数据文件
            ALTER DATABASE ADD DATAFILE 'c:/ddd/eee.dbf' SIZE 200M;
   13. 移动数据文件
       a.)脱机文件移动(Oracle逻辑上,具体移动文件请通过操作系统命令)
           ALTER TABLESPACE xxx RENAME DATAFILE 'c:/ddd/eee.dbf' TO 'c:/aaa/eee.dbf';
       b.)移动不可脱机的文件.数据库必须处于Mount状态,同样只做逻辑变动
           ALTER DATABASE RENAME FILE 'c:/ddd/eee.dbf' TO 'c:/aaa/eee.dbf';
   14. 变更表空间管理类型
       a.)数据字典管理 到 本地管理
       EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('xxx);
       b.)本地管理 到 数据字典管理(SYSTEM表空间必须是数据字典管理)
       EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL('xxx);
   15. 删除表空间
       DROP TABLESPACE xxx;
       
       可选参数[INCLUDING CONTENTS [AND DATAFILES] [CASCADE CONSTRAINTS]]
   16. 使用OMF(Oracle管理文件)管理表空间. 默认大小为100M,AUTOEXTEND为UNLIMITED
       a.) 必须设定DB_CREATE_FILE_DEST参数,才能使OMF自动管理表空间.所有新建表空间会在此目录下.
            ALTER SYSTEM SET db_create_file_dest = 'c:/zzz/hhh/';
       b.) 使用类似下列命令管理表空间
             CREATE TABLESPACE xxx; 
             ALTER TABLESPACE xxx ADD DATAFILE SIZE 50M;
             DROP TABLESPACE xxx;