Home » RDBMS Server » Server Utilities » Export Statistics of schema from the database (11g, 11.2.0.1, window7)
Export Statistics of schema from the database [message #632680] Wed, 04 February 2015 04:50 Go to next message
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 #632683 is a reply to message #632680] Wed, 04 February 2015 06:24 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3149
Registered: May 2013
Location: World Wide on the Web
Senior Member
Why not use dbms_stats package to export and import the statistics. You could first create a stats table, move it to the required destination and then import it to the same schema.
Re: Export Statistics of schema from the database [message #632684 is a reply to message #632683] Wed, 04 February 2015 06:47 Go to previous messageGo to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Yes !! I got some info at site... I am looking into it.
Re: Export Statistics of schema from the database [message #632814 is a reply to message #632684] Fri, 06 February 2015 00:26 Go to previous messageGo to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Dear Lalit,

My doubts of getting stats from one database to other is clear now.
Is it possible to check from views that import of stats are happened properly or not? Or stattab=>'MY_STAT_TABLE' is the only table to check?

Since, output shows procedure is executed successfully.

[Updated on: Fri, 06 February 2015 00:33]

Report message to a moderator

Re: Export Statistics of schema from the database [message #632818 is a reply to message #632814] Fri, 06 February 2015 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 66554
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If Data Pump tells that he has exported/imported the statistics then it has done it.

Re: Export Statistics of schema from the database [message #632821 is a reply to message #632814] Fri, 06 February 2015 01:39 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3149
Registered: May 2013
Location: World Wide on the Web
Senior Member
ashishkumarmahanta80 wrote on Fri, 06 February 2015 11:56
output shows procedure is executed successfully.


As Michel said, if there are no errors then it has succeeded in it's task.
Re: Export Statistics of schema from the database [message #632825 is a reply to message #632821] Fri, 06 February 2015 02:45 Go to previous messageGo to next message
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 #632827 is a reply to message #632825] Fri, 06 February 2015 02:47 Go to previous messageGo to next message
Michel Cadot
Messages: 66554
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Have a look at DBA%STATISTICS views.
But if you don't trust Oracle package I advise you to use another RDBMS or write your own. Smile

[Updated on: Fri, 06 February 2015 02:48]

Report message to a moderator

Re: Export Statistics of schema from the database [message #632834 is a reply to message #632827] Fri, 06 February 2015 03:04 Go to previous messageGo to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Dear Michel,

It's not about trust of Oracle packages !!!
For my knowledge, I have requested you all to convey me the view name through which can able to see stats are imported.

If there is no view then okay.

Thanks,

Regards,
Ashish
Re: Export Statistics of schema from the database [message #632839 is a reply to message #632834] Fri, 06 February 2015 03:26 Go to previous messageGo to next message
Michel Cadot
Messages: 66554
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
If there is no view then okay.


So you didn't read my post.

Re: Export Statistics of schema from the database [message #633100 is a reply to message #632839] Tue, 10 February 2015 23:04 Go to previous messageGo to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Thanks Michel,

Please let us know one more thing. If, I will do export of stats at production database during peak time, do it impact performance of the database else, will schedule it at night time.

Regards,
Ashish Kumar Mahanta

[Updated on: Tue, 10 February 2015 23:31]

Report message to a moderator

Re: Export Statistics of schema from the database [message #633101 is a reply to message #633100] Tue, 10 February 2015 23:34 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3149
Registered: May 2013
Location: World Wide on the Web
Senior Member
ashishkumarmahanta80 wrote on Wed, 11 February 2015 10:34
I 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 #633169 is a reply to message #633101] Wed, 11 February 2015 22:43 Go to previous messageGo to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Thank you.
Re: Export Statistics of schema from the database [message #633177 is a reply to message #633169] Thu, 12 February 2015 01:34 Go to previous messageGo to next message
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 #633180 is a reply to message #633177] Thu, 12 February 2015 01:39 Go to previous messageGo to next message
Michel Cadot
Messages: 66554
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Maybe.
Did you try what the message suggest?

Re: Export Statistics of schema from the database [message #633182 is a reply to message #633180] Thu, 12 February 2015 01:50 Go to previous messageGo to next message
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) 


Re: Export Statistics of schema from the database [message #633183 is a reply to message #633182] Thu, 12 February 2015 01:55 Go to previous messageGo to next message
Michel Cadot
Messages: 66554
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So you have your answer now...

Re: Export Statistics of schema from the database [message #633198 is a reply to message #633183] Thu, 12 February 2015 02:52 Go to previous messageGo to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Yes !!!

It' related to version mismatch only...
Re: Export Statistics of schema from the database [message #633204 is a reply to message #633198] Thu, 12 February 2015 04:07 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3149
Registered: May 2013
Location: World Wide on the Web
Senior Member
Thanks for letting us know.
Re: Export Statistics of schema from the database [message #633409 is a reply to message #633204] Tue, 17 February 2015 23:56 Go to previous messageGo to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Dear All,

Is there any parameter defined in oracle. Though which we can able to import stats of 11.2.0.3 version to 11.2.0.1 (without doing any database upgrade)??

If you know about it then revert. Else, earlier mentioned option is available.

Regards,
Ashish
Re: Export Statistics of schema from the database [message #633411 is a reply to message #633409] Wed, 18 February 2015 02:02 Go to previous messageGo to next message
Michel Cadot
Messages: 66554
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The note gives you a workaround, is there any reason you don't want to apply it?
Note if MOS does not mention an other way then it is most likely it does not exist.

Re: Export Statistics of schema from the database [message #633419 is a reply to message #633411] Wed, 18 February 2015 03:12 Go to previous message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Thank you
Previous Topic: ORA-31694: master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" failed to load/unload
Next Topic: datapump option for DB upgrade from solaris to Linux
Goto Forum:
  


Current Time: Wed Sep 18 12:38:19 CDT 2019