如何在oracle数据库中查询记录在100万条以上的表？SQL语句怎么写？

Question

就是用pl/SQL语句实现查询记录在100万以上的表，谢谢了

Answer 1

select table_name, num_rows from user_tables
where num_rows>=1000000;

Answer 2

music@SZTYORA> select table_name, num_rows from user_tables
  2  where num_rows>=1000000;TABLE_NAME                                                     NUM_ROWS
------------------------------------------------------------ ----------
MUSICRESOURCEDOWNLOAD_LOG                                       2227660
MUSICLOGUSER                                                   42828762
MUSICLOGUSER_DETAIL                                            42885121
MUSIC_DOWNLOADLOG                                              13205609

Answer 3

-- 如果查询整个数据库（所有用户)下超过1000000行记录的所有表的话，则用sys用户登录，查询dba_tables，
-- 操作类似如下：
music@SZTYORA> conn sys/wzs138322@sztyoralf7 as sysdba
已连接。
sys@SZTYORA> desc dba_tables;
 名称                                                                                      是否为空? 类型
 ----------------------------------------------------------------------------------------- -------- ------------------------------------------------------------
 OWNER                                                                                     NOT NULL VARCHAR2(30)
 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(40)
 INSTANCES                                                                                          VARCHAR2(40)
 CACHE                                                                                              VARCHAR2(20)
 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)
 DROPPED                                                                                            VARCHAR2(3)sys@SZTYORA> select owner, table_name, num_rows
  2  from dba_tables
  3  where num_rows>=1000000;OWNER                                                        TABLE_NAME                                             NUM_ROWS
------------------------------------------------------------ ------------------------------------------------------------ ----------
HLL                                                          BLACK_MOBILE                                           21576386
HLL                                                          DATA_LOGIN_LOG_BAK                                     19138109
LFTEST                                                       CQHKPAYDES2                                             1839799
HLL                                                          USER_SIGNLOG_1                                          7620580
HLL                                                          RESOURCE_MOBILE_TJ                                      1948608
SCOTT                                                        USERSTATUS_TMP0225F_BK                                  1825151
HLL                                                          CQHKPAYDOWN1                                            1815400
HLL                                                          CQHKPAYDES2                                             1919724
HLL                                                          MOBILEFRENDS                                           16033068
HLL                                                          USER_SIGNONLINE_TIME_TJ                                38188695
HLL                                                          USERPOINTSLOG                                           2245112
LFTEST                                                       ONLINEUSER_201006                                       1380327
HLL                                                          USER_SIGNLOG                                            7671621
HLL                                                          DATA_LOGIN_LOG                                          8691408已选择14行。

Answer 4

--方法1:用SQL生成一个脚本,然后把查询出的记录手复制作为脚本运行
select 'select '''||table_name||''' TALBE_NAME, count(*)  from '||t.TABLE_NAME||';' from user_tables t--方法2:生成统计方法(注意,可能影响你的执行计划,正式环境慎用)
exec dbms_stats.gather_schema_stats(user);--如果不执行,可能下面的不是特别精确
select t.TABLE_NAME,t.NUM_ROWS,t.LAST_ANALYZED from user_tables t;