oracle表空间,临时表空间管理

一、表空间的管理方式
 
    表空间分区（extent）管理方式：local（默认，bitmap）、dictionary
    表空间段（segment）管理方式：  auto（默认，bitmap）、manual（free list，该方式在并发量大的时候可能会造成free list块头的争用，pctfree和pctused参数）
 
二、将dictionary管理方式的表空间转换为local管理方式
 
    1.移动table、index
    
    alter table tablename move tablespace tablespacename;
    alter index indexname rebuild tablespace tablespacename;
 
    2.转换表空间的管理方式为local
     
    execute dbms_space_admin.tablespace_migrate_to_local('tablespacename');
 
三、表空间创建，大小更改，删除，重命名，读写
   
    显式指定参数（local，autoallocate，auto均为默认）
    create tablespace test datafile '/u01/app/oracle/oradata/oemrep/test01.dbf' size 10m
    extent management local
    autoallocate  --extent随segment的增长，从64k，1m，8m  --uniform size 2m 默认为1m
    segment space management auto;
   
    表空间增加数据文件：
    alter tablespace test add datafile '/u01/app/oracle/oradata/oemrep/test02.dbf' size 10m;
 
    Resize数据文件：
    alter database datafile '/u01/app/oracle/oradata/oemrep/test01.dbf' resize 20m；
 
    数据文件自动扩展：
    alter database datafile '/u01/app/oracle/oradata/oemrep/test01.dbf' autoextend on maxsize 40m;
   
    删除表空间
    drop tablespace tablespacename including contents and datafiles;
   
    如果其他表空间有指向要删除的表空间的完整性约束，则：
    drop tablespace tablespacename cascade constraints;
   
    表空间重命名
    alter tablespace tablespacename rename to newtablespacename;
   
    数据文件重命名
    1.offline tablespace
    2.on system，cp /u01/app/oracle/oradata/oemrep/test01.dbf /u01/app/oracle/oradata/oemrep/newtest01.dbf
    3.alter tablspace tablespacename rename datafile '' to '';
   
    只读表空间
    alter tablespace tablespacename read only;
    alter tablespace tablespacename read write;
   
四、表空间和数据文件的脱机
   
    alter tablespace tablespacename offline;
    --表空间正常脱机时会在tablespace上生成检查点（部分检查点），下次online不需要做恢复操作
    alter tablespace tablespacename offline immediate;
    --offline immediate 立刻offline 不生成检查点，下次online需要recover
    alter database datafile datafile# offline;
    --datafile offline 不生成检查点，在online需要做recover
   
    反正，把offline改为online使之在线
 
五、临时表空间   
    
        v$sort_segment:查看排序段的空间分配和解除空间分配
        v$sort_usage:查看当前谁在使用排序段
 
    1.创建临时表空间
    create temporary tablespace tablespacename tempfile '' size 1g;
   
    2.增加临时表空间数据文件
    alter tablespace tablespacename add tempfile '' size 1g;
   
    3.删除临时表空间数据文件
    alter database tempfile '' drop including datafiles;
 
    4.收缩临时表空间
    alter tablespace tablespacename shrink space;
    alter tablespace tablespacename shrink space keep 500m;
 
    5.设置默认临时表空间
    alter database default temporary tablespace tablespacename;
 
六、临时表空间组
 
    1.创建临时表空间时指定临时表空间组，该组会自动创建   
    create temporary tablespace tablespacename tempfile '' size 1g tablespace group groupname;
   
    2.创建好的临时表空间加入临时表空间组
    alter tablespace tablespacename tablespace group groupname;
 
附：监控数据库情况的常用sql
   
    1、表空间使用率
    SELECT D.TABLESPACE_NAME "表空间",
           D.STATUS "状态",
           (A.BYTES / 1024 / 1024) as "总大小",
           ((A.BYTES - DECODE(F.BYTES, NULL, 0, F.BYTES)) / 1024 / 1024) as "已使用",
           (DECODE(F.BYTES, NULL, 0, F.BYTES) / 1024 / 1024) as "剩余",
           ((A.BYTES - DECODE(F.BYTES, NULL, 0, F.BYTES)) / 1024 / 1024) /
           (A.BYTES / 1024 / 1024) as "利用率",
           DECODE(sign(((A.BYTES - DECODE(F.BYTES, NULL, 0, F.BYTES)) / 1024 / 1024) /
                       (A.BYTES / 1024 / 1024) - 0.9),
                  1,
                  '剩余不到10%，请考虑扩表空间',
                  '正常') as "温馨提示"
      FROM SYS.DBA_TABLESPACES D, SYS.SM$TS_AVAIL A, SYS.SM$TS_FREE F
     WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME
       AND F.TABLESPACE_NAME(+) = D.TABLESPACE_NAME
       ORDER BY 6 DESC;
   
    2、慎用！表空间数据文件收缩
    SELECT a.file_id,
           a.file_name file_name,
           CEIL((NVL(hwm, 1) * blksize) / 1024 / 1024) smallest,
           CEIL(blocks * blksize / 1024 / 1024) currsize,
           CEIL(blocks * blksize / 1024 / 1024) -
           CEIL((NVL(hwm, 1) * blksize) / 1024 / 1024) savings,
           'alter database datafile ''' || file_name || ''' resize ' ||
           CEIL((NVL(hwm, 1) * blksize) / 1024 / 1024) || 'm;' cmd
      FROM DBA_DATA_FILES a,
           (SELECT file_id, MAX(block_id + blocks - 1) hwm
              FROM DBA_EXTENTS
             GROUP BY file_id) b,
           (SELECT TO_NUMBER(value) blksize
              FROM V$PARAMETER
             WHERE name = 'db_block_size')
     WHERE a.file_id = b.file_id(+)
       AND CEIL(blocks * blksize / 1024 / 1024) -
           CEIL((NVL(hwm, 1) * blksize) / 1024 / 1024) > 0;