Examine this command:
SQL> ALTER TABLE ORDERS SHRINK SPACE COMPACT
Which two statements are true? (Choose two.)
Examine this command:
SQL> ALTER TABLE ORDERS SHRINK SPACE COMPACT
Which two statements are true? (Choose two.)
During the ALTER TABLE SHRINK SPACE COMPACT operation, rows are moved to empty space starting from the beginning of the ORDERS segment, ensuring efficient use of space from the start of the segment. Additionally, both queries and DML statements are allowed on the ORDERS table while the shrink operation is executing, making it possible to perform the shrink without significantly impacting the availability of the table for other operations.
AB are correct
can u explain
Correct answers are B and F, because rows are moved up and free space goes down. For COMPACT clause, watermarks are not updated. DML and queries are both supported in this "phase".
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ALTER-TABLE.html#GUID-552E7373-BF93-477D-9DA3-B2C9386F2877
FALSE: F.Contrary to the claim, the SHRINK operation moves rows to empty space starting from the beginning of the segment, not toward the end. E: If you specify COMPACT, then Oracle Database only defragments the segment space and compacts the table rows for subsequent release. The database does not readjust the high water mark and does not release the space immediately. You must issue another ALTER TABLE ... SHRINK SPACE statement later to complete the operation. This clause is useful if you want to accomplish the shrink operation in two shorter steps rather than one longer step. i go for AB also.
ALTER TABLE SHRINK SPACE The ALTER TABLE SHRINK SPACE algorithm starts from the bottom of the segment and starts moving rows to the beginning of the segment. Compacting the segment requires row movement. Therefore, you must enable row movement for the object you want to shrink before specifying this clause. By default, Oracle Database compacts the segment, adjusts the High Water Mark, and releases the recuperated space immediately. B. ALTER TABLE MY_TABLE SHRINK SPACE COMPACT Compact rows, without adjusting HWM. http://aleckaplandba.blogspot.com/2015/07/shrink-table-by-example.html
A: TRUE; It wouldn´t make sense if the data were filled from the end of segment, because it could remain some empty space after shrinking. Data are moved to empty space to use maximum of empty space. I could not find any proof in the documentation. If anyone find it, feel free and post it here. B: TRUE, https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-space-for-schema-objects.html#GUID-37DF35E0-116B-4AAB-BFB3-DDBDBAB29BEB DML operations and queries can be issued during the data movement phase of segment shrink. C: FALSE, same URL as B, Indexes are maintained during the shrink operation and remain usable after the operation is complete. D: FALSE, also DML is allowed, see B E: FALSE; https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ALTER-TABLE.html#GUID-552E7373-BF93-477D-9DA3-B2C9386F2877 F: FALSE; opposite to A