This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Nov 13, 2012 8:48 AM by Mark Williams-Oracle RSS

Getting ORA-01542: tablespace TS_TEST_DATA is offline

user564785 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Employee ACE
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    This is what we trying to tell you , Make tablespace Online .
  • 12. Re: Getting ORA-01542: tablespace TS_TEST_DATA is offline
    user564785 Newbie
    Currently Being Moderated
    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 Employee ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points