OraFAQ Forum: General » Import schema Statistics in different oracle database

Home » Other » General » Import schema Statistics in different oracle database (oracle 11.2.0.4 ,11.2.0.1RHEL6.0,windows server)

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

Import schema Statistics in different oracle database [message #663194]

Thu, 25 May 2017 02:15

arin.oradba
Messages: 33
Registered: January 2016
Location: Kolkata

Member

We need to import schema Statistics from oracle 11.2.0.4 version to 11.2.0.1 version.
The following problem did not occur in same version of 11g.

We did the export here with mentioning the version parameter in expdp command. But the problem is still persist.

Create stats of table:

begin
dbms_STATS.CREATE_STAT_TABLE(ownname =>'XYZ'  ,stattab => 'XYZ_STATS_TABLE');
end;
/

Export the tables stats:

begin
dbms_STATS.export_schema_STATS(ownname =>'XYZ'  ,stattab => 'XYZ_STATS_TABLE' , statid=>'CURRENT_STATS');
end;
/

Export the tables into dump file:

expdp USERID/PASSWORD dumpfile=File1.dmp logfile=File1.log directory=DP_DIR tables=XYZ.XYZ_STATS_TABLE version=11.2.0.1.0

IMPORT SCHEMA STATS PROCESSES IN DIFFERENT DATABASE:

Import the stats

impdp USERID/PASSWORD directory=dp_dir dumpfile=File1.dmp logfile=impdp_File1.log  tables=XYZ.XYZ_STATS_TABLE

Stats to import into respective schemas

begin
dbms_STATS.import_schema_STATS(ownname =>'XYZ'  ,stattab => 'XYZ_STATS_TABLE' , statid=>'CURRENT_STATS');
end;
/


*
ERROR at line 1:
ORA-20002: Unknown error when using statistics table XYZ.XYZ_STATS_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

We did not create dbms_stat at destination database (11.2.0.1). During import,table itself create in it.Then we had executed dbms_stats.import_schema_stats command.

I have no metalink userid and password. So kindly help me.

Report message to a moderator

Re: Import schema Statistics in different oracle database [message #663197 is a reply to message #663194]

Thu, 25 May 2017 02:57

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You must first create the statistics table with the package then import with option data only.

Report message to a moderator

Re: Import schema Statistics in different oracle database [message #663198 is a reply to message #663197]

Thu, 25 May 2017 03:24

arin.oradba
Messages: 33
Registered: January 2016
Location: Kolkata

Member

Did you asked to create the statistics table with package like following at destination ?

begin
dbms_STATS.CREATE_STAT_TABLE(ownname =>'XYZ'  ,stattab => 'XYZ_STATS_TABLE');
end;
/

Then import with data only?
So it will be completed?

Please share your comments once again please.

Report message to a moderator

Re: Import schema Statistics in different oracle database [message #663199 is a reply to message #663198]

Thu, 25 May 2017 03:28

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Did you asked...

Yes.

Quote:

So it will be completed?

As the source is at a higher version than the destination, it can't be sure, you have to test it.

Report message to a moderator

Re: Import schema Statistics in different oracle database [message #663202 is a reply to message #663199]

Thu, 25 May 2017 03:47

arin.oradba
Messages: 33
Registered: January 2016
Location: Kolkata

Member

Thank you sir.I will be back with the testing result.Also I had taken export backup with version=11.2.0.1. So hope your tips will be work.

Report message to a moderator

Re: Import schema Statistics in different oracle database [message #663203 is a reply to message #663202]

Thu, 25 May 2017 03:49

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The version on export guarantee that the export file can be read with this version but it does not say anything about the version of the table and its content.
Even with export version=11.2.0.1 you will export a table and statistics that are of version 11.2.0.4.

[Updated on: Thu, 25 May 2017 03:50]

Report message to a moderator

Re: Import schema Statistics in different oracle database [message #663252 is a reply to message #663203]

Sat, 27 May 2017 02:04

arin.oradba
Messages: 33
Registered: January 2016
Location: Kolkata

Member

Good afternoon sir. I did as your advice and successfully completed the following steps. Now how can I get information about the updated statistics.Please help once again.

Thanks a lots sir.


begin
dbms_STATS.CREATE_STAT_TABLE(ownname =>'XYZ'  ,stattab => 'XYZ_STATS_TABLE');
end;
/

impdp directory=DP_DIR dumpfile=DB_SCHEMA_STATS-27052017.dmp logfile=impdp_DB_SCHEMA_STATS-27052017.log tables=XYZ.XYZ_STATS_TABLE CONTENT=DATA_ONLY

output :

..............................
..............................

. . imported "XYZ"."XYZ_STATS_TABLE"                     15.25 KB       4 rows
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at 12:39:07

[Updated on: Sat, 27 May 2017 02:24]

Report message to a moderator

Re: Import schema Statistics in different oracle database [message #663255 is a reply to message #663252]

Sat, 27 May 2017 02:33

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You have the information in this table.
If you want to actually import them into the target table(s), you have to use dbms_stats.import_%_stats as you originally exported them, then you will have the statistics in the DBA/ALL standard views.
Don't forget to then lock the statistics on these tables to prevent Oracle from regathering them.

Report message to a moderator

Re: Import schema Statistics in different oracle database [message #663257 is a reply to message #663255]

Sat, 27 May 2017 02:57

arin.oradba
Messages: 33
Registered: January 2016
Location: Kolkata

Member

Very sorry sir.But not understand properly.

1. dbms_STATS.import_schema_STATS is not working as follow.Then what to do?
2. Is Oracle regather stats from them automatically?
3. What will happen if we execute gather stats for all schema?
4.How can I use the information in this table to major sql query cost?

SQL> begin
2 dbms_STATS.import_schema_STATS(ownname =>'XYZ' ,stattab => 'XYZ_STATS_TABLE' , statid=>'CURRENT_STATS');
3 end;
4 /
begin
*
ERROR at line 1:
ORA-20002: Unknown error when using statistics table ACC.ACC_STATS_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

SQL>

Report message to a moderator

Re: Import schema Statistics in different oracle database [message #663259 is a reply to message #663257]

Sat, 27 May 2017 03:06

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I don't understand what you did.
Did you follow what I said?
Restart from the beginning and COPY AND PASTE ALL what you do and get. EVERYTHING.

Report message to a moderator

Re: Import schema Statistics in different oracle database [message #663263 is a reply to message #663259]

Sat, 27 May 2017 04:04

arin.oradba
Messages: 33
Registered: January 2016
Location: Kolkata

Member

I did the following.

In Source database:

Create stats of table:

begin
dbms_STATS.CREATE_STAT_TABLE(ownname =>'XYZ'  ,stattab => 'XYZ_STATS_TABLE');
end;
/

Export the tables stats: 
begin
dbms_STATS.export_schema_STATS(ownname =>'XYZ'  ,stattab => 'XYZ_STATS_TABLE' , statid=>'CURRENT_STATS');
end;
/

Export the tables into dump file:

expdp USERID/PASSWORD dumpfile=File1.dmp logfile=File1.log directory=DP_DIR tables=XYZ.XYZ_STATS_TABLE version=11.2.0.1.0

In Destination Server:

Create stats of table:
begin
dbms_STATS.CREATE_STAT_TABLE(ownname =>'XYZ'  ,stattab => 'XYZ_STATS_TABLE');
end;
/

Import the tables from dump file with CONTENT=DATA_ONLY:

impdp directory=DP_DIR dumpfile=DB_SCHEMA_STATS-27052017.dmp logfile=impdp_DB_SCHEMA_STATS-27052017.log tables=XYZ.XYZ_STATS_TABLE CONTENT=DATA_ONLY

Report message to a moderator

Re: Import schema Statistics in different oracle database [message #663264 is a reply to message #663263]

Sat, 27 May 2017 04:13

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Michel Cadot wrote on Sat, 27 May 2017 10:06

I don't understand what you did.
Did you follow what I said?
Restart from the beginning and COPY AND PASTE ALL what you do AND GET. EVERYTHING.

Report message to a moderator

Re: Import schema Statistics in different oracle database [message #663265 is a reply to message #663264]

Sat, 27 May 2017 04:27

arin.oradba
Messages: 33
Registered: January 2016
Location: Kolkata

Member

I did the following.

In Source database:

1. Create stats of table:

begin
dbms_STATS.CREATE_STAT_TABLE(ownname =>'XYZ'  ,stattab => 'XYZ_STATS_TABLE');
end;
/

Result - completed successfully


2. Export the tables stats: 
begin
dbms_STATS.export_schema_STATS(ownname =>'XYZ'  ,stattab => 'XYZ_STATS_TABLE' , statid=>'CURRENT_STATS');
end;
/

Result - completed successfully

3. Export the tables into dump file:

expdp USERID/PASSWORD dumpfile=File1.dmp logfile=File1.log directory=DP_DIR tables=XYZ.XYZ_STATS_TABLE version=11.2.0.1.0

Result - completed successfully

Copy the backup dump into destination server

In Destination Server we did the following:


1. Create stats of table:
begin
dbms_STATS.CREATE_STAT_TABLE(ownname =>'XYZ'  ,stattab => 'XYZ_STATS_TABLE');
end;
/

Result - completed successfully

2. Import the tables from dump file with CONTENT=DATA_ONLY:

impdp directory=DP_DIR dumpfile=DB_SCHEMA_STATS-27052017.dmp logfile=impdp_DB_SCHEMA_STATS-27052017.log tables=XYZ.XYZ_STATS_TABLE CONTENT=DATA_ONLY


Result - 

. . imported "XYZ"."XYZ_STATS_TABLE"                     15.25 KB       4 rows
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at 12:39:07

Report message to a moderator

Re: Import schema Statistics in different oracle database [message #663266 is a reply to message #663265]

Sat, 27 May 2017 04:31

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

What don't you understand in "COPY AND PASTE ALL what you do AND GET"?

Report message to a moderator

Re: Import schema Statistics in different oracle database [message #663291 is a reply to message #663266]

Mon, 29 May 2017 00:25

arin.oradba
Messages: 33
Registered: January 2016
Location: Kolkata

Member

Good Morning sir.I am very sorry but there are some restriction to copy from server. Please trust me that all the steps I had given was successfully executed.No errors occurred.Please help me to understand where I am standing?

Report message to a moderator

Re: Import schema Statistics in different oracle database [message #663294 is a reply to message #663291]

Mon, 29 May 2017 00:40

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

So can't be sure what you posted is what you did and got.

Report message to a moderator

Re: Import schema Statistics in different oracle database [message #663296 is a reply to message #663294]

Mon, 29 May 2017 02:51

arin.oradba
Messages: 33
Registered: January 2016
Location: Kolkata

Member

1. I have created stats table at source database the following.It is created successfully

begin
dbms_STATS.CREATE_STAT_TABLE(ownname =>'XYZ' ,stattab => 'XYZ_STATS_TABLE');
end;
/

2. Then we export the tables stats at source database using the following.It is executed successfully .

begin
dbms_STATS.export_schema_STATS(ownname =>'XYZ' ,stattab => 'XYZ_STATS_TABLE' , statid=>'CURRENT_STATS');
end;
/

3. After that we have taken export backup using the following syntax at source database.It is completed successfully .

expdp USERID/PASSWORD dumpfile=File1.dmp logfile=File1.log directory=DP_DIR tables=XYZ.XYZ_STATS_TABLE version=11.2.0.1.0

4. Copy the backup dump into destination server

In Destination Server we did the following:

5. In Destination Server, we have Created the stats table first using the following syntax as you said.It is created successfully at destination server.

begin
dbms_STATS.CREATE_STAT_TABLE(ownname =>'XYZ' ,stattab => 'XYZ_STATS_TABLE');
end;
/

6. Now we have Imported the tables from dump file with CONTENT=DATA_ONLY as you said.It is completed successfully at destination server.:

impdp directory=DP_DIR dumpfile=DB_SCHEMA_STATS-27052017.dmp logfile=impdp_DB_SCHEMA_STATS-27052017.log tables=XYZ.XYZ_STATS_TABLE CONTENT=DATA_ONLY

Result output-
...........................................................................
............................................................................

. . imported "XYZ"."XYZ_STATS_TABLE" 15.25 KB 4 rows
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at 12:39:07

Now Please help me to understand where I am standing?

Report message to a moderator

Previous Topic:	DB version Question
Next Topic:	installing oracle 11g for oracle 8i Enterprise edition license

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Dec 27 21:24:50 CST 2024