You need to rename a data file of a tablespace. How would you perform this?
A.
bring the database to the NOMOUNT state, rename the data file using the operating system, and
then execute the ALTER TABLESPACE…RENAME DATAFILE .. statement
B.
take the tablespace that contains the data file offline, rename the data file using the operating
system, execute the ALTER DATABASE…RENAME DATAFILE .. statement, and bring the tablespace
online
C.
take the tablespace that contains the data file offline, rename the data file using the operating
system, execute the ALTER TABLESPACE…RENAME DATAFILE .. statement, and bring the tablespace
online
D.
make the tablespace that contains the data file read only, rename the data file using the
operating system, execute the ALTERTABLESPACE… RENAME DATAFILE .. statement to rename the
data file, and make the tablespace read/write
B. is the correct answer
Looks like both B and C are correct. Here is why ….
Here is a query to show us the current situation.
SELECT T.NAME TABLESPACENAME, D.NAME DATAFILENAME
FROM V$TABLESPACE T JOIN V$DATAFILE D USING (TS#)
WHERE T.NAME LIKE ‘%US%’;
Lets rename a datafile using ALTER DATABASE RENAME FILE … TO …
SQL> ALTER TABLESPACE USERS OFFLINE NORMAL;
Tablespace altered.
SQL> HOST RENAME D:\ORADATA\AHEADPRD\USERS01.DBF USRS01.DBF
SQL> ALTER DATABASE RENAME FILE ‘D:\ORADATA\AHEADPRD\USERS01.DBF’ TO ‘D:\ORADATA\AHEADPRD\USRS01.DBF’;
Database altered.
SQL> ALTER TABLESPACE USERS ONLINE;
Tablespace altered.
Lets rename it back to its original name using ALTER TABLESPACE USERS RENAME DATAFILE … TO …
SQL> ALTER TABLESPACE USERS OFFLINE NORMAL;
Tablespace altered.
SQL> HOST RENAME D:\ORADATA\AHEADPRD\USRS01.DBF USERS01.DBF
SQL> ALTER TABLESPACE USERS RENAME DATAFILE ‘D:\ORADATA\AHEADPRD\USRS01.DBF’ TO ‘D:\ORADATA\AHEADPRD\USERS01.DBF’;
Tablespace altered.
SQL> ALTER TABLESPACE USERS ONLINE;
Tablespace altered.
Peace to all
https://docs.oracle.com/cd/B28359_01/server.111/b28310/dfiles005.htm
Procedures for renaming and relocating datafiles that can be used for a single tablespace. You must have ALTER TABLESPACE system privileges.
To rename datafiles in a single tablespace, complete the following steps:
Take the tablespace that contains the datafiles offline. The database must be open.
Rename the datafiles using the operating system.
Use the ALTER TABLESPACE statement with the RENAME DATAFILE clause to change the filenames within the database.
ALTER TABLESPACE users
RENAME DATAFILE ‘/u02/oracle/rbdb1/user1.dbf’,
‘/u02/oracle/rbdb1/user2.dbf’
TO ‘/u02/oracle/rbdb1/users01.dbf’,
‘/u02/oracle/rbdb1/users02.dbf’;
Always provide complete filenames (including their paths) to properly identify the old and new datafiles. In particular, specify the old datafile name exactly as it appears in the DBA_DATA_FILES view of the data dictionary.
Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.
Use the ALTER TABLESPACE statement with the RENAME DATAFILE clause to change the filenames within the database.
OFF – ALTER – DB
rename a data file of a tablespace
ALTER TB RENAME DATAFILE
For most up-to-date information you have to pay a visit web and on the web I found this web site as a finest web page for latest updates.|