请教一个问题： 如何统计某个用户下所建表的数量

Question

如题：如何统计某个用户下所建表的数量？比如， 创建表的用户名是 testGao， 系统中此用户下创建的表名称是testGao.tablename，如何统计testGao用户创建表的数量？有没有sql语句能实现什么的？

Answer 1

select count(*) from dba_tables where owner = 'TESTGAO'
or
select count(*) from all_tables where owner = 'TESTGAO'
or
使用用户TESTGAO登录，然后select count(*) from user_tables

Answer 2

正解
select count(*) from dba_tables where owner = 'TESTGAO'
这个要以dba身份登录

Answer 3

select * from user_objects where object_type = 'TABLE'

Answer 4

SELECT * FROM user_objects t WHERE t.object_type = 'TABLE';

Answer 5

SELECT * FROM user_objects t WHERE t.object_type = 'TABLE';
晕，楼上抢我生意

Answer 6

sys@SZTYORA> select owner,
  2  count(*) as all_tables,
  3  sum(decode(temporary,'Y',1,0)) as all_temporary_tables,
  4  sum(decode(temporary,'N',1,0)) as all_notTemporary_tables
  5  from dba_tables
  6  group by owner;OWNER                                                        ALL_TABLES ALL_TEMPORARY_TABLES ALL_NOTTEMPORARY_TABLES
------------------------------------------------------------ ---------- -------------------- -----------------------
MDSYS                                                                49                   12              37
EYGLE                                                                 4                    0               4
JULIA                                                                 1                    0               1
TSMSYS                                                                1                    0               1
DMSYS                                                                 2                    0               2
OUTLN                                                                 3                    0               3
CTXSYS                                                               37                    0              37
OLAPSYS                                                             126                   12             114
SYSTEM                                                              141                    5             136
SCOTT                                                                35                    1              34
EXFSYS                                                               44                    2              42
LYM                                                                   4                    0               4
BOOKMGR                                                               8                    0               8
DBSNMP                                                               21                    3              18
ORDSYS                                                                4                    0               4
SYSMAN                                                              337                    0             337
XDB                                                                  11                    0              11
SZTYLYM                                                               1                    0               1
SYS                                                                 707                   42             665
WMSYS                                                                40                    1              39已选择20行。

Answer 7

sys@SZTYORA> conn sys/wzs138322@sztyoralf8 as sysdba
已连接。
sys@SZTYORA> select owner,
  2  count(*) as all_tables,
  3  sum(decode(temporary,'Y',1,0)) as "所有临时表数量",
  4  sum(decode(temporary,'N',1,0)) as "所有非临时表数量"
  5  from dba_tables
  6  group by owner;OWNER                                                        ALL_TABLES 所有临时表数量 所有非临时表数量
------------------------------------------------------------ ---------- -------------- ----------------
MDSYS                                                                54             13               41
MUSICWAP                                                             63              1               62
TSMSYS                                                                1              0                1
DMSYS                                                                 2              0                2
MUSIC                                                                75              1               74
HLL                                                                 257             25              232
OUTLN                                                                 3              0                3
CTXSYS                                                               37              0               37
OLAPSYS                                                             126             12              114
SYSTEM                                                              141              5              136
HLL_QUERY                                                           131              8              123
EXFSYS                                                               44              2               42
SCOTT                                                                 6              0                6
CUSTOMERSERVICE                                                      39              5               34
MUSIC1                                                               30              1               29
DBSNMP                                                               21              3               18
ORDSYS                                                                4              0                4
SYSMAN                                                              341              0              341
XDB                                                                  11              0               11
SYS                                                                 710             43              667
MOBILECARD                                                           40              1               39
WMSYS                                                                42              1               41已选择22行。

Answer 8

select * from dba_tables t where t.owner='';