oracle数据库扩容sql

--查看表空间信息
SELECT TABLESPACE_NAME "表空间",
To_char(Round(BYTES / 1024, 2), '99990.00')
|| '' "实有",
To_char(Round(FREE / 1024, 2), '99990.00')
|| 'G' "现有",
To_char(Round(( BYTES - FREE ) / 1024, 2), '99990.00')
|| 'G' "使用",
To_char(Round(10000 * USED / BYTES) / 100, '99990.00')
|| '%' "比例"
FROM (SELECT A.TABLESPACE_NAME TABLESPACE_NAME,
Floor(A.BYTES / ( 1024 * 1024 )) BYTES,
Floor(B.FREE / ( 1024 * 1024 )) FREE,
Floor(( A.BYTES - B.FREE ) / ( 1024 * 1024 )) USED
FROM (SELECT TABLESPACE_NAME TABLESPACE_NAME,
Sum(BYTES) BYTES
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME TABLESPACE_NAME,
Sum(BYTES) FREE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME)
--WHERE TABLESPACE_NAME LIKE 'CDR%' --这一句用于指定表空间名称
ORDER BY Floor(10000 * USED / BYTES) DESC;

 

--查看表空间的位置和物理文件
select TABLESPACE_NAME,FILE_ID,FILE_NAME,BYTES/(1024*1024)TOTAL_SPACE from dba_data_files

 

---扩大表空间-增加表空间文件
ALTER TABLESPACE NPPT ADD DATAFILE
'D:\SOFTWARE\ORACLE\ORADATA\CDRDB\NPPT07.DBF' SIZE 10240M
AUTOEXTEND ON NEXT 100M MAXSIZE 20480M;

 

--扩大表空间-修改原有表空间大小
alter database datafile 'D:\DATABASE\CDRDB\PLANT_SERVICE11.DBF' resize 8192m;
alter database datafile 'D:\DATABASE\CDRDB\PLANT_SERVICE12.DBF' resize 8192m;

 

--是否自动增长
SELECT FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE FROM dba_data_files;

 

--空表查看
select 'alter table ' ||table_name ||' allocate extent;' from user_tables where segment_created='NO';

 

--查询用户下各个表中的数据条数
SELECT table_name, nvl(num_rows,1) FROM dba_tables t where t.owner = 'NPPT'; -- 用户名一定要大写

 

--查询某个表空间下面的表 占用大小降序
select segment_name,
tablespace_name,
bytes B,
round(bytes / 1024, 1) KB,
round(bytes / 1024 / 1024, 1) MB,
round(bytes / 1024 / 1024 / 1024, 2) GB
from dba_segments
where segment_type = 'TABLE'
and tablespace_name = 'USERS'
order by bytes desc;

 

SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME LIKE 'RUN_LOG_%';

RUN_LOG_847
RUN_LOG_857
RUN_LOG_858
RUN_LOG_868
RUN_LOG_870
RUN_LOG_877
RUN_LOG_878
RUN_LOG_879
RUN_LOG_880
RUN_LOG_887
RUN_LOG_888
RUN_LOG_889
RUN_LOG_890
RUN_LOG_892
RUN_LOG_893
RUN_LOG_894
RUN_LOG_895
RUN_LOG_896

--清空RUN_LOG_开头的表数据
truncate table RUN_LOG_887;

alter TABLE RUN_LOG_887 enable row movement;

alter TABLE RUN_LOG_887 shrink SPACE;

alter TABLE RUN_LOG_887 DISABLE row movement;