How to move or rename datafiles in Oracle 11gR2
In the RENAME table statement: First, specify the name of the existing table which you want to rename. Second, specify the new table name. The new name must not be the same as another table in the same schema. rename to new_table_name; For example, we could rename the customer table to old_customer with this syntax: alter table customer rename to old_customer; When you rename an Oracle table you must be aware that Oracle does not update applications (HTML-DB, PL/SQL that referenced the old table name) and PL/SQL procedures may become invalid.
There are multiple ways to move or rename the data files in oracle 11g, oracle 12c has come with a wonderful feature where we can move the files being online. However prior to 12c we need to do this practice after making the database offline, there are online ways are also available but in those also there is some time delay in processing.
Database dismounted. Start the database in mount state, because control how to drink japanese tea does not know new location of datafiles or new name of datafiles, we need to update in the control file also with the alter command:. So we are done with moving the files in Offline Mode. Hopefully in next blog i will discuss how can we do that Online in Oracle 11g.
You are commenting using your WordPress. You are commenting using your Google account. You are commenting using your Twitter account. You are commenting using your Facebook account. Notify me of new comments via email. Notify me of new posts via email.
Skip to content There are multiple ways to move or rename the data files in oracle 11g, oracle 12c has come with a wonderful feature where we can move the files being online. Rename the physical file on the OS. Start the database in mount mode. Open the database. Hope it helps. Share this: Twitter Facebook. Like this: Like Loading Leave a Reply Cancel reply Enter your comment here Fill in your details below or click an icon to log in:.
Email required Address never made public. Name required.
RENAME TABLE allows you to rename an existing table in any schema (except the schema SYS).. To rename a table, you must either be the database owner or the table owner. In order to rename a table in a different schema, try: ALTER TABLE kristinfrey.come RENAME TO othertable; The rename command (as in " rename mytable to othertable ") only supports renaming a table in the same schema. In more recent versions of Oracle you can rename tables, columns and constraints. Here are examples of the syntax: ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name; (from Oracle9i Release 2).
Join Stack Overflow to learn, share knowledge, and build your career. Connect and share knowledge within a single location that is structured and easy to search. If the view is not in your schema, you can recompile the view with the new name and then drop the old view. The rename command as in " rename mytable to othertable " only supports renaming a table in the same schema.
One can rename indexes the same way:. Past 10g the current answer no longer works for renaming views. The only method that still works is dropping and recreating the view. The best way I can think of to do this would be:. Stack Overflow for Teams — Collaborate and share knowledge with a private group.
Create a free Team What is Teams? Learn more. Asked 11 years, 11 months ago. Active 5 years, 7 months ago. Viewed k times. What is the syntax to rename a table or view in Oracle? Improve this question. Steven Steven Add a comment.
Active Oldest Votes. Improve this answer. Quassnoi Quassnoi k 83 83 gold badges silver badges bronze badges. In oracle versions older than 10g, views can no longer be renamed.
Check separate answer for an example. Jeffrey Kemp Jeffrey Kemp Pop Pop 3, 1 1 gold badge 14 14 silver badges 12 12 bronze badges. One can rename indexes the same way: alter index owner. Would have been more suitable as a comment, not an answer.
Wouter Wouter 1, 22 22 silver badges 33 33 bronze badges. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name.
Email Required, but never shown. The Overflow Blog. Podcast Non-fungible Talking. Featured on Meta. New onboarding for review queues. Outdated Answers: results from use-case survey. Downvotes Survey results. Linked 4. Related Hot Network Questions. Question feed.
<- How long does the shuttle take to france - What is the afqt for the navy->