Find junk values in all tables and columns:
Loop through all tables in a schema (Oracle 18c)
Loop through each number and text column
If columns have values that are junk, then add a statistic/record to a log table:
0 (number)
(text; single space)
(text; double space)
0 (text)
- (text)
NUL...
@User1973 I'm not an Oracle guy, but my swag says that this is gonna SUCK perf wise. It's gotta scan everything right?
So if you run it today, and then Thursday, it has to rescan everything that it did today when it runs later, right?
How many tables and how many rows are we talking here? My databases at work are tiny, a few terabyes each down to 10s of GBs, so this is gonna take a LONG time and then you have to ask if you need to lock each row to read it or can you just do a read-through/fly-by and just hope the value you captured was "good enough".
What's the business case of detecting this data? Are we talking something academic, proof of concept, business need, data analysis, pet project, schema validation, etc?
@jcolebrand It runs on the whole DB in under 10 seconds and flags 1.25 million records. So that kind of performance is pretty good in my books. Data entry/cleanup people will be able to run it once or twice in a day without issue.
We're talking maybe 100 tables with maybe 500,000 records. I don't know what the file size would be (I'm just an analyst, I don't have access to the DB/server).
The business case is to flag junk records in a Geographic Information System (GIS) database. Users will go investigate issues and correct them using a GIS tool called the 'Field Calculator'. It's kind of…
If you really need to run something like this "once or twice in a day" you'll be better off solving the problem at the source. Databases are "wildly messy" only when they are allowed to be, not "always". But then again, if "data entry/cleanup people" are paid by the hour, I'm sure they'll find good reasons to keep dropping food on the floor and mop it up, again and again...
Find junk values in all tables and columns:
Loop through all tables in a schema (Oracle 18c)
Loop through each number and text column
If columns have values that are junk, then add a statistic/record to a log table:
0 (number)
(text; single space)
(text; double space)
0 (text)
- (text)
NUL...
