看一下这2个视图:select * from dba_tables;select * from user_tables;
要找出所有表中存在数据的,可以有这个思路:--收集统计信息 begin dbms_stats.gather_schema_stats(user); end;--查找出有数据的表 select * from user_tables where NUM_ROWS > 0;
但要注意,这样有可能影响到你的生产系统的performance情况 ,慎重对待
为了避免不必要的负作用,写一段plsql脚本吧 SQL> SET SERVEROUTPUT ON; SQL> SQL> DECLARE 2 v_tablename VARCHAR2(30); 3 v_cnt PLS_INTEGER; 4 BEGIN 5 FOR c IN (SELECT t.TABLE_NAME FROM user_tables t) LOOP 6 EXECUTE IMMEDIATE 'select count(*) from ' || c.table_name INTO v_cnt; 7 IF v_cnt > 0 THEN 8 dbms_output.put_line(c.table_name); 9 END IF; 10 END LOOP; 11 END; 12 /
DEPT TEST EMP1 EMPLOYEENAME STUDENT LL1 OPERLOG LL PL/SQL 过程已成功完成。SQL>
CREATE OR REPLACE FUNCTION func_tab_rowcount(p_tabname IN VARCHAR2) RETURN NUMBER AS v_count NUMBER; BEGIN EXECUTE IMMEDIATE 'select count(1) from "'||p_tabname||'" where rownum<2' INTO v_count; RETURN v_count; exception when others then RETURN -1; END; /select * from user_tables where func_tab_rowcount(table_name)>0;
要找出所有表中存在数据的,可以有这个思路:--收集统计信息 begin dbms_stats.gather_schema_stats(user); end;--查找出有数据的表 select * from user_tables where NUM_ROWS > 0; 学习了,的确好用。
begin
dbms_stats.gather_schema_stats(user);
end;--查找出有数据的表
select * from user_tables where NUM_ROWS > 0;
SQL> SET SERVEROUTPUT ON;
SQL>
SQL> DECLARE
2 v_tablename VARCHAR2(30);
3 v_cnt PLS_INTEGER;
4 BEGIN
5 FOR c IN (SELECT t.TABLE_NAME FROM user_tables t) LOOP
6 EXECUTE IMMEDIATE 'select count(*) from ' || c.table_name INTO v_cnt;
7 IF v_cnt > 0 THEN
8 dbms_output.put_line(c.table_name);
9 END IF;
10 END LOOP;
11 END;
12 /
DEPT
TEST
EMP1
EMPLOYEENAME
STUDENT
LL1
OPERLOG
LL
PL/SQL 过程已成功完成。SQL>
RETURN NUMBER
AS
v_count NUMBER;
BEGIN
EXECUTE IMMEDIATE 'select count(1) from "'||p_tabname||'" where rownum<2' INTO v_count;
RETURN v_count;
exception
when others then
RETURN -1;
END;
/select * from user_tables where func_tab_rowcount(table_name)>0;
begin
dbms_stats.gather_schema_stats(user);
end;--查找出有数据的表
select * from user_tables where NUM_ROWS > 0;
学习了,的确好用。