ORACLE－常用表空间操作指令集

后4844

　　为了方便，下面用到的表空间统一名称myspace,数据文件存放在$ORACLE_BASE/oradata/wilson 下面
　　1、创建表空间
　　a、最简形式
　　create tablespace myspace datafile '/u01/oradata/wilson/myspace01.dbf' size 10m;
　　tablespace created
　　b、带自动扩展空能的表空间
　　有很多时候，我们都是无法准确的预测到数据库将来的数据量，因此在创建表空间的时候无法准确定义数据文件的大小，这时
　　我们可以让表空间有自动扩展的功能，当数据量大于数据文件初始值时，让它自动增长。
　　create tablespace myspace datafile '/u01/oradata/wilson/myspace01.dbf' size 10m autoextend on next 2M
　　maxsize 100M;
　　tablespace created
　　使用上面的命令，我们就创建了一个初始大小为10M，带自动扩展功能的表空间了，扩展的步长为2M，最大值为100M。如果你
　　不想加空间限制的话可以使用maxsize unlimited，如果不设maxsize，系统默认为unlimited。（还是受到硬盘大小的限制！）
　　c、local management 的表空间。
　　以前的ORACLE的表空都是由系统统一管理，从XX开始，oracle对表空间提供了locally management，他的好处是不需要查询
　　数据字典，在每个数据文件头部加bit map 来控制空间的使用，分配空间不用UNDO。具体内容大家可以到网上去GOOGLE一下。
　　create tablespace myspace datafile '/u01/oradata/wilson/myspace01.dbf' size 10m extent management
　　local uniform size 128K;
　　tablespace created
　　2、修改表空间状态
　　a、修改状态为只读/非只读
　　有时候因为某种需要，我们让表空间处于只读状态，就可以用如下的SQL statement
　　
　　alter tabespace myspace read only;
　　修改回来为
　　alter tablespace myspace read write;
　　当表空间被设为只读的时候有如下特点：
　　--系统会触发CHECKPIONT，把REDO里面的东西写入数据文件；
　　--数据只读；
　　--可以在表空间里DROP对象（不能truncate table）。
　　b、修改状态为offline/online
　　有时候，我们为了避免用户的数据连接，可以把表空间offline
　　alter tablespace myspace offline;
　　修改为online，可以使用
　　alter tablespace myspace online;
　　
　　在offline后面有三个参数分别为normal,immediate,temporary。9i之前还有for recovery 到了10g后就没有了。
　　有些表空间是无法offline的:
　　--system表空间
　　--正在交易的表空间
　　--系统默认临时表空间
　　3、修改表空间大小
　　a、使用autoextend on实现
　　在创建表空间的时候，可以使用autoextend on来实现，具体请看上面的 b、带自动扩展空能的表空间。
　　如果在创建表空间的时候没有设置autoextend on 那么可以使用alter database来实现
　　alter database datafile '/u01/oradata/wilson/myspace01.dbf' autoextend on next 2M maxsize 100M;
　　database altered
　　b、手动RESIZE表空间
　　如果想手动修改表空间到一定的大小，可以使用resize方法
　　alter database datafile '/u01/oradata/wilson/myspace01.dbf' resize 20M;
　　database altered
　　
　　使用这个命令可以随意的改变表空间的大小。不过有一点要注意的就是，在改小表空的时候，重新设定的值不能小于表空间
　　内现存的数据大小，否则报错。
　　c、通过添加数据文件修改
　　我们知道表空间是个逻辑上的概念，数据存放的物理地址是磁盘上的数据文件，因此我们可以通过添加新数据文件来扩充表空间
　　alter tablespace myspace add datafile '/u01/oradata/wilson/myspace02.dbf' size 20M;
　　tablespace altered
　　4、移动数据文件
　　有时候可能会因为磁盘损坏或者添加新磁盘，我们需要把原来的数据文件移动到新磁盘上，对此，ORACLE提供了两种方法
　　a、ALTER TABLESPACE
　　准备工作：
　　--表空间状态必须是offline
　　--新数据文件必须已经存在
　　
　　alter tablespace myspace rename datafile '/u01/oradata/wilson/myspace01.dbf' to '/u01/oradata/
　　myspace01.dbf';
　　tablespace altered
　　b、ALTER DATABASE
　　准备工作
　　--数据库必须处于mounted状态
　　--新数据文件必须已经存在
　　startup mount
　　alter database rename file'/u01/oradata/wilson/myspace01.dbf' to '/u01/oradata/myspace01.dbf';
　　database altered
　　5、删除表空间
　　a、删除表空间保留数据文件
　　
　　drop tablespace myspace including contents;
　　这时，数据空中的myspace表空间已经被删除了，但是磁盘上面的数据文件还存在。
　　b、删除表空间同时删除数据文件
　　drop tablespace myspace including contents and datafiles;
　　这时表空间和磁盘上面的数据文件都被删除了。
　　c、删除表空间的主外键约束
　　如果要删除的表空间中的表和其他表空间中的表有主外键约束关系，我们必须删除其约束关系，否则无法删除表空间。
　　drop tablespace myspace including contents cascade contraints;
　　这样我们就可以吧表空间种的约束条件都删除了。
　　
　　大家可能发现上面的三个删除方式可以写成一条SQL语句
　　drop tablespace myspace including contents and datafiles cascade contraints;
　　用这条语句就可以成功地将表空间完全删除。
　　6、存放表空间信息的数据字典和动态视图
　　a、表空间信息
　　dba_tablespace
　　v$tablespace
　　
　　b、数据文件信息
　　dba_data_files
　　v$datafile
　　c、临时表空间文件信息
　　dba_temp_files
　　v$tempfile
　　上面我们说的都是permanet类型的表空间，undo和temporary类型的表空间没有什么太大的差异，有机会我再写。
　　睡觉了(～ o ～)~zZ。
　　添加一条查看数据库里表空间使用情况的SQL：
　　select f.tablespace_name,a.total||‘M’,u.used||‘M’,f.free||‘M’,round((u.used/a.total)*100) "% used",
　　round((f.free/a.total)*100) "% Free"   
  from 
      (select tablespace_name, sum(bytes/(1024*1024)) total   
         from dba_data_files group by tablespace_name) a,  
      (select tablespace_name, round(sum(bytes/(1024*1024))) used   
         from dba_extents group by tablespace_name) u,  
      (select tablespace_name, round(sum(bytes/(1024*1024))) free   
         from dba_free_space group by tablespace_name) f  
 WHERE a.tablespace_name = f.tablespace_name and a.tablespace_name = u.tablespace_name;