Different values of count(*) and num_rows of table [message #651842] |
Thu, 26 May 2016 05:43 |
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 #651848 is a reply to message #651846] |
Thu, 26 May 2016 06:39 |
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 #651859 is a reply to message #651855] |
Thu, 26 May 2016 08:32 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
ashishkumarmahanta80 wrote on Thu, 26 May 2016 14:20Dear 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?
|
|
|
|