我是PL/SQL编程的新手。
我的要求是收集一个特定模式下的表列表，它的count（*）和num_rows沿着，以及n个数据库的last_analyzed日期，并将其填充到一个表中。我正在尝试通过DB链接访问远程数据库。设法写下面的代码与互联网资源的帮助。但无法获得输出。请帮助在此…
CREATE OR REPLACE PROCEDURE fetch_tables_stats
AS
BEGIN
FOR src_link IN (SELECT db_link FROM dba_db_links WHERE USERNAME = '<user>')
LOOP
FOR tbl IN (SELECT table_name FROM dba_tables@src_link.db_link WHERE owner = '<owner>')
LOOP
DECLARE
v_row_count NUMBER;
v_num_rows NUMBER;
sql_stmt1 VARCHAR2(1000);
sql_stmt2 VARCHAR2(1000);
BEGIN
sql_stmt1 := 'SELECT COUNT(*) FROM ' || tbl || '.' || table_name || '@' ||src_link || '.' || db_link;
EXECUTE IMMEDIATE sql_stmt1 INTO v_row_count;
sql_stmt2 := 'SELECT num_rows FROM dba_tab_statistics@' || src_link || '.' || db_link || ' WHERE table_name = ' || tbl ||'.' || table_name || ' AND owner = ' || q'['<owner>']';
EXECUTE IMMEDIATE sql_stmt2 INTO v_num_rows;
INSERT INTO SWMS_TAB_COUNT_STATS_RDS (OPCO_NAME,TABLE_NAME,ACTUAL_ROWCOUNT,STATS_NUM_ROWS,STATS_DATE)
VALUES (src_link.db_link,tbl.table_name, v_row_count, v_num_rows,sysdate);
COMMIT;
END;
END LOOP;
END LOOP;
END fetch_tables_stats;
/输出量：
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/17 PL/SQL: ORA-00942: table or view does not exist
6/17 PL/SQL: SQL Statement ignored
14/6 PL/SQL: Statement ignored
14/46 PLS-00364: loop index variable 'TBL' use is invalid
16/3 PL/SQL: Statement ignored
16/16 PLS-00306: wrong number or types of arguments in call to '||'
18/6 PL/SQL: SQL Statement ignored
19/34 PLS-00364: loop index variable 'TBL' use is invalid
19/38 PL/SQL: ORA-00984: column not allowed here
SQL>现在可以创建程序。但它并没有在表中填充数据。
SQL> create or replace procedure fetch_tables_stats
as
begin
declare
rc sys_refcursor;
l_table_name dba_tables.table_name%type;
v_row_count number;
v_num_rows NUMBER;
v_last_analyzed NUMBER;
sql_stmt1 VARCHAR2(1000);
sql_stmt2 VARCHAR2(1000);
sql_stmt3 VARCHAR2(1000);
begin
for src_link in (select db_link from user_db_links)
loop
open rc for 'select table_name from dba_tables@' || src_link.db_link || 'where owner = ' || q'['SWMS']';
loop
fetch rc into l_table_name;
exit when rc%notfound;
sql_stmt1 := 'select count(*) from ' || l_table_name ||'@'|| src_link.db_link;
execute immediate sql_stmt1 into v_row_count;
sql_stmt2 := 'SELECT num_rows,last_analyzed FROM dba_tables@' || src_link.db_link || ' WHERE table_name = ' || l_table_name || ' AND owner = ' || q'['SWMS']';
execute immediate sql_stmt2 into v_num_rows, v_last_analyzed;
sql_stmt3 := 'INSERT INTO SWMS_TAB_COUNT_STATS_RDS (OPCO_NAME,TABLE_NAME,ACTUAL_ROWCOUNT,STATS_NUM_ROWS,STATS_DATE)
VALUES (' || src_link.db_link || ',' || l_table_name || ', v_row_count, v_num_rows,v_last_analyzed)';
execute immediate sql_stmt3;
commit;
end loop;
close rc;
end loop;
end;
end fetch_tables_stats;
/ 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
Procedure created.
SQL> exec fetch_tables_stats;
PL/SQL procedure successfully completed.
SQL> select count(*) from SWMS_TAB_COUNT_STATS_RDS;
COUNT(*)
----------
0
1条答案
按热度按时间zy1mlcev1#
因为你必须在查询中包含表名和数据库链接名，所以你需要动态SQL。
这里有一个例子我使用
user_...表，因为我没有访问dba_...表的权限，所以-你必须解决这个问题。另外，我只是将输出显示到屏幕上-您将把它插入到一个表中（也要修复它）。[编辑]**
您的过程有一些错误，例如
RC中WHERE前面缺少空间SQL_STMT2中缺少用于将表名括起来的单引号V_LAST_ANALYZED变量的数据类型错误（应为DATE，而不是NUMBER）dbms_output.put_line），然后，当你确定它看起来正常时，使用execute immediate实际运行它。没有它，你就不能真正确定你要执行的是什么当修复时，它 * 工作 *：
示例目标表：
步骤（我使用
all_tables和我自己的用户mike）：测试：