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

落日映苍穹つ 2023-01-19 03:11 27阅读 0赞

一、表空间的管理方式       表空间分区(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;