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

柔光的暖阳◎ 2022-11-27 08:46 210阅读 0赞

在做数据库测试的时候，一直向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][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][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_blog.csdn.net_LiJiVV_article_details_98346120]

[https://www.cnblogs.com/dbseeker/p/8850332.html][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][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_type
      from dba_segments t
      left join dba_tables q
      on t.segment_name=q.table_name
       and t.owner=q.owner
     where t.segment_type='TABLE'
       and t.tablespace_name='MYSQL_INCRE'  --需要查看的表空间
     order by 4 desc

[https_blog.csdn.net_weixin_41840720_article_details_96314763]: https://blog.csdn.net/weixin_41840720/article/details/96314763
[https_blog.csdn.net_LiJiVV_article_details_98346120]: https://blog.csdn.net/LiJiVV/article/details/98346120
[https_www.cnblogs.com_dbseeker_p_8850332.html]: https://www.cnblogs.com/dbseeker/p/8850332.html
[https_www.cnblogs.com_bayu_articles_9191335.html]: https://www.cnblogs.com/bayu/articles/9191335.html