1 2 Previous Next 18 Replies Latest reply: Nov 13, 2012 10:48 AM by Mark Williams-Oracle RSS

    Getting ORA-01542: tablespace TS_TEST_DATA is offline

    user564785
      Current DB= 11gR1
      Current OS=Linux5

      When trying to split one large partition into several smaller partitions, I'm getting the following error. What I can't understand that the partition tables that I am alter has nothing does not reside on this tablespace. Any ideas?


      ERROR at line 1:
      ORA-01542: tablespace TS_TEST_DATA is offline, cannot allocate space
      in it
        • 1. Re: Getting ORA-01542: tablespace TS_TEST_DATA is offline
          sb92075
          user564785 wrote:
          Current DB= 11gR1
          Current OS=Linux5

          When trying to split one large partition into several smaller partitions, I'm getting the following error. What I can't understand that the partition tables that I am alter has nothing does not reside on this tablespace. Any ideas?


          ERROR at line 1:
          ORA-01542: tablespace TS_TEST_DATA is offline, cannot allocate space
          in it
          01542, 00000, "tablespace '%s' is offline, cannot allocate space in it"
          // *Cause: Tried to allocate space in an offline tablespace
          // *Action: Bring the tablespace online or create the object in other
          //          tablespace
          post results from SQL below
          SQL> select tablespace_name, status from dba_tablespaces;
          
          TABLESPACE_NAME                STATUS
          ------------------------------ ---------
          SYSTEM                         ONLINE
          SYSAUX                         ONLINE
          UNDOTBS1                       ONLINE
          TEMP                           ONLINE
          USERS                          ONLINE
          EXAMPLE                        ONLINE
          APEX_1930613455248703          ONLINE
          • 2. Re: Getting ORA-01542: tablespace TS_TEST_DATA is offline
            user564785
            TABLESPACE_NAME STATUS
            ------------------------------ ---------
            SYSTEM ONLINE
            SYSAUX ONLINE
            UNDOTBS1 ONLINE
            TEMP ONLINE
            USERS ONLINE
            TS_AA      ONLINE
            TS_DEV      ONLINE
            TS_TEST_DATA OFFLINE
            • 3. Re: Getting ORA-01542: tablespace TS_TEST_DATA is offline
              sb92075
              user564785 wrote:
              TABLESPACE_NAME STATUS
              ------------------------------ ---------
              SYSTEM ONLINE
              SYSAUX ONLINE
              UNDOTBS1 ONLINE
              TEMP ONLINE
              USERS ONLINE
              TS_AA      ONLINE
              TS_DEV      ONLINE
              TS_TEST_DATA OFFLINE
              TS_TEST_DATA tablespace must be brought ONLINE before you can load data into it.
              • 4. Re: Getting ORA-01542: tablespace TS_TEST_DATA is offline
                user564785
                I'm not loading data onto that tablespace. That's what's so puzzling.
                • 5. Re: Getting ORA-01542: tablespace TS_TEST_DATA is offline
                  sb92075
                  user564785 wrote:
                  I'm not loading data onto that tablespace. That's what's so puzzling.
                  we don't know what caused the error since you decided to not share this detail with us.

                  if you don't issue any SQL, then you won't get any error!
                  • 6. Re: Getting ORA-01542: tablespace TS_TEST_DATA is offline
                    jgarry
                    Show us the results of querying the ddl for the table and
                    select PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME
                    from user_Tab_partitions where table_name='your table name'

                    And like sb said, show us how you are doing it.
                    • 7. Re: Getting ORA-01542: tablespace TS_TEST_DATA is offline
                      Osama_Mustafa
                      user564785 wrote:
                      I'm not loading data onto that tablespace. That's what's so puzzling.
                      COPY & PASTE WHAT ARE YOU DOING !!!
                      • 8. Re: Getting ORA-01542: tablespace TS_TEST_DATA is offline
                        user564785
                        select partition_NAME,HIGH_VALUE,TABLESPACE_NAME
                        2* from dba_tab_partitions where table_owner='AA_DEV' AND TABLE_NAME='DM_FOLDER_S'

                        PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
                        ------------------------------ -------------------------------------------------------------------------------- ------------------------------
                        LAST_PARTITION MAXVALUE TS_AA


                        ALTER TABLE AA_DEV.DM_FOLDER_S
                        SPLIT PARTITION LAST_PARTITION AT (20)
                        INTO (PARTITION p2, PARTITION LAST_PARTITION);


                        ERROR at line 1:
                        ORA-01542: tablespace 'TS_TEST_DATA' is offline, cannot allocate space
                        • 9. Re: Getting ORA-01542: tablespace TS_TEST_DATA is offline
                          sb92075
                          user564785 wrote:
                          select partition_NAME,HIGH_VALUE,TABLESPACE_NAME
                          2* from dba_tab_partitions where table_owner='AA_DEV' AND TABLE_NAME='DM_FOLDER_S'

                          PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
                          ------------------------------ -------------------------------------------------------------------------------- ------------------------------
                          LAST_PARTITION MAXVALUE TS_AA


                          ALTER TABLE AA_DEV.DM_FOLDER_S
                          SPLIT PARTITION LAST_PARTITION AT (20)
                          INTO (PARTITION p2, PARTITION LAST_PARTITION);


                          ERROR at line 1:
                          ORA-01542: tablespace 'TS_TEST_DATA' is offline, cannot allocate space
                          you can NOT do any operation against any object when the tablespace in which it resides if OFFLINE!
                          • 10. Re: Getting ORA-01542: tablespace TS_TEST_DATA is offline
                            Mark Williams-Oracle
                            Does this table have a local index in tablespace TS_TEST_DATA by chance?

                            That's one way to reproduce this behaviour.

                            Here's a sample using the EXAMPLE and USERS tablespaces to illustrate:
                            create table ptest
                            (
                              p_id number,
                              c_data varchar2(46)
                            )
                            partition by range (p_id)
                            (
                              partition p1 values less than (100),
                              partition p2 values less than (200)
                            )
                            tablespace example;
                            
                            insert into ptest values (1, 'Number 1');
                            insert into ptest values (101, 'Number 101');
                            commit;
                            
                            create index ptest_idx01
                            on ptest (p_id)
                            local
                            tablespace users;
                            
                            alter tablespace users offline;
                            
                            alter table ptest
                            split partition p2 at (150) into
                            (
                              partition p2a,
                              partition p2b
                            );
                            
                            alter table ptest
                                        *
                            ERROR at line 1:
                            ORA-01542: tablespace 'USERS' is offline, cannot allocate space in it
                            • 11. Re: Getting ORA-01542: tablespace TS_TEST_DATA is offline
                              Osama_Mustafa
                              This is what we trying to tell you , Make tablespace Online .
                              • 12. Re: Getting ORA-01542: tablespace TS_TEST_DATA is offline
                                user564785
                                Found out the problem. Although there were no indexes in TS_TEST_DATA, the DDL for the indexes was created using two different tablespaces

                                CREATE UNIQUE INDEX "AA_DEV"."D_1F002EE180000144" ON "AA_DEV"."DM_FOLDER_S" ("R_OBJECT_ID", "I_PARTITION")
                                PCTFREE 10 INITRANS 2 MAXTRANS 255
                                STORAGE(INITIAL 16384 NEXT 106496
                                PCTINCREASE 10 BUFFER_POOL DEFAULT)
                                TABLESPACE "TS_TEST_DATA" LOCAL
                                (PARTITION "LAST_PARTITION"
                                PCTFREE 10 INITRANS 2 MAXTRANS 255
                                STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                                PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
                                TABLESPACE "TS_AA" ) ;
                                • 13. Re: Getting ORA-01542: tablespace TS_TEST_DATA is offline
                                  Mark Williams-Oracle
                                  user564785 wrote:
                                  Found out the problem. Although there were no indexes in TS_TEST_DATA, the DDL for the indexes was created using two different tablespaces

                                  CREATE UNIQUE INDEX "AA_DEV"."D_1F002EE180000144" ON "AA_DEV"."DM_FOLDER_S" ("R_OBJECT_ID", "I_PARTITION")
                                  PCTFREE 10 INITRANS 2 MAXTRANS 255
                                  STORAGE(INITIAL 16384 NEXT 106496
                                  PCTINCREASE 10 BUFFER_POOL DEFAULT)
                                  TABLESPACE "TS_TEST_DATA" LOCAL
                                  (PARTITION "LAST_PARTITION"
                                  PCTFREE 10 INITRANS 2 MAXTRANS 255
                                  STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                                  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
                                  TABLESPACE "TS_AA" ) ;
                                  Umm, so this is exactly the situation I asked about and demonstrated just above then isn't it?

                                  How can you say "Although there were no indexes in TS_TEST_DATA" when the DDL for the index clearly shows it is both LOCAL and in TS_TEST_DATA as I suggested?

                                  TABLESPACE "TS_TEST_DATA" LOCAL
                                  • 14. Re: Getting ORA-01542: tablespace TS_TEST_DATA is offline
                                    user564785
                                    I did a select distinct tablespace_name from dba_ind_partition and dba_indexes tables and didnt' see any indexes on TS_TEST_DATA. Currently, all the data is loaded to the one partition (LAST_PARTITION), which resides in TS_AA.
                                    1 2 Previous Next