Home » RDBMS Server » Performance Tuning » Different partitioned table import
Different partitioned table import [message #300519] Fri, 15 February 2008 14:17 Go to next message
kulkarni_m6
Messages: 10
Registered: February 2008
Junior Member
Hi,
am trying to import a table from production to dev database. Prodcution has 122 partition on this table while the dev has 117. And when i try to import this table am receiving the following error.

Please guide me to over come this.


SQL> exec dbms_stats.import_table_stats('APP', 'IPS_TRAN',NULL, 'STATS_TRAN', NULL, TRUE);

*
ERROR at line 1:
ORA-20000: partition "ITRN2454619" of table "APP"."IPS_TRAN" does not exist
or insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 6516
ORA-06512: at "SYS.DBMS_STATS", line 7252
ORA-06512: at line 1
Re: Different partitioned table import [message #300521 is a reply to message #300519] Fri, 15 February 2008 14:34 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Obviously, does partition ITRN2454619 exist?
why not import stats only to existing partitions ( using partname)?
Not sure whether it works.
Why not use set_table_stats directly?

[Updated on: Fri, 15 February 2008 14:44]

Report message to a moderator

Re: Different partitioned table import [message #300524 is a reply to message #300521] Fri, 15 February 2008 14:43 Go to previous messageGo to next message
kulkarni_m6
Messages: 10
Registered: February 2008
Junior Member
ITRN2454619 partition exist in the production database table. But in Dev database table its not..

As i said, table got 122 partition in prodcution database and table in dev database got 117 tables.

This is one case, i have got some more such tables which needs to imported to dev database from production.
Re: Different partitioned table import [message #300526 is a reply to message #300524] Fri, 15 February 2008 14:45 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Excuse~.
I have updated my previous posting.
Re: Different partitioned table import [message #300530 is a reply to message #300521] Fri, 15 February 2008 15:07 Go to previous messageGo to next message
kulkarni_m6
Messages: 10
Registered: February 2008
Junior Member
I can do it with the partition name parameter. But i have got some more tables with the same difference in tables. and every time i cannot pass the parameter.

Can i programatically compare the partition between these to tables and do exp/imp from 1 database to other?
Re: Different partitioned table import [message #300531 is a reply to message #300530] Fri, 15 February 2008 15:14 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Information is in user_tab_partitions.
But why?
Unless you are troubleshooting something / benchmarking , there is not need to import stats (already there is difference in data).
Why not collect new stats?
Re: Different partitioned table import [message #300533 is a reply to message #300519] Fri, 15 February 2008 15:22 Go to previous messageGo to next message
kulkarni_m6
Messages: 10
Registered: February 2008
Junior Member
Collectiong new stats is a good idea.

Look at these steps.

- gather global table stats for the partitioned table into special stat table using dbms_stats.gather_table_stats
-export these statistics
-import those into dev databse.

Do you think this work?
Re: Different partitioned table import [message #300536 is a reply to message #300533] Fri, 15 February 2008 15:29 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
I meant, why not collect new stats on dev database directly?
Why are you looking to export/import?
Re: Different partitioned table import [message #300540 is a reply to message #300519] Fri, 15 February 2008 15:38 Go to previous messageGo to next message
kulkarni_m6
Messages: 10
Registered: February 2008
Junior Member
Can you please elaborate this, collecting new stats in dev?
Re: Different partitioned table import [message #300541 is a reply to message #300540] Fri, 15 February 2008 15:41 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> - gather global table stats for the partitioned table into special stat table using dbms_stats.gather_table_stats

Why not directly do this? In dev database. Instead of collecting stats in prod and getting it it to dev.
Unless
Quote:
you are troubleshooting something / benchmarking , there is not need to import stats (already there is difference in data).
Why not collect new stats?

[Updated on: Fri, 15 February 2008 15:44]

Report message to a moderator

Re: Different partitioned table import [message #300545 is a reply to message #300519] Fri, 15 February 2008 16:12 Go to previous messageGo to next message
kulkarni_m6
Messages: 10
Registered: February 2008
Junior Member
We have a huge data divergence between production and dev. There is a performance issue, i want to test which is not possible to do it in production. So i want to imp stats from prodcution to dev then do testing..

Re: Different partitioned table import [message #300546 is a reply to message #300545] Fri, 15 February 2008 16:48 Go to previous message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
I would just set the table stats (using dbms_stats.set_table_stats) instead of export/import.

Previous Topic: sql tuning
Next Topic: Query tunning required
Goto Forum:
  


Current Time: Mon Nov 04 06:06:07 CST 2024