Oracle表空间操作详解

1、Oracle表空间操作详解1. 创建表空间：createtablespacedatafile size autoexte nd onn ext maxsizemaxsize un limitedeg:createtablespace sales datafile c:1.txt size 10m autoexte nd on next 1m maxsize 100m2. 为表空间增加数据文件：altertablespacetablespace_ nameadd datafile filepath size autoexte nd onn ext maxsizefilemaxsize un li

2、mited;eg:altertablespace sales datafile c:2.txt size 10m autoextend on next 1m maxsize unlimited3调整表空间：alter database datafile filepath resize ;-重置表空间的大小eg:alter database datafile c:2.txt resize 10m在实际使用中我们经常会遇到oracle某个表空间占用了大量的空间而其中的数据量却只占用了少量空间， 此时我们可以用 ALTER DATABASE DATAFILE *.dbf RESIZE nnM 的命

3、令来收缩表空间，但在收缩的过程中会遇到ora-03297错误，表示在所定义的空间之后有数据存在，不能收缩，此时的解决办法有：(1).先估算该表空间内各个数据文件的空间使用情况：SQLselect file# ,n ame from v$datafile;SQLselect max(block_id) from dba_exte nts where file_id=11;MAX(BLOCK_ID)13657SQLshow parameter db_block_sizeNAME TYPE VALUEdb_block_size in teger 8192SQLselect 13657*8/1024

4、from dual;13657*8/1024106.695313这说明该文件中最大使用块位于106M与107M之间，SQL alter database datafile /ora_data/cni nsite/i nsitedev02.dbf resize 100M;alter database datafile /ora_data/cnin site/i nsitedev02.dbf resize 100M*ERROR at line 1:ORA-03297: file contains used data beyond requested RESIZE valueSQL alter da

5、tabase datafile /ora_data/cni nsite/i nsitedev02.dbf resize 107M;Database altered.(2).如果某些表占用了数据文件的最后一些块，则需要先将该表导出或移动到其他的表空间中，然后删除表，再进行收缩。不过如果是移动到其他的表空间，需要重建其索引。SQL alter table t_obj move tablespace t_tbs1;(2)删除某个表空间中的某个数据文件export 该表空间的内容：expdp 用户名 /密码 DUMPFILE=XXX.dmp tablespaces=xxx;1) shutdow n(a

6、bort)2) startup mount3) alter databasedatafile fullpath_of_removed_DF offline drop在非归档模式下，可以使用offline drop，在归档模式下，使用 offline这个命令的意思并不是从表空间将这个数据文件彻底删除，而是说将这个数据文件冻结”住:它的sen不在变化，不会再向它写入任何数据；但是原有存在这个文件中对象和数据还可 以继续使用。因此在使用这个命令后，通过修改控制文件将它删除，数据库在启动时就会报文件丢失的错误，并且会在数据字典中产生一个MISGXXXXX的纪录。因此，以一个数据文件是无法被直接从数据库

7、中删除掉的。刪除表格空間SQL DROP TABLESPACE users INCLUDING CONTENTS and datafiles;在drop users表空間時遇到如下錯誤：SQL DROP TABLESPACE users INCLUDING CONTENTS and datafiles;DROP TABLESPACE users INCLUDING CONTENTS and datafiles*ERROR 在行 1:ORA-12919:無法刪除預設永久表格空間原來在oraclelOg中，必須爲數據庫指定一個默認的永久表空間。而默認是users表空間，所以會出現上面的錯誤信息。把

8、默認的永久表空間改爲system表空間就可以了：SQL alter database default tablespace system;4) SQL alter databaseope n5) 重建被drop掉的表空间，再import前面备份的表空间impdp 用户名 /密码 DUMPFILE=XXX.dmp tablespaces=xxx;4.关闭表空间的自动扩展属性：alter database datafile filepath autoextend offeg:alter database datafile c:2.txt autoexte nd off5打开表空间的自动扩展属性:a

9、lter database datafile filepath autoextend on eg:alter database datafile c:2.txt autoexte nd on6. 使表空间脱机：altertablespacetablespace_ name offline7. 使表空间联机：altertablespacetablespace_ name on li ne8. 设置表空间为只读：altertablespacetablespace_ name read only9. 设置表空间为读写：altertablespacetablespace_ name read writ

10、e11. 删除表空间：droptablespacetablespace_ name12. 删除表空间的同时，删除数据文件droptablespacetablespace_ name in cludi ng contents and datefiles13. 查看每个表空间占用空间的大小：selecttablespace_ name,sum(bytes)/1024/1024 from dba_segme nts group bytablespace_ name10.oracle中如何移动控制文件，数据文件，日志文件oracle9i/10G-xG 中移动控制文件，数据文件，日志文件ORACLE数据

11、库由数据文件，控制文件和联机日志文件三种文件组成。windows 环境中用： host copy 或 host move 其它均相同。以下是unix或linux环境中，一. 移动数据文件：ALTER TABLESPACE 方法（联机状态）：用此方法，要求此数据文件既不属于SYSTEM表空间，也不属于含有ACTIVE回滚段或临时段的表空间。$ sqlplus /as sysdba#把需要移动的数据文件对应的表空间offli neSQL alter tablespaceipas_acct_data offline#把数据文件cp到想要放的目录下。SQL !mv /opt/oracle/wacosd

12、ata/ipas_acct_data001.dbf/opt/oracle/nm s/oradata/ipas_acct_data001.dbf#修改表空间中数据库文件的位置。SQL alter tablespaceipas_acct_data ren ame datafile7opt/oracle/wacosdata/ipas_acct_data001.dbf to/opt/oracle/nm s/oradata/ipas_acct_data001.dbf#把表空间online。SQL alter tablespaceipas_acct_data on li ne#查看修改结果。SQL sel

13、ect file_ name from dba_data_files where tablespace_ name =IPAS_ACCT_DATA：ALTER DATABASE 方法（脱机状态）：用此方法，可以移动任何表空间的数据文件。$ sqlplus /as sysdbaSQL shutdow n immediateSQL !mv /opt/oracle/oradata/wacos002.dbf /ora10g/oradata/wacos002.dbfSQL startup mountSQL alter database ren ame file 7opt/oracle/oradata/w

14、acos002.dbf to 7ora10g/oradata/wacos002.dbf;SQL alter database ope n;SQL set head offSQL SELECT file_ name from dba_data_files where tablespace_ name = WACOS：二. 移动控制文件：$ sqlplus /as sysdba#我是用的spfile启动的，spfile文件不能修改，所以要做这一步。SQL create pfile from spfile#关闭数据库。SQL shutdow n immediate#cp控制文件到目标位置。SQLcp

15、 /opt/oracle/oradata/c on trol* /opt/oracle/oratest/修改$ORACLE_HOME/dbs/init$ORACLE_SID.ora文件中的控制文件的位置。#启动数据库指定参数文件。SQL startup pfile=/opt/oracle/product/9.2.0.4/dbs/i nit$ORACLE_SID.ora#下次启动数据库是直接就可以用 startup启动了。SQL create spfile from pfile;三. 移动重做日志文件：$ sqlplus /as sysdba#关闭数据库。SQL shutdow n immedi

16、ate#cp日志文件到目标位置。SQL !cp /opt/oracle/oradata/redo* /opt/oracle/oratest/#让数据库以mount模式启动。SQLstartup mount;#修改数据库中日志文件的位置。SQL alter database ren ame file 7opt/oracle/oradata/redo01 .lo g to/opt/oracle/oratest/redo01 .logSQL alter database ren ame file 7opt/oracle/oradata/redo02 .lo g to/opt/oracle/orate

17、st/redo02 .logSQL alter database ren ame file /opt/oracle/oradata/redo03 .lo g to/opt/oracle/oratest/redo03 .log#修改数据库的状态。SQL alter database ope n;#查看修改结果。SQL select * from v$logfile;增加重做日志文件alter database add logfile D:oracleoradatage01redo04.log size 100M;管理临时表空间注意：TEMP数据文件不能移动，只能通过重建临时表空间来重建，方法如下

18、：尤其值得注意：重建临时表空间的大小一定要足够大，一定要大于或等于现有临时表空间的大小，否则会出现：提示：无法通过128 （在表空间temp中）扩展temp段。Oracle9i为我们提供了一个全局的临时表空间。有的时候我们会发现在做了大量的排序操作后，temp表空间并没有去释放，而且在更大的程度上面占用了我们的磁盘空间，其实我们 可以通过重建或者切换的手段来使我们的temp表空间得到重生。首先我们可以先看一下，当前用户所属的临时表空间有那些SQL select user name ,temporary_tablespace from dba_users;USERNAMETEMPORARY_TA

19、BLESPACESYSTEMPSYSTEMTEMPDBSNMPTEMPHUJINPEITEMPALAN1TEMPPERFUSERTEMPALAN2TEMPMYUSERTEMPOUTLNTEMPWMSYSTEMP已选择10行。查看当前有那些临时文件SQL select n ame from v$tempfile;NAMED:ORACLEORADATAICMNLSDBTEMP_01.DBFD:ORACLEORADATAICMNLSDBTEMP_02.DBF为了切换这个临时文件，我们可以重新建立一个临时表空间SQL create temporary tablespace temp2 tempfile

20、D:ORACLEORADATAICMNLSDBTEMP01.DBF size 10M ;表空间已创建。SQL alter tablespace temp2 add tempfileD:ORACLEORADATAICMNLSDBTEMP02.DBF size 10M;表空间已更改。这个时候我们就可以将刚刚建好的TEMP表空间设置为我们数据库默认的临时表空间:SQL alter database default temporary tablespace temp2;数据库已更改。SQL select user name,temporary_tablespace from dba_usersUSER

21、NAMETEMPORARY_TABLESPACESYSTEMP2SYSTEMTEMP2DBSNMPTEMP2HUJINPEITEMP2ALAN1TEMP2PERFUSERTEMP2ALAN2TEMP2MYUSERTEMP2OUTLNTEMP2WMSYSTEMP2已选择10行。当然我们还可以选择 DROP掉以前旧的TEMP的表空间SQL drop tablespace temp in cludi ng contents ;表空间已丢弃。临时表空间组Oracle 10g引进了临时表空间组(temporary tablespace group )的概念，它允许用户在不 同的会话中同时利用多个临时表空

22、间。1临时表空间组的主要特征? 一个临时表空间组必须由至少一个临时表空间组成，并且无明确的最大数量限制。?如果删除了一个临时表空间组的所有成员，该组也自动被删除。?临时表空间的名字不能与临时表空间组的名字相同。?在给用户分配一个临时表空间时，可以使用临时表空间组的名字代替实际的临时表空间名；在给数据库分配默认临时表空间时也可以使用临时表空间组的名字。2临时表空间组的有点使用临时表空间组而非普通的临时表空间，有如下好处：?由于SQL查询可以并发使用几个临时表空间进行排序操作，因此SQL查询很少会出现排序空间超出，避免当临时表空间不足时所引起的磁盘排序问题。?可以在数据库级指定多个默认临时表空间。

23、? 一个并行操作的并行服务器将有效地利用多个临时表空间。? 一个用户在不同会话中可以同时使用多个临时表空间。注意：从Oracle10gR2开始，使用RMAN恢复数据库之后，Oracle会自动重新创建临时文 档，由于临时文档是 Sparse File，所以创建会很迅速，通常您不会感觉得到。3. 管理临时表空间组临时表空间组是在创建临时表空间时通过指定GROUP字句创建的。可以将一个表空间从一个组移动另一个组，或是从一个组中删除临时表空间，或是往组里添加新的表空间。1. 创建临时表空间组创建临时表空间时指定 GROUP :CREATE SMALLFILE TEMPORARY TABLESPACE

24、TEMP01TEMPFILE 7opt/oracle/oradata/ge01/temp01.dbf SIZE 50M REUSEAUTOEXTEND ON NEXT 5M MAXSIZE 500M EXTENT MANAGEMENT LOCALUNIFORM. SIZE 1M TABLESPACE GROUP TMP_GROUP_1;CREATE SMALLFILE TEMPORARY TABLESPACE TEMP02TEMPFILE 7opt/oracle/oradata/ge01/temp02.dbf SIZE 50M REUSEAUTOEXTEND ON NEXT 5M MAXSIZ

25、E 500M EXTENT MANAGEMENT LOCALUNIFORM. SIZE 1M TABLESPACE GROUP TMP_GROUP_1;2. 查询临时表空间组SELECT * FROM DBA_TABLESPACE_GROUPSGROUP_NAMETABLESPACE_NAMETBS_GROUP_1TEMP01TBS_GROUP_1TEMP02TBS_GROUP_2TEMP03TBS GROUP 2TEMP043. 临时表空间的移动可以将 未分组”或 已分组”的临时表空间移动到指定的临时表空间中，或者是移出：?将未分组的临时表空间 TEMP_01加入分组TBS_GROUP_1A

26、LTER TABLESPACE TEMP TABLESPACE GROUP TMP_GROUP_1;4. 把临时表空间组指定给用户?查看用户的默认表空间SELECT USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE FROMDBA_USERS;?创建用户时指定CREATE USER zha ngzj IDENTITY BY zha ngzjDEFAULT TABLESPACE usersTEMPORARY TABLESPACE tbs_group_1?修改已有用户ALTER USER gedb TEMPORARY TABLESPACE TMP

27、_GROUP_1;5. 设置数据库默认临时表空间组ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TMP_GROUP_1;6.同一个用户的不同session使用不同的临时表空间首先用sys用户查询：SELECT USERNAME, SESSION_NUM, TABLESPACE FROM V$SORT_USAGE测试过程：新开session1执行SQL conn zha ngzj/zha ngzj已连接。SELECT A.TABLE_NAME, B.TABLE_NAME FROM DICT A, DICT B ORDER BYA.TABLE NAME新开

28、session2执行SQL conn zha ngzj/zha ngzj已连接。SELECT A.TABLE_NAME, B.TABLE_NAME FROM DICT A, DICT B sys查询:SQL SELECT USERNAME, SESSION_NUM, TABLESPACE FROM V$SORT_USAGE;USERNAMESESSION NUMTABLESPACEZHANGZJ35743TEMP01ZHANGZJ25683TEMP02可以发现，同一个用户ZHANGZJ的两个session分别使用了两个不同的临时表空间。6. 删除临时表空间组即：删除组成临时表空间组的所有临时表

29、空间先查看要删除的临时表空间组包含哪些临时表空间SELECT * FROM DBA_TABLESPACE_GROUPS然后删除这些临时表空间，即可DROP TABLESPACE TEMP01 INCLUDING CONTENTS AND DATAFILESDROP TABLESPACE TEMP02 INCLUDING CONTENTS AND DATAFILES11.查询表空间的信息：select tablespace_ name,bytes/1024/1024 file_size_mb,file_ name fromDBA_DATA_FILES-注意数据库中的实体都是以大写表示12如果在数

30、据库创建期间没有指定默认表空间，它将默认为SYSTEM。但您如何才能知道现有的数据库的默认表空间是哪一个？发出以下查询：SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = DEFAULT_PERMANENT_TABLESPACE：DATABASE_PROPERTIES视图显示默认表空间之外，还显示一些非常重要的信息一例如默认临时表空间、全局数据库名、时区等13在Oracle Database 10g 中，您可以类似地为用户指定一个默认表空间。在数据库创建期间，CREATE DATABASE 命令可以包含子句

31、DEFAULT TABLESPACE。在创建之后，您可以通过发出以下命令来使一个表空间变成默认表空间：ALTER DATABASE DEFAULT TABLESPACE ts name；14重命名一个表空间在数据仓库环境中（典型地，对于数据中心体系结构），在数据库之间传输表空间是很常见 的。但源数据库和目标数据库必须不存在拥有相同名称的表空间。如果存在两个拥有相同名称的表空间，则目标表空间中的段必须转移到一个不同的表空间中，然后重新创建这个表空间一这个任务说起来容易做起来难。Oracle Database 10g提供了一个方便的解决方案：您可以用以下命令来简单地重命名一个现有的表空间（SYST

32、EM 和SYSAUX 除外） 一无论是永久表空间还是临时表空间：ALTER TABLESPACE old nameRENAME TO ;一个重要的包 DBMS_SPACE_ADMIN :这个包为本地管理的所有表空间提供带有故障诊断和修复功能的管理程序。包含的管理程序有：SEGMENT_VERIFY :验证该段盘区映射的一致性SEGMENT_CURRUPT :标注该段为损坏或有效，以便执行恰当的错误恢复SEGMENT_DROP_CORRUPT:取消一个当前标注为损坏的段（不回收空间）SEGMENT_DUMP :卸下一个给定段的段头部和盘区映射TABLESPACE_VERIFY :验证该表空间中段

33、的位图和盘区映射是否同步TABLESPACE_REBUILD_BITMAPS:重建适当的位图TABLESPACE_FIX_BITMAPS:在位图中标注适当的数据块地址范围为空闲或已用TABLESPACE_REBUILED_QUOTAS:为给定表空间重建限额TABLESPACE_MIGERATE_FROM_LOCAL :将一个本地管理的表空间移植为字典管理的 表空间TABLESPACE_MIGRATE_TO_LOCAL :将一个字典管理的表空间移植为本地管理的表空 间TABLESPACE_RELOCATE_BITMAPES:将位图重定位到指定的目的地TABLESPACE_FIX_SEGMENT_

34、STATES :修改移植被放弃的表空间中数据段的状态些有关表空间信息的表或视图:V$TABLESPACE :来自控制文件的所有表空间的名称和编号DBA_TABLESPACE,USER_TABLESPACE:所有用户的表空间说明DBA_SEGMENTS,USER_SEGMENTS :所有用户表空间种段的信息DBA_EXTENTS,USER_EXTENTS :所有用户表空间中数据盘区的信息DBA_FREE_SPACE,USER_FREE_SPACE:所有用户表空间中的空闲盘区的信息V$DATAFILE :关于所有数据文件的信息，包括所属表空间和表空间号V$TEMPFILE :关于所有临时文件的信息

35、，包括所属表空间和表空间号DBA_DATA_FILES :显示属于表空间的数据文件DBA_TEMP_FILES :显示属于表空间的临时文件V$TEMP_EXTENT_MAP :所有本地管理的临时表空间中所有盘区的信息V$TEMP_EXTENT_POOL :由每个实例缓存和使用临时表空间（本地管理的）的状态V$TEMP_SPACE_HEADER :显示每个临时文件的已用/空闲空间DBA_USERS :所有用户默认的和临时表空间DBA_TS_QUOTAS :列出所有用户表空间限额V$SORT_SEGMENT :关于一个给定实例的每个排序段的信息，只有在表空间是TEMPOARY :类型时更新V$SO

36、RT_USER :用户使用的临时排序空间和临时的/永久的表空间1. 如果lz是因为drop table 时没有purge，或者说recyclebin 里还有其他东西的话，那么需要先purge recyclebin，再试试。2. 如果lz已经purge 了 recyclebin ，那很有可能是因为有其他segment在datafile 的末尾，需要你找到哪个segment在末尾。一个小实验：oracleoracle $ sqlplus /no logSQL*Plus: Release 10.2.0.1.0 - Production on Wed Jun 8 18:32:40 2011Copyri

37、ght (c) 1982,2005 , Oracle. All rights reserved.SQL conn /as sysdbaConn ected.SQL create tablespacetest on cedatafile 7u01/app/oradata/test/levit on 01.dbf size 50m;Tablespace created.SQL create table test on cetablespacetest once as select * from dba_objects;Table created.SQL in sert into test once

38、 select * from test once;50559 rows created.SQL /101118 rows created.SQL /202236 rows created.SQL /in sert into test once select * from test once*ERROR at line 1:ORA-01653: un able to exte nd table SYS.TESTONCE by 128 in tablespace TESTONCESQL commit;Commit complete.SQL select sum(BYTES/1024/1024) M

39、B from dba_exte nts whereSEGMENT_NAME=TESTONCE：MB49SQL create table test once2 (id nu mber) tablespacetest on ce;Table created.SQL insert into test on ce2values(1);1 row created.SQLcommit;Commit complete.SQL select SEGMENT_NAME,mi n(BLOCK_ID),max(BLOCK_ID) from dba_exte nts where TABLESPACE_NAME=TES

40、TONCE2 ;select SEGMENT_NAME,mi n(BLOCK_ID),max(BLOCK_ID) from dba_exte nts whereTABLESPACE_NAME=TESTONCE*ERROR at line 1:ORA-00937: not a sin gle-group group fun cti onSQL select SEGMENT_NAME,mi n(BLOCK_ID),max(BLOCK_ID) from dba_exte nts where TABLESPACE_NAME=TESTONCE2 group by SEGMENT_NAME;SEGMENT

41、_NA MIN(BLOCK_ID) MAX(BLOCK_ID)TESTONCE96153TESTONCE262816281前面49M的空间是 TESTONC这张表，TESTONCE从 49.07MB开始占用datafileSQL select 6281*8192/1024/1024 fromdual;6281*8192/1024/102449.0703125SQL selectSEGMENT_NAME,mi n(BLOCK_ID),max(BLOCK_ID),mi n(EXTENT_ID),max(EXTENT_lD) from dba_exte nts where TABLESPACE_ N

42、AME=TESTONCE2 group bySEGMENT_NAME;SEGMENT_NA MIN(BLOCK_ID) MAX(BLOCK_ID) MIN(EXTENT_ID) MAX(EXTENT_ID)TESTONCETESTONCE29615306281 6281 0630from dba_exte nts whereSQL select SEGMENT_NAME,BLOCK_ID,EXTENT_IDTABLESPACE_NAME=TESTONCE2 ;SEGMENT_NABLOCK_IDEXTENT_IDTESTONCE90TESTONCE171TESTONCE252TESTONCE3

43、33TESTONCE414TESTONCE495TESTONCE576TESTONCE657TESTONCE738TESTONCE819TESTONCE8910SEGMENT_NABLOCK_IDEXTENTTESTONCE9711TESTONCE10512TESTONCE11313TESTONCE12114TESTONCE12915TESTONCE13716TESTONCE26517TESTONCE39318TESTONCE52119TESTONCE64920TESTONCE77721SEGMENT_NABLOCK_IDEXTENTTESTONCE90522TESTONCE103323TESTONCE116124TESTONCE128925TESTONCE141726