For InnoDB, the following seems to work: create the new empty database, then rename each table in turn into the new database:
RENAME TABLE old_db.table TO new_db.table;
You will need to adjust the permissions after that.
For scripting in a shell, you can use either of the following:
mysql -u username -ppassword old_db -sNe 'show tables' | while read table; \
do mysql -u username -ppassword -sNe "rename table old_db.$table to new_db.$table"; done
OR
for table in `mysql -u root -ppassword -s -N -e "use old_db;show tables from old_db;"`; do mysql -u root -ppassword -s -N -e "use old_db;rename table old_db.$table to new_db.$table;"; done;
Notes:
- There is no space between the option
-p
and the password. If your database has no password, remove the -u username -ppassword
part.
If some table has a trigger, it cannot be moved to another database using above method (will result Trigger in wrong schema
error). If that is the case, use a traditional way to clone a database and then drop the old one:
mysqldump old_db | mysql new_db
If you have stored procedures, you can copy them afterwards:
mysqldump -R old_db | mysql new_db
Use the SC command, like this (you need to be on a command prompt to execute the commands in this post):
SC STOP shortservicename
SC DELETE shortservicename
Note: You need to run the command prompt as an administrator, not just logged in as the administrator, but also with administrative rights. If you get errors above about not having the necessary access rights to stop and/or delete the service, run the command prompt as an administrator. You can do this by searching for the command prompt on your start menu and then right-clicking and selecting "Run as administrator". Note to PowerShell users: sc
is aliased to set-content
. So sc delete service
will actually create a file called delete
with the content service
. To do this in Powershell, use sc.exe delete service
instead
If you need to find the short service name of a service, use the following command to generate a text file containing a list of services and their statuses:
SC QUERY state= all >"C:\Service List.txt"
For a more concise list, execute this command:
SC QUERY state= all | FIND "_NAME"
The short service name will be listed just above the display name, like this:
SERVICE_NAME: MyService
DISPLAY_NAME: My Special Service
And thus to delete that service:
SC STOP MyService
SC DELETE MyService
Best Answer
Quote by @DAC
Not strictly true. SID = unique name of the INSTANCE (eg the oracle process running on the machine). Oracle considers the "Database" to be the files.
Service Name = alias to an INSTANCE (or many instances). The main purpose of this is if you are running a cluster, the client can say "connect me to
SALES.acme.com
", the DBA can on the fly change the number of instances which are available toSALES.acme.com
requests, or even moveSALES.acme.com
to a completely different database without the client needing to change any settings.