Home » Other » General » Import schema Statistics in different oracle database (oracle 11.2.0.4 ,11.2.0.1RHEL6.0,windows server)
Import schema Statistics in different oracle database [message #663194] Thu, 25 May 2017 02:15 Go to next message
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.
Re: Import schema Statistics in different oracle database [message #663197 is a reply to message #663194] Thu, 25 May 2017 02:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

Re: Import schema Statistics in different oracle database [message #663198 is a reply to message #663197] Thu, 25 May 2017 03:24 Go to previous messageGo to next message
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.
Re: Import schema Statistics in different oracle database [message #663199 is a reply to message #663198] Thu, 25 May 2017 03:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: Import schema Statistics in different oracle database [message #663202 is a reply to message #663199] Thu, 25 May 2017 03:47 Go to previous messageGo to next message
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.
Re: Import schema Statistics in different oracle database [message #663203 is a reply to message #663202] Thu, 25 May 2017 03:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: Import schema Statistics in different oracle database [message #663257 is a reply to message #663255] Sat, 27 May 2017 02:57 Go to previous messageGo to next message
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>
Re: Import schema Statistics in different oracle database [message #663259 is a reply to message #663257] Sat, 27 May 2017 03:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: Import schema Statistics in different oracle database [message #663263 is a reply to message #663259] Sat, 27 May 2017 04:04 Go to previous messageGo to next message
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
Re: Import schema Statistics in different oracle database [message #663264 is a reply to message #663263] Sat, 27 May 2017 04:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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.
Re: Import schema Statistics in different oracle database [message #663265 is a reply to message #663264] Sat, 27 May 2017 04:27 Go to previous messageGo to next message
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

Re: Import schema Statistics in different oracle database [message #663266 is a reply to message #663265] Sat, 27 May 2017 04:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

Re: Import schema Statistics in different oracle database [message #663291 is a reply to message #663266] Mon, 29 May 2017 00:25 Go to previous messageGo to next message
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?
Re: Import schema Statistics in different oracle database [message #663294 is a reply to message #663291] Mon, 29 May 2017 00:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

Re: Import schema Statistics in different oracle database [message #663296 is a reply to message #663294] Mon, 29 May 2017 02:51 Go to previous message
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?
Previous Topic: DB version Question
Next Topic: installing oracle 11g for oracle 8i Enterprise edition license
Goto Forum:
  


Current Time: Thu Mar 28 15:28:01 CDT 2024