OraFAQ Forum: Performance Tuning » export statistics form production to test database

Home » RDBMS Server » Performance Tuning » export statistics form production to test database (oracle 10g, solaris 9 , redhat linux AS4)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

export statistics form production to test database [message #277347]

Mon, 29 October 2007 12:43

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

Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Use dbms_stats.export_system_stats function.

Regards
Michel

Report message to a moderator

Re: export statistics form production to test database [message #277355 is a reply to message #277350]

Mon, 29 October 2007 14:32

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

Report message to a moderator

Re: export statistics form production to test database [message #277356 is a reply to message #277355]

Mon, 29 October 2007 14:45

Michel Cadot
Messages: 68664
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

Report message to a moderator

Re: export statistics form production to test database [message #277358 is a reply to message #277356]

Mon, 29 October 2007 14:51

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?

Report message to a moderator

Re: export statistics form production to test database [message #277359 is a reply to message #277358]

Mon, 29 October 2007 14:55

Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

First answer all the previous questions.

Regards
Michel

Report message to a moderator

Re: export statistics form production to test database [message #277361 is a reply to message #277359]

Mon, 29 October 2007 15:13

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.

Report message to a moderator

Re: export statistics form production to test database [message #277363 is a reply to message #277361]

Mon, 29 October 2007 15:26

Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You did it wrong.
dbms_stats must be owned by SYS.

Regards
Michel

Report message to a moderator

Re: export statistics form production to test database [message #277365 is a reply to message #277363]

Mon, 29 October 2007 15:40

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?

Report message to a moderator

Re: export statistics form production to test database [message #277366 is a reply to message #277365]

Mon, 29 October 2007 15:42

Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Yes.

Regards
Michel

Report message to a moderator

Re: export statistics form production to test database [message #277367 is a reply to message #277366]

Mon, 29 October 2007 15:48

skumar.dba
Messages: 82
Registered: October 2007

Member

Michel,

Thank you very much Michel.

Report message to a moderator

Re: export statistics form production to test database [message #277410 is a reply to message #277367]

Tue, 30 October 2007 00:08

Michel Cadot
Messages: 68664
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

Report message to a moderator

Previous Topic:	Fragmented Tables
Next Topic:	statspack report

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jun 25 02:21:17 CDT 2024