1 2 Previous Next 15 Replies Latest reply: Mar 22, 2013 6:13 PM by jgarry RSS

    Why is my table here when it shouldn't be?

    Dird
      Hi guys,

      According to this Tom Kyte book (and subsequent googles when I couldn't match it) my create table shouldn't list anything in dba_segments until rows are inserted but I'm seeing them. Was this reverted in 11.2.0.3? I read that if deferred is set to true & compatailtiy was 11.2 then it would not generate segments without data.
      SQL> create table dirded (x varchar2(2)) tablespace users;
      
      SQL> select segment_name from dba_segments where segment_name='DIRDED';
      
      SEGMENT_NAME
      --------------------------------------------------------------------------------
      DIRDED
      
      SQL> show parameter DEFERRED_SEGMENT_CREATION
      
      NAME                                 TYPE          VALUE
      ------------------------------------ ---------------------------------
      deferred_segment_creation    boolean      TRUE
      
      SQL> show parameter compat
      
      NAME                                 TYPE           VALUE
      ------------------------------------ ---------------------------------
      compatible                           string       11.2.0
      Mike
        • 1. Re: Why is my table here when it shouldn't be?
          JohnWatson
          Did you do that while connected as sysdba?
          • 2. Re: Why is my table here when it shouldn't be?
            977635
            Actually, I think it will still be listed in dba_segments, but if you check the sum of the bytes, it will be zero.

            With that said, it might be more accurate to say that it will not create any "extents" without data.
            • 3. Re: Why is my table here when it shouldn't be?
              jgarry
              Not necessary to be sys:
              SQL> create table dirded (x varchar2(2)) tablespace users;
              
              Table created.
              
              SQL> select segment_name from dba_segments where segment_name='DIRDED'
              select segment_name from dba_segments where segment_name='DIRDED'
                                       *
              ERROR at line 1:
              ORA-00942: table or view does not exist
              
              
              SQL> edit
              Wrote file afiedt.buf
              
                1*              select segment_name from user_segments where segment_name='DIRDED'
              SQL> /
              
              SEGMENT_NAME
              --------------------------------------------------------------------
              
              DIRDED
              
              SQL> desc user_segments
               Name                                      Null?    Type
               ----------------------------------------- -------- ----------------
              
               SEGMENT_NAME                                       VARCHAR2(81)
               PARTITION_NAME                                     VARCHAR2(30)
               SEGMENT_TYPE                                       VARCHAR2(18)
               SEGMENT_SUBTYPE                                    VARCHAR2(10)
               TABLESPACE_NAME                                    VARCHAR2(30)
               BYTES                                              NUMBER
               BLOCKS                                             NUMBER
               EXTENTS                                            NUMBER
               INITIAL_EXTENT                                     NUMBER
               NEXT_EXTENT                                        NUMBER
               MIN_EXTENTS                                        NUMBER
               MAX_EXTENTS                                        NUMBER
               MAX_SIZE                                           NUMBER
               RETENTION                                          VARCHAR2(7)
               MINRETENTION                                       NUMBER
               PCT_INCREASE                                       NUMBER
               FREELISTS                                          NUMBER
               FREELIST_GROUPS                                    NUMBER
               BUFFER_POOL                                        VARCHAR2(7)
               FLASH_CACHE                                        VARCHAR2(7)
               CELL_FLASH_CACHE                                   VARCHAR2(7)
              
              SQL> edit
              Wrote file afiedt.buf
              
                1*              select segment_name,bytes from user_segments where segment_name='DIRDED'
              SQL> /
              
              SEGMENT_NAME
              --------------------------------------------------------------------
              
                   BYTES
              ----------
              DIRDED
                   65536
              
              
              SQL> show parameter deferred_segment_createion
              ORA-00942: table or view does not exist
              
              
              SQL> connect / as sysdba
              Connected.
              
              Instance HOSTNAME                       VER        SID
              -------- ------------------------------ ---------- ------
              OPID
              ----------------------------------------
              xe       CRP-XXXXX                      11.2.0.2.0 15
              26
              
              
              SQL> show parameter deferred_segment_creation
              
              NAME                                 TYPE        VALUE
              ------------------------------------ ----------- -------------------
              deferred_segment_creation            boolean     TRUE
              SQL> show parameter compat
              
              NAME                                 TYPE        VALUE
              ------------------------------------ ----------- -------------------
              compatible                           string      11.2.0.0.0
              plsql_v2_compatibility               boolean     FALSE
              SQL>
              • 4. Re: Why is my table here when it shouldn't be?
                Aman....
                Works as documented and expected in 11203 running on OEL 5.3
                SQL> conn aman/aman
                Connected.
                SQL> select * from V$version;
                
                BANNER
                --------------------------------------------------------------------------------
                Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
                PL/SQL Release 11.2.0.3.0 - Production
                CORE    11.2.0.3.0      Production
                TNS for Linux: Version 11.2.0.3.0 - Production
                NLSRTL Version 11.2.0.3.0 - Production
                
                SQL> create table amant (a number) tablespace users;
                
                Table created.
                
                SQL> show parameter deferred
                
                NAME                                 TYPE        VALUE
                ------------------------------------ ----------- ------------------------------
                deferred_segment_creation            boolean     TRUE
                SQL> select segment_name from user_segments where segment_name='AMANT';
                
                no rows selected
                
                SQL> select table_name from user_tables where table_name='AMANT';
                
                TABLE_NAME
                ------------------------------
                AMANT
                
                SQL> insert into amant values(1);
                
                1 row created.
                
                SQL> commit;
                
                Commit complete.
                
                SQL> select segment_name from user_segments where segment_name='AMANT';
                
                SEGMENT_NAME
                --------------------------------------------------------------------------------
                AMANT
                
                SQL>
                Aman....
                • 5. Re: Why is my table here when it shouldn't be?
                  jgarry
                  What's your compatible? Maybe the minor version is necessary.
                  • 6. Re: Why is my table here when it shouldn't be?
                    977635
                    I just ran the same test and the difference is if you are logged in as SYS or someone else.
                    BANNER
                    --------------------------------------------------------------------------------
                    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
                    PL/SQL Release 11.2.0.2.0 - Production
                    CORE    11.2.0.2.0      Production
                    TNS for Solaris: Version 11.2.0.2.0 - Production
                    NLSRTL Version 11.2.0.2.0 - Production
                    
                    SQL> select user from dual;
                    
                    USER
                    ------------
                    SYS
                    
                    1 row selected.
                    
                    SQL> create table t (x varchar2(2)) tablespace users;
                    
                    Table created.
                    
                    SQL> select segment_name, tablespace_name, bytes from dba_segments
                      2  where segment_name = 'T';
                    
                    SEGMENT_NAME      TABLESPACE_NAME                 BYTES
                    ----------------- -------------------- ----------------
                    T                 USERS                           65536
                    
                    1 row selected.
                    
                    SQL> sho parameter deferred_segment_creation
                    
                    NAME                        TYPE        VALUE
                    --------------------------- ----------- -----------------------
                    deferred_segment_creation   boolean     TRUE    <=======
                    SQL>
                    SQL> drop table t;
                    
                    Table dropped.
                    Now let's use another user (happens to have DBA role).
                    SQL> connect testuser
                    Enter password:
                    Connected.
                    
                    SQL> create table t (x varchar2(2)) tablespace users;
                    
                    Table created.
                    
                    SQL> select segment_name, tablespace_name, bytes from dba_segments
                      2  where segment_name = 'T';
                    
                    no rows selected   <<=======================  no segment created
                    
                    SQL> drop table t;
                    
                    Table dropped.
                    • 7. Re: Why is my table here when it shouldn't be?
                      Srini Chavali-Oracle
                      Check the restrictions in this MOS Doc

                      11.2 Database New Feature Deferred Segment Creation [Video] [ID 887962.1]

                      Same restrictions are here - http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_7002.htm#SQLRF54479

                      HTH
                      Srini

                      Edited by: Srini Chavali - added doc link
                      • 8. Re: Why is my table here when it shouldn't be?
                        Dird
                        Srini Chavali wrote:
                        Same restrictions are here - http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_7002.htm#SQLRF54479

                        HTH
                        Srini
                        We have a winner. A somewhat interesting note (?) you don't actually need commited rows in the table for the segment to persist:
                        SQL> create table dird.hi_ora_forums(x varchar2(2)) tablespace users;
                        
                        Table created.
                        
                        SQL> select segment_name from dba_segments where segment_name='HI_ORA_FORUMS';
                        
                        no rows selected
                        
                        SQL> insert into dird.hi_ora_forums values('s');
                        
                        1 row created.
                        
                        SQL> select segment_name from dba_segments where segment_name='HI_ORA_FORUMS';
                        
                        SEGMENT_NAME
                        --------------------------------------------------------------------------------
                        HI_ORA_FORUMS
                        
                        SQL> rollback;
                        
                        Rollback complete.
                        
                        SQL> select segment_name from dba_segments where segment_name='HI_ORA_FORUMS';
                        
                        SEGMENT_NAME
                        --------------------------------------------------------------------------------
                        HI_ORA_FORUMS
                        Mike
                        • 9. Re: Why is my table here when it shouldn't be?
                          Aman....
                          Well, that's understandable as once with the insert, the HWM is extended, with a rollback,it won't be cleared off. So the segment allocated would remain available.

                          Aman....
                          • 10. Re: Why is my table here when it shouldn't be?
                            rp0428
                            >
                            A somewhat interesting note (?) you don't actually need commited rows in the table for the segment to persist:
                            >
                            Well that makes sense doesn't it? Segment creation is DDL and doesn't get rollbacked.

                            Besides, once Oracle does the work of creating the segment what would be the point of dropping it again? It is going to be needed at some point or the table wouldn't exist.

                            In a multi-user environment there could be several users that insert data. Oracle certainly wouldn't wait until the last of them did a rollback and then drop the segment.
                            • 11. Re: Why is my table here when it shouldn't be?
                              977635
                              Thanks Srini. I agree as I proved it without knowing it. :-)

                              Also, I found that you can use truncate now to drop all the extents with the addition of the 'drop all storage' clause.

                              truncate table <tablename> drop all storage
                              • 12. Re: Why is my table here when it shouldn't be?
                                977635
                                Thanks Srini. I agree (now that i proved it to myself without reading it). :-)

                                Also, I found that you can use truncate now to drop all the extents with the addition of the 'drop all storage' clause.

                                truncate table <tablename> drop all storage;
                                • 13. Re: Why is my table here when it shouldn't be?
                                  jgarry
                                  I thought the fact that I couldn't see a dba view was a clue that the segment was created without being sys.
                                  • 14. Re: Why is my table here when it shouldn't be?
                                    marksmithusa
                                    It's a NINJA table.

                                    (Sorry, I couldn't resist. Please carry on...)
                                    1 2 Previous Next