如何在ORACLE中查询某一用户下所有的空表！？

Question

如题目。谢谢。。

Answer 1

简单的动态sql循环即可。没难度。

Answer 2

declare 
  s varchar2(30);
  name2 varchar2(30);
  cursor cur_emp is select table_name from user_tables;
begin 
   for  i in cur_emp loop
        name2 := 'select count(*) from '||i.table_name;
  
        execute immediate name2 into s;
        dbms_output.put_line(i.table_name||':'||s);
  
   end loop;
  
  
end;

Answer 3

SQL> desc user_tables;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ------------------------
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 TABLESPACE_NAME                                    VARCHAR2(30)
 CLUSTER_NAME                                       VARCHAR2(30)
 IOT_NAME                                           VARCHAR2(30)
 STATUS                                             VARCHAR2(8)
 PCT_FREE                                           NUMBER
 PCT_USED                                           NUMBER
 INI_TRANS                                          NUMBER
 MAX_TRANS                                          NUMBER
 INITIAL_EXTENT                                     NUMBER
 NEXT_EXTENT                                        NUMBER
 MIN_EXTENTS                                        NUMBER
 MAX_EXTENTS                                        NUMBER
 PCT_INCREASE                                       NUMBER
 FREELISTS                                          NUMBER
 FREELIST_GROUPS                                    NUMBER
 LOGGING                                            VARCHAR2(3)
 BACKED_UP                                          VARCHAR2(1)
 NUM_ROWS                                           NUMBER
 BLOCKS                                             NUMBER
 EMPTY_BLOCKS                                       NUMBER
 AVG_SPACE                                          NUMBER
 CHAIN_CNT                                          NUMBER
 AVG_ROW_LEN                                        NUMBER
 AVG_SPACE_FREELIST_BLOCKS                          NUMBER
 NUM_FREELIST_BLOCKS                                NUMBER
 DEGREE                                             VARCHAR2(10)
 INSTANCES                                          VARCHAR2(10)
 CACHE                                              VARCHAR2(5)
 TABLE_LOCK                                         VARCHAR2(8)
 SAMPLE_SIZE                                        NUMBER
 LAST_ANALYZED                                      DATE
 PARTITIONED                                        VARCHAR2(3)
 IOT_TYPE                                           VARCHAR2(12)
 TEMPORARY                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 NESTED                                             VARCHAR2(3)
 BUFFER_POOL                                        VARCHAR2(7)
 ROW_MOVEMENT                                       VARCHAR2(8)
 GLOBAL_STATS                                       VARCHAR2(3)
 USER_STATS                                         VARCHAR2(3)
 DURATION                                           VARCHAR2(15)
 SKIP_CORRUPT                                       VARCHAR2(8)
 MONITORING                                         VARCHAR2(3)
 CLUSTER_OWNER                                      VARCHAR2(30)
 DEPENDENCIES                                       VARCHAR2(8)
 COMPRESSION                                        VARCHAR2(8)
 COMPRESS_FOR                                       VARCHAR2(18)
 DROPPED                                            VARCHAR2(3)
 READ_ONLY                                          VARCHAR2(3)SQL> dbms_stats.gather_schema_stats(user);
SP2-0734: 未知的命令开头 "dbms_stats..." - 忽略了剩余的行。
SQL> exec dbms_stats.gather_schema_stats(user);PL/SQL 过程已成功完成。SQL> select table_name from user_tables where NUM_ROWS=0;TABLE_NAME
------------------------------
BONUS
TCSQL> select table_name,NUM_ROWS from user_tables where NUM_ROWS=0;TABLE_NAME                       NUM_ROWS
------------------------------ ----------
BONUS                                   0
TC                                      0

Answer 4

先做一个分析,把用户下所有的表分析一边,然后就可以求取NUM_ROWS 为0的表名就可以了,其实很简单的问题