6 Replies Latest reply on Jan 21, 2010 5:40 AM by 722733

    ORACLE SPACE ALLOCATION

    722733
      I am having a small doubt regarding oracle extent allocation, Will oracle allocate extents to table with no data?

      Edited by: user8710159 on Jan 20, 2010 8:17 PM
        • 1. Re: ORACLE SPACE ALLOCATION
          sb92075
          Will oracle allocate extents to table with no data?
          yes
          • 2. Re: ORACLE SPACE ALLOCATION
            722733
            Thanks for your reply sir, but I just created a table and inserted no data into it and queried DBA_SEGMENTS to find out the allocated extents but my queries give me that no rows are found. Its a 11g database. Please correct me if I am getting it wrong.

            create table sample(sno number,sname varchar2(100));


            select bytes,initial_extent,extents,blocks from dba_segments where segment_name='SAMPLE' and OWNER='SCOTT';

            no rows selected
            • 3. Re: ORACLE SPACE ALLOCATION
              sb92075
              Copyright (c) 1982, 2005, Oracle.  All rights reserved.
              
              Enter user-name: scott/tiger
              
              Connected to:
              Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
              With the Partitioning, OLAP and Data Mining options
              
              SQL> create table sample(sno number,sname varchar2(100));
              
              Table created.
              
              SQL> select bytes,initial_extent,extents,blocks from dba_segments where segment_name='SAMPLE' and OWNER='SCOTT';
              select bytes,initial_extent,extents,blocks from dba_segments where segment_name='SAMPLE' and OWNER='SCOTT'
                                                              *
              ERROR at line 1:
              ORA-00942: table or view does not exist
              
              
              SQL> connect dbadmin/<redacted>
              Connected.
              SQL> select bytes,initial_extent,extents,blocks from dba_segments where segment_name='SAMPLE' and OWNER='SCOTT';
              
                   BYTES INITIAL_EXTENT    EXTENTS      BLOCKS
              ---------- -------------- ---------- ----------
                   65536         65536        1           8
              • 4. Re: ORACLE SPACE ALLOCATION
                722733
                Thanks for your reply sir, but I get the following results which seems to be different so I am wondering is this feature new in 11g; Please correct me if I am wrong.

                SESSION:1(SCOTT)
                ---------------
                SQL> create table sample(sno number,sname varchar2(100));

                Table created.

                SQL>
                SESSION 2:(SYS)
                ----------------
                SQL> select bytes,initial_extent,extents,blocks from dba_segments where segment_name='SAMPLE' and OWNER='SCOTT';

                no rows selected

                SQL>

                SESSION:1(SCOTT)
                ---------------------------
                SQL> select * from sample;

                no rows selected

                SQL> insert into sample values(1,'ORACLE');

                1 row created.

                SQL>

                SESSION:2(SYS)
                -----------------------
                SQL> select bytes,initial_extent,extents,blocks from dba_segments where segment_name='SAMPLE' and OWNER='SCOTT';

                no rows selected

                SQL> /

                BYTES INITIAL_EXTENT EXTENTS BLOCKS
                ---------- -------------- ---------- ----------
                65536 65536 1 8

                SQL>

                Edited by: user8710159 on Jan 20, 2010 8:42 PM
                • 5. Re: ORACLE SPACE ALLOCATION
                  NavneetU
                  Hi,

                  As you are working in Oracle 11g, your result is correct....in 11g first extent is allocated when first row of data is inserted into the table.....

                  http://antognini.ch/2009/09/deferred-segment-creation/

                  Regards,
                  Navneet

                  Edited by: NKU on Jan 21, 2010 10:56 AM
                  1 person found this helpful
                  • 6. Re: ORACLE SPACE ALLOCATION
                    722733
                    Thanks for your reply sir, Yes I am using 11g.. thanks for the link...