ORACLE中查找用户表和用户表字段等

最新推荐文章于 2025-04-17 16:15:31 发布

最新推荐文章于 2025-04-17 16:15:31 发布 · 94 阅读

文章标签：

#数据库

本文提供了一系列实用的SQL查询技巧，包括如何查找特定用户的表及表字段、如何发现并处理重复数据等，对于数据库管理和优化非常实用。

1、查找用户表

select table_name,num_rows from all_tables where OWNER='TIGER' AND TABLE_NAME like '%MASTER'
order by num_rows desc

2、查找用户表字段

select * from user_tab_columns
where DATA_TYPE='CHAR' and DATA_LENGTH=119
 and Table_name=upper('TYIMBLMASTER')

3、查找重复字段，去掉count函数则取出记录

select count(*) from sys_data
where trxref in (select trxref from sys_data group by trxref having count(trxref) > 1)
--或
select sum(n) from (select trxref,count(trxref) as n from sys_datas group by trxref having count(trxref) > 1)

4、查找不重复的记录

select count(trxref) from (select trxref,count(trxref) as n from sys_data group by trxref having count(trxref) > 1)

5、查找多余的重复记录

select count(*) from sys_data
where trxref in (select trxref from sys_datas group by trxref having count(trxref) > 1)
and rowid not in (select min(rowid) from   sys_data group by trxref having count(trxref)>1)

6、删除多余的重复记录

delete from sys_data
where trxref in (select trxref from sys_data group by trxref having count(trxref) > 1)
and rowid not in (select min(rowid) from   sys_data