大于10M表无统计信息的收集

declare
v_sql varchar2(2000) :='';
v_count number;
begin
for r in (
select tab.owner, tab.TABLE_NAME ,partitioned
from (SELECT seg.owner, seg.segment_name, sum(bytes / 1024 / 1024) mb
FROM dba_segments seg
where seg.owner in ('COMMON','TBCS')
and seg.segment_type in ('TABLE', 'TABLE PARTITION')
group by owner, segment_name
having sum(seg.bytes / 1024 / 1024) > 10) seg,
dba_tables tab
where tab.TABLE_NAME = seg.segment_name
and tab.OWNER = seg.owner
and tab.segment_created<>'NO'
and tab.NUM_ROWS = 0
and tab.owner in ('COMMON','TBCS'))
loop
v_sql:='select /*+parallel (t 8)*/count(*) from '||r.owner||'.'||r.TABLE_NAME||' t';
dbms_output.put_line(v_sql);
execute immediate v_sql into v_count;
if v_count>0 then
dbms_output.put_line('exec dbms_stats.GATHER_TABLE_STATS(OWNNAME=>'||''''||r.owner||''''||',TABNAME=>'||''''||r.table_name||''''||'，cascade=>TRUE,estimate_percent => dbms_stats.auto_sample_size,method_opt=>''FOR TABLE FOR ALL COLUMNS SIZE REPEAT'', degree => 8,no_invalidate=>false);');
end if;
end loop;
end;
/

 

PL/SQL中两个单引号会输出一个单引号

begin
for r in (select tablespace_name from dba_data_files where online_status='OFFLINE')
loop
begin dbms_output.put_line('select dbms_metadata.get_ddl(''TABLESPACE'','||''''||r.tablespace_name||''''||') from dual;');
end;
end loop;
end;
/