oracle表空间操作

wewopargt的头像 wewopargt 0 2016-03-09 15:33 3

 基本信息

× 1   

浏览数: 30675

分享时间: 2 年 前

8

1,查询表空间剩余

-- 剩余量
select tablespace_name,sum(bytes) from dba_free_space group by tablespace_name;
-- 总量
select tablespace_name,sum(bytes) from DBA_DATA_FILES group by tablespace_name;

2,查询数据库原始文件

 select * from DBA_DATA_FILES;

3,增加表空间大小的四种方法

方法1:给表空间增加数据文件
ALTER TABLESPACE app_data ADD DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF' SIZE 50M;
 
方法2:新增数据文件,并且允许数据文件自动增长
ALTER TABLESPACE app_data ADD DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP04.DBF' SIZE 50M AUTOEXTEND ON NEXT 5M MAXSIZE 100M;
 
方法3:允许已存在的数据文件自动增长
ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF' AUTOEXTEND ON NEXT 5M MAXSIZE 100M;
 
方法4:手工改变已存在数据文件的大小
ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP02.DBF' RESIZE 100M;

4,更改表及分区表的表空间

可以通过alter方法,将一个表移动到另外一个表空间中:
sql: alter table spaceOne.tablename move tablespace spaceTwo;
解释:以上语句就是把tablename表从spaceOne移动到spaceTwo中。
备注一:
当前的用户必须对spaceTwo、spaceOne都有操作权限才可以。
备注二:
其实如果对两个表空间都有权限的话,可以通过
create spaceTwo.tablename as select * from spaceOne.tablename;
之后再删除spaceOne中tablename表的间接方式也能实现。

-- 分区表
alter table slz_ysz move partition YSZ_200901  tablespace 你要定义的tablespacename;

5,其它

查询数据服务地址信息:tnsping service_name


12 [下一页]

  • ossaa的头像 ossaa 2018-05-16 22:12 代码数:0

    Your music is amazing. You have some very talented artists. I wish you the best of success.

    Montana ranches for sale

  • ossaa的头像 ossaa 2018-05-16 21:07 代码数:0

    Your music is amazing. You have some very talented artists. I wish you the best of success.

    Nebraska SEO services

  • ossaa的头像 ossaa 2018-05-16 19:21 代码数:0

    Your music is amazing. You have some very talented artists. I wish you the best of success.

    SEO

  • ossaa的头像 ossaa 2018-05-10 18:29 代码数:0

    This is such a great resource that you are providing and you give it away for free. I love seeing websites that understand the value of providing a quality resource for free. It is the old what goes around comes around routine.

    business ideas in india

  • ossaa的头像 ossaa 2018-05-09 16:19 代码数:0

    This is such a great resource that you are providing and you give it away for free. I love seeing websites that understand the value of providing a quality resource for free. It is the old what goes around comes around routine.

    currency converter

  • gererik的头像 gererik 2018-04-24 18:10 代码数:0

    Thank you very much for writing such an interesting article on this topic.  This has really made me think and I hope to read more.read here

  • gererik的头像 gererik 2018-04-24 16:57 代码数:0

    I’ve read some good stuff here. Definitely worth bookmarking for revisiting. I surprise how much effort you put to create such a great informative website. look at this

  • gererik的头像 gererik 2018-04-23 17:32 代码数:0

    this is really nice to read..informative post is very good to read..thanks a lot! acim

  • gererik的头像 gererik 2018-04-23 16:27 代码数:0

    Thanks for sharing nice information with us. i like your post and all you share with us is uptodate and quite informative, i would like to bookmark the page so i can come here again to read you, as you have done a wonderful job.  acim

  • gererik的头像 gererik 2018-04-21 15:07 代码数:0

    i am always looking for some free stuffs over the internet. there are also some companies which gives free samples.fridge repair near me

您的评论: