Next Extent for Tablespace

[peterx]

hi Tim,

I saw a oracle doc. claim that:

Code: Select all

select s.owner, s.segment_name, s.segment_type,
s.tablespace_name, s.next_extent
from dba_segments s
where s.next_extent > (select MAX(f.bytes)
from dba_free_space f
where f.tablespace_name = s.tablespace_name);


We can get the segments that are unable to allocate their next extent

But, when i created a Tablespace and create some records to use up 100% of this tablespace with a table
until can't insert.

And I try to run the above sql. It get no return value.

And i found that, no any records related to my new created tablespace inside "dba_free_space".

The question i want to know is......

Is any tablespace will have record(s) inside dba_free_space to reflect the free extent?

thanks.

[Tim...]

Hi.

Here's a test case.

Code: Select all

create tablespace tim_test datafile 'C:\APP\ORACLE\ORADATA\DB11G\tim_test.dbf' size 10M;
create table tim_test_tab (data VARCHAR2(4000)) TABLESPACE tim_test;

BEGIN
  LOOP
    INSERT INTO tim_test_tab VALUES (RPAD('X', 4000, 'X'));
    COMMIT;
  END LOOP;
END;
/

Now the tbale is full, is it listed?

Code: Select all

select s.owner, s.segment_name, s.segment_type,
s.tablespace_name, s.next_extent
from dba_segments s
where s.next_extent > (select MAX(f.bytes)
from dba_free_space f
where f.tablespace_name = s.tablespace_name);

OWNER                          SEGMENT_NAME                                                                      SEGMENT_TYPE       TABLESPACE_NAME                NEXT_EXTENT
------------------------------ --------------------------------------------------------------------------------- ------------------ ------------------------------ -----------
SYS                            TIM_TEST_TAB                                                                      TABLE              TIM_TEST                           1048576
.
.
.

That seems to have worked.

If I truncate it, then it stops being in the list.

Cheers

Tim...

[peterx]

hi Tim,

Please see my result

Code: Select all

SQL>
SQL> CREATE SMALLFILE TABLESPACE "TEST_TS_01" DATAFILE '+DATA/tmsqa/datafile/TEST_TS_01.dbf' SIZE 10M;

CREATE TABLE TEST_TAB (data VARCHAR2(4000)) TABLESPACE TEST_TS_01;

Tablespace created.

SQL> SQL>
Table created.

SQL>
SQL>
SQL> select s.owner, s.segment_name, s.segment_type,
s.tablespace_name, s.next_extent
from dba_segments s
where s.next_extent > (select MAX(f.bytes)
from dba_free_space f
where f.tablespace_name = s.tablespace_name);  2    3    4    5    6 

no rows selected

SQL>
SQL> BEGIN
  LOOP
    INSERT INTO TEST_TAB VALUES (RPAD('X', 4000, 'X'));
    COMMIT;
  END LOOP;
END;
/  2    3    4    5    6    7 
BEGIN
*
ERROR at line 1:
ORA-01653: unable to extend table SYS.TEST_TAB by 128 in tablespace TEST_TS_01
ORA-06512: at line 3


SQL>
SQL> select s.owner, s.segment_name, s.segment_type,
s.tablespace_name, s.next_extent
from dba_segments s
where s.next_extent > (select MAX(f.bytes)
from dba_free_space f
where f.tablespace_name = s.tablespace_name);  2    3    4    5    6 

no rows selected

SQL>


Do you think it is related to ASM.
very thanks for your help.

[Tim...]

Hi.

I love this one. It is one of the classic problems with deferred segment creation.

http://www.oracle-base.com/articles/11g ... -11gr2.php

You've create the tablespace and not given yourself a quota on it. Prior to 11g you would get an error when creating the table, as at least one segment would get created. In 11g, segments are not created until data is inserted, so the table creation complete successfully, fooling you into thinking you have a quota. It's only when you try to insert into the table you notice the issue.

Give yourself a quota on the tablespace and try the insert again.

I did my test on a user with unlimited quota on everything, so I didn't get that issue. Sorry. I should have included the alter user in my example.

Code: Select all

ALTER USER ???? QUOTA UNLIMITED ON ????;

Cheers

Tim...

[peterx]

hi Tim,

very thanks for your information, learn a lots....again.

But I want to ask, that means, if i do want to use:

Code: Select all

select s.owner, s.segment_name, s.segment_type,
s.tablespace_name, s.next_extent
from dba_segments s
where s.next_extent > (select MAX(f.bytes)
from dba_free_space f
where f.tablespace_name = s.tablespace_name);


to detect the "about full tablespace", then i needed to set a quota to each user.

That means, the above sql is not checking tablespace full, it is checking the user can extent the TS or not ??

thanks

[Tim...]

Hi.

No. That is not what I am saying. I am saying the error you are getting may be caused by deferred segment creation, not by lack of space in the tablespace.

The query looks good to me. The error is a different matter.

Cheers

Tim...