oracle查看所有链路语句,建立数据库链路、统计表记录数、查看会话及最大会话数-Oracle...

建立数据库链路、统计表记录数、查看会话及最大会话数

1. 生成统计表数据SQL

Sql代码

select ‘analyze table ‘ || table_name || ‘ compute statistics;’ from user_tables where table_name like ‘PR\_%\_%’  escape ‘\’ and tablespace_name = ‘PR_DATA1’

2. 统计企业和产品总数

Sql代码

select (select sum(NUM_ROWS)

from (select t.TABLE_NAME, t.NUM_ROWS

from user_tables t

where table_name like ‘PR_%_SHOP’)) as 商家,

(select sum(NUM_ROWS)

from (select t.TABLE_NAME, t.NUM_ROWS

from user_tables t

where table_name like ‘PR_%_PRODUCT’)) as 产品

from dual;

3. 统计表中的记录数

Sql代码

CREATE VIEW v_pr_sum as

SELECT t.table_name as 表名, SUM(t.num_rows) as 记录数

FROM  user_tables t

WHERE  (t.table_name LIKE ‘PR\_%\_%’ ESCAPE ‘\’ and t.table_name not like ‘%_IMAGE’)

GROUP BY  t.table_name

ORDER BY t.table_name

4. 查看数据库当前会话和最大会话

Sql代码

select to_number(value) from V$parameter where name=’processes’ union

select count(*) from V$process;

5. 建立数据库链路

Sql代码

create database link dl_temp connect to zq identified by zq using ‘(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.4.210)(PORT=1521)))(CONNECT_DATA=(SID=orcl)))’