I need a way to iterate through all tables in tablespace "T_ECOS" and find all ID with null value in oracle database
Any suggestion?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this communityYou can do something like this to dynamically generate a query against the tables with a nullable ID column to see whether they actually contain nulls or not:
declare
l_cnt pls_integer;
begin
for tabs in (SELECT c.owner, c.table_name FROM dba_segments s, dba_tab_cols c
where s.segment_name = c.table_name
and c.column_name = 'ID'
and c.nullable = 'Y'
and s.tablespace_name = 'T_ECOS') loop
execute immediate 'select count(*) from ' || tabs.owner || '.' || tabs.table_name ||
' where id is null and rownum = 1' -- to short-circuit the count as soon as a null entry is found
into l_cnt;
if l_cnt > 0 then
dbms_output.put_line(tabs.table_name);
end if;
end loop;
end;
/
dba_
with all_
(everything you have privilege to see) or user_
(just the objects your user owns). If you have multiple schemas with objects in the T_ECOS tablespace you'll need to ensure you have privileges to access them all.
– Chris Saxon
Oct 25 '12 at 14:15
ID
columns [in a sensibly designed schema] are normally keyed and cannot beNULL
. Have you checked this first? – Philᵀᴹ Oct 25 '12 at 11:11NOT NULL
, which is distinctly different from checking each individual table column value recursively to check for NULL values. Your answer is incorrect. – Philᵀᴹ Oct 25 '12 at 11:31