首页 > 数据库技术 > 详细

orcle数据库扩容

时间:2019-09-03 10:00:00      阅读:104      评论:0      收藏:0      [点我收藏+]

//首先查出来表空间使用率

SELECT a.tablespace_name "SYSTEM",total 表空间大小,free 表空间剩余大小,
(total-free) 表空间使用大小,
ROUND((total-free)/total,4)*100 "使用率 %"
FROM (SELECT tablespace_name,SUM(bytes) free FROM DBA_FREE_SPACE
GROUP BY tablespace_name ) a,
(SELECT tablespace_name,SUM(bytes) total FROM DBA_DATA_FILES
GROUP BY tablespace_name) b
WHERE a.tablespace_name=b.tablespace_name;

 

//上面查的不准

select total.tablespace_name,round(total.MB,2) as Total_MB,round(total.MB-free.MB, 2)
as Used_MB,round((1-free.MB/total.MB)*100, 2) as Used_Pct from (select tablespace_name,
sum(bytes)/1024/1024 as MB from dba_free_space group by tablespace_name) free,(select tablespace_name,
sum(bytes)/1024/1024 as MB from dba_data_files group by tablespace_name) total where free.tablespace_name=total.tablespace_name;

 

//然后查询表空间使用路径

select a.file#,

a.name,

a.bytes / 1024 / 1024 CurrentMB,

ceil(HWM * a.block_size / 1024 / 1024) Resizeto,

(a.bytes - HWM * a.block_size) /1024 / 1024 releaseMB,

‘alter database datafile ‘‘‘ || a.name || ‘‘‘ resize ‘ ||

ceil(HWM * a.block_size / 1024 / 1024) || ‘M;‘ ResizeCmd

from v$datafile a,

(select file_id, max(block_id + blocks - 1) HWM

from dba_extents

group by file_id) b

where a.file# = b.file_id(+)

and (a.bytes - HWM * a.block_size) > 0

order by 5

 

//扩容

alter  tablespace 表名
add datafile ‘e:\oradb\data2_data3.dbf‘ //扩容表空间路径相同名字不同
size 10240m  //扩容大小
autoextend on  
next 500m maxsize 20480m; 

 

//然后执行

1.用以下这句查找空表并生成执行命令
select ‘alter table ‘||table_name||‘ allocate extent;‘ from user_tables where num_rows=0;

orcle数据库扩容

原文:https://www.cnblogs.com/YingJian/p/11451014.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!