Evidently not... SQL> grant dba to blah identified by blah;
Grant succeeded. SQL> conn blah/blah Connected. SQL> create table IOT ( x number primary key, 2 y char(100) ) organization index 3 including x overflow; Table created. SQL> select segment_name from user_segments; SEGMENT_NAME ---------------------------------------- SYS_IOT_OVER_3688 SYS_IOT_TOP_3688 SQL> exec system.dbstat.ANALYZE_DB('BLAH'); BEGIN dbstat.ANALYZE_DB('BLAH'); END; * ERROR at line 1: ORA-25191: cannot reference overflow table of an index-organized table ORA-06512: at "SYS.DBMS_STATS", line 4481 ORA-06512: at "SYSTEM.DBSTAT", line 85 ORA-06512: at "SYSTEM.DBSTAT", line 185 ORA-06512: at line 1 My lazy fix has been just to catch a 25191 and ignore :-) Cheers Connor --- "MacGregor, Ian A." <[EMAIL PROTECTED]> wrote: > Does this script properly skip IOT overflow objects? > > Ian MacGregor > Stanford Linear Accelerator Center > [EMAIL PROTECTED] > > -----Original Message----- > Sent: Monday, October 07, 2002 9:19 AM > To: Multiple recipients of list ORACLE-L > > > Here is a work-in-progress utility, ie, I'm posting > this on an "all care, no responsibility" basis. > "Features" include: > > - will process all schemas or a nominated one > - has been deliberately restricted tables and > indexes > (so if you want lobs etc, you'll need to edit it a > little) > - can run in synchronous (foreground) mode or > asynchronous ( submits itself as a dbms_job) > - can run in parallel (multiple streams done via > modulo the object_id) > - has a debugging mode > - uses dbms_space to derive a meaningful estimate > size > for each segment > - records progress in v$session_longops > - doesn't go against DBA_SEGMENTS 'cos thats so slow > - cranks up sort_area_size to improve perf. > > Cheers > Connor > > create or replace > package system.dbstat is > > procedure analyze_db ( > p_owner varchar2 default null, > -- > if only one owner to be processed > p_debug number default 0, > -- > 0=do work, 1=msgs+work, 2=msgs only > p_segment_type varchar2 default null, > -- > TABLE or INDEX > p_parallel number default 1, > -- > concurrency (>1 means must be asych) > p_mode varchar2 default 'S', > -- > A=run as dbms_job, S=run synchronous > p_int1 number default 1, > -- > internal use only > p_int2 number default 0) ; > -- > internal use only > end; > / > create or replace > package body system.dbstat is > > -- > ---------------------------------------------------------------------------------- > -- Routines > -- > ---------------------------------------------------------------------------------- > > procedure analyze_db ( > p_owner varchar2 default null, > -- > if only one owner to be processed > p_debug number default 0, > -- > 0=do work, 1=msgs+work, 2=msgs only > p_segment_type varchar2 default null, > -- > TABLE or INDEX > p_parallel number default 1, > -- > concurrency (>1 means must be asych) > p_mode varchar2 default 'S', > -- > A=run as dbms_job, S=run synchronous > p_int1 number default 1, > -- > internal use only > p_int2 number default 0) is > -- > internal use only > > type varchar_list is table of varchar2(80); > > v_start date := sysdate; > v_tot_count number := 0; > v_cum_count number := 0; > v_cum_bytes number := 0; > > v_owner varchar_list; > v_segment_name varchar_list; > v_segment_type varchar_list; > v_partitioned varchar_list; > > v_longop_rindex pls_integer; > v_longop_slno pls_integer; > v_job pls_integer; > v_job_plsql varchar2(240); > > procedure process_segment(p_owner > varchar2,p_segment_name varchar2, > p_segment_type > varchar2,p_part_name varchar2 default null, > p_granularity varchar2 > default 'GLOBAL') is > v_total_blocks number; > v_total_bytes number; > v_unused_blocks number; > v_unused_bytes number; > v_last_file_id number; > v_last_block_id number; > v_last_block number; > v_amount_to_analyze number; > v_ana_command varchar2(500); > begin > > > dbms_application_info.set_client_info(p_owner||','||p_segment_name||','||p_segment_type||','||p_part_name); > > dbms_space.unused_space ( > p_owner, > p_segment_name, > p_segment_type, > v_total_blocks, > v_total_bytes, > v_unused_blocks, > v_unused_bytes, > v_last_file_id, > v_last_block_id, > v_last_block, > p_part_name); > > -- > -- This gives a reasonable degree of analysis. Up > to > about 10M is effectively a compute, and > -- it reduces from there, eventually down to about > 0.5% for a 1G segment > -- The formula is: percent to analyze := 500 * > power(used megabytes,-1.05) > -- with a ceiling of 99.99 percent (since dbms_stats > does not allow a '100' to be passed) > -- > v_amount_to_analyze := > least(99.99,round(131072000*power(greatest(v_total_bytes-v_unused_bytes,1),-1.05),5)); > > if p_debug > 0 then > dbms_output.put_line(p_segment_type||': > '||p_owner||'.'||p_segment_name||' '||p_part_name); > dbms_output.put_line(v_total_bytes||' bytes > allocated'); > > dbms_output.put_line((v_total_bytes-v_unused_bytes)||' > bytes in use'); > dbms_output.put_line('Analyze > '||nvl(v_amount_to_analyze,100)||'%'); > dbms_output.put_line('---------------------'); > end if; > > dbms_application_info.set_client_info('Obj: > '||v_cum_count||' '||p_owner||'.'||p_segment_name||' > '|| > > (v_total_bytes-v_unused_bytes)||' byt > '||nvl(v_amount_to_analyze,100)||'%'); > > if p_debug < 2 then > > dbms_application_info.set_session_longops(v_longop_rindex, > v_longop_slno, > 'Analyze', 0, 0, v_cum_count, > v_tot_count, p_segment_type, 'objects'); > if p_segment_type like 'TABLE%' then -- > could be a table or a table partition > sys.dbms_stats.gather_table_stats( > ownname=>'"'||p_owner||'"', > tabname=>'"'||p_segment_name||'"', > granularity=>'ALL', > estimate_percent=>v_amount_to_analyze, > partname=>p_part_name); > else > sys.dbms_stats.gather_index_stats( > ownname=>'"'||p_owner||'"', > indname=>'"'||p_segment_name||'"', > estimate_percent=>v_amount_to_analyze, > partname=>p_part_name); > end if; > v_cum_count := v_cum_count + 1; > v_cum_bytes := v_cum_bytes + > v_total_bytes-v_unused_bytes; > end if; > end; > > begin > if upper(p_mode) not in ('A','S') then > raise_application_error(-20000,'Mode must be A > or > S'); > end if; > if p_parallel > 1 and upper(p_mode) != 'A' then > raise_application_error(-20000,'Cannot run > parallel in synchronous mode'); > end if; > if p_parallel not between 1 and 4 then > raise_application_error(-20000,'Parallel > limited > to 1 to 4'); > end if; > > if p_parallel > 1 or p_mode = 'A' then > for i in 1 .. p_parallel loop > v_job_plsql := > 'dbstat.analyze_db('''||p_owner||''',0,'''||p_segment_type|| > > ''',1,''S'','||p_parallel||','||(i-1)||');'; > if p_debug > 0 then > dbms_output.put_line('Job: '||v_job_plsql); > end if; > if p_debug < 2 then > dbms_job.submit(v_job,v_job_plsql); > end if; > end loop; > commit; > return; > end if; > > dbms_output.put_line('---------------------'); > > dbms_output.enable(999999); > > if p_debug > 0 then > execute immediate 'alter session set > sort_area_size = 8192000' ; > execute immediate 'alter session set > sort_area_retained_size = 8192000' ; > v_longop_rindex := > dbms_application_info.set_session_longops_nohint; > end if; > > SELECT segment_TYPE,segment_NAME,owner, > decode(subname,null,'NO','YES') partitioned > bulk collect into > v_segment_type, v_segment_name, v_owner, > v_partitioned > from ( select u.name owner, o.name segment_name, > o.subname, so.object_type segment_type, o.obj# > object_id > from sys.user$ u, sys.obj$ o, sys.ts$ ts, > sys.seg$ s, sys.file$ f, > ( select 'TABLE' object_type, 2 > object_type_id, 5 segment_type_id, t.obj#, t.file#, > t.block# , t.ts# > from sys.tab$ t > where bitand(t.property, 1024) = 0 > and bitand(t.property,8192) = 0 > union all > select 'INDEX', 1, 6, i.obj#, > i.file#, i.block#, i.ts# > from sys.ind$ i > where i.type# in (1, 2, 3, 4, 6, 7, > 9) > ) so > where s.file# = so.file# > and s.block# = so.block# > and s.ts# = so.ts# > and s.ts# = ts.ts# > and o.obj# = so.obj# > and o.owner# = u.user# > and s.type# = so.segment_type_id > and o.type# = so.object_type_id > and s.ts# = f.ts# > and s.file# = f.relfile# > and u.name not in ('SYS','SYSTEM')) > where owner = nvl(upper(p_owner),owner) > and segment_type = > nvl(upper(p_segment_type),segment_type) > and mod(object_id,p_int1) = p_int2; > > v_tot_count := v_segment_type.count; > for i in v_segment_type.first .. > v_segment_type.last > loop > if v_partitioned(i) = 'YES' then > for j in ( > SELECT O.SUBNAME PART_NAME, > decode(O.TYPE#,19,'TABLE > PARTITION', > 20,'INDEX > PARTITION', > 34,'TABLE > SUBPARTITION', > 35,'INDEX > SUBPARTITION') segment_type > FROM SYS.USER$ U,SYS.OBJ$ O > WHERE U.NAME = v_owner(i) > AND O.OWNER# = U.USER# > AND O.NAME = v_segment_name(i) > AND O.TYPE# in (19 ,20,34,35) > ORDER BY PART_NAME ) loop > > process_segment(v_owner(i),v_segment_name(i),j.segment_type,j.part_name,'PARTITION'); > end loop; > else > > process_segment(v_owner(i),v_segment_name(i),v_segment_type(i)); > end if; > end loop; > dbms_output.put_line('Objects Analyzed: > '||v_cum_count); > dbms_output.put_line('Bytes scanned: > '||v_cum_bytes); > dbms_output.put_line('Elapsed Time: > '||round((sysdate-v_start)*86400,1)); > end; > > END; > / > > > --- "Jesse, Rich" <[EMAIL PROTECTED]> wrote: > > > Yes, the DBMS_STATS package is very quirky in 8i, > > IMHO. Knowing the bug in > > DATABASE_STATS, I've written a procedure to > > iteratively use SCHEMA_STATS > > instead. Of course, this too has a bug that will > > report ORA-1403 on the > > "first" table in the schema, so I needed to code > > around that. > > > > And for all this trouble Oracle still recommends > > using DBMS_STATS over > > ANALYZE. Then fix it! <sigh> > > > > Rich Jesse > System/Database > > Administrator > > [EMAIL PROTECTED] Quad/Tech > > International, Sussex, WI USA > > > > > > > -----Original Message----- > > > From: Post, Ethan [mailto:[EMAIL PROTECTED]] > > > Sent: Friday, October 04, 2002 5:09 PM > > > To: Multiple recipients of list ORACLE-L > > > Subject: RE: DBMS_STATS.GATHER_DATABASE_STATS > > analyzes SYS and SYSTEM > > > inde > > > > > > > > > Arrgggg... > > > > > > So anyway, I am thinking, hey it's high time I > > start using DBMS_STATS > > > instead of my own procedure so I kick of the > > following > > > (Oracle 8.1.7.4). > > > After the first run I have SYS and SYSTEM stats > on > > indexes > > > and on other > > > schemas with NO STATS it just ignored those > tables > > even > > > though you can see I > > > have GATHER EMPTY below. So I kick it off again > > and guess > > > what, it starts > > > analyzing the tables it missed the first time, > > including SYS > > > and SYSTEM. > > > Guess I am going to use > > DBMS_STATS.GATHER_TABLE_STATS and be > > > a bit more > > > specific about what I get. > > -- > > Please see the official ORACLE-L FAQ: > > http://www.orafaq.com > > -- > > Author: Jesse, Rich > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services -- 858-538-5051 > > http://www.fatcity.com > > San Diego, California -- Mailing list and > web > > hosting services > > > --------------------------------------------------------------------- > > To REMOVE yourself from this mailing list, send an > > E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of > > 'ListGuru') and in > > the message BODY, include a line containing: UNSUB > > ORACLE-L > > (or the name of mailing list you want to be > removed > > from). You may > > also send the HELP command for other information > > (like subscribing). > > ===== > Connor McDonald > http://www.oracledba.co.uk > http://www.oaktable.net > > "Remember amateurs built the ark - Professionals > built the Titanic" > > __________________________________________________ > Do You Yahoo!? > Everything you'll ever need on one web page > from News and Sport to Email and Music Charts > http://uk.my.yahoo.com > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: =?iso-8859-1?q?Connor=20McDonald?= > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 > http://www.fatcity.com > San Diego, California -- Mailing list and web > hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in the message BODY, include a line > containing: UNSUB ORACLE-L (or the name of mailing > list you want to be removed from). You may also > send the HELP command for other information (like > subscribing). > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: MacGregor, Ian A. > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 > http://www.fatcity.com > San Diego, California -- Mailing list and web > hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). ===== Connor McDonald http://www.oracledba.co.uk http://www.oaktable.net "Remember amateurs built the ark - Professionals built the Titanic" __________________________________________________ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).