droping tables in a dbspace 1

[nAtHaNs]

Hello,

I have created a dbspace as a temporary storage for performing db reorganization using "Tables in Dbspace by 'alter fragment..." method. Now for I can not drop the dbspace because it say it is not empty. How do I drop the tables in the dbspace (say 2000+ tables)?

Any help would be appreciated. Thank you.

nAtHaNs

 

[vpshriyan]

Hi,

Well, if you know the table names you need to drop them one by one, before attempting to drop related dbspace - there is no short cut I feel. If do NOT know the table names, you can identify them using the sql below:

-- following sql maps tables/indexes to dbspaces
select tabname,
trunc(systabnames.partnum/1048576) dbspace,
sysdbspaces.name
from sysmaster:systabnames, sysmaster:sysdbspaces
where sysdbspaces.dbsnum = trunc(systabnames.partnum/1048576) ;

Direct the output to a file and globally replace with "drop table" keyword and execute the file using syntax below:

dbaccess <dbname> <file.sql>

Regards,
Shriyan