Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: freelists and dba_free_space
Ben wrote:
> I just tried the GATHER STALE option and got an error.
>
> ****************
> SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS ('TRDTA', 'GATHER STALE');
> BEGIN DBMS_STATS.GATHER_SCHEMA_STATS ('TRDTA', 'GATHER STALE'); END;
>
> *
> ERROR at line 1:
> ORA-06502: PL/SQL: numeric or value error: character to number
> conversion error
> ORA-06512: at line 1
> ************************
>
> Do you have to put place holders for the parameters that you don't want
> to pass in?
>
If you pass parameters by position, yes.
By value: DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'SCOTT', method_opt
=>'GATHER STALE')
>
> With the dbms_space procedure. It is created under the SYS owner and
> resides in SYS's schema. All of my production tables are in a different
> schema, so when I run the procedure it tells me that the tables don't
> exist. I tried granting EXECUTE ANY PROCEDURE to the schema owner and
> logging on as that owner to run, but then encountered more errors. How
> is this supposed to be ran?
>
Do a describe, and you'll see the parameters:
SQL> show user
USER is "SCOTT"
SQL> desc sys.dbms_stats
... lots of output, one of which is:
PROCEDURE GATHER_SCHEMA_STATS
Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- OWNNAME VARCHAR2 IN ESTIMATE_PERCENT NUMBER IN DEFAULT BLOCK_SAMPLE BOOLEAN IN DEFAULT METHOD_OPT VARCHAR2 IN DEFAULT DEGREE NUMBER IN DEFAULT GRANULARITY VARCHAR2 IN DEFAULT CASCADE BOOLEAN IN DEFAULT STATTAB VARCHAR2 IN DEFAULT STATID VARCHAR2 IN DEFAULT OPTIONS VARCHAR2 IN DEFAULT OBJLIST DBMS_STATS OUT STATOWN VARCHAR2 IN DEFAULT NO_INVALIDATE BOOLEAN IN DEFAULT GATHER_TEMP BOOLEAN IN DEFAULT GATHER_FIXED BOOLEAN IN DEFAULT
-- Regards, Frank van Bortel Top-posting is one way to shut me up...Received on Wed Feb 15 2006 - 13:57:55 CST