By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE test (id VARCHAR2(20) PRIMARY KEY);
INSERT INTO test(id) VALUES ('1');
1 rows affected
DECLARE
v_owner VARCHAR2(40);
v_table_name VARCHAR2(40);
v_column_name VARCHAR2(40);
v_count_rows NUMBER;
v_count_real_rows NUMBER;
v_count_rows_diff NUMBER;
v_rn_tables NUMBER;
v_count_tables NUMBER;
v_max_primary_key NUMBER;
sql_stmt VARCHAR2(32767);
CURSOR get_tables IS
SELECT
cons.owner,
cols.table_name,
cols.column_name,
nvl(num_rows, - 1) AS count_rows,
ROW_NUMBER()
OVER(PARTITION BY cons.owner
ORDER BY cols.table_name
) AS rn_tables,
COUNT(DISTINCT cols.table_name)
OVER(PARTITION BY cons.owner
-- ORDER BY cols.table_name
) AS count_tables
FROM
all_constraints cons,
all_cons_columns cols,
all_tables tab
WHERE
cols.table_name = tab.table_name
AND cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
AND tab.table_name NOT LIKE '%_MV'
AND cols.position = 1
dbms_output:
1 out of 3 CTXSYS.DR$OBJECT_ATTRIBUTE: 502: 502:
2 out of 3 CTXSYS.DR$THS: 0: 0:
3 out of 3 CTXSYS.DR$THS_PHRASE: 0: 0:
1 out of 1 FIDDLE_FCNXRMFTFFTGEYVPHZHQ.TEST: 1: -1:
1 out of 35 MDSYS.NTV2_XML_DATA: 0: 0:
2 out of 35 MDSYS.OGIS_SPATIAL_REFERENCE_SYSTEMS: 0: 0:
ORA-01722: invalid number
ORA-06512: at line 60
DECLARE
v_owner VARCHAR2(40);
v_table_name VARCHAR2(40);
v_column_name VARCHAR2(40);
v_count_rows NUMBER;
v_count_real_rows NUMBER;
v_count_rows_diff NUMBER;
v_rn_tables NUMBER;
v_count_tables NUMBER;
v_max_primary_key NUMBER;
sql_stmt VARCHAR2(32767);
CURSOR get_tables IS
SELECT
cons.owner,
cols.table_name,
cols.column_name,
nvl(num_rows, - 1) AS count_rows,
ROW_NUMBER()
OVER(PARTITION BY cons.owner
ORDER BY cols.table_name
) AS rn_tables,
COUNT(DISTINCT cols.table_name)
OVER(PARTITION BY cons.owner
-- ORDER BY cols.table_name
) AS count_tables
FROM all_constraints cons
INNER JOIN all_cons_columns cols
ON ( cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner)
INNER JOIN all_tables tab
ON ( cols.table_name = tab.table_name
AND cols.owner = tab.owner)
WHERE cons.constraint_type = 'P'
AND tab.table_name NOT LIKE '%_MV'
AND cols.position = 1
AND tab.owner = USER
1 rows affected
dbms_output:
1 out of 1 FIDDLE_FCNXRMFTFFTGEYVPHZHQ.TEST: 1: -1:
INSERT INTO test(id) VALUES ('A');
1 rows affected
DECLARE
v_owner VARCHAR2(40);
v_table_name VARCHAR2(40);
v_column_name VARCHAR2(40);
v_count_rows NUMBER;
v_count_real_rows NUMBER;
v_count_rows_diff NUMBER;
v_rn_tables NUMBER;
v_count_tables NUMBER;
v_max_primary_key NUMBER;
sql_stmt VARCHAR2(32767);
CURSOR get_tables IS
SELECT
cons.owner,
cols.table_name,
cols.column_name,
nvl(num_rows, - 1) AS count_rows,
ROW_NUMBER()
OVER(PARTITION BY cons.owner
ORDER BY cols.table_name
) AS rn_tables,
COUNT(DISTINCT cols.table_name)
OVER(PARTITION BY cons.owner
-- ORDER BY cols.table_name
) AS count_tables
FROM all_constraints cons
INNER JOIN all_cons_columns cols
ON ( cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner)
INNER JOIN all_tables tab
ON ( cols.table_name = tab.table_name
AND cols.owner = tab.owner)
WHERE cons.constraint_type = 'P'
AND tab.table_name NOT LIKE '%_MV'
AND cols.position = 1
AND tab.owner = USER
ORA-01722: invalid number
ORA-06512: at line 60