I had created a dbspace in my database, it had some tables. I lost the physical dbspace file. Now I have only main database file. Now when I connect database with sybase central, I can see the name of the dbspace, and its tables in the database tables list. However, If try to view the table data, i get the message that the table does not exist. I can not delete the table, as i get same error. If i try to create a new table with same name then i get the message 'the item 'tablename' already exists.
Please let me know how can i delete the tables which were in the dbspace. I want to delete all the tables on the dbspace and then delete the dbspace itself. Thanks.
asked 09 Nov '11, 05:19
The catalog metadata for all the tables and indexes in the missing dbspace are held in the main dbspace. Hence you should treat the database as physically corrupt. I assume you do not have backup of your database to recover from, so your best option is to follow Volker's steps and salvage the data that you can out of the existing database file using dbunload, then create a NEW database and reload the data into it.
This is a much better approach that trying to reverse engineer the database catalog structure to try to clean up the (now erroneous) metadata for the missing dbspace, and its tables, still held in the catalog.
answered 09 Nov '11, 07:59
Have you tried to use an existing file name? (The error message seems to imply that.) If so, try with a nonexisting file. (Otherwise, I do not know to proceed.)
You might be able to ignore the "lost" tables by using DBUNLOAD -t <list of "non-lost" tables>. I hope this does not give the error as it should not try to access the lost tables.
If this works, you might be able to rebuild the database with
Note: I have never used these steps myself, so these are just wild guesses...
answered 09 Nov '11, 07:04