Or just identify which tables/indexes - and partitions/subpartitions,
forgot those in my diff sql - exist in the stattab table that do not have a
recipient table/index/etc in the target system and delete those rows from
the stattab table. Then you can do a schema or database import.
But even the scripting is not too bad:
begin
for r in (select owner, table_name from dba_tables) loop
begin
dbms_stats.import_table_stats(r.owner, r.table_name, ...
cascade= >false);
exception when others the null;
end;
end loop;
for r in (select owner, index_name from dba_indexes) loop
begin
dbms_stats.import_index_stats(r.owner, r.index_name, ...);
exception when others the null;
end;
end loop;
end;
In effect that 's what the imp_database/schema_stats does for you, so the
performance should be comparable.
At 08:21 AM 3/5/2004, you wrote:
>Hi Wolfgang,
>
>Yes, actually. This is from prod to dev. I just tried the table level
>import and it does work. Now I have some nasty scripting to do for
>hundreds of tables...
>
>Thank you very much for your response.
>Lisa
>
>-- --Original Message-- --
>From: Wolfgang Breitling [mailto:breitliw@(protected)]
>Sent: Thursday, March 04, 2004 9:49 PM
>To: oracle-l@(protected)
>Subject: RE: Not quite fluff - dbms_stats
>
>Any chance that there are objects in the source database that are not in
>
>the target database? I usually do not do full database statistics
>imports,
>only schema or even individual table imports, but I do get a similar
>error
>when I try to import statistics from a table which has more columns than
>
>the table I am importing into:
>
>SQL > desc t1
> Name Null? Type
> -- ---- ---- ---- ---- ---- ---- ---- --- -- -----
>-- ---- ---- ---- ---- -----
> PK1 NUMBER
> PK2 NUMBER
> D1 DATE
> D2 NUMBER
> D3 VARCHAR2(2000)
>
>SQL > select count(0) from t1;
>
> COUNT(0)
>-- ---- --
> 10000
>
>1 row selected.
>
>SQL > create table t1b as select * from t1 where rownum <= 2000;
>
>Table created.
>
>SQL > alter table t1b add (d4 number);
>
>Table altered.
>
>SQL > update t1b set d4=mod(trunc(999999*dbms_random.value),127)+1;
>
>2000 rows updated.
>
>SQL > commit;
>
>Commit complete.
>
>SQL > @(protected) t1b null
>analyzing table t1b using null%
>
>PL/SQL procedure successfully completed.
>
>SQL > @(protected) t1b lisa
>
>PL/SQL procedure successfully completed.
>
>SQL > update stats_table set c1= 'T1 ' where statid = 'LISA ';
>
>7 rows updated.
>
>SQL > commit;
>
>Commit complete.
>
>SQL > select statid, type, c1, c4 from stats_table where statid = 'LISA ';
>
>STATID T C1 C4
>-- ---- ---- ---- ---- ---- -- - -- ---- ---- ---- ---- ---- --
>-- ---- ---- ---- ---- ---- --
>LISA C T1 D1
>LISA C T1 D2
>LISA C T1 D3
>LISA C T1 D4
>LISA C T1 PK1
>LISA C T1 PK2
>LISA T T1
>
>7 rows selected.
>
>SQL > @(protected) t1 save
>
>PL/SQL procedure successfully completed.
>
>SQL > @(protected) t1 lisa
>BEGIN
>*
>ERROR at line 1:
>ORA-20000 (See ORA-20000.ora-code.com): Unable to set values for column D1: does not exist or
>insufficient privileges
>ORA-06512 (See ORA-06512.ora-code.com): at "SYS.DBMS_STATS ", line 4697
>ORA-06512 (See ORA-06512.ora-code.com): at "SYS.DBMS_STATS ", line 5327
>ORA-06512 (See ORA-06512.ora-code.com): at line 2
>
>SQL > delete from stats_table where statid= 'LISA ' and c1= 'T1 ' and c4 =
> 'D4 ';
>
>1 row deleted.
>
>SQL > commit;
>
>Commit complete.
>
>SQL > @(protected) t1 lisa
>
>PL/SQL procedure successfully completed.
>
>SQL >
>
>
>You should be able to verify that with a query like
>select c5, c1 from DATABASE_STATS where statid = '030404 ' and type 'T '
>minus
>select owner, table_name from dba_tables;
>
>
>At 11:07 AM 3/4/2004, you wrote:
> >Hi Wolfgang,
> >
> >Really? Here 's my errors
> >
> >trdev-SYS >exec sys.dbms_Stats.import_database_stats( -
> > > stattab= > 'DATABASE_STATS ', STATID= > '030404 ',STATOWN= > 'SYSTEM ');
> >BEGIN sys.dbms_Stats.import_database_stats( stattab= > 'DATABASE_STATS ',
> >STATID= > '030404 ',STATOWN= > 'S
> >
> >*
> >ERROR at line 1:
> >ORA-20000 (See ORA-20000.ora-code.com): Insufficient privileges to analyze an object
> >within the database
> >ORA-06512 (See ORA-06512.ora-code.com): at "SYS.DBMS_STATS ", line 4914
> >ORA-06512 (See ORA-06512.ora-code.com): at "SYS.DBMS_STATS ", line 4943
> >ORA-06512 (See ORA-06512.ora-code.com): at "SYS.DBMS_STATS ", line 5460
> >ORA-06512 (See ORA-06512.ora-code.com): at line 1
> >
> >
> >trdev-SYS >SHOW USER
> >USER is "SYS "
> >trdev-SYS >CONNECT SYSTEM@(protected)
> >Enter password: *********
> >Connected.
> >trdev-SYS >exec sys.dbms_Stats.import_database_stats( -
> > > stattab= > 'DATABASE_STATS ', STATID= > '030404 ',STATOWN= > 'SYSTEM ');
> >BEGIN sys.dbms_Stats.import_database_stats( stattab= > 'DATABASE_STATS ',
> >STATID= > '030404 ',STATOWN= > 'S
> >
> >*
> >ERROR at line 1:
> >ORA-20000 (See ORA-20000.ora-code.com): Insufficient privileges to analyze an object
> >within the database
> >ORA-06512 (See ORA-06512.ora-code.com): at "SYS.DBMS_STATS ", line 4914
> >ORA-06512 (See ORA-06512.ora-code.com): at "SYS.DBMS_STATS ", line 4943
> >ORA-06512 (See ORA-06512.ora-code.com): at "SYS.DBMS_STATS ", line 5460
> >ORA-06512 (See ORA-06512.ora-code.com): at line 1
> >
> >
> >trdev-SYS >
>
>Wolfgang Breitling
>Oracle7, 8, 8i, 9i OCP DBA
>Centrex Consulting Corporation
>http://www.centrexcc.com
>
>-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
>To unsubscribe send email to: oracle-l-request@(protected)
>put 'unsubscribe ' in the subject line.
>--
>Archives are at http://www.freelists.org/archives/oracle-l/
>FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
>-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
>
> "The sender believes that this E-Mail and any attachments were free of any
>virus, worm, Trojan horse, and/or malicious code when sent. This message
>and its attachments could have been infected during transmission. By
>reading the message and opening any attachments, the recipient accepts
>full responsibility for taking proactive and remedial action about viruses
>and other defects. The sender 's business entity is not liable for any loss
>or damage arising in any way from this message or its attachments. "
>
>-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
>To unsubscribe send email to: oracle-l-request@(protected)
>put 'unsubscribe ' in the subject line.
>--
>Archives are at http://www.freelists.org/archives/oracle-l/
>FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
>-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
