| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: ORA-1654 , Oracle asking for very large extent
> Thank you. Seems I was wrong about the max-extent nolonger applies for
> lmt tablespace. Is this true for 9i as well? I don't have access ot
> unix now so can't test.
Yes, and it has been true ever since LMT's debuted.
> Can you do a :
> select bytes, count(*) from dba_extents where segment_name='YOUR BIG
> SEG' GROUP BY BYTES,
>
> See at which stage, it start to use 512M? The last time I did some test
> and I was unable to get to a larger size than 64M, with something like
> 500gb space; --data could be differnet, it was 3 years ago.
In my case, the object in question is part of the Data Dictionary:
SQL> select bytes,count(*) from dba_extents
2 where segment_name='C_OBJ#_INTCOL#' group by bytes order by 1;
BYTES COUNT(*)
------------------- ----------
16,384 2
24,576 1
40,960 1
81,920 1
122,880 1
163,840 1
245,760 1
368,640 1
532,480 1
778,240 1
1,146,880 1
1,720,320 1
2,580,480 1
3,891,200 1
5,816,320 1
8,724,480 1
13,107,200 1
19,660,800 1
29,491,200 1
44,195,840 1
66,281,472 1
99,426,304 1
149,143,552 1
223,715,328 1
335,577,088 1
503,365,632 1
-------------------
1,510,219,776
> Also, I believe for next_extent, oracle won't break it up. If there is
> no free extent >= next_extent, oracle fails. if you can test to prove I
> was wrong, I appreciate it. Can't test now.
That is incorrect. The following should prove the point (test run on 10.2.0.2 running on RHEL):
First, create a tablespace to be used for the test:
SQL> create tablespace test_ts
2 datafile '/edclxs69/orasata01/gasl/test_ts01.dbf'
3 size 1M
4 extent management local autoallocate;
Tablespace created.
Next, create some tables to allocate extents in the tablespace.
SQL> create table test1 (id number) tablespace test_ts;
Table created.
SQL> create table test2 (id number) tablespace test_ts;
Table created.
SQL> create table test3 (id number) tablespace test_ts;
Table created.
SQL> create table test4 (id number) tablespace test_ts;
Table created.
SQL> create table test5 (id number) tablespace test_ts;
Table created.
SQL> create table test6 (id number) tablespace test_ts;
Table created.
SQL> create table test7 (id number) tablespace test_ts;
Table created.
Now, let's look at a mapping of these extent in the tablespace. To do that, I'll use a canned script which I have had for many years. The canned script (ts_map.sql) shows a map of the tablespace extents. The script contents are as follows:
SET ECHO OFF
SET VERIFY OFF
SET PAGESIZE 40
SELECT tablespace_name FROM dba_tablespaces;
ACCEPT ts PROMPT 'Enter tablespace name: '
COLUMN tablespace FORMAT a15
COLUMN file_id FORMAT 990
COLUMN block_id FORMAT 9,999,990
COLUMN blocks FORMAT 999,990
COLUMN segment FORMAT a38
SELECT tablespace_name as tablespace,
file_id,
block_id,
blocks,
owner||'.'||segment_name as segment
FROM dba_extents
file_id,
block_id,
blocks,
'<free>' as segment
Now I'll use that script to show the map of the extents and where they lie in the tablespace:
SQL> @ts_map
TABLESPACE_NAME
Enter tablespace name: test_ts
TABLESPACE FILE_ID BLOCK_ID BLOCKS SEGMENT
------------- ------- ---------- -------- ---------------------------- TEST_TS 18 5 8 SYSTEM.TEST1 TEST_TS 18 13 8 SYSTEM.TEST2 TEST_TS 18 21 8 SYSTEM.TEST3 TEST_TS 18 29 8 SYSTEM.TEST4 TEST_TS 18 37 8 SYSTEM.TEST5 TEST_TS 18 45 8 SYSTEM.TEST6 TEST_TS 18 53 8 SYSTEM.TEST7 TEST_TS 18 61 4 <free>
8 rows selected.
I will now remove segments TEST2 and TEST4 to illustrate fragmented free space. I will show the output from my ts_map.sql script as well:
SQL> drop table test2 purge;
Table dropped.
SQL> drop table test4 purge;
Table dropped.
SQL> @ts_map
<snip>
TABLESPACE FILE_ID BLOCK_ID BLOCKS SEGMENT
--------------- ------- ---------- -------- ---------------------------- TEST_TS 18 5 8 SYSTEM.TEST1 TEST_TS 18 13 8 <free> TEST_TS 18 21 8 SYSTEM.TEST3 TEST_TS 18 29 8 <free> TEST_TS 18 37 8 SYSTEM.TEST5 TEST_TS 18 45 8 SYSTEM.TEST6 TEST_TS 18 53 8 SYSTEM.TEST7 TEST_TS 18 61 4 <free>
Notice the "holes" of free space. I will now create a new segment with INITIAL set to a value larger than any of these holes. My db block size is 16KB and the largest hole (as seen above) is 8 blocks, so if I use INITIAL larger than 128KB (16KB * 8 = 128KB) than we can see how LMT's allocate subextents when it does not have a contiguous chunk of space for the extent to be allocated. First, we'll create the table:
SQL> show parameter db_block_size
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_block_size integer 16384
SQL> create table frag_table (id number)
2 tablespace test_ts storage (initial 256k);
Table created.
Now, we'll verify the table's INITIAL extent in USER_SEGMENTS:
SQL> select initial_extent,extents from user_segments
2 where segment_name='FRAG_TABLE';
INITIAL_EXTENT EXTENTS
-------------- ----------
262144 3
Notice that the INITIAL extent value is 256K (256*1024 bytes = 262,144 bytes) but all of that is really 3 extents now!!! That's because Oracle could not find one chunk of free space large enough and it broke this down into what some of us have been calling "subextents". Let's see how Oracle broke this down inside the tablespace:
SQL> @ts_map
<snip>
TABLESPACE FILE_ID BLOCK_ID BLOCKS SEGMENT --------------- ------- ---------- --------
TEST_TS 18 5 8 SYSTEM.TEST1 TEST_TS 18 13 8 SYSTEM.FRAG_TABLE TEST_TS 18 21 8 SYSTEM.TEST3 TEST_TS 18 29 4 SYSTEM.FRAG_TABLE TEST_TS 18 33 4 SYSTEM.FRAG_TABLE TEST_TS 18 37 8 SYSTEM.TEST5 TEST_TS 18 45 8 SYSTEM.TEST6 TEST_TS 18 53 8 SYSTEM.TEST7 TEST_TS 18 61 4 <free>
Notice that FRAG_TABLE is now occupying the space in multiple chunks, or extents. Even though I only attempted to allocate the INITIAL extent for the tablespace. I've done this test for INITIAL, but the same holds true for the NEXT extent as well.
Cheers,
Brian
-- =================================================================== Brian Peasland dba_at_nospam.peasland.net http://www.peasland.net Remove the "nospam." from the email address to email me. "I can give it to you cheap, quick, and good. Now pick two out of the three" - UnknownReceived on Mon Jul 31 2006 - 15:31:49 CDT
 |
 |