answer is A & E
Queries and DML and DDL operations can be performed while the data file is being moved, for example:
1-SELECT statements against tables and partitions
2-Creation of tables and indexes
3- Rebuilding of indexes
Other notes:
1- If objects are compressed while the data file is moved, the compression remains the same.
2- You do not have to shut down the database or take the data file offline while you move a data file to another
location, disk, or storage system.
3- You can omit the TO clause only when an Oracle-managed file is used. In this case, the
DB_CREATE_FILE_DEST initialization parameter should be set to indicate the new location.
4-If the REUSE option is specified, the existing file is overwritten.
note: The REUSE keyword indicates the new file should be created even if it already exists.
5-If the KEEP clause is specified, the old file will be kept after the move operation. The KEEP clause is not allowed
if the source file is an Oracle-managed file.
See documentation "14.5.1 Renaming and Relocating Online Data Files" in Release 19 Database Administrator’s Guide
See documentation "ALTER DATABASE" in Release 19 SQL Language Reference
A - TRUE; ALTER DATABASE MOVE DATAFILE ... This statement enables you to rename or relocate a data file while the database is open and users are accessing the data file."
B - FALSE; There is no condition to alter moved datafile READ ONLY before executing ALTER ... MOVE command.
C - FALSE; The datafile is online during the movement, see answer A.
D - FALSE; the file will be moved to specified folder only if TO is omitted. If TO is not omitted, Oracle will create specified file. "If you are using Oracle Managed Files, then you can omit the TO clause. In this case, Oracle Database creates a unique name for the data file and saves it in the directory specified by the DB_CREATE_FILE_DEST initialization parameter."
E - TRUE; its obvious from the command listed in question
A and E are correct answers. The Alter tablespace MOVE command works just like the linux mv command which either renames the file or moves the file to the specified location if it doesnt exist there.
Note, while this movement is ongoing, DML operations are allowed.
Also, the tablespace does not have to be in OFFLINE mode or READ ONLY mode for this operation to be performed.
The correct answers are C,E, for Oracle 12c I understand
C is correct because to rename a datafile you have to put it offline. Due to the fact that it is a physical Windows file and if it is in use it does not allow you to rename it. Same as any other file you want to rename in windows
E is correct , because it is not just moving the datafile and renaming it
For Oracle 19 the correct answer is A,E. Could be?
It's funny how A and B are actually contradicting one another... if the datafile is Read Only, how can you execute any DML on tables residing there?
Considering the ability of renaming datafiles online from 12c, I would say the more correct answers are A and E.
D is wrong because, evn with OMF, you can still force the creation of a datafile with a custom name on filesystem if you give the full path. If you only gave the file name, on the contrary, it would in fact create the new datafile on the default location.
For old folks, note that "ALTER DATABASE MOVE..." syntax replaces "ALTER DATABASE RENAME..." (pre-12c era), so is no longer to set the tablespace offline and DML operations can be performed.
A E
ALTER DATABASE MOVE DATAFILE ( 'filename' | 'ASM_filename' | file_number )
[ TO ( 'filename' | 'ASM_filename' ) ]
[ REUSE ] [ KEEP ]
The source file can be specified using the file number or name, while the destination file must be specified by the file name.
The REUSE keyword indicates the new file should be created even if it already exists.
The KEEP keyword indicates the original copy of the datafile should be retained.
When the source file is an OMF file the KEEP option can not be used.
If the destination file is an OMF file, the TO clause can be omitted and the file will be created with
an OMF name in the DB_CREATE_FILE_DEST location.
But in the question they're defining a destination in the TO clause, hence the datafile would be created in that destination, not in the OMF destination.
Why B is correct ? according to the Oracle documentation, Move Option is new and only possible in 12c and can be executed ONLINE without taking anything offline
https://oracle-base.com/articles/12c/online-move-datafile-12cr1
A voting comment increases the vote count for the chosen answer by one.
Upvoting a comment with a selected answer will also increase the vote count towards that answer by one.
So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.
Rivaldo11
Highly Voted 3 years, 11 months agoNowOrNever
3 years, 11 months agokhalilshahin01
Highly Voted 3 years, 11 months agoHassanShami
Most Recent 1 month, 2 weeks agonautil2
9 months, 3 weeks agodoyinbare
9 months, 3 weeks agoOracle2020
10 months, 2 weeks agotrgbighero
1 year, 8 months agocratostt
2 years, 3 months agoemburria
2 years, 5 months agoryuah
2 years, 6 months agogabriel3600
3 years agoEkos
3 years, 6 months agoSimoneF
3 years, 7 months agoelvegaa
3 years, 7 months agoavanand
3 years, 10 months agoyou1234
4 years agoama
4 years agoNiciMilo
4 years agoelvegaa
3 years, 7 months agoSimoneF
3 years, 7 months agoama
4 years ago