-
duplicate rows
select * FROM table WHERE ROWID NOT IN ( SELECT MIN(ROWID) FROM table GROUP BY timestamp)
and timestamp between 1038351600 and 1038437940
order by timestamp DESC;
I have the sql above giving me duplicate rows for a given day on a specific table. How can I run the same query for all my tables in a given schema, the problem is I have many tables but need to see all the ones with duplicates rather than the sql above which does it table by table, I need to query the data dictionary user_tables etc.
Any ideas?
-
use dynamic sql to build the query
-
You have to write a dynamic sql query to generate the necessary sql for all the tables for that schema.
It should look like:
------------------
set heading off
spool xxx.sql
select "select * FROM", table_name , "WHERE ROWID NOT IN ( SELECT MIN(ROWID) FROM ", table_name, "GROUP BY timestamp)
and timestamp between 1038351600 and 1038437940
order by timestamp DESC; " from user_tables;
spool off
--------------------------
You can then run the xxx.sql to get the output you want(each row for each table for the schema).
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|