| 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
Initial is different from next. In LMT tablespace, initial will be
breakdown into several extents if it is larger than the
autoallocate/uniform size.
So they are different. Document says that, and you proves that.
If you checkk the next , then just re-do my test. Next_extent decide whether your application will continue to run without reporting errors, as the original requester needs, keep the avalibility of the database.
-- Brian Peasland wrote:Received on Mon Jul 31 2006 - 19:04:34 CDT
> > 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:
>
> -- ts_map.sql
> -- by Brian Peasland
> -- 06 March 2000
> --
> -- This script shows a "map" of database block allocations
> -- for a tablespace. This script can be used to examine
> -- free space fragmentation among other issues.
> --
>
> 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
> WHERE tablespace_name = UPPER('&ts')
> UNION
> SELECT tablespace_name as tablespace,
> file_id,
> block_id,
> blocks,
> '<free>' as segment
> FROM dba_free_space
> WHERE tablespace_name = UPPER('&ts');
>
> 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
> ------------------------------
> DATA_LOAD1
> SDE
> SYSAUX
> SYSTEM
> TEMP
> TEST_TS
> UNDOTBS1
> USERS
>
> 8 rows selected.
>
> 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" - Unknown
 |
 |