Oracle 11G如何实现表空间操作

2025-03-11 技术教程

这篇文章主要为大家展示了“Oracle 11G如何实现表空间操作”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“Oracle 11G如何实现表空间操作”这篇文章吧。

查询表空间信息

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

SELECTTABLESPACE_NAME,STATUS,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT,CONTENTSFROMDBA_TABLESPACES;

Result:

1.2：查询数据字典视图DBA_FREE_SPACE，获取数据库中各个表空间空闲的统计信息：

SELECTTABLESPACE_NAME"TABLESPACE",FILE_ID,COUNT(*)"PIECES",MAX(blocks)"MAXIMUM",MIN(blocks)"MINIMUM",AVG(blocks)"AVERAGE",SUM(blocks)"TOTAL"FROMDBA_FREE_SPACEGROUPBYTABLESPACE_NAME,FILE_ID;

1.3：查询数据字典视图DBA_TEMP_FREE_SPACE,获取临时表空间的表空间使用信息：

SELECT*FROMDBA_TEMP_FREE_SPACE

2.创建表空间

CREATE TABLESPACE语法：

CREATE [BIGFILE|SMALLFILE] [TEMPORARY|UNDO] TABLESPACE tbs_name

DATAFILE|TEMPFILE path/filename SIZE integer [K|M] [REUSE]

[AUTOEXTEND [OFF|ON] NEXT integer [K|M]

MAXSIZE [UNLIMITED|integer [K|M]]

[TABLESPACE GROUP tablespace_group_name]

[EXTENT MANAGEMENT DICTIONARY|LOCAL]

[AUTOALLOCATE|UNIFORM SIZE integer [K|M]]

2.1 创建一个本地管理的永久表空间TBS1,区、段采用自动管理：

CREATETABLESPACETBS1DATAFILE'/usr/oracle/app/product/11.2.0/dbhome_1/dbs/TBS1_1.DBF'size50M;

2.2 创建一个本地管理的永久表空间TBS2，区采用定制分配,大小为512k，段采用自动管理。

CREATETABLESPACETBS2DATAFILE'/usr/oracle/app/product/11.2.0/dbhome_1/dbs/TBS2_1.DBF'SIZE50MEXTENTMANAGEMENTLOCALUNIFORMSIZE512K;

2.3 创建一个本地管理的永久表空间TBS4，区采用定制分配方式，大小为512K,段采用手动管理：

CREATETABLESPACETBS4DATAFILE'/usr/oracle/app/product/11.2.0/dbhome_1/dbs/TBS4_1.DBF'SIZE50MEXTENTMANAGEMENTLOCALUNIFORMSIZE512KSEGMENTSPACEMANAGEMENTMANUAL;

2.4 创建一个索引表空间INDX，存储所有索引信息，表空间文件自动扩展每次5M，最大为100M：

CREATETABLESPACEINDXDATAFILE'/usr/oracle/app/product/11.2.0/dbhome_1/dbs/INDX_1.DBF'SIZE50MAUTOEXTENDONNEXT5MMAXSIZE100M;

2.5 创建一个TBS5表空间，第一个区10M,第二个区为10M，后续10%增长

CREATETABLESPACETBS5DATAFILE'/usr/oracle/app/product/11.2.0/dbhome_1/dbs/TBS5_1.DBF'SIZE50MDEFAULTSTORAGE(INITIAL10MNEXT10MPCTINCREASE10)SEGMENTSPACEMANAGEMENTMANUAL;

3. 创建大文件表空间

3.1 查询数据库默认创建大文件还是小文件的表空间：

SELECTPROPERTY_NAME,PROPERTY_VALUEFROMDATABASE_PROPERTIESWHEREPROPERTY_NAME='DEFAULT_TBS_TYPE';

3.2 创建一个大文件表空间BIGTBS1:

CREATEBIGFILETABLESPACEBIGTBS1DATAFILE'/usr/oracle/app/product/11.2.0/dbhome_1/dbs/BIGTBS1_1.DBF'SIZE50MAUTOALLOCATESEGMENTSPACEMANAGEMENTAUTO;

3.3 大文件表空间信息查询：

SELECTT.NAMETBSNAME,RFILE#,D.NAMEFNAMEFROMV$TABLESPACET,V$DATAFILEDWHERET.TS#=D.TS#ANDT.NAME='BIGTBS1'

3.4 创建非标准块表空间

ALTERSYSTEMSETDB_16K_CACHE_SIZE=16384;CREATETABLESPACETBS16DATAFILE'/usr/oracle/app/product/11.2.0/dbhome_1/dbs/TBS16_1.DBF'SIZE60MEXTENTMANAGEMENTLOCALUNIFORMSIZE128KBLOCKSIZE16K;SELECTTABLESPACE_NAME,BLOCK_SIZEFROMDBA_TABLESPACES;

4. 维护表空间

4.1 增加表空间文件

ALTERTABLESPACETBS1ADDDATAFILE'/usr/oracle/app/product/11.2.0/dbhome_1/dbs/TBS1_2.DBF'SIZE10M;

4.1.2 增加TEMP表空间文件

ALTERTABLESPACETEMPADDTEMPFILE'/usr/oracle/app/product/11.2.0/dbhome_1/dbs/TEMP02.DBF'SIZE10M;

4.2 改变数据文件大小

ALTERDATABASEDATAFILE'/usr/oracle/app/product/11.2.0/dbhome_1/dbs/TBS1_2.DBF'RESIZE20M;

4.3 更改文件的扩展方式:

ALTERDATABASEDATAFILE'/usr/oracle/app/product/11.2.0/dbhome_1/dbs/TBS2_1.DBF'AUTOEXTENDONNEXT5MMAXSIZE100M;

4.5 删除表空间文件：

ALTERTABLESPACETEMPDROPTEMPFILE'/usr/oracle/app/product/11.2.0/dbhome_1/dbs/TEMP02.DBF';

普通表空间只要将TEMPFILE换成DATAFILE即可

5. 设置默认表空间：

5.1 查看当前默认表空间：

SELECTPROPERTY_NAME,PROPERTY_VALUEFROMDATABASE_PROPERTIESWHEREPROPERTY_NAMEIN('DEFAULT_PERMANENT_TABLESPACE','DEFAULT_TEMP_TABLESPACE');

5.2 设置默认表空间：

ALTERDATABASEDEFAULTTABLESPACETBS1;

5.2.1 设置默认临时表空间：

ALTERDATABASEDEFAULTTEMPORARYTABLESPACEtablespace_name;

6. 管理临时表空间

创建临时表空间与创建普通表空间语法差不多，如创建一个TEMP2临时表空间：

CREATETEMPORARYTABLESPACETEMP2TEMPFILE'/usr/oracle/app/product/11.2.0/dbhome_1/dbs/temp2_1.DBF'SIZE20MEXTENTMANAGEMENTLOCALUNIFORMSIZE2M;

可以在create与temporary之间加入BIGFILE创建大文件临时表空间（CREATE BIGFILE TEMPORARY ...）

6.1 临时表空间组：

CREATETEMPORARYTABLESPACETEMP4TEMPFILE'/usr/oracle/app/product/11.2.0/dbhome_1/dbs/TEMP4_1.DBF'SIZE20MEXTENTMANAGEMENTLOCALUNIFORMSIZE2MTABLESPACEGROUPTEMP_GROUP1;

可以使用alter tablespace语句将临时表空加入组或者从表空间租移除

ALTERTABLESPACETEMP2TABLESPACEGROUPTEMP_GROUP1;ALTERTABLESPACETEMP4TABLESPACEGROUP'';ALTERDATABASEDEFAULTTEMPORARYTABLESPACETEMP_GROUP1;

6.2 收缩临时表空间：

ALTERTABLESPACEtempSHRINKSPACE;ALTERTABLESPACETEMP2SHRINKTEMPFILE'/usr/oracle/app/product/11.2.0/dbhome_1/dbs/temp2_1.DBF';

7 undo表空间

7.1 创建undo表空间：

CREATEUNDOTABLESPACEUNDOTBS2DATAFILE'/usr/oracle/app/product/11.2.0/dbhome_1/dbs/UNDOTBS2_1.DBF'SIZE20MAUTOEXTENDONNEXT2MMAXSIZE100M;

增加undo表空间文件与普通表空间做法一样

切换undo表空间：

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2;

7.2 UNDO信息保留时间设置：

通过初始化参数UNDO_RETENTION设置回退信息的最短保留时间，单位为妙。