1. Search for user tables
- SelectTable_name, num_rowsFromAll_tablesWhereOWNER ='Tiger' ANDTABLE_NAMELike '% Master'
- Order ByNum_rowsDesc
2. Search user table fields
- Select*FromUser_tab_columns
- WhereDATA_TYPE ='Char' AndDATA_LENGTH = 119
- AndTable_name =Upper('Imblmaster')
3. Search for repeated fields. Remove the count function to retrieve records.
- Select Count(*)FromSys_data
- WhereTrxrefIn(SelectTrxrefFromSys_dataGroup ByTrxrefHaving Count(Trxref)> 1)
- -- Or
- Select Sum(N)From(SelectTrxref,Count(Trxref)AsNFromSys_datasGroup ByTrxrefHaving Count(Trxref)> 1)
4. Search for records that are not repeated
- Select Count(Trxref)From(SelectTrxref,Count(Trxref)AsNFromSys_dataGroup ByTrxrefHaving Count(Trxref)> 1)
5. Search for redundant duplicate records
- Select Count(*)FromSys_data
- WhereTrxrefIn(SelectTrxrefFromSys_datasGroup ByTrxrefHaving Count(Trxref)> 1)
- AndRowidNot In(Select Min(Rowid)FromSys_dataGroup ByTrxrefHaving Count(Trxref)> 1)
6. Delete redundant duplicate records
- Delete FromSys_data
- WhereTrxrefIn(SelectTrxrefFromSys_dataGroup ByTrxrefHaving Count(Trxref)> 1)
- AndRowidNot In(Select Min(Rowid)FromSys_data