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 doesn't 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.
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, we'll be able to get: Free Blocks...... Number of blocks on the freelist Total Blocks..... Total blocks allocated to the table Total Bytes...... Total bytes allocated to the table Unused Blocks.... Blocks that have never contained data Unused Bytes..... The above in bytes It 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 table ops$tkyte@8i> analyze table t compute statistics; Table analyzed. ops$tkyte@8i> compute sum of blocks on report ops$tkyte@8i> break on report 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 32 This shows that there are 32 blocks allocated in 5 extents to this table (as expected) ops$tkyte@8i> clear breaks ops$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 1 Since I analyzed the table, I have acccess to the above information. You'll 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.............................1 Total Blocks............................32 Total Bytes.............................262144 Unused Blocks...........................30 Unused Bytes............................245760 Last Used Ext FileId....................1 Last Used Ext BlockId...................64816 Last Used Block.........................2 PL/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 -- I'm 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 have o 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.............................3 Total Blocks............................32 Total Bytes.............................262144 Unused Blocks...........................12 Unused Bytes............................98304 Last Used Ext FileId....................1 Last Used Ext BlockId...................64681 Last Used Block.........................5 PL/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.............................19 Total Blocks............................32 Total Bytes.............................262144 Unused Blocks...........................12 Unused Bytes............................98304 Last Used Ext FileId....................1 Last Used Ext BlockId...................64681 Last Used Block.........................5 PL/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 don't 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.............................0 Total Blocks............................32 Total Bytes.............................262144 Unused Blocks...........................31 Unused Bytes............................253952 Last Used Ext FileId....................1 Last Used Ext BlockId...................64816 Last Used Block.........................1 PL/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 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Reviewer: spmurthy from Singapore Hi Tom, Thanks for your reply it is more useful to me to know the table space. Regards
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 don't you write a book :-) Regards BN
Reviewer: Helena Markova from Bratislava, Slovakia
Reviewer: D.C.L. from Seattle, USA Right on. Awesome grip of the subject matter.
Reviewer: Vikram from Delhi, India Excellent
Reviewer: k.v.s.Raju from Sydney, Australia Its excellent
Reviewer: Jim from MA Very, Very Good!!!
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 exist ORA-06512: at "SYS.DBMS_SPACE", line 55 ORA-06512: at "TOM.SHOW_SPACE", line 22 ORA-06512: at line 1 the 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 ? <script language=JavaScript1.1 type=text/javascript> document.getElementById("latestLink").style.display = "inline" </script>
Reviewer: Igor from France I don't understand how you knew it would be 32 blocks for one row of 2000 chars and number ?
<script language=JavaScript1.1 type=text/javascript> document.getElementById("latestLink").style.display = "inline" </script>
Reviewer: YK LIU from CA, USA
Reviewer: an from DE it's excellent!
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 on SQL> EXEC SHOW_SPACE('T'); Free Blocks.............................0 Total Blocks............................10 Total Bytes.............................81920 Unused Blocks...........................9 Unused Bytes............................73728 Last Used Ext FileId....................5 Last Used Ext BlockId...................126659 Last Used Block.........................1 PL/SQL procedure successfully completed. T_P -- is a partitioned table SQL> EXEC SHOW_SPACE('T_P'); BEGIN SHOW_SPACE('T_P'); END; * ERROR at line 1: ORA-14107: partition specification is required for a partitioned object ORA-06512: at "SYS.DBMS_SPACE", line 55 ORA-06512: at "myschema.SHOW_SPACE", line 22 ORA-06512: at line 1 Why Iam I running into this error while trying to use show_space on a partitioned table. <script language=JavaScript1.1 type=text/javascript> document.getElementById("latestLink").style.display = "inline" </script>
Reviewer: Reader from USA Tom, Could you clarify, why the *first block* of a datafile being used for the OS., 2nd for segment header, 3rd... for data. I created a table ts1 in tablespace ts1 , db_block_size 8k Results: SQL> set serveroutput on size 1000000 SQL> exec show_space('TS1','SYS','TABLE'); Free Blocks.............................1 Total Blocks............................2 Total Bytes.............................16384 Unused Blocks...........................0 Unused Bytes............................0 Last Used Ext FileId....................2 Last Used Ext BlockId...................2 Last Used Block.........................2 PL/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 TS1 SQL> 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 0 SQL> select TABLE_NAME,INITIAL_EXTENT,NEXT_EXTENT from dba_tables where TABLE_NAME = 'TS1'; TABLE_NAME INITIAL_EXTENT NEXT_EXTENT ------------------------------ -------------- ----------- TS1 16384 8192 SQL> 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 TS1 The datafile has 3 blocks, segment has used 2 blocks (including segment header). When I try to allocate one extent = 1 block, get ora-1653 Thanks
<script language=JavaScript1.1 type=text/javascript> document.getElementById("latestLink").style.display = "inline" </script>
Reviewer: Reader from USA Tom, I am sorry, I did try 8k and got this ora-1653 SQL> 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 TS1 I have found the 1st block seemed to have been used by OS, not sure if this is platform specific (Silicon Graphics) Thanks
<script language=JavaScript1.1 type=text/javascript> document.getElementById("latestLink").style.display = "inline" </script>
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 spend too much time on this. I agree Although, if the datafile is for example 1000M and the segment in the tablespace is initial 500M next 500M, since 1 block (8/1024M) is used for whatever reason other than database EXTENTS, there is fragmentation induced; 500M that can be allocated for segments and 499.99M gets unusable; unless size the datafile to be 1001M to start with. Thanks <script language=JavaScript1.1 type=text/javascript> document.getElementById("latestLink").style.display = "inline" </script>
Reviewer: Reader from USA Tom, Thanks very much I do plan on using LMT in new databases
Reviewer: Dixit from NJ,USA Very Good
Reviewer: Mini from OH Exactly what I was looking for. Thank you so much Tom
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 32 this 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
<script language=JavaScript1.1 type=text/javascript> document.getElementById("latestLink").style.display = "inline" </script>
Reviewer: Doug from CT, USA
Reviewer: Tony from India Does Oracle allocate only one system block even for very big table?
<script language=JavaScript1.1 type=text/javascript> document.getElementById("latestLink").style.display = "inline" </script>
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 MANAGEMENT and 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 statement How do you identify free blocks in this case?
<script language=JavaScript1.1 type=text/javascript> document.getElementById("latestLink").style.display = "inline" </script>
Reviewer: PINGU Hi I am trying to use your show_space procedure but it seems that it does not work with partitioned tables? I think the cursor for 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?
<script language=JavaScript1.1 type=text/javascript> document.getElementById("latestLink").style.display = "inline" </script>
Reviewer: PINGU Hi I had a closer look and I dont understand why we need the for loop for 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
<script language=JavaScript1.1 type=text/javascript> document.getElementById("latestLink").style.display = "inline" </script>
Reviewer: A reader Hi Tom, I am getting error when i use show_space procedure SQL> exec show_space('T1'); Error ORA-10618: Operation not allowed on this segment -10618 BEGIN show_space('T1'); END; * ERROR at line 1: ORA-10618: Operation not allowed on this segment ORA-06512: at "GAURANG.SHOW_SPACE", line 49 ORA-06512: at line 1 Thanks
<script language=JavaScript1.1 type=text/javascript> document.getElementById("latestLink").style.display = "inline" </script>
Reviewer: A reader T1 IS TABLE . I am using oracle 9iR2 <script language=JavaScript1.1 type=text/javascript> document.getElementById("latestLink").style.display = "inline" </script>
Reviewer: Raghu from Milwaukee, WI USA Hi Tom What 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. EDEV Free Blocks 4 Total Blocks 1984 Total Bytes 16252928 Unused Blocks 1 Unused Bytes 8192 Last Used Ext FileId 112 Last Used Ext BlockId 5193 Last Used Block 15 EPROD Free Blocks 20 Total Blocks 2352 Total Bytes 19267584 Unused Blocks 11 Unused Bytes 90112 Last Used Ext FileId 346 Last Used Ext BlockId 3065 Last Used Block 5 BLOCKS EMPTY_BLOCKS AVG_SPACE NUM_FREELIST_BLOCKS 2340 6 4462 2103 select blocks, empty_blocks, avg_space, num_freelist_blocks from user_tables where table_name = 'BACKLOG_ITEM' BLOCKS EMPTY_BLOCKS AVG_SPACE NUM_FREELIST_BLOCKS 3956 11 4570 2004 2. 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. <script language=JavaScript1.1 type=text/javascript> document.getElementById("latestLink").style.display = "inline" </script>
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. <script language=JavaScript1.1 type=text/javascript> document.getElementById("latestLink").style.display = "inline" </script>
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/a Connected. SQL> @D:/share/oracle/asktom/show_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 segment ORA-06512: at "SYS.DBMS_SPACE", line 74 ORA-06512: at "A.SHOW_SPACE", line 22 ORA-06512: at line 1 SQL> desc t Name Null? Type ------------------------------------- -------- -------- X NUMBER(38) SQL> insert into t values(100); 1 row created. SQL> desc dbms_space PROCEDURE 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 DEFAULT PROCEDURE 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 DEFAULT PROCEDURE 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 DEFAULT SQL> <script language=JavaScript1.1 type=text/javascript> document.getElementById("latestLink").style.display = "inline" </script>
Reviewer: Saminathan Seerangan from NJ,USA Small typo in the above procedure Line # 46 'select ts.segment_space_management appears 2 times.
<script language=JavaScript1.1 type=text/javascript> document.getElementById("latestLink").style.display = "inline" </script>
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. Isn't it possible to work around this?
<script language=JavaScript1.1 type=text/javascript> document.getElementById("latestLink").style.display = "inline" </script>
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_PART' sys@DV9IR2.US.ORACLE.COM> / TABLE_OWNER PARTITION_NAME ------------------------------ ---------------- VALASEKD P030915 VALASEKD P030916 VALASEKD 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_name sys@DV9IR2.US.ORACLE.COM> / SEGMEN ------ AUTO sys@DV9IR2.US.ORACLE.COM> ed Zapsá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 partition ORA-06512: na "SYS.DBMS_SPACE", line 97 ORA-06512: na "SYS.SHOW_SPACE", line 64 ORA-06512: na line 1
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. <script language=JavaScript1.1 type=text/javascript> document.getElementById("latestLink").style.display = "inline" </script>
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 following 11:21:50 test@prod> exec show_space('V9SPD140'); Free Blocks.............................4051 Total Blocks............................7418 Total Bytes.............................30384128 Unused Blocks...........................0 Unused Bytes............................0 Last Used Ext FileId....................5 Last Used Ext BlockId...................89500 Last Used Block.........................3 I 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.............................19 Total Blocks............................32 Total Bytes.............................262144 Unused Blocks...........................12 Unused Bytes............................98304 Last Used Ext FileId....................1 Last Used Ext BlockId...................64681 Last Used Block.........................5 PL/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 data Total Blocks are : 7418 = Blocks on free list : 4051 + Unused Blocks : 0 + System Block : 1 Then 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 & regards parag jayant patankar
<script language=JavaScript1.1 type=text/javascript> document.getElementById("latestLink").style.display = "inline" </script>
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!! :)
Reviewer: Cyrille from FRANCE Thanks for all your explanation.
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.
<script language=JavaScript1.1 type=text/javascript> document.getElementById("latestLink").style.display = "inline" </script>
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 is 1.17 GB. My idea this is abnormal, Please help me to free space from my server. Thanks/Hoque <script language=JavaScript1.1 type=text/javascript> document.getElementById("latestLink").style.display = "inline" </script>
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 definer's 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 <script language=JavaScript1.1 type=text/javascript> document.getElementById("latestLink").style.display = "inline" </script>
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 don't 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, it'll 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.... Thanks Nazmul Hoque <script language=JavaScript1.1 type=text/javascript> document.getElementById("latestLink").style.display = "inline" </script>
Reviewer: ABC Tom Please provide single line definitio of HWM <script language=JavaScript1.1 type=text/javascript> document.getElementById("latestLink").style.display = "inline" </script>
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 option to 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.. Thanks Nazmul Hoque <script language=JavaScript1.1 type=text/javascript> document.getElementById("latestLink").style.display = "inline" </script>
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 100M AUTOEXTEND ON NEXT 10M SHALL I USE BELOW FOR TEMPORARY TABLESPACE ========================================== CREATE TEMPORARY TABLESPACE TEMP DATAFILE 'D:/ORACLE/ORADATA/NTSERVER/TEMP01.DBF' SIZE 100M AUTOEXTEND ON NEXT 5M TO 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. THANKS NAMZUL HOQUE
<script language=JavaScript1.1 type=text/javascript> document.getElementById("latestLink").style.display = "inline" </script> |