oracle 表空间压缩

时间:2023-01-25 01:04:12

SELECT --B.file_name "文件名",
A.TABLESPACE_NAME "表空间名",
TOTAL "表空间大小",
FREE "表空间剩余大小",
(TOTAL - FREE) "表空间使用大小",
TOTAL / (1024 * 1024 * 1024) "表空间大小(G)",
FREE / (1024 * 1024 * 1024) "表空间剩余大小(G)",
(TOTAL - FREE) / (1024 * 1024 * 1024) "表空间使用大小(G)",
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,
-- file_name,
SUM(BYTES) TOTAL
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME/*,file_name*/) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME;

 

SELECT
V.FI "文件ID",
V.TN "表空间",
V.FN "物理文件名",
V.BS "总字节数 ",
V.BS/ (1024 * 1024 * 1024) "总字节数(G)",
V.P "剩余百分比",
V.U "已使用",
V.U/ (1024 * 1024 * 1024)  "已使用(G)",
V.NS/ (1024 * 1024 * 1024) " 剩余(G)"
FROM (
select b.file_id  FI,
b.tablespace_name  TN,
b.file_name     FN,
b.bytes       BS,
(b.bytes-sum(nvl(a.bytes,0)))    U,
sum(nvl(a.bytes,0))        NS,
sum(nvl(a.bytes,0))/(b.bytes)*100 P
  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 
) V
 order by V.TN

;

select file#,name from v$datafile;

--- 1. 查该数据文件中数据处在最大位置
select max(block_id) from dba_extents where file_id=6;

--- 查出最大块位置 5755M
select 736640*8/1024 from dual;
--- ALTER DATABASE datafile 'H:\ORACLE11G\APP\ADMIN\ORADATA\ORCL\SYNC_PLUS_1_02.DBF' RESIZE 3000M;

-- alter database datafile 7 resize 5755M;













为人:谦逊、激情、博学、审问、慎思、明辨、 笃行
学问:纸上得来终觉浅,绝知此事要躬行
为事:工欲善其事,必先利其器。
态度:道阻且长,行则将至;行而不辍,未来可期
.....................................................................
------- 桃之夭夭,灼灼其华。之子于归,宜其室家。 ---------------
------- 桃之夭夭,有蕡其实。之子于归,宜其家室。 ---------------
------- 桃之夭夭,其叶蓁蓁。之子于归,宜其家人。 ---------------
=====================================================================
转载请标注出处!