[code=SQL]SQL> select blocks from all_tables where table_name='DEPT'; BLOCKS ---------- 5SQL> show user USER 为 "SCOTT" SQL> drop table t2;表已删除。SQL> create table t2(id number);表已创建。SQL> begin 2 dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T2'); 3 end; 4 /PL/SQL 过程已成功完成。SQL> select blocks from all_tables where table_name='T2'; BLOCKS ---------- 0SQL> insert into t2 values(1);已创建 1 行。SQL> commit;提交完成。SQL> select blocks from all_tables where table_name='T2'; BLOCKS ---------- 0SQL> begin 2 dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T2'); 3 end; 4 /PL/SQL 过程已成功完成。SQL> select blocks from all_tables where table_name='T2'; BLOCKS ---------- 5code]我看了看文档中ALL_TABLES中有个BLOCKS字段比较合适 个人看法哦 查询BLOCKS字段前要分析表才会有数据
。。没显示好 重新来一次 SQL> select blocks from all_tables where table_name='DEPT'; BLOCKS ---------- 5 SQL> show user USER 为 "SCOTT" SQL> drop table t2; 表已删除。 SQL> create table t2(id number); 表已创建。 SQL> begin 2 dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T2'); 3 end; 4 / PL/SQL 过程已成功完成。 SQL> select blocks from all_tables where table_name='T2'; BLOCKS ---------- 0 SQL> insert into t2 values(1); 已创建 1 行。 SQL> commit; 提交完成。 SQL> select blocks from all_tables where table_name='T2'; BLOCKS ---------- 0 SQL> begin 2 dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T2'); 3 end; 4 / PL/SQL 过程已成功完成。 SQL> select blocks from all_tables where table_name='T2'; BLOCKS ---------- 5
写个过程 declare v_count number; begin for cur in (select owner,table_name from all_tables where tablespace_name not in ('SYSTEM','SYSAUX','EXAMPLE')) loop execute immediate 'select count(1) from "'||cur.owner||'"."'||cur.table_name||'" where rownum=1' into v_count; if v_count=0 then dbms_output.put_line('table '||cur.table_name||' is empty'); end if; end loop; end;
[TEST@ora10gr1#2009-11-24/21:32:21] SQL>set serveroutput on [TEST@ora10gr1#2009-11-24/21:32:21] SQL>create or replace procedure find_no_data_table_name 2 is 3 cursor cur1 is 4 select table_name from all_tables where owner = user; 5 sql_str varchar(1000); 6 sql_cnt integer := 0; 7 begin 8 for rec in cur1 loop 9 sql_str := 'select count(*) from '||user||'.'||rec.table_name; 10 execute immediate sql_str into sql_cnt; 11 if( sql_cnt = 0 ) then 12 dbms_output.put_line(rec.table_name); 13 end if; 14 end loop; 15 end; 16 /Procedure created.[TEST@ora10gr1#2009-11-24/21:32:21] SQL>exec find_no_data_table_name; BSYEAR TEST TEST2 TEST3 BL_LOG TEST02PL/SQL procedure successfully completed.
----------
5SQL> show user
USER 为 "SCOTT"
SQL> drop table t2;表已删除。SQL> create table t2(id number);表已创建。SQL> begin
2 dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T2');
3 end;
4 /PL/SQL 过程已成功完成。SQL> select blocks from all_tables where table_name='T2'; BLOCKS
----------
0SQL> insert into t2 values(1);已创建 1 行。SQL> commit;提交完成。SQL> select blocks from all_tables where table_name='T2'; BLOCKS
----------
0SQL> begin
2 dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T2');
3 end;
4 /PL/SQL 过程已成功完成。SQL> select blocks from all_tables where table_name='T2'; BLOCKS
----------
5code]我看了看文档中ALL_TABLES中有个BLOCKS字段比较合适
个人看法哦
查询BLOCKS字段前要分析表才会有数据
重新来一次
SQL> select blocks from all_tables where table_name='DEPT'; BLOCKS
----------
5 SQL> show user
USER 为 "SCOTT"
SQL> drop table t2; 表已删除。 SQL> create table t2(id number); 表已创建。 SQL> begin
2 dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T2');
3 end;
4 / PL/SQL 过程已成功完成。 SQL> select blocks from all_tables where table_name='T2'; BLOCKS
----------
0 SQL> insert into t2 values(1); 已创建 1 行。 SQL> commit; 提交完成。 SQL> select blocks from all_tables where table_name='T2'; BLOCKS
----------
0 SQL> begin
2 dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T2');
3 end;
4 / PL/SQL 过程已成功完成。 SQL> select blocks from all_tables where table_name='T2'; BLOCKS
----------
5
用上面这个语句就可以判断哪个表是空的了 如果BLOCKS为0的话接下来查询哪些表无数据就看你自己了哦 呵呵
所有表的相关信息可以在DBA_TABLES、ALL_TABLES、USER_TABLES三个视图中查到
其实内容都差不多 范围大小而已不会的再问就行了
declare
v_count number;
begin
for cur in (select owner,table_name from all_tables where tablespace_name not in ('SYSTEM','SYSAUX','EXAMPLE'))
loop
execute immediate 'select count(1) from "'||cur.owner||'"."'||cur.table_name||'" where rownum=1' into v_count;
if v_count=0 then
dbms_output.put_line('table '||cur.table_name||' is empty');
end if;
end loop;
end;
[TEST@ora10gr1#2009-11-24/21:32:21] SQL>create or replace procedure find_no_data_table_name
2 is
3 cursor cur1 is
4 select table_name from all_tables where owner = user;
5 sql_str varchar(1000);
6 sql_cnt integer := 0;
7 begin
8 for rec in cur1 loop
9 sql_str := 'select count(*) from '||user||'.'||rec.table_name;
10 execute immediate sql_str into sql_cnt;
11 if( sql_cnt = 0 ) then
12 dbms_output.put_line(rec.table_name);
13 end if;
14 end loop;
15 end;
16 /Procedure created.[TEST@ora10gr1#2009-11-24/21:32:21] SQL>exec find_no_data_table_name;
BSYEAR
TEST
TEST2
TEST3
BL_LOG
TEST02PL/SQL procedure successfully completed.
我用的是BenChiM888同学的
多给两分哈