Is there any way to change the schema name of a table or do I have to drop and recreate the table with new schema name?

Thanks,
toby

Toby,
No, no direct and simple way. The best way would be to:

1) create new table (CREATE TABLE NewSchema.MyTable like OldSchema.MyTable)
2) copy data (INSERT INTO NewSchema.MyTable select * from OldSchema.MyTable)
3) Create any indexes on new table
4) drop old table.
5) fix all references to OldSchame.MyTable to NewSchema.MyTable

HTH

Andy

...... or just create an alias for the table with the new schema

create alias newalias.tablename for oldalias.tablename

Cheers
Sathyaram