olace查主键表名称、统计条数

  select
x.*,s.constraint_name
from
(select segment_name, sum(s.bytes/1024/1024) as kj,T.NUM_ROWS,w.comments
from user_segments s,user_tables t, user_tab_comments w
where segment_type = 'TABLE'  
AND S.segment_name=T.TABLE_NAME and w.table_name=s.segment_name
group by segment_name,T.NUM_ROWS,w.comments)x left join user_constraints s
on x.segment_name=s.table_name and s.constraint_type='P'

 

统计条数

SELECT TABLE_NAME,num_rows  FROM SYS.ALL_TABLES T WHERE T.OWNER = 'SCOTT';

 

错误解决：

 访问接口 "OraOLEDB.Oracle" 返回了消息 "ORA-00936: 缺失表达式"。

原因：字段有xml类型，

CREATE VIEW V_JHOIS_TEMPLATE_INDEX_CHECK_全量     
AS 
select * from  openquery(ICU,'
select
TEMPLATE_ID    ,
CASE_SEQ,
CHECK_TYPE,
CHECK_NAME    ,
CHECK_NOTE,
xmlelement("CHECK_CONTENT").getstringval() CHECK_CONTENT    ,
CREATE_DATE    ,
CREATE_USER_ID    ,        
STATE
 from  ICUJHOIS_TEMPLATE_INDEX_CH_QL')
go

 

 

查询主键表

SELECT distinct f.TABLE_NAME FROM ALL_TABLES f,user_tables a WHERE OWNER='JHICU' and     not      exists (select *
                         from user_constraints b
                       where b.constraint_type = 'P' 
                          and a.table_name = b.table_name) and a.TABLE_NAME=f.TABLE_NAME  and f.TABLE_NAME not like 'MLOG$_%' and f.TABLE_NAME not like 'RUPD$__%'  
                         order by  f.TABLE_NAME