|
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. |
|
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. FWIW (fortunalety I haven't had the case): Is dropping a dbspace possible when there are tables/indizes still located on it? Or will the observed behaviour only occur if a dbspace file gets lost (though I would then suspect that the dbspace metadata would still be in the syscatalog...)?
(09 Nov '11, 08:06)
Volker Barth
2
My experience ist that DROP DBSPACE is not permitted until all tables, indexes etc. in this dbspace have been dropped .
(09 Nov '11, 08:25)
Reimer Pods
Replies hidden
That's surely what I have expected - particulary to the ol' "Watcom does things the way they should be done" rule:)
(09 Nov '11, 08:27)
Volker Barth
|
|
I assume
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... |
What happens when you create the lost dbspace again? Is it then possible to delete the tables?
AFAIK there's no way to move a table (or index) from one dbspace to another. This seems to require a reload with a modified reload.sql script (or using dbunload -kd to reload into a single dbspace).
@Volker, If i try to create the dbspace again, I get the error: 'Cannot use existing file name for the new dbspace.' I am trying to unload the data (to reload into new db, as you mentioned) but the unload fails with the error: table not found Any suggestions?