RE: Not quite fluff - dbms

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Thu, 04 Mar 2004 19:48:30 -0700
Message-Id: <6.0.0.22.2.20040304192915.02afc5a0@pop.centrexcc.com>

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
-----------------------------------------------------------------

Received on Thu Mar 04 2004 - 20:43:12 CST

RE: Not quite fluff - dbms_stats