| Export Statistics of schema from the database [message #632680] |
Wed, 04 February 2015 04:50  |
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
|
Senior Member |
|
|
Dear All,
I want to export only statistics of schema's from the database through datapump ( expdp).
Please append my export command.
system/******** dumpfile=expdp_statistics_38_73.dmp logfile=expdp_statistics_38_73.log schemas=INS include=statistics directory=dp_dir
Here, export is happening for all including statistics for schemas INS. I neither want metadata nor data.
Please assist me in writing command.
Regards,
Ashish Kumar Mahanta
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Export Statistics of schema from the database [message #632825 is a reply to message #632821] |
Fri, 06 February 2015 02:45  |
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
|
Senior Member |
|
|
Dear Michel,
Data pump shows stats table imported to the desired location, which I also agree.
After that, we executed dbms_stats.import_schema_stats to import statistics to the schema.
Now, I want to check whether, stats got change or not? Is there any view through which I can check the same?
Regards,
Ashish
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Export Statistics of schema from the database [message #633101 is a reply to message #633100] |
Tue, 10 February 2015 23:34 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
ashishkumarmahanta80 wrote on Wed, 11 February 2015 10:34I will do export of stats at production database, do it impact performance of the database?
If you could do it at an idle time, then it would be better. Thus, not impacting the other activities, as the export would consume CPU and add to the I/O.
|
|
|
|
|
|
| Re: Export Statistics of schema from the database [message #633177 is a reply to message #633169] |
Thu, 12 February 2015 01:34 |
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
|
Senior Member |
|
|
Dear All,
While importing dbms_stats, getting error as below -
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> begin
2 dbms_stats.import_schema_stats( ownname=>'INS'
3 , stattab=>'INS_TABLE'
4 , statid=>'CURRENT_STATS'
5 );
6 end;
7 /
begin
*
ERROR at line 1:
ORA-20002: Unknown error when using statistics table INS.INS_TABLE. Please
drop and recreate with dbms_stats.drop_stat_table and
dbms_stats.create_stat_table
ORA-06512: at "SYS.DBMS_STATS", line 9468
ORA-06512: at "SYS.DBMS_STATS", line 9484
ORA-06512: at "SYS.DBMS_STATS", line 10600
ORA-06512: at line 2
Whether, this error occurs because of version mismatch?
Because production database is of 11.2.0.3 and importing on 11.2.0.1 database.
When doing export/import on same version, i am not getting any error.
Please suggest....
Regards,
Ashish Kumar Mahanta
|
|
|
|
|
|
| Re: Export Statistics of schema from the database [message #633182 is a reply to message #633180] |
Thu, 12 February 2015 01:50 |
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
|
Senior Member |
|
|
Dear Michel,
We don't create dbms_stat at auxiliary database (where, import is to be done). Since, while import dump to the auxiliary database, table itself create in it and then we execute dbms_stats.import_schema_stats.
But when i do between the same version of 11g, i am able to import it.
I got some idea from metalink -
ORA-20002 on Importing Statistics using DBMS_STATS Procedures (Doc ID 740345.1)
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
