Results 1 to 6 of 6

Thread: hi

  1. #1
    Join Date
    Nov 2003
    Posts
    12

    Unanswered: hi

    hello all,
    I am trying to drop a tablespace but it gives me the following error, I am not sure what it really means.

    SQL> drop tablespace example including contents;
    drop tablespace example including contents
    *
    ERROR at line 1:
    ORA-23515: materialized views and/or their indices exist in the tablespace
    Pleaseeeeeeeeeeeeeeeee Help

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It means "materialized views and/or their indices exist in the tablespace"

    What part of the sentence about do you not understand?

    What does
    SELECT SEGMENT_NAME
    FROM DBA_SEGMENTS
    WHERE TABLESPACE = 'EXAMPLE';
    display?

  3. #3
    Join Date
    Oct 2003
    Location
    St.Louis,MO
    Posts
    120

    Re: hi

    Originally posted by diana197
    hello all,
    I am trying to drop a tablespace but it gives me the following error, I am not sure what it really means.

    SQL> drop tablespace example including contents;
    drop tablespace example including contents
    *
    ERROR at line 1:
    ORA-23515: materialized views and/or their indices exist in the tablespace
    ...Be aware of the concequences.....
    SQL> drop tablespace example including contents cascade constraints;

  4. #4
    Join Date
    Nov 2003
    Posts
    12
    that select statement displays the following

    SELECT SEGMENT_NAME FROM DBA_SEGMENTS
    WHERE TABLESPACE_NAME='EXAPLE';

    SEGMENT_NAME
    --------------------------------------------------------------------------------
    SYS_IOT_TOP_29882
    SYS_IOT_TOP_29885
    SYS_IL0000029894C00028$$
    SYS_C002555
    SYS_C002558
    SYS_IOT_TOP_29903

    ...and on and on and on...
    374 rows selected.
    Pleaseeeeeeeeeeeeeeeee Help

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    After getting rid of all 374 objects which reside with the EXAMPLE tablespace it will drop quickly & quietly.

    You need to learn the art of writing SQL which writes SQL.

    This will allow you to write a single SQL statement
    which generates 374 SQL statements which will drop the objects from the EXAMPLE tablespace one at a time.

  6. #6
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up Re: hi

    Hi,

    An attempt was made to drop a tablespace which contains materialized views and/or their indices. So Drop the materialized views in this tablespace. Also, find indices belonging to materialized views in this tablespace and drop then. Then try dropping the tablespace.


    Originally posted by diana197
    hello all,
    I am trying to drop a tablespace but it gives me the following error, I am not sure what it really means.

    SQL> drop tablespace example including contents;
    drop tablespace example including contents
    *
    ERROR at line 1:
    ORA-23515: materialized views and/or their indices exist in the tablespace
    SATHISH .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •