| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Not quite fluff - dbms_stats
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;
begin
dbms_stats.import_index_stats(r.owner, r.index_name, ...);
exception when others the null;
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_at_centrexcc.com]
>Sent: Thursday, March 04, 2004 9:49 PM
>To: oracle-l_at_freelists.org
>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> @gather_table_stats t1b null
>analyzing table t1b using null%
>
>PL/SQL procedure successfully completed.
>
>SQL> @exp_tbl_stats 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> @exp_tbl_stats t1 save
>
>PL/SQL procedure successfully completed.
>
>SQL> @imp_tbl_stats t1 lisa
>BEGIN
>*
>ERROR at line 1:
>ORA-20000: Unable to set values for column D1: does not exist or
>insufficient privileges
>ORA-06512: at "SYS.DBMS_STATS", line 4697
>ORA-06512: at "SYS.DBMS_STATS", line 5327
>ORA-06512: 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> @imp_tbl_stats 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: Insufficient privileges to analyze an object
> >within the database
> >ORA-06512: at "SYS.DBMS_STATS", line 4914
> >ORA-06512: at "SYS.DBMS_STATS", line 4943
> >ORA-06512: at "SYS.DBMS_STATS", line 5460
> >ORA-06512: at line 1
> >
> >
> >trdev-SYS>SHOW USER
> >USER is "SYS"
> >trdev-SYS>CONNECT SYSTEM_at_TRDEV
> >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: Insufficient privileges to analyze an object
> >within the database
> >ORA-06512: at "SYS.DBMS_STATS", line 4914
> >ORA-06512: at "SYS.DBMS_STATS", line 4943
> >ORA-06512: at "SYS.DBMS_STATS", line 5460
> >ORA-06512: 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_at_freelists.org
>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_at_freelists.org
>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
>-----------------------------------------------------------------
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Fri Mar 05 2004 - 10:55:24 CST
 |
 |