Fundamental I 08 Maintaining Tablespaces and Data Files

TS: Tablespace, 记住表空间只是一个逻辑结构,并不是物理结构,他架构在Data files上

TTS: Temporary Tablespace

管理有2种

  Local managed ( 推荐使用, 当SYSTEM 这个TS被创建为local managed之后, 其他的TS也只能选择Local Managed )

  Dictionary managed

    因为历史原因, 有的DB采用Dictionary Managed, 需要将dictionary managed改变为local的操作:

    1. 全备份

    2. startup open read only

    3. dbms_space_admin.tablespace_migrate_to_local('SYSTEM');

TS的类型

  * Undo TS: store undo segments, 未结束的事物

  created undo tablespace temp

  * Temporary TS: 用于排序操作, 可以被多个用户共享, 不能存放permanent object, 在创建时需要指定一个database-wide default TTS

    当SYSTEM 被locally managed, 必须指定一个默认的TTS.

    当SYSTEM 被dictionary managed, SYSTEM会被指定为默认的TTS, 但是在alert.log中会有警告

  * Read-Only TS: 指定表空间为只读

    

  * 创建default TTS, 一个DB可以有多个TS,但是只能有一个active的TTS

    * Method 1 创建DB时加上

      default temporary tablespace temp

      tempfile '$ORACLE_HOME/oradata/u09/temp01.dbf' size 4g

    * Method 2 DB创建后, 再指定default TTP

      1. select * from database_properties where property_name ='DEFAULT_TEMP_TABLESPACE';    --查询TTS

      2. create temporary tablespace mytemp01 tempfile '$ORACLE_BASE/oradata/$ORACLE_SID/mytemp01.dbf' size 100m extent management local;    --创建TTS

      3. alter database default temporary tablespace mytemp01;    --改变TTS

      4. select * from database_properties where property_name ='DEFAULT_TEMP_TABLESPACE';    --Checkup.

* 修改表空间为只读

  1. CREATE READ ONLY TABLESPACE mro01 datafile '$ORACLE_BASE/oradata/$ORACLE_SID/mro01.dbf' SIZE 10m EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128k;    --创建temp表空间

  2. ALTER TABLESPACE mro01 READ ONLY;    --修改TS为只读, 此时如有transaction没有commit完成,会hang在此处

* 改变表空间状态为ONLINE/OFFLINE (SYSTEM, Default TTS, 有active segment的TS不能被deactivate,)

  ALTER TABLESPACE mro01 OFFLINE/ONLINE;

* 改变表空间的存储 ( 只针对dictionary managed, 过时了 )

* 修改TS的大小( 改变Data file 的大小) 查询TS的使用状况, 见下方command

  * 设置Data file大小为自增长,推荐使用 在创建DB和TS的默认加上 AUTOEXTEND ON NEXT 10m MAXSIZE 500m

    *. ALTER DATABASE DATAFILE '$ORACLE_BASE/oradata/$ORACLE_SID/mro01.dbf' AUTOEXTEND ON NEXT 20m MAXSIZE 100M;    --针对已存在的TS, 改变现存的Dafile 

    *, ALTER TABLESPACE mro01 ADD DATAFILE '$ORACLE_BASE/oradata/$ORACLE_SID/mro02.dbf' SIZE 20m AUTOEXTEND ON NEXT 20m MAXSIZE 100M; -- 针对已存在的TS增加datafile,并且指定为auto extend

  * 改变Data file大小

    ALTER DATABASE DATAFILE user_data RESIZE 200m;

  * 移动 TS

  Method 1: 修改TS

    1. --创建新的Data File.

    2. Offline TS

    3. ALTER TABLESPACE userdate RENAME DATAFILE 'old.dbf' TO 'new.dbf';    --将表空间的指向移动到new.dbf

  Method 2: 修改DB

    1. --创建新的Data File.

    2. startup mount;

    3. ALTER DATABASE RENAME FILE 'old.dbf' TO 'new.dbf';    --将DB的datafile指向到new.dbf

* Drop TS

  * DROP TABLESPACE userdata INCLUDING CONTENTS AND DATAFILES;    --将TS, TS的内容和Data File都删除掉

-------------------Tables & Views---------------------------------------

v$tablespace;    --表空间信息

  select * from v$tablespace;

dba_tablespaces;    --表空间信息

  select tablespace_name, contents, extent_management from dba_tablespaces where contents ='TEMPORARY';

database_properties

  select * from database_properties where property_name ='DEFAULT_TEMP_TABLESPACE';

DBA_DATA_FILES;    --数据文件信息

V$DATAFILE;    --数据文件信息

  SELECT file_name, tablespace_name, autoextensible FROM DBA_DATA_FILES;    --查询datafile大小

DBA_TEMP_FILES;    --临时文件信息

V$TEMPFILE;    --临时文件信息

---------------------------Command--------------------------------

CREATE USER readUser01 IDENTIFIED BY oracle DEFAULT TABLESPACE mro01;    --创建用户readUser01,指定默认表空间

GRANT CONNECT, RESOURCE to readUser01;     --对用户readUser01授权

查询表空间的使用情况

SELECT a.tablespace_name,total,free,
(total-free) used,
ROUND((total-free)/total,4)*100 as Rate
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
WHERE a.tablespace_name=b.tablespace_name;

Truncate和Drop的区别: Truncate可以理解为物理删除, drop指的是从该TS移除等.