Oracle 11G 表空间

yanqiufang

• 　　查询表空间信息
  　　1.1： 查询数据字典视图DBA_TABLESPACES获取数据库中个表空间的名称，状态，管理方式，区的分配方式，段的管理方式，表空间类型等基本信息。
  

SELECT TABLESPACE_NAME,STATUS,EXTENT_MANAGEMENT,　　
ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT,CONTENTS
　　
FROM DBA_TABLESPACES;
　　Result:
　　1.2：查询数据字典视图DBA_FREE_SPACE，获取数据库中各个表空间空闲的统计信息：
SELECT TABLESPACE_NAME"TABLESPACE",FILE_ID,　　
COUNT(*)"PIECES",MAX(blocks)"MAXIMUM",MIN(blocks)"MINIMUM",
　　
AVG(blocks)"AVERAGE", SUM(blocks)"TOTAL"
　　
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME,FILE_ID;
　　1.3：查询数据字典视图DBA_TEMP_FREE_SPACE,获取临时表空间的表空间使用信息：
SELECT * FROM DBA_TEMP_FREE_SPACE　　2.创建表空间
　　CREATE TABLESPACE语法：
　　CREATE [BIGFILE|SMALLFILE] [TEMPORARY|UNDO] TABLESPACE tbs_name

　　DATAFILE|TEMPFILE path/filename>　　[AUTOEXTEND [OFF|ON] NEXT integer [K|M]
　　MAXSIZE [UNLIMITED|integer [K|M]]
　　[TABLESPACE GROUP tablespace_group_name]
　　[EXTENT MANAGEMENT DICTIONARY|LOCAL]

　　[AUTOALLOCATE|UNIFORM>　　2.1 创建一个本地管理的永久表空间TBS1,区、段采用自动管理：
CREATE TABLESPACE TBS1 DATAFILE　　
'/usr/oracle/app/product/11.2.0/dbhome_1/dbs/TBS1_1.DBF' size 50M;
　　2.2 创建一个本地管理的永久表空间TBS2，区采用定制分配,大小为512k，段采用自动管理。
CREATE TABLESPACE TBS2 DATAFILE　　
'/usr/oracle/app/product/11.2.0/dbhome_1/dbs/TBS2_1.DBF' SIZE 50M
　　
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K;
　　2.3 创建一个本地管理的永久表空间TBS4，区采用定制分配方式，大小为512K,段采用手动管理：
CREATE TABLESPACE TBS4 DATAFILE　　
'/usr/oracle/app/product/11.2.0/dbhome_1/dbs/TBS4_1.DBF' SIZE 50M
　　
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K
　　
SEGMENT SPACE MANAGEMENT MANUAL;
　　2.4 创建一个索引表空间INDX，存储所有索引信息，表空间文件自动扩展每次5M，最大为100M：
CREATE TABLESPACE INDX DATAFILE　　
'/usr/oracle/app/product/11.2.0/dbhome_1/dbs/INDX_1.DBF' SIZE 50M
　　
AUTOEXTEND ON NEXT 5M MAXSIZE 100M;
　　2.5 创建一个TBS5表空间，第一个区10M,第二个区为10M，后续10%增长
CREATE TABLESPACE TBS5 DATAFILE　　
'/usr/oracle/app/product/11.2.0/dbhome_1/dbs/TBS5_1.DBF' SIZE 50M
　　
DEFAULT STORAGE(INITIAL 10M NEXT 10M PCTINCREASE 10)
　　
SEGMENT SPACE MANAGEMENT MANUAL;
　　3. 创建大文件表空间
　　3.1 查询数据库默认创建大文件还是小文件的表空间：
SELECT PROPERTY_NAME,PROPERTY_VALUE　　
FROM DATABASE_PROPERTIES
　　
WHERE PROPERTY_NAME='DEFAULT_TBS_TYPE';
　　3.2 创建一个大文件表空间BIGTBS1:
CREATE BIGFILE TABLESPACE BIGTBS1 DATAFILE　　
'/usr/oracle/app/product/11.2.0/dbhome_1/dbs/BIGTBS1_1.DBF' SIZE 50M
　　
AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
　　3.3 大文件表空间信息查询：
SELECT T.NAME TBSNAME,RFILE#,D.NAME FNAME　　
FROM V$TABLESPACE T,V$DATAFILE D
　　
WHERE T.TS#=D.TS# AND T.NAME='BIGTBS1'
　　3.4 创建非标准块表空间
ALTER SYSTEM SET DB_16K_CACHE_SIZE=16384;　　
CREATE TABLESPACE TBS16
　　
DATAFILE '/usr/oracle/app/product/11.2.0/dbhome_1/dbs/TBS16_1.DBF' SIZE 60M
　　
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K BLOCKSIZE 16K;
　　
SELECT TABLESPACE_NAME,BLOCK_SIZE FROM DBA_TABLESPACES;
　　4. 维护表空间
　　4.1 增加表空间文件
ALTER TABLESPACE TBS1　　
ADD DATAFILE '/usr/oracle/app/product/11.2.0/dbhome_1/dbs/TBS1_2.DBF' SIZE 10M;
　　4.1.2 增加TEMP表空间文件
    ALTER TABLESPACE TEMP ADD TEMPFILE　　
'/usr/oracle/app/product/11.2.0/dbhome_1/dbs/TEMP02.DBF' SIZE 10M;
　　4.2 改变数据文件大小
ALTER DATABASE DATAFILE '/usr/oracle/app/product/11.2.0/dbhome_1/dbs/TBS1_2.DBF' RESIZE 20M;　　4.3 更改文件的扩展方式:
ALTER DATABASE DATAFILE　　
'/usr/oracle/app/product/11.2.0/dbhome_1/dbs/TBS2_1.DBF'
　　
AUTOEXTEND ON NEXT 5M MAXSIZE 100M;
　　4.5 删除表空间文件：
ALTER TABLESPACE TEMP　　
DROP TEMPFILE '/usr/oracle/app/product/11.2.0/dbhome_1/dbs/TEMP02.DBF';
　　普通表空间只要将TEMPFILE换成DATAFILE即可
　　5. 设置默认表空间：
　　5.1 查看当前默认表空间：
  SELECT PROPERTY_NAME,PROPERTY_VALUE FROM DATABASE_PROPERTIES　　
WHERE PROPERTY_NAME IN ('DEFAULT_PERMANENT_TABLESPACE','DEFAULT_TEMP_TABLESPACE');
　　5.2 设置默认表空间：
    ALTER DATABASE DEFAULT TABLESPACE TBS1;　　5.2.1 设置默认临时表空间：
    ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tablespace_name;　　6. 管理临时表空间
　　创建临时表空间与创建普通表空间语法差不多，如创建一个    TEMP2临时表空间：
CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE　　
'/usr/oracle/app/product/11.2.0/dbhome_1/dbs/temp2_1.DBF' SIZE 20M
　　
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M;
　　可以在create与temporary之间加入BIGFILE创建大文件临时表空间（CREATE BIGFILE TEMPORARY ...）
　　6.1 临时表空间组：
CREATE TEMPORARY TABLESPACE TEMP4 TEMPFILE　　
'/usr/oracle/app/product/11.2.0/dbhome_1/dbs/TEMP4_1.DBF' SIZE 20M
　　
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M
　　
TABLESPACE GROUP TEMP_GROUP1;
　　可以使用alter tablespace语句将临时表空加入组或者从表空间租移除
ALTER TABLESPACE TEMP2 TABLESPACE GROUP TEMP_GROUP1;　　
ALTER TABLESPACE TEMP4 TABLESPACE GROUP '';
　　
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_GROUP1;
　　6.2 收缩临时表空间：
ALTER TABLESPACE temp SHRINK SPACE;　　
ALTER TABLESPACE TEMP2 SHRINK TEMPFILE '/usr/oracle/app/product/11.2.0/dbhome_1/dbs/temp2_1.DBF';
　　7 undo表空间
　　7.1 创建undo表空间：
    CREATE UNDO TABLESPACE UNDOTBS2　　
DATAFILE '/usr/oracle/app/product/11.2.0/dbhome_1/dbs/UNDOTBS2_1.DBF' SIZE 20M
　　
AUTOEXTEND ON NEXT 2M MAXSIZE 100M;
　　增加undo表空间文件与普通表空间做法一样
　　切换undo表空间：
　　ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2;
　　7.2 UNDO信息保留时间设置：
　　通过初始化参数UNDO_RETENTION设置回退信息的最短保留时间，单位为妙。
　　show parameter UNDO
　　可以查出当前UNDO_RETENTION的设置，修改默认值：
　　ALTER SYSTEM SET UNDO_RETENTION=1800;
　　再启用undo表空间的RETENTION GUARANTEE特性确保UNDO信息至少保留UNDO_RETENTION指定的时间。
　　ALTER TABLESPACE UNDOTBS2 RETENTION GUARANTEE;