Home » SQL & PL/SQL » SQL & PL/SQL » Different values of count(*) and num_rows of table (11g, 11.2.0.1, windows server 2008)
Different values of count(*) and num_rows of table [message #651842] Thu, 26 May 2016 05:43 Go to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Dear All,

Metadata of production database is imported in newly created database and DATA is imported of local database. Import is done successfully but difference is appearing on "select count(*)" & "select num_rows " of table.

Below are the scripts -


SQL> select count(*) from ins.CNFGTR_RISK_GRID_DETAIL_TAB;

  COUNT(*)
----------
         0

SQL> exec dbms_stats.gather_database_stats;

PL/SQL procedure successfully completed.


SQL>  select count(*) from ins.CNFGTR_RISK_GRID_DETAIL_TAB;

  COUNT(*)
----------
         0

SQL> SELECT owner, table_name,num_rows FROM DBA_TABLES
  2  where table_name='CNFGTR_RISK_GRID_DETAIL_TAB'
  3  ;

OWNER                          TABLE_NAME                       NUM_ROWS
------------------------------ ------------------------------ ----------
INS                            CNFGTR_RISK_GRID_DETAIL_TAB         74955


Kindly suggest me whether it is correct or I am doing something wrong to check the count of records of tables.

Regards,
Ashish Kumar Mahanta
Re: Different values of count(*) and num_rows of table [message #651843 is a reply to message #651842] Thu, 26 May 2016 06:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

NUM_ROWS is filled when statistics are gathered and does not reflect the actual number just an estimation of it at statistics gathering time.

[Updated on: Thu, 26 May 2016 06:01]

Report message to a moderator

Re: Different values of count(*) and num_rows of table [message #651844 is a reply to message #651842] Thu, 26 May 2016 06:00 Go to previous messageGo to next message
John Watson
Messages: 8930
Registered: January 2010
Location: Global Village
Senior Member
Check the import log. It looks as though no rows were imported.
Re: Different values of count(*) and num_rows of table [message #651845 is a reply to message #651842] Thu, 26 May 2016 06:05 Go to previous messageGo to next message
Frank Naude
Messages: 4579
Registered: April 1998
Senior Member
Check if the table's stats are locked with SELECT stattype_locked FROM dba_tab_statistics WHERE...
Re: Different values of count(*) and num_rows of table [message #651846 is a reply to message #651845] Thu, 26 May 2016 06:10 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Fun fact. Gather database/schema stats will suppress any individual object level errors. Check that too Smile
Re: Different values of count(*) and num_rows of table [message #651848 is a reply to message #651846] Thu, 26 May 2016 06:39 Go to previous messageGo to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Dear Frank,

I was checking the error, and getting stats are locked. Below the errors-

SQL> exec dbms_stats.gather_table_stats('INS', 'CNFGTR_RISK_GRID_DETAIL_TAB');
BEGIN dbms_stats.gather_table_stats('INS', 'CNFGTR_RISK_GRID_DETAIL_TAB'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 20337
ORA-06512: at "SYS.DBMS_STATS", line 20360
ORA-06512: at line 1


SQL> select STATTYPE_LOCKED from dba_tab_statistics where TABLE_NAME='CNFGTR_RISK_GRID_DETAIL_TAB';

STATT
-----
ALL


Please let me know what should I do now to make the stats of database unlock and get correct information. As of now many tables shows wrong data.
While doing unlock for table is giving correct data.

How to do, if i need to import metadata and then data_only form local database? What is proper steps to get correct stats?

[Updated on: Thu, 26 May 2016 06:54]

Report message to a moderator

Re: Different values of count(*) and num_rows of table [message #651849 is a reply to message #651848] Thu, 26 May 2016 06:49 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
The first thing you do when encountering locked stats should actually be finding out why they are locked.
Re: Different values of count(*) and num_rows of table [message #651852 is a reply to message #651849] Thu, 26 May 2016 07:22 Go to previous messageGo to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Dear Roachcoach,

My motive is also the same. I need to analyse it but unable to get the right path of it. Steps are alreday mentioned. Request you to guide me.

Regards,
Ashish
Re: Different values of count(*) and num_rows of table [message #651853 is a reply to message #651848] Thu, 26 May 2016 07:25 Go to previous messageGo to next message
John Watson
Messages: 8930
Registered: January 2010
Location: Global Village
Senior Member
Quote:
make the stats of database unlock
dbms_stats.unlock_table_stats
Quote:
What is proper steps to get correct stats
dbms_Stats.gather_table_stats
Re: Different values of count(*) and num_rows of table [message #651855 is a reply to message #651853] Thu, 26 May 2016 08:20 Go to previous messageGo to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Dear John,

Request you to assist me in finding of locks happened in tables. What could be the reason for it? Since, I had gather stats for database and then done for table is caused locks on tables? Or something else.
Here, I am unable to get the clue.
Re: Different values of count(*) and num_rows of table [message #651856 is a reply to message #651855] Thu, 26 May 2016 08:27 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
The same reason I said instead of removing it, find out why it was done.

It is a manual action taken explicitly by someone for a (hopefully) damned good reason. Consider it a big sign saying "do not touch, ever". If you want to mess with it, the smart thing to do is first find out why it was locked.

[Updated on: Thu, 26 May 2016 08:27]

Report message to a moderator

Re: Different values of count(*) and num_rows of table [message #651859 is a reply to message #651855] Thu, 26 May 2016 08:32 Go to previous messageGo to next message
John Watson
Messages: 8930
Registered: January 2010
Location: Global Village
Senior Member
ashishkumarmahanta80 wrote on Thu, 26 May 2016 14:20
Dear John,

Request you to assist me in finding of locks happened in tables. What could be the reason for it? Since, I had gather stats for database and then done for table is caused locks on tables? Or something else.
Here, I am unable to get the clue.
Have you read the docs describing any of the dbms_Stats procedures mentioned in this topic so far? Or the description of the view Frank pointed you towards?
Re: Different values of count(*) and num_rows of table [message #651861 is a reply to message #651859] Thu, 26 May 2016 08:41 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Here, I am unable to get the clue.

Some folks are clue resistant.
Others are clue repellent.

You can lead some folks to knowledge, but you can't make them think.

We have no control over what has been done or will be done to your database.
Previous Topic: join table for top 5 and bottom 5
Next Topic: Dynamic SQL Select query (merged)
Goto Forum:
  


Current Time: Tue Apr 23 05:19:07 CDT 2024