2 select * from dba_tab_columns where column_name like '%PATRONAGE%';
1. 这个不能通过sql实现,除非先分析所有表。可以写个过程,用plsql实现 2. select distinct owner,table_name from dba_tab_cols where column_name='PATRONAGE';
1 select distinct table_name from dba_tab_statistics where num_rows = 0
1:select distinct TABLE_NAME from user_tables t where TABLE_NAME is not null 你这个不为空的表名要求万分奇怪哈....... 2:SELECT USER_TAB_COLS.TABLE_NAME as 表名, USER_TAB_COLS.COLUMN_NAME as 列名 , USER_TAB_COLS.DATA_TYPE as 数据类型, USER_TAB_COLS.DATA_LENGTH as 长度, USER_TAB_COLS.NULLABLE as 是否为空, USER_TAB_COLS.COLUMN_ID as 列序号, user_col_comments.comments as 备注 , USER_TAB_COLS.* FROM USER_TAB_COLS inner join user_col_comments on user_col_comments.TABLE_NAME=USER_TAB_COLS.TABLE_NAME and user_col_comments.COLUMN_NAME=USER_TAB_COLS.COLUMN_NAME where USER_TAB_COLS.column_name='PATRONAGE'
1.--写个过程来判断,将非空的表名和所有者名称存到临时表中 declare v_count number; begin select count(1) into v_count from user_tables where table_name = upper('temp_notEmpty_tables') and rownum < 2; if v_count = 0 then execute immediate 'create global temporary table temp_notEmpty_tables(owner varchar2(30),table_name varchar2(30)) on commit preserve rows'; else execute immediate 'truncate table temp_notEmpty_tables'; end if; for cur in (select * from all_tables) loop begin execute immediate 'select count(1) from ' || cur.owner || '."' || cur.table_name || '" where rownum<2' into v_count; if v_count > 0 then execute immediate 'insert into temp_notEmpty_tables values(:1,:2)' using cur.owner, cur.table_name; end if; exception when others then dbms_output.put_line(cur.owner||'.'||cur.table_name); end; end loop; commit; end;--查询结果 select * from temp_notEmpty_tables;
select *
from dba_tab_columns
where column_name like '%PATRONAGE%';
这个不能通过sql实现,除非先分析所有表。可以写个过程,用plsql实现
2.
select distinct owner,table_name from dba_tab_cols where column_name='PATRONAGE';
select distinct table_name from dba_tab_statistics where num_rows = 0
你这个不为空的表名要求万分奇怪哈.......
2:SELECT USER_TAB_COLS.TABLE_NAME as 表名,
USER_TAB_COLS.COLUMN_NAME as 列名 ,
USER_TAB_COLS.DATA_TYPE as 数据类型,
USER_TAB_COLS.DATA_LENGTH as 长度,
USER_TAB_COLS.NULLABLE as 是否为空,
USER_TAB_COLS.COLUMN_ID as 列序号,
user_col_comments.comments as 备注 ,
USER_TAB_COLS.*
FROM USER_TAB_COLS
inner join user_col_comments on
user_col_comments.TABLE_NAME=USER_TAB_COLS.TABLE_NAME
and user_col_comments.COLUMN_NAME=USER_TAB_COLS.COLUMN_NAME
where USER_TAB_COLS.column_name='PATRONAGE'
declare
v_count number;
begin
select count(1)
into v_count
from user_tables
where table_name = upper('temp_notEmpty_tables')
and rownum < 2;
if v_count = 0 then
execute immediate 'create global temporary table temp_notEmpty_tables(owner varchar2(30),table_name varchar2(30)) on commit preserve rows';
else
execute immediate 'truncate table temp_notEmpty_tables';
end if;
for cur in (select * from all_tables) loop
begin
execute immediate 'select count(1) from ' || cur.owner || '."' ||
cur.table_name || '" where rownum<2'
into v_count;
if v_count > 0 then
execute immediate 'insert into temp_notEmpty_tables values(:1,:2)'
using cur.owner, cur.table_name;
end if;
exception
when others then
dbms_output.put_line(cur.owner||'.'||cur.table_name);
end;
end loop;
commit;
end;--查询结果
select * from temp_notEmpty_tables;
反正我知道没有数据表 num_rows = 0 / select count 也为0也不是没有用处的,我们在公网的服务器经常被黑,人家就是找的表不为空,而且字段定义为字符串的然后加上一串脚本. 不过我的用处是我帮我朋友恢复数据库, 里面有很多表.我只想把有数据的表导出来,没有数据的我就不导了.
而且他这些数据里面最重要的就是'PATRONAGE'. 不过自己ORACLE 不熟悉.
1、begin
dbms_utility.analyze_schema('TEST','COMPUTE');--'TEST'是你要找的表所在的schema。
end;
2、执行
SELECT table_name,tablespace_name , blocks, empty_blocks, num_rows
FROM user_tables where blocks>0
结果中的表就是所有不为空的表。
没有数据的表导出是很快的,在导出文件中也不占用多大的空间
况且要指定很多表导出,命令行直接指定是不可能的
那你要写一个导出参数文件parfile,将要导出的表列在上面
其他那些没有数据的不是多余的操作了,在说了表很多
啊, 1 2百个呢. 我就是想减少点工作量