Home » RDBMS Server » Performance Tuning » export statistics form production to test database (oracle 10g, solaris 9 , redhat linux AS4)
export statistics form production to test database [message #277347] Mon, 29 October 2007 12:43 Go to next message
skumar.dba
Messages: 82
Registered: October 2007
Member
Hi All,

I Have one question. how can i export statistics from production to test database.

to wich i have to connect to take the statistics using this dbms_stats.get_system_stat.

[Updated on: Mon, 29 October 2007 12:49]

Report message to a moderator

Re: export statistics form production to test database [message #277350 is a reply to message #277347] Mon, 29 October 2007 12:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use dbms_stats.export_system_stats function.

Regards
Michel
Re: export statistics form production to test database [message #277355 is a reply to message #277350] Mon, 29 October 2007 14:32 Go to previous messageGo to next message
skumar.dba
Messages: 82
Registered: October 2007
Member
Hi ,

i was created user called "psuer" and i tried to execute this procedure to export statistics. i am getting this error. what could be the reason.

SQL> exec dbms_stats.export_system_stats('STAT_TAB');
BEGIN dbms_stats.export_system_stats('STAT_TAB'); END;

*
ERROR at line 1:
ORA-04067: not executed, package body "PUSER.DBMS_STATS" does not exist
ORA-06508: PL/SQL: could not find program unit being called: "PUSER.DBMS_STATS"
ORA-06512: at line 1
Re: export statistics form production to test database [message #277356 is a reply to message #277355] Mon, 29 October 2007 14:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

what could be the reason.

Wrong installation. Check dbms_stats is owned by SYS and there is no other dbms_stats and public synonym points to correct package and there is no private synonym...

Regards
Michel


Re: export statistics form production to test database [message #277358 is a reply to message #277356] Mon, 29 October 2007 14:51 Go to previous messageGo to next message
skumar.dba
Messages: 82
Registered: October 2007
Member
Michel,

for this i created one user called "puser" and i assigned sysdba role to this user. after i was created table called "stat_tab".

so do i need to connect as a sysdba and execute same procedure?

what is the step i have take?
Re: export statistics form production to test database [message #277359 is a reply to message #277358] Mon, 29 October 2007 14:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First answer all the previous questions.

Regards
Michel
Re: export statistics form production to test database [message #277361 is a reply to message #277359] Mon, 29 October 2007 15:13 Go to previous messageGo to next message
skumar.dba
Messages: 82
Registered: October 2007
Member
Michel,

my intentin is to export system statistics form production to test database. for this i approched like this.

1) i crated one user called puser and assigned sysdba role.
2) i ran the script called dbmsstat.sql in $oracle_home/rdbms/admin
3) and i connected as puser and i was created one table called stat_tab.
4)finally i was tried to export system statistics.

what was the wrong i did in this case.

Re: export statistics form production to test database [message #277363 is a reply to message #277361] Mon, 29 October 2007 15:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You did it wrong.
dbms_stats must be owned by SYS.

Regards
Michel
Re: export statistics form production to test database [message #277365 is a reply to message #277363] Mon, 29 October 2007 15:40 Go to previous messageGo to next message
skumar.dba
Messages: 82
Registered: October 2007
Member
Michel,

Thanks for your reply. i took the statisitcs. all are in a table called "stat_tab". do i need to export this table using exp from production and import into test database?
Re: export statistics form production to test database [message #277366 is a reply to message #277365] Mon, 29 October 2007 15:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes.

Regards
Michel
Re: export statistics form production to test database [message #277367 is a reply to message #277366] Mon, 29 October 2007 15:48 Go to previous messageGo to next message
skumar.dba
Messages: 82
Registered: October 2007
Member
Michel,

Thank you very much Michel.
Re: export statistics form production to test database [message #277410 is a reply to message #277367] Tue, 30 October 2007 00:08 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I forgot to mention after importing the table you have to import the statistics using dbms_stats.import_system_stats.

Regards
Michel
Previous Topic: Fragmented Tables
Next Topic: statspack report
Goto Forum:
  


Current Time: Thu Dec 08 02:37:28 CST 2016

Total time taken to generate the page: 0.09484 seconds