oracle dbms_space,dbms_space.free_space-数据库专栏,SQL Server

what is the use of this stored proc?

it tells you how many blocks have free space for updates, right ?

but it does not tell you how much free space in each block. we can

get free space info. from dba_free_space.

can you show how this proc can be of value to us?

another procs in this package is unused_space.

if it reports 35 blocks. does it mean 35 blocks have never

had data in it ?

it seems that it doesnt report any empty blocks above

the high water mark, does it?

how can we make use of this info ? can you give some examples

that we can use these procedures to help manage space.

thanks, tom.

and we said…

here is an example showing how to use dbms_space and how to interpret the output. basically between the 2 procedures free blocks and unused space, well be able to get:free blocks…… number of blocks on the freelisttotal blocks….. total blocks allocated to the tabletotal bytes…… total bytes allocated to the tableunused blocks…. blocks that have never contained dataunused bytes….. the above in bytesit does not tell you how many blocks have free space for updates. we can tell you how many blocks are candidates for inserts (they are on the freelist) and blocks on the freelist have space for updates — but — there are blocks in the table that have space for updates but that are not on the freelist. we cannot see them in any report.it does not tell you how much space is free in each block (nothing does, typically there are thousands or hundreds of thousands of blocks in a table — an analysis of the free space block by block is not practical. we can get an average free space but not block by block).this report does show blocks above the high water mark. unused blocks are exactly the block above the high water mark. you can get most of the information supplied by this package by analyzing the table and using queries against user_tables and user_segments. the freelist analysis is more detailed using this package as you can look at each freelist independently.below is a procedure you can use to make using dbms_space a little easier. after that i create a table and show how space is being used in it after various operations. comments in bold explain the output.ops$tkyte@8i> create or replace 2 procedure show_space 3 ( p_segname in varchar2, 4 p_owner in varchar2 default user, 5 p_type in varchar2 default table ) 6 as 7 l_free_blks number; 8 9 l_total_blocks number; 10 l_total_bytes number; 11 l_unused_blocks number; 12 l_unused_bytes number; 13 l_lastusedextfileid number; 14 l_lastusedextblockid number; 15 l_last_used_block number; 16 procedure p( p_label in varchar2, p_num in number ) 17 is 18 begin 19 dbms_output.put_line( rpad(p_label,40,.) || 20 p_num ); 21 end; 22 begin 23 dbms_space.free_blocks 24 ( segment_owner => p_owner, 25 segment_name => p_segname, 26 segment_type => p_type, 27 freelist_group_id => 0, 28 free_blks => l_free_blks ); 29 30 dbms_space.unused_space 31 ( segment_owner => p_owner, 32 segment_name => p_segname, 33 segment_type => p_type, 34 total_blocks => l_total_blocks, 35 total_bytes => l_total_bytes, 36 unused_blocks => l_unused_blocks, 37 unused_bytes => l_unused_bytes, 38 last_used_extent_file_id => l_lastusedextfileid, 39 last_used_extent_block_id => l_lastusedextblockid, 40 last_used_block => l_last_used_block ); 41 42 p( free blocks, l_free_blks ); 43 p( total blocks, l_total_blocks ); 44 p( total bytes, l_total_bytes ); 45 p( unused blocks, l_unused_blocks ); 46 p( unused bytes, l_unused_bytes ); 47 p( last used ext fileid, l_lastusedextfileid ); 48 p( last used ext blockid, l_lastusedextblockid ); 49 p( last used block, l_last_used_block ); 50 end; 51 /procedure created.ops$tkyte@8i> ops$tkyte@8i> create table t ( x int, y char(2000) default * ) 2 storage ( initial 40k next 40k minextents 5 ) 3 tablespace system;table created.i create a table with >1 extent to make it interesting. i also put a char(2000) in there to make the minimum row length be 2000 bytes (chars always take their max space right away). this just makes my rows “big”ops$tkyte@8i> insert into t (x) values ( 1 );1 row created.i create one row just to use a little space in the tableops$tkyte@8i> analyze table t compute statistics;table analyzed.ops$tkyte@8i> compute sum of blocks on reportops$tkyte@8i> break on reportops$tkyte@8i> select extent_id, bytes, blocks 2 from user_extents 3 where segment_name = t 4 and segment_type = table 5 / extent_id bytes blocks———- ———- ———- 2 40960 5 3 81920 10 4 57344 7 0 40960 5 1 40960 5 ———-sum 32this shows that there are 32 blocks allocated in 5 extents to this table (as expected)ops$tkyte@8i> clear breaksops$tkyte@8i> select blocks, empty_blocks, 2 avg_space, num_freelist_blocks 3 from user_tables 4 where table_name = t 5 / blocks empty_blocks avg_space num_freelist_blocks———- ———— ———- ——————- 1 30 6091 1since i analyzed the table, i have acccess to the above information. youll find that it maps exactly to the data below. there are a total of 32 blocks allocated to the table (below and as confirmed by user_extents above). there are 30 empty_blocks (above)/ unused_blocks (below). these are blocks above the hwm. this leaves 2 blocks unaccounted for — 1 block has data in it, the other has the extent map for the table (the first block of each table is used by the system itself).ops$tkyte@8i> exec show_space( t )free blocks………………………..1total blocks……………………….32total bytes………………………..262144unused blocks………………………30unused bytes……………………….245760last used ext fileid………………..1last used ext blockid……………….64816last used block…………………….2pl/sql procedure successfully completed.ops$tkyte@8i> insert into t (x) 2 select rownum 3 from all_users 4 where rownum < 50 5 /49 rows created.ops$tkyte@8i> commit;commit complete.so now we have 50 rows with 2k each — im using a 8k blocksize so i expect about 3 rows / block. that means about 18 blocks of data plus 1 for the system = about 19 blocks should be “used” now. below i see that i haveo 3 blocks on the freelist. they have more space for new inserts (they have not hit their pctused yet and may be inserted into)o 12 unused blocks, leaving 20 blocks “used”. since i have 3 on the freelist — we probably used a little more then the 18 for data — we used 19 for the 50 rows. we have one for the system — all accounted for.ops$tkyte@8i> exec show_space( t )free blocks………………………..3total blocks……………………….32total bytes………………………..262144unused blocks………………………12unused bytes……………………….98304last used ext fileid………………..1last used ext blockid……………….64681last used block…………………….5pl/sql procedure successfully completed.ops$tkyte@8i> delete from t;50 rows deleted.ops$tkyte@8i> commit;commit complete.now we can see what a delete does to our utilization.ops$tkyte@8i> exec show_space( t )free blocks………………………..19total blocks……………………….32total bytes………………………..262144unused blocks………………………12unused bytes……………………….98304last used ext fileid………………..1last used ext blockid……………….64681last used block…………………….5pl/sql procedure successfully completed.the above shows that the delete simply put all of our blocks on the free list. we have 19 blocks on the free list + 12 unused blocks + 1 system block = 32 blocks. all accounted for. note that the hwm stayed the same — we dont have 31 unused blocks — we have 12 as before. the hwm for a table will never decrease unless we…..ops$tkyte@8i> truncate table t;table truncated.ops$tkyte@8i> exec show_space( t )free blocks………………………..0total blocks……………………….32total bytes………………………..262144unused blocks………………………31unused bytes……………………….253952last used ext fileid………………..1last used ext blockid……………….64816last used block…………………….1pl/sql procedure successfully completed.truncate it. that puts all of the blocks below the hwm. now we have 31 unused blocks + 1 system block = 32 blocks total. none on the free list since none of them have any data.

reviews   bookmark review | bottom | toptable space used for a particular table  march 17, 2001

reviewer:  spmurthy  from singapore

hi tom, thanks for your reply it is more useful to me to know the table space.regards

bookmark review | bottom | topdbms_space usage  march 22, 2001

reviewer:  b.n.sarma  from usa

tom,excellent , nothing less.it would have been nice had you shown a select statement with autot on doing fts upto hwm, even if you have delted all the rows and the same with truncate. it would have become a good notes.your explanation with examples makes things very clear.why dont you write a book :-)regardsbn

bookmark review | bottom | top  march 23, 2001

reviewer:  helena markova  from bratislava, slovakia

bookmark review | bottom | topdbms_space.free_space  may 09, 2001

reviewer:  d.c.l.  from seattle, usa

right on. awesome grip of the subject matter.

bookmark review | bottom | top  may 10, 2001

reviewer:  vikram  from delhi, india

excellent

bookmark review | bottom | top  august 21, 2001

reviewer:  k.v.s.raju  from sydney, australia

its excellent

bookmark review | bottom | topdbms_space   september 19, 2001

reviewer:  jim  from ma

very, very good!!!

bookmark review | bottom | toperrors in show_space  september 20, 2001

reviewer:  a reader

tom, tried using your show_space procedure. it compiled successfully but on using it i get following errors:sql> exec show_space(t)begin show_space(t); end;*error at line 1:ora-00942: table or view does not existora-06512: at “sys.dbms_space”, line 55ora-06512: at “tom.show_space”, line 22ora-06512: at line 1the table t exists under schema tom and the show_space procedure was compiled under user tom.dbmsutil/prvtutil etc. have all been run. what am i missing ?

followup:

something must be wrong — give me a full example like this (that shows it works)ops$tkyte@ora817dev.us.oracle.com> create user a identified by a;user created.ops$tkyte@ora817dev.us.oracle.com> grant create session, create procedure, create table to a;grant succeeded.ops$tkyte@ora817dev.us.oracle.com> alter user a quota unlimited on users;user altered.ops$tkyte@ora817dev.us.oracle.com> connect a/aconnected.a@ora817dev.us.oracle.com> @showspaceprocedure created.a@ora817dev.us.oracle.com> create table t ( x int ) tablespace users;table created.a@ora817dev.us.oracle.com> exec show_space( t )pl/sql procedure successfully completed.ops$tkyte@ora817dev.us.oracle.com> set serveroutput onops$tkyte@ora817dev.us.oracle.com> exec show_space( t );free blocks………………………..0total blocks……………………….64total bytes………………………..524288unused blocks………………………63unused bytes……………………….516096last used ext fileid………………..7last used ext blockid……………….4809last used block…………………….1pl/sql procedure successfully completed.so, do the whole create user/install the procedure/run the test and see if it reproduces. if not, either you were not logged in as tom, tom did not own t, etc… (is t a view or synonym in your case??)

bookmark review | bottom | topa little question  october 18, 2001

reviewer:  igor  from france

i dont understand how you knew it would be 32 blocksfor one row of 2000 chars and number ?

followup:

the 5 extents were expected. the 32 blocks just happened.

bookmark review | bottom | top5 extents 40k each (8k block size) why not 5×5=25 blocks?  october 22, 2001

reviewer:  yk liu  from ca, usa

bookmark review | bottom | topfree space  october 30, 2001

reviewer:  an  from de

its excellent!

bookmark review | bottom | topora-14107: partition specification is required for a partitioned object  november 19, 2001

reviewer:  a reader

sql> create table t (x varchar2(20));table created.sql> exec show_space(t);pl/sql procedure successfully completed.sql> set serveroutput onsql> exec show_space(t);free blocks………………………..0total blocks……………………….10total bytes………………………..81920unused blocks………………………9unused bytes……………………….73728last used ext fileid………………..5last used ext blockid……………….126659last used block…………………….1pl/sql procedure successfully completed.t_p — is a partitioned tablesql> exec show_space(t_p);begin show_space(t_p); end;*error at line 1:ora-14107: partition specification is required for a partitioned objectora-06512: at “sys.dbms_space”, line 55ora-06512: at “myschema.show_space”, line 22ora-06512: at line 1why iam i running into this error while trying to use show_space on a partitioned table.

followup:

ok, time for an update of this utility! i had this sitting around already — it does two things 1) adds partition support2) makes it so this runs in sql for anything… gives a result set instead of printing. you can easily make it dbms_output.put_line if you want…first we start with the types:create or replace type show_space_typeas object( owner varchar2(30), segment_name varchar2(30), partition_name varchar2(30), segment_type varchar2(30), free_blocks number, total_blocks number, unused_blocks number, last_used_ext_fileid number, last_used_ext_blockid number, last_used_block number)/create or replace type show_space_table_typeas table of show_space_type/and then the function:create or replacefunction show_space_for( p_segname in varchar2, p_owner in varchar2 default user, p_type in varchar2 default table, p_partition in varchar2 default null )return show_space_table_typeauthid current_useras pragma autonomous_transaction; type rc is ref cursor; l_cursor rc; l_free_blks number; l_total_blocks number; l_total_bytes number; l_unused_blocks number; l_unused_bytes number; l_lastusedextfileid number; l_lastusedextblockid number; l_last_used_block number; l_sql long; l_conj varchar2(7) default where ; l_data show_space_table_type := show_space_table_type(); l_owner varchar2(30); l_segment_name varchar2(30); l_segment_type varchar2(30); l_partition_name varchar2(30); procedure add_predicate( p_name in varchar2, p_value in varchar2 ) as begin if ( instr( p_value, % ) > 0 ) then l_sql := l_sql || l_conj || p_name || like || upper(p_value) || ; l_conj := and ; elsif ( p_value is not null ) then l_sql := l_sql || l_conj || p_name || = || upper(p_value) || ; l_conj := and ; end if; end;begin l_sql := select owner, segment_name, segment_type, partition_name from dba_segments ; add_predicate( segment_name, p_segname ); add_predicate( owner, p_owner ); add_predicate( segment_type, p_type ); add_predicate( partition, p_partition ); execute immediate alter session set cursor_sharing=force; open l_cursor for l_sql; execute immediate alter session set cursor_sharing=exact; loop fetch l_cursor into l_owner, l_segment_name, l_segment_type, l_partition_name; exit when l_cursor%notfound; begin dbms_space.free_blocks ( segment_owner => l_owner, segment_name => l_segment_name, segment_type => l_segment_type, partition_name => l_partition_name, freelist_group_id => 0, free_blks => l_free_blks ); dbms_space.unused_space ( segment_owner => l_owner, segment_name => l_segment_name, segment_type => l_segment_type, partition_name => l_partition_name, total_blocks => l_total_blocks, total_bytes => l_total_bytes, unused_blocks => l_unused_blocks, unused_bytes => l_unused_bytes, last_used_extent_file_id => l_lastusedextfileid, last_used_extent_block_id => l_lastusedextblockid, last_used_block => l_last_used_block ); l_data.extend; l_data(l_data.count) := show_space_type( l_owner, l_segment_name, l_partition_name, l_segment_type, l_free_blks, l_total_blocks, l_unused_blocks, l_lastusedextfileid, l_lastusedextblockid, l_last_used_block ); exception when others then null; end; end loop; close l_cursor; return l_data;end;/then we can:ops$tkyte@ora817dev.us.oracle.com> select segment_name, partition_name segment_type, free_blocks,total_blocks,unused_blocks 2 from table( cast( show_space_for( hashed,user,% ) as show_space_table_type ) ) 3 /segment_na segment_type free_blocks total_blocks unused_blocks———- —————– ———– ———— ————-hashed part_2 1 64 62hashed part_3 1 64 62hashed part_4 1 64 62hashed part_1 1 64 62ops$tkyte@ora817dev.us.oracle.com> and in 9i, wed change the function to be pipelined:ops$tkyte@ora9i.world> create or replace 2 function show_space_for 3 ( p_segname in varchar2, 4 p_owner in varchar2 default user, 5 p_type in varchar2 default table, 6 p_partition in varchar2 default null ) 7 return show_space_table_type 8 authid current_user 9 pipelined 10 as 11 pragma autonomous_transaction; 12 type rc is ref cursor; 13 l_cursor rc; 14 15 l_free_blks number; 16 l_total_blocks number; 17 l_total_bytes number; 18 l_unused_blocks number; 19 l_unused_bytes number; 20 l_lastusedextfileid number; 21 l_lastusedextblockid number; 22 l_last_used_block number; 23 l_sql long; 24 l_conj varchar2(7) default where ; 25 l_owner varchar2(30); 26 l_segment_name varchar2(30); 27 l_segment_type varchar2(30); 28 l_partition_name varchar2(30); 29 30 procedure add_predicate( p_name in varchar2, p_value in varchar2 ) 31 as 32 begin 33 if ( instr( p_value, % ) > 0 ) 34 then 35 l_sql := l_sql || l_conj || p_name || like || upper(p_value) || ; 36 l_conj := and ; 37 elsif ( p_value is not null ) 38 then 39 l_sql := l_sql || l_conj || p_name || = || upper(p_value) || ; 40 l_conj := and ; 41 end if; 42 end; 43 begin 44 l_sql := select owner, segment_name, segment_type, partition_name 45 from dba_segments ; 46 47 add_predicate( segment_name, p_segname ); 48 add_predicate( owner, p_owner ); 49 add_predicate( segment_type, p_type ); 50 add_predicate( partition, p_partition ); 51 52 execute immediate alter session set cursor_sharing=force; 53 open l_cursor for l_sql; 54 execute immediate alter session set cursor_sharing=exact; 55 56 loop 57 fetch l_cursor into l_owner, l_segment_name, l_segment_type, l_partition_name; 58 dbms_output.put_line( l_segment_name || , || l_segment_type ); 59 exit when l_cursor%notfound; 60 begin 61 dbms_space.free_blocks 62 ( segment_owner => l_owner, 63 segment_name => l_segment_name, 64 segment_type => l_segment_type, 65 partition_name => l_partition_name, 66 freelist_group_id => 0, 67 free_blks => l_free_blks ); 68 69 dbms_space.unused_space 70 ( segment_owner => l_owner, 71 segment_name => l_segment_name, 72 segment_type => l_segment_type, 73 partition_name => l_partition_name, 74 total_blocks => l_total_blocks, 75 total_bytes => l_total_bytes, 76 unused_blocks => l_unused_blocks, 77 unused_bytes => l_unused_bytes, 78 last_used_extent_file_id => l_lastusedextfileid, 79 last_used_extent_block_id => l_lastusedextblockid, 80 last_used_block => l_last_used_block ); 81 82 pipe row ( show_space_type( l_owner, l_segment_name, l_partition_name, 83 l_segment_type, l_free_blks, l_total_blocks, l_unused_blocks, 84 l_lastusedextfileid, l_lastusedextblockid, l_last_used_block ) ); 85 exception 86 when others then null; 87 end; 88 end loop; 89 close l_cursor; 90 91 return; 92 end; 93 /function created.ops$tkyte@ora9i.world> set arraysize 1ops$tkyte@ora9i.world> select segment_name, segment_type, free_blocks,total_blocks,unused_blocks 2 from table( show_space_for( %,user,% ) ) 3 /segment_name segment_type free_blocks total_blocks unused_blocks————— —————– ———– ———— ————-keep_scn table 1 64 62employees table 0 64 63stinky table 0 64 63object_table table 1 64 62run_stats table 2 64 53emp table 0 64 62proj table 0 64 62x table 1 64 62words table 0 64 63docs table 0 64 63keywords table 0 64 63dept table 2 64 61c table 1 64 62dsinvlines table 1 64 62num_str table 1 64 23t table 4 64 28t1 table 0 64 63t2 table 0 64 63bom table 1 64 62parts table 1 64 62sys_c001371 index 0 64 62sys_c001372 index 0 64 62sys_c001574 index 0 64 62sys_c001694 index 0 64 62sys_c001695 index 0 64 62bom_pk index 0 64 62parts_pk index 0 64 6227 rows selected.

bookmark review | bottom | topreader  december 26, 2001

reviewer:  reader  from usa

tom,could you clarify, why the *first block* of a datafilebeing used for the os., 2nd for segment header, 3rd… fordata.i created a table ts1 in tablespace ts1 , db_block_size 8kresults:sql> set serveroutput on size 1000000sql> exec show_space(ts1,sys,table);free blocks………………………..1total blocks……………………….2total bytes………………………..16384unused blocks………………………0unused bytes……………………….0last used ext fileid………………..2last used ext blockid……………….2last used block…………………….2pl/sql procedure successfully completed.sql> select lpad(file_name,40), bytes/1024/8 blocks , tablespace_name from dba_data_files where tablespace_name = ts1;lpad(file_name,40) blocks tablespace_name—————————————- ———- ———————– /u07/oradata/iiim/ts1.dbf 3 ts1sql> select segment_name,tablespace_name,block_id,blocks, extent_id from dba_extents where segment_name = ts1;segm tablespace_name block_id blocks extent_id—- —————————— ———- ———- ———-ts1 ts1 2 2 0sql> select table_name,initial_extent,next_extent from dba_tables where table_name = ts1;table_name initial_extent next_extent—————————— ————– ———–ts1 16384 8192sql> alter table ts1 allocate extent (size 8192k);alter table ts1 allocate extent (size 8192k)*error at line 1:ora-01653: unable to extend table sys.ts1 by 1024 in tablespace ts1the datafile has 3 blocks, segment has used 2 blocks (including segment header). when i try to allocate oneextent = 1 block, get ora-1653thanks

followup:

i never said the first block would be used by the os. in any case, you are asking for 8192k (8 meg) of space, not 8k.

bookmark review | bottom | topreader  december 26, 2001

reviewer:  reader  from usa

tom,i am sorry, i did try 8k and got this ora-1653sql> alter table ts1 allocate extent (size 8k);alter table ts1 allocate extent (size 8k)*error at line 1:ora-01653: unable to extend table sys.ts1 by 1 in tablespace ts1i have found the 1st block seemed to have been used by os, not sure if this is platform specific (silicon graphics)thanks

followup:

what is the create tablespace command you used (and why are we losing sleep over 1 block)

bookmark review | bottom | topreader  december 26, 2001

reviewer:  reader  from usa

tom,create tablespace command:create tablespace ts1 datafile /u07/oradata/iiim/ts1.dbf size 24k;create table ts1 storage(initial 8k next 8k pctincrease 0) tablespace ts1;this is purely of academic interest. ofcourse, we do not need to spendtoo much time on this. i agreealthough, if the datafile is for example 1000m and thesegment in the tablespace is initial 500m next 500m,since 1 block (8/1024m) is used for whatever reason other thandatabase extents, there is fragmentation induced; 500m that canbe allocated for segments and 499.99m gets unusable; unlesssize the datafile to be 1001m to start with.thanks

followup:

yes, the first block of a file in a dictionary managed tablespace is used by the system (us, oracle)just like the first 64k of a locally managed tablespaces.additionally, on my system we allocated 32k for the datafiles — not 24k. the following shows what you ask for — what you get and how much is usable by you (i would highly recomment lmts btw — avoid dmts):ops$tkyte@ora817dev.us.oracle.com> create tablespace ts1 datafile /tmp/ts1.dbf size 24k reuse;tablespace created.ops$tkyte@ora817dev.us.oracle.com> host ls -l /tmp/ts1.dbf-rw-r—– 1 ora817 32768 dec 26 13:33 /tmp/ts1.dbfops$tkyte@ora817dev.us.oracle.com> @free 1 maxposs maxtablespace name kbytes used free used largest kbytes used—————- ——- ———- ——— —— ——- ——– ——…..ts1 24 8 16 33.3 16 0 .0 ——- ———- ———sum 3,818,848 1,605,144 2,213,70413 rows selected.see, 24k in size — 8 is used, 16 free…ops$tkyte@ora817dev.us.oracle.com> alter tablespace ts1 add datafile /tmp/ts2.dbf size 24k reuse;tablespace altered.ops$tkyte@ora817dev.us.oracle.com> host ls -l /tmp/ts2.dbf-rw-r—– 1 ora817 32768 dec 26 13:33 /tmp/ts2.dbfops$tkyte@ora817dev.us.oracle.com> @free 1maxposs maxtablespace name kbytes used free used largest kbytes used—————- ——- ——— ——— —— ——– ——– ——ts1 48 16 32 33.3 16 0 .0 ——- ——— ———sum 3,818,872 1,605,152 2,213,72013 rows selected.now, 48k (24*2), 16k used (1 block / file )ops$tkyte@ora817dev.us.oracle.com>

bookmark review | bottom | topextremely useful  december 26, 2001

reviewer:  reader  from usa

tom,thanks very muchi do plan on using lmt in new databases

bookmark review | bottom | top  december 27, 2001

reviewer:  dixit  from nj,usa

very good

bookmark review | bottom | topwow !  march 20, 2002

reviewer:  mini  from oh

exactly what i was looking for.thank you so much tom

bookmark review | bottom | tophow the extents are allocated in bytes  april 30, 2002

reviewer:  santosh jadhav  from india

it was a very good explanation tom. but i have one doubt ops$tkyte@8i> select extent_id, bytes, blocks 2 from user_extents 3 where segment_name = t 4 and segment_type = table 5 / extent_id bytes blocks———- ———- ———- 2 40960 5 3 81920 10 4 57344 7 0 40960 5 1 40960 5 ———-sum 32this is what after creating table t with 40k initial and next extent. so why it is 81920 (3rd extent) and 57344(4th extent). and why 7 and 10 blocks are allocated respectively

followup:

it is because i created the table in a dictionary managed tablespace and we allocate things to within +- 5 blocks (to avoid fragmenting the tablespace freespace totally)read

http://download-west.oracle.com/docs/cd/a87860_01/doc/server.817/a76965/c02block.htm#2846

it covers the algorithm.

bookmark review | bottom | topgood stuff  september 16, 2002

reviewer:  doug  from ct, usa

bookmark review | bottom | topsystem block is always 1 ?  january 09, 2003

reviewer:  tony  from india

does oracle allocate only one system block even for very big table?

followup:

at least one, it might get more later. depends on block size and number of extents.

bookmark review | bottom | topwhat auto segment space management tss?  march 20, 2003

reviewer:  matt  from australia

what should the value of freelist_group_id be in each call to dbms_space.free_blocks? you default your value to 1, should this be 1 for tables in all cases?i just executed dbms_space.free_blocks for a segment in a locally managed ts with auto segment space managementand got the following error:10618, 00000, “operation not allowed on this segment”// *cause: this dbms_space operation is not permitted on segments in// tablespaces with auto segment space management// *action: recheck the segment name and type and re-issue the statementhow do you identify free blocks in this case?

followup:

i just assumed one freelist. if you have more, this routine is not “sophisticated enough”there is a dbms_space.space_usage routine for assm

bookmark review | bottom | topyour show_space procedure and partitoned tables  may 06, 2003

reviewer:  pingu

hii am trying to use your show_space procedure but it seems that it does not work with partitioned tables?i think the cursorfor x in ( select tablespace_name from dba_tablespaces where tablespace_name = ( select tablespace_name from dba_segments where segment_type = p_type and segment_name = p_segname and segment_space_management <> auto )the subquery should we add distinct?

followup:

go for it. i adjust it as i need.

bookmark review | bottom | topafter a closer look…  may 06, 2003

reviewer:  pingu

hii had a closer look and i dont understand why we need the for loopfor x in ( select tablespace_name from dba_tablespaces where tablespace_name in ( select distinct tablespace_name from dba_segments where segment_type = p_type and segment_name = p_segname and segment_space_management <> auto ) ) loop dbms_space.free_blocks ( segment_owner => p_owner, segment_name => p_segname, segment_type => p_type, partition_name => p_partition, freelist_group_id => 0, free_blks => l_free_blks ); end loop;i think we dont use anything from the loop…..? or iterating for anything

followup:

it only calls free blocks if the segment space management is not auto and the object exists.just add a “and rownum = 1” to the query. lose the distinct, it is not relevant.

bookmark review | bottom | top  may 19, 2003

reviewer:  a reader

hi tom, i am getting error when i use show_space proceduresql> exec show_space(t1);error ora-10618: operation not allowed on this segment -10618begin show_space(t1); end;*error at line 1:ora-10618: operation not allowed on this segmentora-06512: at “gaurang.show_space”, line 49ora-06512: at line 1thanks

followup:

having no clue what t1 is, i have no comment.

bookmark review | bottom | top  may 20, 2003

reviewer:  a reader

t1 is table .i am using oracle 9ir2

followup:

sorry, maybe if you show us the entire thing — are you using the script that does auto segment space management “show space”, etc…have you read about the dbms_* packages i use (they are documented). it is pretty straightforward stuff. works for me in 9iops$tkyte@ora920> l 1 create or replace procedure show_space 2 ( p_segname in varchar2, 3 p_owner in varchar2 default user, 4 p_type in varchar2 default table, 5 p_partition in varchar2 default null ) 6 authid current_user 7 as 8 l_free_blks number; 9 10 l_total_blocks number; 11 l_total_bytes number; 12 l_unused_blocks number; 13 l_unused_bytes number; 14 l_lastusedextfileid number; 15 l_lastusedextblockid number; 16 l_last_used_block number; 17 procedure p( p_label in varchar2, p_num in number ) 18 is 19 begin 20 dbms_output.put_line( rpad(p_label,40,.) || 21 p_num ); 22 end; 23 begin 24 for x in ( select tablespace_name 25 from dba_tablespaces 26 where tablespace_name = ( select tablespace_name 27 from dba_segments 28 where segment_type = p_type 29 and segment_name = p_segname 30 and owner = p_owner 31 and segment_space_management <> auto ) 32 ) 33 loop 34 dbms_space.free_blocks 35 ( segment_owner => p_owner, 36 segment_name => p_segname, 37 segment_type => p_type, 38 partition_name => p_partition, 39 freelist_group_id => 0, 40 free_blks => l_free_blks ); 41 dbms_output.put_line( old fashioned ); 42 end loop; 43 44 dbms_space.unused_space 45 ( segment_owner => p_owner, 46 segment_name => p_segname, 47 segment_type => p_type, 48 partition_name => p_partition, 49 total_blocks => l_total_blocks, 50 total_bytes => l_total_bytes, 51 unused_blocks => l_unused_blocks, 52 unused_bytes => l_unused_bytes, 53 last_used_extent_file_id => l_lastusedextfileid, 54 last_used_extent_block_id => l_lastusedextblockid, 55 last_used_block => l_last_used_block ); 56 57 p( free blocks, l_free_blks ); 58 p( total blocks, l_total_blocks ); 59 p( total bytes, l_total_bytes ); 60 p( total mbytes, trunc(l_total_bytes/1024/1024) ); 61 p( unused blocks, l_unused_blocks ); 62 p( unused bytes, l_unused_bytes ); 63 p( last used ext fileid, l_lastusedextfileid ); 64 p( last used ext blockid, l_lastusedextblockid ); 65 p( last used block, l_last_used_block ); 66* end;ops$tkyte@ora920> drop table t;table dropped.ops$tkyte@ora920> create table t ( x int );table created.ops$tkyte@ora920> exec show_space(t)free blocks………………………..total blocks……………………….8total bytes………………………..65536total mbytes……………………….0unused blocks………………………5unused bytes……………………….40960last used ext fileid………………..9last used ext blockid……………….48last used block…………………….3pl/sql procedure successfully completed.

bookmark review | bottom | topfree block vs performance  june 12, 2003

reviewer:  raghu  from milwaukee, wi usa

hi tomwhat is the impact of the large high water mark or free blocks or unused block on the table performacne (insert/update/select). i have a case in my environment where a batch takes 9 minutes in one instance (edev) amd ot takes 2 hrs in another (eprod). the outputs from yr procedure and from all_tables is given below for both the instance.edevfree blocks 4total blocks 1984total bytes 16252928unused blocks 1unused bytes 8192last used ext fileid 112last used ext blockid 5193last used block 15eprodfree blocks 20total blocks 2352total bytes 19267584unused blocks 11unused bytes 90112last used ext fileid 346last used ext blockid 3065last used block 5blocks empty_blocks avg_space num_freelist_blocks2340 6 4462 2103select blocks, empty_blocks, avg_space, num_freelist_blocks from user_tables where table_name = backlog_itemblocks empty_blocks avg_space num_freelist_blocks3956 11 4570 20042. if my process does not prevent me to issue a truncate statement, but my batch involves lots of deletes and insert, then how can i prevent large values for high water mark?. do we have any utility/script that we can run to reduce the high water mark of a table.

followup:

1) both of those tables are so small, if i had a “batch” that took 9 minutes to process 20meg of data, id be concerned about that in itself. that is probably 8 minutes and 30 seconds longer then it should be.use tkprof and sql_trace to find your low hanging fruit. 2) you have a very very small table, i think you are looking in the wrong place here. you have most likely a bad algorithm. you need to diagnose where the problem is, then fix it. not “fix something” and see if it was the right thing.

bookmark review | bottom | topthanks  june 13, 2003

reviewer:  raghu  from milwaukee, wi usa

hi tom,thanks for your response. i absolutely agree with you that the performance of the batch i had given in my example could be improved by changing the algorithm. i am sorry that my example diverted you from the “real” reason for my comment.i was wondering how i can make use of the output of your query in my db maintenenace. for instance. is there a co-relation between the number of free blocks and performance.in my original example, can i attribute the difference in batch performance between two instance to number of free blocks?(again i accept that the performance could be improved further).another example, i have a table that has around 6 million rows. the average row length is 1250 bytes. we do lots of “delete/insert/update” on this table. we recently re-partitioned this table (using export, create table and import). we noticed a huge performance changes for selects..over the period of time, it performance is going back to the “normal”. can i attribute this to free blocks and do you think that we should constantly look for such blocks and “clean it” and how can we do it?. note: at this time, i am trying to find the root cause for the performance changes. when i read this article of yours i felt that i might have discovered “one” of the casues and wanted a confirmation from you. i also like to point out that the db are very similar in terms of size, parameter etc. the code is also same.

followup:

there could be — but in this case, the numbers are far too small. i thought it was clear that i do not think that the small number of blocks you have on freelists here would have any material affect on anything….no, you cannot attribute anything to anything here. there is insufficient data to say anything reasonable.you dont say which way the performance change was, what type of operations you do on this table, nothing. again, totally insufficient data to say anything about anything (except hypothesize, which i dont like doing too much). now, if you had some metrics (tkprofs!!! statspacks!!!) – hard numerical, incontravertible data points that would provide information as to the number of consistent gets, query plans, inputs, etc over time — then, well, then wed be cooking with gas…(but, the performance of queries is unaffected by the number of blocks on the freelist — i could set pctfree/pctused so that every block is on the freelist. i could set the pctfree/pctused so that no block is on the freelist. i could have both of these tables — one with every block on and other with every block off — organized in such a way that block for block they are in effect “the same”. they would perform identically. it is nothing to do with how many blocks are on the freelist (although you may be able to use that as an indicator if you understand how pctfree/pctused are set and how they are used). it has everything to do with how many blocks oracle must process in order to answer your queries.you need a tkprof. you need a tkprof of this “batch” on dev, of this “batch” on prod. you need to compare them. thatll get you 90% of your way along in your root cause analysis.

bookmark review | bottom | topdear tom, what could be wrong here?  july 24, 2003

reviewer:  saminathan seerangan  from nj,usa

tom,i am not able to use show_space procedure. please help me out.sql> create user a identified by a;user created.sql> grant create session, create procedure, 2 create table to a;grant succeeded.sql> alter user a quota unlimited on users;user altered.sql> connect a/aconnected.sql> @d: hare\oracle\asktom how_space.sql 51 /procedure created.sql> create table t ( x int ) tablespace users;table created.sql> exec show_space( t )begin show_space( t ); end;*error at line 1:ora-10618: operation not allowed on this segmentora-06512: at “sys.dbms_space”, line 74ora-06512: at “a.show_space”, line 22ora-06512: at line 1sql> desc t name null? type ————————————- ——– ——– x number(38)sql> insert into t values(100);1 row created.sql> desc dbms_spaceprocedure free_blocks argument name type in/out default? —————————— ———————– —— ——– segment_owner varchar2 in segment_name varchar2 in segment_type varchar2 in freelist_group_id number in free_blks number out scan_limit number in default partition_name varchar2 in defaultprocedure space_usage argument name type in/out default? —————————— ———————– —— ——– segment_owner varchar2 in segment_name varchar2 in segment_type varchar2 in unformatted_blocks number out unformatted_bytes number out fs1_blocks number out fs1_bytes number out fs2_blocks number out fs2_bytes number out fs3_blocks number out fs3_bytes number out fs4_blocks number out fs4_bytes number out full_blocks number out full_bytes number out partition_name varchar2 in defaultprocedure unused_space argument name type in/out default? —————————— ———————– —— ——– segment_owner varchar2 in segment_name varchar2 in segment_type varchar2 in total_blocks number out total_bytes number out unused_blocks number out unused_bytes number out last_used_extent_file_id number out last_used_extent_block_id number out last_used_block number out partition_name varchar2 in defaultsql>

followup:

create or replace procedure show_space( p_segname in varchar2, p_owner in varchar2 default user, p_type in varchar2 default table, p_partition in varchar2 default null )– this procedure uses authid current user so it can query dba_*– views using privileges from a role and so it can be installed– once per database, instead of once per user that wanted to use itauthid current_useras l_free_blks number; l_total_blocks number; l_total_bytes number; l_unused_blocks number; l_unused_bytes number; l_lastusedextfileid number; l_lastusedextblockid number; l_last_used_block number; l_segment_space_mgmt varchar2(255); l_unformatted_blocks number; l_unformatted_bytes number; l_fs1_blocks number; l_fs1_bytes number; l_fs2_blocks number; l_fs2_bytes number; l_fs3_blocks number; l_fs3_bytes number; l_fs4_blocks number; l_fs4_bytes number; l_full_blocks number; l_full_bytes number; — inline procedure to print out numbers nicely formatted — with a simple label procedure p( p_label in varchar2, p_num in number ) is begin dbms_output.put_line( rpad(p_label,40,.) || to_char(p_num,999,999,999,999) ); end;begin — this query is executed dynamically in order to allow this procedure — to be created by a user who has access to dba_segments/tablespaces — via a role as is customary. — note: at runtime, the invoker must have access to these two — views! — this query determines if the object is a assm object or not begin execute immediate select ts.segment_space_management from dba_segments seg, dba_tablespaces ts where seg.segment_name = :p_segname and (:p_partition is null or seg.partition_name = :p_partition) and seg.owner = :p_owner and seg.tablespace_name = ts.tablespace_name into l_segment_space_mgmt using p_segname, p_partition, p_partition, p_owner; exception when too_many_rows then dbms_output.put_line ( this must be a partitioned table, use p_partition => ); return; end; — if the object is in an assm tablespace, we must use this api — call to get space information, else we use the free_blocks — api for the user managed segments if l_segment_space_mgmt = auto then dbms_space.space_usage ( p_owner, p_segname, p_type, l_unformatted_blocks, l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes, l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes, l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_full_bytes, p_partition); p( unformatted blocks , l_unformatted_blocks ); p( fs1 blocks (0-25) , l_fs1_blocks ); p( fs2 blocks (25-50) , l_fs2_blocks ); p( fs3 blocks (50-75) , l_fs3_blocks ); p( fs4 blocks (75-100), l_fs4_blocks ); p( full blocks , l_full_blocks ); else dbms_space.free_blocks( segment_owner => p_owner, segment_name => p_segname, segment_type => p_type, freelist_group_id => 0, free_blks => l_free_blks); p( free blocks, l_free_blks ); end if; — and then the unused space api call to get the rest of the — information dbms_space.unused_space ( segment_owner => p_owner, segment_name => p_segname, segment_type => p_type, partition_name => p_partition, total_blocks => l_total_blocks, total_bytes => l_total_bytes, unused_blocks => l_unused_blocks, unused_bytes => l_unused_bytes, last_used_extent_file_id => l_lastusedextfileid, last_used_extent_block_id => l_lastusedextblockid, last_used_block => l_last_used_block ); p( total blocks, l_total_blocks ); p( total bytes, l_total_bytes ); p( total mbytes, trunc(l_total_bytes/1024/1024) ); p( unused blocks, l_unused_blocks ); p( unused bytes, l_unused_bytes ); p( last used ext fileid, l_lastusedextfileid ); p( last used ext blockid, l_lastusedextblockid ); p( last used block, l_last_used_block );end;/try that one, you must be in 9i using assm with the older version from 8i — assm does not have freelists

bookmark review | bottom | topthank you so much  july 24, 2003

reviewer:  saminathan seerangan  from nj,usa

small typo in the above procedureline # 46 select ts.segment_space_management appears 2 times.

followup:

thanks — i fixed that up

bookmark review | bottom | topaccess to dba_* views needed  september 17, 2003

reviewer:  dusan  from czech republic

well, this is a bit frustrating! i used to use this excellent utility (show_space) at many client sites (8i), to investigate space issues. basically, every schema might use it! now, on 9i, i have to ask access for those views. isnt it possible to work around this?

followup:

instead of me querying the dba_views, you can pass in that information (what type of tablespace is it in)use the “8i” like version, just parameterize it to call the proper api

bookmark review | bottom | topseems it does not work for partitioned table  september 17, 2003

reviewer:  dusan  from czech rep.

problems appeared when i tried to use this utility for partitioned table. 1 select table_owner, partition_name 2 from all_tab_partitions 3 where table_owner=valasekd 4* and table_name=dv_all_objects_partsys@dv9ir2.us.oracle.com> /table_owner partition_name—————————— —————-valasekd p030915valasekd p030916valasekd pmax 1 select ts.segment_space_management 2 from dba_segments seg, dba_tablespaces ts 3 where seg.segment_name = dv_all_objects_part 4 and (pmax is null or 5 seg.partition_name = pmax) 6 and seg.owner = valasekd 7* and seg.tablespace_name = ts.tablespace_namesys@dv9ir2.us.oracle.com> /segmen——autosys@dv9ir2.us.oracle.com> edzapsáno soubor afiedt.buf 1* exec show_space(dv_all_objects_part, valasekd,table,pmax);sys@dv9ir2.us.oracle.com> exec show_space(dv_all_objects_part, valasekd,table,pmax);begin show_space(dv_all_objects_part, valasekd,table,pmax); end;*error na øádku 1:ora-03204: zadání typu segmentu by mìlo vyznaèovat dìlení na partitionora-06512: na “sys.dbms_space”, line 97ora-06512: na “sys.show_space”, line 64ora-06512: na line 1

bookmark review | bottom | topdifferent extent sizes  october 02, 2003

reviewer:  kailash  from east brunswick, nj

thanks for a wonderful response. however, in response to your first review, i am not clear as to why the dba_extents shows different extent sizes (ie. the number of blocks in the extent are 5,5,5,10,7 a total of 32 extents). in this example, we did not specify any pctincrease. so my understanding is that all extents must be of equal size and 1 of the extents must have less space compared to other extents because of the 1 insert. however, the query shows a different result. i would appreciate if you could explain me how dba_extents calculated the above mentioned extent sizes.

followup:

did you read the followup i posted with a link to the docs?when you ask for 40k in a dictionary managed tablespace and we find a free extent that is within 5 blocks of your request (eg: 40k = 5 * 8k blocks — anything that is free and has 5, 6, 7, 8, 9, 10 blocks will be used). this is to avoid the situation whereby you have a free extent of say 7 blocks — you asked for 5, so we take 5 leaving “2 totally useless blocks” hanging out there.if the free extent was 11 blocks — we would have given you your 5 and left 6 out there for the next guy.otherwise you would end up with lots of 1/2/3 block free extents that couldnt be used for anything.

bookmark review | bottom | topmore explanation please  june 01, 2004

reviewer:  parag jayanat patankar  from india

hi tom,thanks for explaining this thing in great way. i have done analysis on my table and found following11:21:50 test@prod> exec show_space(v9spd140);free blocks………………………..4051total blocks……………………….7418total bytes………………………..30384128unused blocks………………………0unused bytes……………………….0last used ext fileid………………..5last used ext blockid……………….89500last used block…………………….3i am confused with above data as it is not matching with your formula as explained in this thread :-ops$tkyte@8i> exec show_space( t )free blocks………………………..19total blocks……………………….32total bytes………………………..262144unused blocks………………………12unused bytes……………………….98304last used ext fileid………………..1last used ext blockid……………….64681last used block…………………….5pl/sql procedure successfully completed.the above shows that the delete simply put all of our blocks on the free list. we have 19 blocks on the free list + 12 unused blocks + 1 system block = 32 blocks.in my datatotal blocks are : 7418 = blocks on free list : 4051 + unused blocks : 0 + system block : 1then where are other blocks gone ? total blocks should be matched as per your formula kindly explain as i have missed out some imp part.thanks & regardsparag jayant patankar

followup:

the other blocks have data on them and just are not on the freelist (not available for inserts)

bookmark review | bottom | topits amazing what you find when you actually read the documentation .. 😉  july 13, 2004

reviewer:  greg  from toronto, ontario

funny that … i was having trouble with some privilege rights on this dbms_space package and pulling my hair out .. i finally come here and do a search …. do i find my problem conveniently answered by tom?no.what do i get instead: “have you read about the dbms_* packages i use (they are documented). it is pretty straightforward stuff.”… right, so a slap in the face, and the answer sitting right under my nose in the docs … hehe*sigh* … thanks, tom!! .. i needed that!!:)

bookmark review | bottom | topgood explanation  august 02, 2004

reviewer:  cyrille  from france

thanks for all your explanation.

bookmark review | bottom | tophwm below or above?  august 26, 2004

reviewer:  rd  from nz

hi tom,at the beginning of the thread you said”truncate it. that puts all of the blocks below the hwm. now we have 31 unused blocks + 1 system block = 32 blocks total. none on the free list since none of them have any data.”should that have been “that puts all of the blocks above the hwm”cos truncate will always lower the hwm or am i wrong??thanks,regards,rd.

followup:

correct, the hwm would not even “exist” really.

bookmark review | bottom | topfree space  august 30, 2004

reviewer:  nazmul hoque  from bangladesh

dear tom,i need your help, i am useing oracle 8.1.7, i have three table space in my data base size are 38 mb, 204 mb & 51 mb, but my database useing space from my server more than 3 gb.and file size temp01.dbf is 1.19 gb and users01.dbf file is1.17 gb.my idea this is abnormal, please help me to free space from my server.thanks/hoque

followup:

do you have a dba you can work with? you control the size of temp, that is entirely up to you. if you dont like temp being so large, drop it and recreate it.search this site for maxshrink, itll tell you if you can shrink your users01.dbf file.it is normal for a database to consume space. you have indexes, you have free space in there. 3gig is tiny.

bookmark review | bottom | topquestions  august 30, 2004

reviewer:  a reader

1. why do you have the exception when others then null in your code above? you are very much against hiding exceptions, arent you?2. i understand the authid current_user part so that it can be installed once per database and each calling user doesnt get any extra privileges due to it being definers right.but how does authid current_user address “this procedure uses authid current user so it can query dba_* views using privileges from a role”3. “– this query is executed dynamically in order to allow this procedure to be created by a user who has access to dba_segments/tablespaces via a role as is customary”instead, why not simply use the all_* views so that the execute immediate is not needed at all? every user calling this proc automatically sees what they are supposed to see i.e. the all_* views do all the heavy lifting instead of us?4. what benefit is pipelined giving us in the 9ir2 version of this utility?5. why is the pragma autonomous_transaction needed?thanks

followup:

1) because here i have a simple report, rather then blow up part way thru on an object that cannot be “free space reported” on i skip it.2) l_sql := select owner, segment_name, segment_type, partition_name from dba_segments ; add_predicate( segment_name, p_segname ); add_predicate( owner, p_owner ); add_predicate( segment_type, p_type ); add_predicate( partition, p_partition ); execute immediate alter session set cursor_sharing=force; open l_cursor for l_sql; execute immediate alter session set cursor_sharing=exact;i used dynamic sql — so the creator of the procedure need not have dba_* access, just the invoker of the procedure. and they can use the privs from a role since roles can be in place during the execution of an invokers rights routine.3) because this was written to be a dba_ utility? dba_ views have no filters on them.4) more efficient, search for pipelined for many examples5) it isnt, dont know what i was thinking leaving it in.

bookmark review | bottom | topfree space (table space)  august 31, 2004

reviewer:  nazmul hoque  from dhaka – bangladesh

** do you have a dba you can work with? i am the programmer and dba both !!!**** you control the size of temp, that is entirely up to you. if you dont like temp being so large, drop it and recreate it.please advise me how i drop and rrcreate, temp is created by the stystem, if i dtop that, is it create any problem ?*** search this site for maxshrink, itll tell you if you can shrink your users01.dbf file.please write with details…..*** it is normal for a database to consume space. you have indexes, you have free space in there. 3gig is tiny. in my server now only 400 mb is free on that drive.please help me…. thanksnazmul hoque

followup:

dba and programmer both is going to be a problem. this is straight forward dba stuff really. to downsize your temp, create new:ops$tkyte@ora9ir2> create temporary tablespace temp tempfile size 100m;tablespace created.and create new ops$tkyte@ora9ir2> drop tablespace temp2;tablespace dropped. the details on maxshrink are as follows:search this site for maxshrink, read the first couple of pages you find. perhaps you need “more disk”.

bookmark review | bottom | topdefine  september 02, 2004

reviewer:  abc

tomplease provide single line definitio of hwm

followup:

high water mark — the biggest the table ever was, hwms are pushed “up” if you fill up a table with 1,000 blocks of data and then delete all of the data — there will still be 1,000 blocks of “data” there that would be full scanned.

bookmark review | bottom | toptemp tablespace create and drop  september 02, 2004

reviewer:  nazmul hoque  from bangladesh, dhaka

dba and programmer both is going to be a problem. this is straight forward dba stuff really. — no other optionto downsize your temp, create new:ops$tkyte@ora9ir2> create temporary tablespace temp tempfile size 100m;tablespace created.and create new — getting below message :sql> create temporary tablespace temp tempfile size 100m;create temporary tablespace temp tempfile size 100m *error at line 1:ora-02236: invalid file name**** please help me ****ops$tkyte@ora9ir2> drop tablespace temp2;tablespace dropped. the details on maxshrink are as follows:search this site for maxshrink, read the first couple of pages you find. perhaps you need “more disk”. *** we are using server compaq proliant 1600, now a days it so difficault to add hdd at my end.**** dear tom,i know how create table space and drop tabel space from sql and from dba studio both, my problem is temp tablespace, i never create temp tablespace or drop before. so far i have seen it is created by the system at time of installation. if i drop and recreate temp with 100 mb space my problem will be solved, please give some more trips to drop and creat temp tablespace. please please please..thanksnazmul hoque

followup:

youll need to refer to your sql reference, i cannot (will not) be your dba.you are using 817, you didnt have oracle managed files way back then. you need to name your tempfile. the sql reference (available on otn.oracle.com) will give you the syntax.

bookmark review | bottom | toptemporary tablespace  september 03, 2004

reviewer:  nazmul hoque  from bangladesh

my tablespace list as below :================================tablespace initial_extent next_extent min_extents max_extents pct_increase min_extlen status ———- ————– ———– ———– ———– ———— ——————system 65536 65536 1 2.147e+09 50 65536 online rbs 524288 524288 8 4096 50 524288 online users 131072 131072 1 4096 0 131072 online temp 65536 65536 1 0 65536 online tools 32768 32768 1 4096 0 32768 online indx 131072 131072 1 4096 0 131072 online drsys 65536 65536 1 2.147e+09 50 65536 online dhaka 40960 40960 1 505 50 0 online ssldata 40960 40960 1 505 50 0 online ================================normaly we use below command from sql to create a tablespace (path mentioned as per our server location) :create tablespace test datafile d:\oracle\oradata\ntserver\data1.dat size 100mautoextend on next 10mshall i use below for temporary tablespace==========================================create temporary tablespace tempdatafile d:\oracle\oradata\ntserver\temp01.dbf size 100mautoextend on next 5mto drop tabelspace :drop tablespace test;for temporary tablespace:========================drop temporary tablespace temp;dear tom, please advise me, i am doing wrong or right. if wrong than please write only two lines to correct me.thanksnamzul hoque

followup:

create temporary tablespace temp tempfile ……. …..^^^^^^^^ tempfile, not datafile. documentation is very useful for this stuff

http://download-west.oracle.com/docs/cd/a87860_01/doc/server.817/a85397/statem5e.htm#2063472

(hint, hit the shiftlock key…)