建立 数据库链路、统计表记录数、查看会话及最大会话数
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)))'
|
