ALL_TABLES describes the relational tables accessible to the current user. To gather statistics for this view, use the ANALYZE SQL statement.Related ViewsDBA_TABLES describes all relational tables in the database.USER_TABLES describes the relational tables owned by the current user. This view does not display the OWNER column.Note:Columns ed with an asterisk (*) are populated only if you collect statistics on the table with the ANALYZE statement or the DBMS_STATS package. Column Datatype NULL Description OWNER VARCHAR2(30) NOT NULL Owner of the table TABLE_NAME VARCHAR2(30) NOT NULL Name of the table TABLESPACE_NAME VARCHAR2(30) Name of the tablespace containing the table; null for partitioned, temporary, and index-organized tables CLUSTER_NAME VARCHAR2(30) Name of the cluster, if any, to which the table belongs IOT_NAME VARCHAR2(30) Name of the index-organized table, if any, to which the overflow or mapping table entry belongs. If the IOT_TYPE column is not null, then this column contains the base table name. STATUS VARCHAR2(8) If a previous DROP TABLE operation failed, indicates whether the table is unusable (UNUSABLE) or valid (VALID) PCT_FREE NUMBER Minimum percentage of free space in a block; null for partitioned tables PCT_USED NUMBER Minimum percentage of used space in a block; null for partitioned tables INI_TRANS NUMBER Initial number of transactions; null for partitioned tables MAX_TRANS NUMBER Maximum number of transactions; null for partitioned tables INITIAL_EXTENT NUMBER Size of the initial extent (in bytes); null for partitioned tables NEXT_EXTENT NUMBER Size of secondary extents (in bytes); null for partitioned tables MIN_EXTENTS NUMBER Minimum number of extents allowed in the segment; null for partitioned tables MAX_EXTENTS NUMBER Maximum number of extents allowed in the segment; null for partitioned tables PCT_INCREASE NUMBER Percentage increase in extent size; null for partitioned tables FREELISTS NUMBER Number of process freelists allocated to the segment; null for partitioned tables FREELIST_GROUPS NUMBER Number of freelist groups allocated to the segment; null for partitioned tables LOGGING VARCHAR2(3) Logging attribute; NULL for partitioned tables BACKED_UP VARCHAR2(1) Has table been backed up since last change NUM_ROWS* NUMBER Number of rows in the table
all_tables 中 所有的num_rows 的值都为空!
declare cnt number; begin for rs in (select distinct table_name from user_tables ) loop execute immediate 'select count(*) from '||rs.table_name into cnt; if cnt=19955 then Dbms_Output.put_line('table name is :'||rs.table_name); end if; end loop; end;
--如果是你本用户建的表就user_tables 不是则all_tables declare num number; begin for i in(select table_name from user_tables) loop execute immediate 'select count(*) from '||i.table_name into num; if num=19955 then dbms_output.put_line('表记录为19955的表是 '||i.table_name); end if; end loop; end;
FROM all_tables
WHERE num_rows = 19955
Column Datatype NULL Description
OWNER VARCHAR2(30) NOT NULL Owner of the table
TABLE_NAME VARCHAR2(30) NOT NULL Name of the table
TABLESPACE_NAME VARCHAR2(30) Name of the tablespace containing the table; null for partitioned, temporary, and index-organized tables
CLUSTER_NAME VARCHAR2(30) Name of the cluster, if any, to which the table belongs
IOT_NAME VARCHAR2(30) Name of the index-organized table, if any, to which the overflow or mapping table entry belongs. If the IOT_TYPE column is not null, then this column contains the base table name.
STATUS VARCHAR2(8) If a previous DROP TABLE operation failed, indicates whether the table is unusable (UNUSABLE) or valid (VALID)
PCT_FREE NUMBER Minimum percentage of free space in a block; null for partitioned tables
PCT_USED NUMBER Minimum percentage of used space in a block; null for partitioned tables
INI_TRANS NUMBER Initial number of transactions; null for partitioned tables
MAX_TRANS NUMBER Maximum number of transactions; null for partitioned tables
INITIAL_EXTENT NUMBER Size of the initial extent (in bytes); null for partitioned tables
NEXT_EXTENT NUMBER Size of secondary extents (in bytes); null for partitioned tables
MIN_EXTENTS NUMBER Minimum number of extents allowed in the segment; null for partitioned tables
MAX_EXTENTS NUMBER Maximum number of extents allowed in the segment; null for partitioned tables
PCT_INCREASE NUMBER Percentage increase in extent size; null for partitioned tables
FREELISTS NUMBER Number of process freelists allocated to the segment; null for partitioned tables
FREELIST_GROUPS NUMBER Number of freelist groups allocated to the segment; null for partitioned tables
LOGGING VARCHAR2(3) Logging attribute; NULL for partitioned tables
BACKED_UP VARCHAR2(1) Has table been backed up since last change
NUM_ROWS* NUMBER Number of rows in the table
all_tables 中 所有的num_rows 的值都为空!
cnt number;
begin
for rs in (select distinct table_name from user_tables ) loop
execute immediate 'select count(*) from '||rs.table_name into cnt;
if cnt=19955 then
Dbms_Output.put_line('table name is :'||rs.table_name);
end if;
end loop;
end;
declare
num number;
begin
for i in(select table_name from user_tables)
loop
execute immediate 'select count(*) from '||i.table_name into num;
if num=19955 then
dbms_output.put_line('表记录为19955的表是 '||i.table_name);
end if;
end loop;
end;