检查oracle数据库数据完整度

来源：互联网发布：邓肯生涯数据编辑：程序博客网时间：2024/05/17 12:01

1、查看表空间总大小，剩余大小，使用大小
select df.tablespace_name,SUM(df.bytes)/1024/1024 total,SUM(fs.bytes)/1024/1024 free,SUM(df.bytes-fs.bytes)/1024/1024 used from dba_data_files df,dba_free_space fs
where df.tablespace_name = fs.tablespace_name
group by df.tablespace_name

2、查看用户下每种对象的个数

SELECT OWNER,OBJECT_TYPE,COUNT(1) FROM ALL_objects where owner='username' GROUP BY OWNER,OBJECT_TYPE

3查看行数大小（有时统计不出来，最好对比导入导出日志，里面会记录导入导出的条数）

SELECT TABLE_NAME,NUM_ROWS FROM DBA_ALL_TABLES WHERE TABLESPACE_NAME='tablespace'

4查看某种对象的详情列表

（1）查看索引

SELECT OBJECT_NAME FROM ALL_objects WHERE OBJECT_TYPE='INDEX' AND owner='username';

（2）查看序列

SELECT OBJECT_NAME FROM ALL_objects WHERE OBJECT_TYPE='SEQUENCE' AND owner='username';

（3）查看job

SELECT OBJECT_NAME FROM ALL_objects WHERE OBJECT_TYPE='JOB' AND owner='username';

（4）查看trigger

SELECT OBJECT_NAME FROM ALL_objects WHERE OBJECT_TYPE='TRIGGER' AND owner='username';

0 0