| dbms_stats [message #118024] |
Mon, 02 May 2005 13:47  |
balavignesh
Messages: 180
Registered: March 2005
|
Senior Member |
|
|
hai all,
i tried to execute the procedure
dbms_stats.get_table_stats to get the table statistics, but it is blowing some errors...help out in this issue.
SQL> variable NUMROWS number
SQL> variable NUMBLKS number
SQL> variable AVGRLEN number
SQL> begin
2 SYS.DBMS_STATS.GET_TABLE_STATS('SYSTEM','PROBLEM','','','',NUMROWS=>:numrows,
3 NUMBLKS=>:numblks,AVGRLEN=>:avgrlen);
4 end;
5 /
begin
*
ERROR at line 1:
ORA-20000: Unable to get values for table PROBLEM
ORA-06512: at "SYS.DBMS_STATS", line 2647
ORA-06512: at line 2
regards
bala
|
|
|
|
| Re: dbms_stats [message #118067 is a reply to message #118024] |
Mon, 02 May 2005 22:57  |
 |
Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Did you gather the table stats before you tried to get them? Please see the demonstration below that first tries to get the table stats without having gathered them first, then gathers them, then sucessfully get them. Also, you shoul probably have your tables in another schema, instead of system.
-- table and data for testing:
scott@ORA92> CREATE TABLE PROBLEM (col1 NUMBER)
2 /
Table created.
scott@ORA92> INSERT INTO PROBLEM VALUES (1)
2 /
1 row created.
scott@ORA92> COMMIT
2 /
Commit complete.
-- unsuccessful attempt to get table stats:
scott@ORA92> VARIABLE numrows NUMBER
scott@ORA92> VARIABLE numblks NUMBER
scott@ORA92> VARIABLE avgrlen NUMBER
scott@ORA92> SET AUTOPRINT ON
scott@ORA92> BEGIN
2 DBMS_STATS.GET_TABLE_STATS
3 ('SCOTT', 'PROBLEM', NULL, NULL, NULL, :numrows, :numblks, :avgrlen);
4 END;
5 /
BEGIN
*
ERROR at line 1:
ORA-20000: Unable to get values for table PROBLEM
ORA-06512: at "SYS.DBMS_STATS", line 2582
ORA-06512: at line 2
AVGRLEN
----------
NUMBLKS
----------
NUMROWS
----------
-- gather table stats:
scott@ORA92> EXECUTE DBMS_STATS.GATHER_TABLE_STATS ('SCOTT', 'PROBLEM')
PL/SQL procedure successfully completed.
-- now you can get the table stats that have been gathered:
scott@ORA92> VARIABLE numrows NUMBER
scott@ORA92> VARIABLE numblks NUMBER
scott@ORA92> VARIABLE avgrlen NUMBER
scott@ORA92> SET AUTOPRINT ON
scott@ORA92> BEGIN
2 DBMS_STATS.GET_TABLE_STATS
3 ('SCOTT', 'PROBLEM', NULL, NULL, NULL, :numrows, :numblks, :avgrlen);
4 END;
5 /
PL/SQL procedure successfully completed.
AVGRLEN
----------
3
NUMBLKS
----------
1
NUMROWS
----------
1
scott@ORA92>
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
