Oracle 11g 修改表的所属表空间

本文主要是介绍Oracle 11g 修改表的所属表空间，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

 

在做数据库测试的时候，一直向oracle库写数据，然后就报错，表空间不足

分析解决：

1、提示表空间不足后，想起当时创建表的时候 没有新建表空间，直接默认的，已经忘记是哪个表空间了，所以查看一下

select  username,  DEFAULT_TABLESPACE  from  dba_users where username='MYSQL_INCRE';

2、查出的结果是：USERS，再去查看表空间USERS的数据文件

select * from dba_data_files where tablespace_name='USERS';

3、查出了数据文件的目录：/usr/local/warehouse/oracle/oradata/orcl/users01.dbf

4、去该目录下 查看这个数据文件到底多大

[root@demo225 tablespace]# cd /usr/local/warehouse/oracle/oradata/orcl/
[root@demo225 orcl]# ll
总用量 26718836
-rw-r----- 1 oracle oinstall     9977856 8月  14 16:41 control01.ctl
-rw-r----- 1 oracle oinstall    52429312 8月  14 11:50 redo01.log
-rw-r----- 1 oracle oinstall    52429312 8月  14 16:41 redo02.log
-rw-r----- 1 oracle oinstall    52429312 8月  14 08:26 redo03.log
-rw-r----- 1 oracle oinstall   943726592 8月  14 16:36 sysaux01.dbf
-rw-r----- 1 oracle oinstall  1761615872 8月  14 16:36 system01.dbf
-rw-r----- 1 oracle oinstall   170926080 8月  14 16:36 temp01.dbf
-rw-r----- 1 oracle oinstall  2998935552 8月  14 16:36 undotbs01.dbf
-rw-r----- 1 oracle oinstall 21318868992 8月  14 15:14 users01.dbf
[root@demo225 orcl]# du -sh ./*
9.6M	./control01.ctl
51M	./redo01.log
51M	./redo02.log
51M	./redo03.log
901M	./sysaux01.dbf
1.7G	./system01.dbf
162M	./temp01.dbf
2.8G	./undotbs01.dbf
20G	./users01.dbf      #已经有20G了
[root@demo225 orcl]# 

5、我清空了测试schame：MYSQL_INCRE中的数据，但是这个文件还是很大，说明这个表空间有很多表在共用，查看有哪些表

select * from dba_tables where tablespace_name='USERS' order by table_name

结果有两千多张表，很多是别人的表 不能动 万一失败了岂不是完蛋，所以我只能动我自己创建的这几个表

6、搜索到一个我想要的解决方案：https://blog.csdn.net/weixin_41840720/article/details/96314763 写的很清晰，只要提前建立一个自己的表空间就可以了，首先看看哪里有大的空间

[oracle@demo225 trace]$ df -h
Filesystem                    Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root   50G   47G   17M 100% /            #已经用完了
tmpfs                          16G  2.1G   14G  13% /dev/shm
/dev/sda1                     485M   39M  421M   9% /boot
/dev/mapper/VolGroup-lv_home  144G   56G   81G  41% /home       #这里空间大

可见/home下空间比较大，新建一个目录，用于存放数据文件，记得赋权限 否则报错

[oracle@demo225 home]$ mkdir -p /home/oracledata/tablespace/
[oracle@demo225 home]$ chown -R oracle:oinstall /home/oracledata/tablespace/

目录赋权限后，新建表空间，之后就按照https://blog.csdn.net/weixin_41840720/article/details/96314763连接里的步骤迁移即可

 create tablespace MYSQL_INCRE
datafile '/home/oracledata/tablespace/MYSQL_INCRE.dbf'
size 50m --初始大小
autoextend on --自动扩展
next 50m maxsize UNLIMITED --自动扩展每du次增加50M，最大可到20480M
extent management local;

备注：

关于数据文件迁移还有很多写的很好的博客

https://blog.csdn.net/LiJiVV/article/details/98346120

https://www.cnblogs.com/dbseeker/p/8850332.html

 https://blog.csdn.net/weixin_41840720/article/details/96314763
https://www.cnblogs.com/caiyi/archive/2012/08/14/2638065.html
https://www.cnblogs.com/ayumie/p/10910312.html

https://www.cnblogs.com/bayu/articles/9191335.html

还有很多实用的sql

1、查看表空间利用率

SELECT 
tbs 表空间名, 
sum(totalM) 总共大小M,  
sum(usedM) 已使用空间M, 
sum(remainedM) 剩余空间M, 
sum(usedM)/sum(totalM)*100 已使用百分比, 
sum(remainedM)/sum(totalM)*100 剩余百分比 
FROM(  SELECT b.file_id ID, b.tablespace_name tbs,  b.file_name name,  b.bytes/1024/1024 totalM, (b.bytes-sum(nvl(a.bytes,0)))/1024/1024 usedM, sum(nvl(a.bytes,0)/1024/1024) remainedM, sum(nvl(a.bytes,0)/(b.bytes)*100), (100 - (sum(nvl(a.bytes,0))/(b.bytes)*100)) 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.file_id,b.bytes  ORDER BY b.tablespace_name  ) GROUP BY tbs 

2、查看表与表空间

-- 哪个表在哪个表空间中：select table_name,tablespace_name from user_tables-- 查看表空间剩余空间：SELECT tablespace_name 表空间,sum(blocks*8/1024) 剩余空间M FROM dba_free_space GROUP BY tablespace_name-- 表空间中各个表占用率select t.owner,t.segment_name,t.tablespace_name,bytes/1024/1024/1024 as sizes,q.num_rows,t.segment_typefrom dba_segments tleft join dba_tables qon t.segment_name=q.table_nameand t.owner=q.ownerwhere t.segment_type='TABLE'and t.tablespace_name='MYSQL_INCRE'  --需要查看的表空间order by 4 desc

 

这篇关于Oracle 11g 修改表的所属表空间的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---