Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Location
    California,USA
    Posts
    72

    Unanswered: How to drop tablespace...

    I am getting the below error when I try to drop a tablespace. Can anyone help to resolve this error...

    SQL0282N Table space "<tablespace-name>" cannot be dropped because at least one of the tables in it, "<table-name>", has one or more of its parts in another table space.

    But the above mentioned "table-name" does not contain any any part in different tablespace.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: How to drop tablespace...

    Originally posted by nagasurir
    I am getting the below error when I try to drop a tablespace. Can anyone help to resolve this error...

    SQL0282N Table space "<tablespace-name>" cannot be dropped because at least one of the tables in it, "<table-name>", has one or more of its parts in another table space.

    But the above mentioned "table-name" does not contain any any part in different tablespace.
    The message reference states:

    "The base table, indexes, or long data may be in another table space, so dropping the table space(s) will not completely drop the table. This would leave the table in an inconsistent state and therefore the table space(s) cannot be dropped".

    Are you sure that no indexes or LOBs for that table are stored in other tablespaces?
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Nov 2003
    Location
    California,USA
    Posts
    72
    YES...

    I am damn sure that no db objects are occupying the other table space...

    Thanks

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    If you are very confident, then ,

    1) Check db2diag.log file ... There may be error messages. If there are none, set the diaglevel to 4 and issue the command again ... this time there shoud be messages.

    2) If you are desperate to drop the tablespace, then contact IBM support with your problem . They should be able to help

    If you want to check for dependencies once again, please read on ...


    1) If you are comfortable with graphical tools, then in the control centre, tablespaces , right click on the tablespace name and select 'Show Related Objects'

    2) If you are comfortable with the command line, then do

    select * from syscat.tables where tbspace=<name> or index_tbspace=<name> or long_tbspace=<name>

    Cheers
    Sathyaram



    Originally posted by nagasurir
    YES...

    I am damn sure that no db objects are occupying the other table space...

    Thanks
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    May 2003
    Posts
    369

    DB2DART

    You should use DB2DART to mark the tablespace as bad and put into drop pending mode to drop the tablespace. Be careful though and work with IBM support when using DB2DART.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why not select from the catalog to prove there aren't any other objects?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Mar 2003
    Posts
    343
    If this is a dms tablespace it is possible that indexes are in another tablespace. Another way to check this out may be to extract the DDL for the table

    db2look -e -p -d <dbname> -t <tabname> -x -z <schemaname> -o <outfile>

    The other thing that may be the case is Foreign key relationships - this will prevent the drop - ie if you are trying to drop a parent while a child exists.

Posting Permissions

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