I have deleted (rm -rf) a bunch of tables from /var/lib/mysql/sites/. Now am not able to execute the same tables in to the sites db and able to execute if the table name got changed. Please help me to solve this issue, I need to use the same table with same table name for execution.

sample of deleted tables are listed below,
site_field.frm
site_field_instance.frm
site_field_instance.MYD
site_field_instance.MYI
site_field.MYD
site_field.MYI
site_circular.frm
site_circular.MYD
site_circular.MYI
site_allery.frm
site_allery.MYD
site_allery.MYI
site_.........
site_.........etc

have you tried to stop my sql service? and check whether you can execute again the same table name.

or if it is okay to reboot the server please reboot it, because there could be some process that still points the old data.

I have restarted the service and rebooted the machine too, but the issue is persisting.
The error file shows the below,
Error Code: 1813 - Tablespace for table '`testtable`.`actions`' exists. Please DISCARD the tablespace before IMPORT.

Did you delete the data from MySQL DB?

looks like you need something like this:
alter table <tablename> discard tablespace;

There are 'n' number of tables, how to alter a group of tables. The db is having other tables too, so have to handle careful.

I would say one by one, you need to collect all the names and create a script to do the work
(next time try to delete tables by drop table instead of removing files)

Thank you very much, I manually deleted the files so thought it is possible to trouble shoot from Linux file system itself, without logging in to MySQL db.

no, the problem is that you did not delete the bookings of those tables, just those files. You need to cleanup that booking now.

There is no way to get the data because it was physically removed from the server . The Only way is you can restore the backup and then you can get the data.

I am not worried about the data, at least take an effort to read my issue before replying. Its just a kind information for you, will help you in future.

*** UPDATE *** (Think before posting...)

pan64 has your answer in post #5,7.

But you will have to login to the MySQL client - this is not a fielsystem issue.

agree this is not a filesystem issue, I think when the MySQL is run. It is trying to find a file which does not exist anymore on the particular location.

Files were deleted, but not the reference in the database.

If i'm not wrong this is a MySQL issue not relating to Linux OS.

This is a nice tip, but in my case I have tables backup, I want to restore it on the same db. It doesn't allow to restore the same tables because deleted manually from /var/lib/mysql/site/. There is a chance of current user’s session id and the batch id to be present there. But service restart and reboot is not a solution for this. Could you help me to trace which files still block me to restore the tables.

I had thought that through and updated my above post earlier.

But the difficulty for you is that it is not a 'file' that is blocking the recreation of the tables - it is the internal MySQL meta-data. SO there is really not a solution that can be performed from the filesystem.

I think that you will need at minimum to do the following from within mysql client or from a script (for each affected table):

You can do that without restarting the MySQL server, but not without access to the mysql client, or the ability to run it as a script on the affected server.