I am entirely new to PL/SQL programming..
My requirement is to collect table list under a specific schema, its count(*) and num_rows along with last_analyzed date for n number of databases and populate it into a single table. I am trying to access remote databases via DB link. Managed to write below code with the help of internet sources. But not able to get the output. Please help on this..
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;
/
Output:
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>
Able to create procedure now.. But it is not populating data inside the table..
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
As you have to include table name and database link name into query, you'll need dynamic SQL.
Here's an example; I'm using user_... tables as I don't have access to dba_... ones so - you'll have to fix that. Also, I'm just displaying the output to the screen - you'll insert that into a table (fix it as well).
SQL> set serveroutput on
SQL> declare
2 rc sys_refcursor;
3 l_table_name user_tables.table_name%type;
4 l_cnt number;
5 begin
6 for src_link in (select db_link from user_db_links) loop
7
8 open rc for 'select table_name from user_tables@' || src_link.db_link;
9
10 loop
11 fetch rc into l_table_name;
12 exit when rc%notfound;
13
14 execute immediate 'select count(*) from ' || l_table_name ||'@'|| src_link.db_link
15 into l_cnt;
16
17 dbms_output.put_line(l_table_name||'@'|| src_link.db_link ||' -> ' || l_cnt || ' row(s)');
18 end loop;
19
20 close rc;
21 end loop;
22 end;
23 /
BONUS@DBL_MIKE -> 0 row(s)
DUMMY@DBL_MIKE -> 1 row(s)
SALGRADE@DBL_MIKE -> 5 row(s)
DEMO_TAGS@DBL_MIKE -> 6 row(s)
<snip>
PL/SQL procedure successfully completed.
SQL>
[EDIT]
Your procedure has some errors, for example
WHERE in RCSQL_STMT2V_LAST_ANALYZED variable (should be DATE, not NUMBER)dbms_output.put_line) and then, when you're sure that it looks OK, actually run it using execute immediate. Without it, you can't be really sure what you're trying to executeWhen fixed, it works:
Sample target table:
SQL> CREATE TABLE swms_tab_count_stats_rds
2 (
3 opco_name VARCHAR2 (30),
4 table_name VARCHAR2 (30),
5 actual_rowcount NUMBER,
6 stats_num_rows NUMBER,
7 stats_date DATE
8 );
Table created.
Procedure (I used all_tables and my own user, mike):
SQL> CREATE OR REPLACE PROCEDURE fetch_tables_stats
2 AS
3 BEGIN
4 DECLARE
5 rc SYS_REFCURSOR;
6 l_table_name all_tables.table_name%TYPE;
7 v_row_count NUMBER;
8 v_num_rows NUMBER;
9 v_last_analyzed DATE;
10 sql_stmt1 VARCHAR2 (1000);
11 sql_stmt2 VARCHAR2 (1000);
12 BEGIN
13 DELETE FROM swms_tab_count_stats_rds;
14
15 FOR src_link IN (SELECT db_link FROM all_db_links)
16 LOOP
17 OPEN rc FOR
18 'select table_name from all_tables@'
19 || src_link.db_link
20 || ' where owner = '
21 || q'['MIKE']';
22
23 LOOP
24 FETCH rc INTO l_table_name;
25
26 EXIT WHEN rc%NOTFOUND;
27 sql_stmt1 :=
28 'select count(*) from ' || l_table_name || '@' || src_link.db_link;
29
30 EXECUTE IMMEDIATE sql_stmt1
31 INTO v_row_count;
32
33 sql_stmt2 :=
34 'SELECT num_rows, last_analyzed FROM all_tables@'
35 || src_link.db_link
36 || ' WHERE table_name = '
37 || CHR (39)
38 || l_table_name
39 || CHR (39)
40 || ' AND owner = '
41 || q'['MIKE']';
42
43 EXECUTE IMMEDIATE sql_stmt2
44 INTO v_num_rows, v_last_analyzed;
45
46 INSERT INTO swms_tab_count_stats_rds (opco_name,
47 table_name,
48 actual_rowcount,
49 stats_num_rows,
50 stats_date)
51 VALUES (src_link.db_link,
52 l_table_name,
53 v_row_count,
54 v_num_rows,
55 v_last_analyzed);
56 END LOOP;
57
58 CLOSE rc;
59 END LOOP;
60
61 COMMIT;
62 END;
63 END fetch_tables_stats;
64 /
Procedure created.
Testing:
SQL> EXEC fetch_tables_stats;
PL/SQL procedure successfully completed.
SQL> SELECT *
2 FROM swms_tab_count_stats_rds
3 ORDER BY stats_date;
OPCO_NAME TABLE_NAME ACTUAL_ROWCOUNT STATS_NUM_ROWS STATS_DATE
---------- -------------------- --------------- -------------- -------------------
DBL_MIKE BONUS 0 0 17.05.2018 08:57:22
DBL_MIKE SALGRADE 5 5 17.05.2018 22:00:10
DBL_MIKE DUMMY 1 1 17.05.2018 22:00:10
DBL_MIKE DEMO_ORDERS 10 10 06.10.2018 22:01:03
DBL_MIKE APEX$TEAM_DEV_FILES 0 0 06.10.2018 22:01:03
<snip>