Oracle表空间的几个操作汇总.docx

Oracle表空间的几个操作一、 Oracle系统中有表来存放表空间信息、空间块、对应文件、对应用户等。 主要集中在 dba_tablespaces dba_free_space dba_data_files user_users 二、Oracle 创建表空间CREATE SMALLFILE TABLESPACE SMALLSPACE LOGGING DATAFILE E:ORACLEPRODUCT10.2.0ORADATAGLOBE1SMALLSPACE.ora SIZE 2M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE, (B.BYTES*100)/A.BYTES % USED,(C.BYTES*100)/A.BYTES % FREE FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME; 三、 删除表空间DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES; 先删除sde用户，再删除sde表空间。 DROP TABLESPACE SDE INCLUDING CONTENTS cascade constraints;四、改变表空间状态1.使表空间脱机ALTER TABLESPACE game OFFLINE;如果是意外删除了数据文件，则必须带有RECOVER选项ALTER TABLESPACE game OFFLINE FOR RECOVER;2.使表空间联机ALTER TABLESPACE game ONLINE;3.使数据文件脱机ALTER DATABASE DATAFILE 3 OFFLINE;4.使数据文件联机ALTER DATABASE DATAFILE 3 ONLINE;5.使表空间只读ALTER TABLESPACE game READ ONLY;6.使表空间可读写ALTER TABLESPACE game READ WRITE;五、删除表空间DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES;六、扩展表空间首先查看表空间的名字和所属文件select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_spacefrom dba_data_filesorder by tablespace_name;1.增加数据文件ALTER TABLESPACE gameADD DATAFILE /oracle/oradata/db/GAME02.dbf SIZE 1000M;2.手动增加数据文件尺寸ALTER DATABASE DATAFILE /oracle/oradata/db/GAME.dbfRESIZE 4000M;3.设定数据文件自动扩展ALTER DATABASE DATAFILE /oracle/oradata/db/GAME.dbfAUTOEXTEND ON NEXT 100MMAXSIZE 10000M;Sql语句 扩大表空间： ALTER DATABASE DATAFILE E:ORACLEPRODUCT10.2.0ORADATAGLOBE1DG RESIZE 15000M七、表空间查询1 查看表空间select a.a1 表空间名称,c.c2 类型,c.c3 区管理,b.b2/1024/1024 表空间大小M,(b.b2-a.a2)/1024/1024 已使用M,substr(b.b2-a.a2)/b.b2*100,1,5) 利用率from (select tablespace_name a1, sum(nvl(bytes,0) a2 from dba_free_space group by tablespace_name) a,(select tablespace_name b1,sum(bytes) b2 from dba_data_files group by tablespace_name) b,(select tablespace_name c1,contents c2,extent_management c3 from dba_tablespaces) c where a.a1=b.b1 and c.c1=b.b1;查询某表空间的大小select a.a1 表空间名称,c.c2 类型,c.c3 区管理,b.b2/1024/1024 表空间大小M,(b.b2-a.a2)/1024/1024 已使用M,substr(b.b2-a.a2)/b.b2*100,1,5) 利用率from (select tablespace_name a1, sum(nvl(bytes,0) a2 from dba_free_space group by tablespace_name) a,(select tablespace_name b1,sum(bytes) b2 from dba_data_files group by tablespace_name) b,(select tablespace_name c1,contents c2,extent_management c3 from dba_tablespaces) c where a.a1=SMALLSPACE and b.b1=SMALLSPACE and a.a1=b.b1 and c.c1=b.b1;select = errora.a1 表空间名称,b.b3 文件路径, = errorc.c2 类型,c.c3 区管理,b.b2/1024/1024 表空间大小M,(b.b2-a.a2)/1024/1024 已使用M,substr(b.b2-a.a2)/b.b2*100,1,5) 利用率from (select tablespace_name a1, sum(nvl(bytes,0) a2 from dba_free_space group by tablespace_name) a,(select tablespace_name b1,sum(bytes) b2, FILE_NAME b3 from dba_data_files group by tablespace_name) b,(select tablespace_name c1,contents c2,extent_management c3 from dba_tablespaces) c where a.a1=SMALLSPACE and b.b1=SMALLSPACE and a.a1=b.b1 and c.c1=b.b1;FILE_NAME b3 错误 ORA-00979: 不是 GROUP BY 表达式SQL select * from dba_tablespaces where TABLESPACE_NAME = SMALLSPACE;SQL select contents , extent_management from dba_tablespaces where TABLESPACE_NAME = SMALLSPACE; 查看表空间的空闲情况SQL select * from dba_free_space where tablespace_name = SMALLSPACE; TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO- - - - - -LUOPENG 10 1786697 65536 8 10LUOPENG 10 1786689 65536 8 10LUOPENG 10 1786681 65536 8 10LUOPENG 10 1786673 65536 8 10LUOPENG 10 1786889 65536 8 10LUOPENG 10 1786969 65536 8 10LUOPENG 10 1787001 65536 8 10LUOPENG 10 1787401 65536 8 10 8806 rows selected查看表空间的空闲情况SQL select a.a1 SPACENAME,b.b2 TOTAL,a.a2 FREE,(b.b2-a.a2) USEDfrom (select tablespace_name a1, sum(nvl(bytes,0) a2 from dba_free_space group by tablespace_name) a,(select tablespace_name b1,sum(bytes) b2 from dba_data_files group by tablespace_name) bwhere a.a1=SMALLSPACE and b.b1=SMALLSPACE and a.a1=b.b1 ; SPACENAME TOTAL FREE USED- - - -SMALLSPACE 2097152 2031616 65536 2 查看数据库文件：dba_data_files 字典表中，存放有 表空间对应 的 物理文件路径及大小。select b.file_name 物理文件名,b.tablespace_name 表空间,b.bytes/1024/1024 大小M,(b.bytes-sum(nvl(a.bytes,0)/1024/1024 已使用M,substr(b.bytes-sum(nvl(a.bytes,0)/(b.bytes)*100,1,5) 利用率 from dba_free_space a,dba_data_files b where a.file_id=b.file_id group by b.tablespace_name,b.file_name,b.bytes order by b.tablespace_nameSQL select tablespace_name , FILE_NAME , bytes from dba_data_files order by tablespace_name; 查看一个表空间 3 查看临时表空间：在oracle数据库中，临时表空间主要用于用户在使用order by 、group by语句进行排序和汇总时所需