Documents

5 pages
8 views

Oracle - Query to Find All Empty Tables - Stack Overflow

Please download to get full document.

View again

of 5
All materials on our website are shared by users. If you have any questions about copyright issues, please report us to resolve them. We are always happy to assist you.
Share
Description
Oracle - Query to Find All Empty Tables - Stack Overflow
Transcript
  11/10/2017oracle - Query to find all empty tables - Stack Overflowhttps://stackoverflow.com/questions/11134992/query-to-find-all-empty-tables1/5 Sathya 14.6k 2077111 user472625 60 2413 4 Answers 3 1 Considering that I have a Schema named SBSTI want to find all empty tables list in this SBST Schema. Is there any PL/SQL procedure to find that.I found few. But those were using user tables where I was not able specify the Schema nameSBST.I was using this select table_name from dba_tables where owner ='SBST'  having count(*)=0 group by table_name What's wrong in the above query?  shareimprove this question edited Jun 26 '12 at 7:16asked Jun 21 '12 at 9:11 6 Similar to @shareef's answer, but using dynamic SQL to avoid having to create thetemporary .sql  file. You'll need dbms_output  to be visible, e.g. with set serveroutput on  inSQL*Plus - don't know about Toad. declare cursor c(p_schema varchar2) is  select 'select ''' || table_name || ''' from ' || p_schema ||'.' || table_name || ' where rownum < 2 ' || ' having count(*) = 0' as query from all_tables where owner = p_schema order by table_name; l_table all_tables.table_name%TYPE; begin for r in c('SBST') loop begin execute immediate r.query into l_table; exception when no_data_found then continue; end; dbms_output.put_line(l_table); Query to find all empty tables  Ask Question oracleoracle11gactiveoldestvotes  11/10/2017oracle - Query to find all empty tables - Stack Overflowhttps://stackoverflow.com/questions/11134992/query-to-find-all-empty-tables2/5  Alex Poole 105k 673131  Andy ♦ 25.9k 1784133 user5243338 31 1  end loop; end; / Using all_tables  seems more useful than dba_tables  here so you know you can select from thetables it lists. I've also included the schema in the from  clause in case there other users havetables with the same name, and so you can still see it if you're connected as a different user -possibly avoiding synonym issues too.Specifically what's wrong with your query... you've got the having  and group by  clauses thewrong way around; but it will always return no data anyway because if SBST has any tablesthen count (*) from dba_tables  must be non-zero, so the having  always matches; and if itdoesn't then, well, there's no data anyway so there's nothing for the having  to match against.You're counting how many tables there are, not how many rows are in each table. shareimprove this answer  edited Jun 21 '12 at 15:40answered Jun 21 '12 at 11:13 1  having  clause is not necessary. SELECT 1 FROM owner.table WHERE rownum < 2  woud be enough todetermine whether there is at least 1 row in the table. – ipip Jun 21 '12 at 15:25  @ipip - yes, and that's slightly faster on my data, which includes some reasonably big tables; but gives youtables that do have data, and the OP wants those that don't. –  Alex Poole Jun 21 '12 at 15:31  No, I'm talking rubbish; it's a lot faster if I run it on the DB that does actually have significant data, as you'dexpect. But it does list tables with data; combining both clauses seems to do the trick though. Thanks. –  Alex Poole Jun 21 '12 at 15:42  Taht's OK, I just wanted to point out the way, how to find an empty table. I often find myself doing the same:-)– ipip Jun 22 '12 at 9:19  @peter:That helped a lot Alex – user472625 Jun 26 '12 at 7:23 3 You can fire below query to find the list of tables haveing no data select * from ALL_TABLES where owner ='SBST'  AND NUM_ROWS = 0; shareimprove this answer  edited Aug 19 '15 at 12:26answered Aug 19 '15 at 12:23 2 the straight forward answer is select 'select ''' || table_name || ''' from ' || table_name || ' having count(*) = 0;' from dba_tables where owner='SBST';  11/10/2017oracle - Query to find all empty tables - Stack Overflowhttps://stackoverflow.com/questions/11134992/query-to-find-all-empty-tables3/5 EXPLANATION You can run this... it will just output the table names of those having no rows: assuming you usesqlplus but i used toad to test it and it worked very well set echo off heading off feedback off lines100 pages 0; spool tmp.sql select 'select ''' || table_name || ''' from ' || table_name || ' having count(*) = 0;' from user_tables where owner='SBST'; spool off; @tmp.sql If you open the tmp.sql file, you'll see for all tables.... select 'PERSONS' from PERSONS having count(*) = 0; select 'DEPARTMENT' from DEPARTMENT having count(*)=0; in your case you want a schema and schema is a user right the above code if you connect with theuser SBST  but if you connect with other then you have to use DBA_TABLES  andassign owner  attribute to SBSTUSER_TABLES  is tables which you own ALL_TABLES  is tables which own, and tables owner by other users, which you have been granted excplicit access to DBA_TABLES  is all tables inthe databaselike this set echo off heading off feedback off lines 100 pages 0; spool tmp.sql select 'select ''' || table_name || ''' from ' || table_name || ' having count(*) = 0;' from dba_tables where owner='SBST'; spool off; @tmp.sql  All three are views of the underlying SYS tables, but the USER_ and ALL_ views joing in your username/security info to limit the results  11/10/2017oracle - Query to find all empty tables - Stack Overflowhttps://stackoverflow.com/questions/11134992/query-to-find-all-empty-tables4/5 shareef  3,805 43560 **SUMMARY **PLEASE JUST RUN THIS QUERY select 'select ''' || table_name || ''' from ' || table_name || ' having count(*) = 0;' from dba_tables where owner='SBST'; shareimprove this answer  edited Jun 26 '12 at 7:24answered Jun 21 '12 at 9:20  @peter Is there any way to specify SBST directly in the query.I am executing in Toad.Datswhy – user472625Jun 21 '12 at 9:32  i added it above in the edit select 'select ''' || table_name || ''' from ' || table_name || 'having count(*) = 0;' from dba_tables where owner='SBST';  – shareef  Jun 21 '12 at 9:32  @peter:The above prints the tablename along with having count.This means i want to copy all the result in afile and execute this.Is there any query as such? – user472625 Jun 21 '12 at 10:12  @peter just run this <br> select 'select ''' || table_name || ''' from ' || table_name || 'having count(*) = 0;' from dba_tables where owner='SBST';  – shareef  Jun 21 '12 at 10:56 1 If table stats are up to date then you could use: SELECT TABLE_NAME FROM ALL_TAB_STATISTICS WHERE (OWNER = 'ME') AND (NUM_ROWS = 0);
We Need Your Support
Thank you for visiting our website and your interest in our free products and services. We are nonprofit website to share and download documents. To the running of this website, we need your help to support us.

Thanks to everyone for your continued support.

No, Thanks
SAVE OUR EARTH

We need your sign to support Project to invent "SMART AND CONTROLLABLE REFLECTIVE BALLOONS" to cover the Sun and Save Our Earth.

More details...

Sign Now!

We are very appreciated for your Prompt Action!

x