Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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

sam's gravatar image

accept rate: 0%

Comment Text Removed

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).

(09 Nov '11, 06:17) Volker Barth

@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?

(09 Nov '11, 06:31) sam

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.

permanent link

answered 09 Nov '11, 07:59

Glenn%20Paulley's gravatar image

Glenn Paulley
accept rate: 43%

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

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

  • the lost dbspace is currently not listed in system views SYSDBSPACE and SYSDBFILE and
  • when re-creating the dbspace you use the same dbspace name as before.

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

  1. dbunload -nl (to generate the complete reload.sql script without data but with load table commands)
  2. dbunload -d -t <list of "non-lost" tables> to unload their data
  3. remove the LOAD TABLE entries for the lost tables from the reload.sql script of step 1
  4. fill a new db with the script from step 3 and the data from step 2.

Note: I have never used these steps myself, so these are just wild guesses...

permanent link

answered 09 Nov '11, 07:04

Volker%20Barth's gravatar image

Volker Barth
accept rate: 34%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 09 Nov '11, 05:19

question was seen: 3,885 times

last updated: 10 Nov '11, 03:11