This forum is currently locked. You can't register or post questions at this time. (read more)

Next Extent for Tablespace

All posts relating to Oracle database administration.

Moderator: Tim...

peterx
Senior Member
Posts: 208
Joined: Wed Feb 09, 2011 7:07 am

Next Extent for Tablespace

Postby peterx » Wed Feb 27, 2013 7:59 am

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Next Extent for Tablespace

Postby Tim... » Thu Feb 28, 2013 8:10 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

peterx
Senior Member
Posts: 208
Joined: Wed Feb 09, 2011 7:07 am

Re: Next Extent for Tablespace

Postby peterx » Thu Mar 07, 2013 8:34 am

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Next Extent for Tablespace

Postby Tim... » Thu Mar 07, 2013 8:45 am

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

peterx
Senior Member
Posts: 208
Joined: Wed Feb 09, 2011 7:07 am

Re: Next Extent for Tablespace

Postby peterx » Thu Mar 07, 2013 10:08 am

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Next Extent for Tablespace

Postby Tim... » Thu Mar 07, 2013 2:47 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 5 guests

cron