oracle常用命令

原文链接:https://www.cnblogs.com/lcword/p/11775630.html

1、查询数据库中所有表的表的记录数      select t.table_name,t.num_rows from user_tables t

     或者先创建存储过程，后续再进行查询 create or replace function count_rows(table_name in varchar2,owner in varchar2 default null)

  return number authid current_user IS
  num_rows number;
  stmt     varchar2(2000);
begin
  if owner is null then
    stmt := 'select count(*) from "' || table_name || '"';
  else
    stmt := 'select count(*) from "' || owner || '"."' || table_name || '"';
  end if;
  execute immediate stmt
    into num_rows;
  return num_rows;
end;
select table_name, count_rows(table_name) nrows from user_tables；

2、查询数据库中以“T_”开头且不包含大字段的表
select distinct(table_name) from user_tab_columns where table_name like 'T\_%' escape '\' AND data_type not in ('CLOB','BLOB')

3、LOB字段转换
  新数据库通过创建dblink直接读取其他相同网段的视图数据。

create database link zxbzkfcs
connect to usr_zxbz identified by "wisedu#123"
using
'(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.7.200)(PORT = 1521)))
(CONNECT_DATA =
(SERVICE_NAME = zhfwdb))
)';

当视图中涉及lob字段时，读取时，异常信息提示：ORA-22992：cannot use lob locators selected from remote tables

可通过如何函数进行住转换--dbms_lob.substr(clobcolumn,4000)--4000建议调整为1-2000