Oracle Database Tips

  • 全局搜索内容
declare
    match_count integer;
    v_search_string varchar2(128) := 'string_to_search';
begin
    for t in (select owner,
                     table_name,
                     column_name
                from all_tab_columns
               where data_type in ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2')
                 and owner in (select username from user_users)) loop
        begin
            execute immediate 'select count(*) from ' || t.owner || '.' ||
                              t.table_name || ' where ' || t.column_name ||
                              ' like ''%'' || :1 || ''%'''
                into match_count
                using v_search_string;
         
            if match_count > 0 then
                dbms_output.put_line(t.owner || '.' || t.table_name || ' <> ' || match_count);
            end if;
        exception
            when others then
                dbms_output.put_line('*** Error encountered trying to read <> from ' || t.owner || '.' || t.table_name);
        end;
    end loop;
end;
/
  • 查询表空间占用
select segment_name,sum(bytes) bytes 
from DBA_EXTENTS where owner='XLDMT'
group by segment_name order by bytes desc
  • 允许导出空表

select ‘alter table ‘||table_name||’ allocate extent;’ from user_tables where num_rows = 0 or num_rows is null;