CODEWITHSUNDEEP
oracle-databaseplsql
Data2explore
Data2explore

Reputation: 452

Query to check the full schema scan for tables in Oracle DB

Hi I have a requirement to scan through the schema and identify the tables which are redundant (candidate for dropping) ,so i did a select in DBA_Dependencies to check whether the tables are being used in any of the DB object types like (Procedure, package body, views, Materialized views....) i was able to find some tables and excluded the tables ,since i also need to capture the total counts, when the table was last loaded/used is there a automated way to select only selected tables (not found in dependencies list) and capture the counts and also when it was used/loaded

Difficulty - so many tables 500+

i have used the below query

Query 1

select table_name, 
       to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from '||owner||'.'||table_name)),'/ROWSET/ROW/C')) as count
from all_tables
where owner = 'SCHEMA_NAME'

Query 2

select owner, table_name, num_rows, sample_size, last_analyzed from all_tables;

Query 1 Result

Filter Table_name=CUST_ORDER

OWNER TABLE_NAME COUNT SAMPLE_SIZE LAST_ANALYZED
ABCD  CUST_ORDER  1083     1023       01.01.2020

Query 2 Result

Filter Table_name=CUST_ORDER

OWNER TABLE_NAME NUM_ROWS SAMPLE_SIZE LAST_ANALYZED
ABCD  CUST_ORDER  1023     1023       01.01.2020

Question

Query 1 - Results not matching when compared with query 2 ,since the same table and filter is applied in both the queries and why the results are not matching ?

but when i randomly checked other filter it is matching , does any one know the reason ?

Upon further testing i encountered an error ,what does this error signify permissions ?

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file **-**.csv in ****_***_***_***** not found
29913. 00000 -  "error in executing %s callout"
*Cause:    The execution of the specified callout caused an error.
*Action:   Examine the error messages take appropriate action.

Upvotes: 0

Views: 1303

Answers (2)

Kevin Seymour
Kevin Seymour

Reputation: 807

The number you see on all_tables is a point in time capture of the number of rows. It will only be updated if the statistics are rebuilt for that table.

Here is an example:

CREATE TABLE t1 AS
SELECT *
  FROM all_objects;

SELECT t.num_rows
  FROM all_tables t
 WHERE t.table_name = 'T1';
-- 78570

SELECT COUNT(*)
  FROM t1;
-- 78570

The stats and the physical number of rows match!

INSERT INTO t1
  SELECT *
    FROM all_objects ao
   WHERE rownum <= 5;
-- 5 rows inserted

SELECT t.num_rows
  FROM all_tables t
 WHERE t.table_name = 'T1';
-- 78570

SELECT COUNT(*)
  FROM t1;
-- 78575

Here we have the mis-match because rows were inserted (or maybe even deleted), but the stats for the table have not been updated. Let's update them:

BEGIN
  dbms_stats.gather_table_stats(ownname => 'SCHEMA',
                                tabname => 'T1');
END;
/

SELECT t.num_rows
  FROM all_tables t
 WHERE t.table_name = 'T1';
-- 78575

Now you can see the rows match. Using the value from all_tables may be good enough for your research (and will certainly be faster to query than counting every table).

Upvotes: 1

Popeye
Popeye

Reputation: 35920

Query - 1 is actual data of the table and hence it is accurate data. One can rely on this query's output.

Query - 2 is not actual data. It is the data captured when table was last analyzed and one should not be dependant on this query for finding number of records in the table.

You can gather the stats on this table and execute the query-2 then you will find the same data as query-1

If records are not inserted or deleted from the table after stats are gathered, then query-1 and query-2 data will match for that table.

Upvotes: 0

Related Questions