Oracle Oracle Certifications 1Z0-053 Questions & Answers

• Question 541:

  You have 100 segments in the USERS tablespace. You realize that the USERS tablespace is running low on space. You can use Segment Advisor to _____.

  A. identify the segments that you should shrink

  B. modify the storage parameters for the tablespace

  C. automatically shrink the segments with unused space

  D. check the alerts generated for the tablespace that is running low on space

  Correct Answer: A

• Question 542:

  You need to perform an online table redefinition of an existing SALES table to partition it into two tablespaces TBS1 and TBS2. The SALES table has a materialized view, materialized log, indexes, referential integrity constraint, and triggers with the PRECEDES clause existing on it.

  What action is required for dependent objects when you perform online table redefinition?

  A. The dependent materialized view should have a complete refresh performed after the online table redefinition process.

  B. Triggers with the PRECEDES clause should be disabled before the online table redefinition process.

  C. Referential integrity constraints must be manually enabled after the online table redefinition process.

  D. The materialized log should be dropped before the online table redefinition process.

  Correct Answer: A

  When performing the online table redefinition, you will:

  Copy dependent objects (such as triggers, indexes, materialized view logs, grants, and constraints) and statistics from the table being redefined to the interim table, using one of the following two methods.

  Method 1 is the preferred method because it is more automatic, but there may be times that you would choose to use method 2. Method 1 also enables you to copy table statistics to the interim table.

  Results of the Redefinition Process (link)

  The following are the end results of the redefinition process:

  The original table is redefined with the columns, indexes, constraints, grants, triggers, and statistics of the interim table.

  Dependent objects that were registered, either explicitly using REGISTER_DEPENDENT_OBJECT or implicitly using

  COPY_TABLE_DEPENDENTS, are renamed automatically so that dependent object names on the redefined table are the same as before redefinition.

  Note:

  If no registration is done or no automatic copying is done, then you must manually rename the dependent objects.

  The referential constraints involving the interim table now involve the redefined table and are enabled. Any indexes, triggers, materialized view logs, grants, and constraints defined on the original table (prior to redefinition) are transferred to

  the interim table and are dropped when the user drops the interim table. Any referential constraints involving the original table before the redefinition now involve the interim table and are disabled. Some PL/SQL objects, views, synonyms, and

  other table-dependent objects may become lidated. Only those objects that depend on elements of the table that were changed are invalidated.

  For example, if a PL/ SQL procedure queries only columns of the redefined table that were unchanged by the redefinition, the procedure remains valid. See "Managing Object Dependencies" for more information about schema object

  dependencies. Restrictions for Online Redefinition of Tables (link) After redefining a table that has a materialized view log, the subsequent refresh of any dependent materialized view must be a complete refresh.

• Question 543:

  You executed the following SQL statement to shrink the EMPLOYEES table segment stored in the EXAMPLE tablespace:

  ALTER TABLE employees SHRINK SPACE CASCADE;

  Which statement is correct in this scenario?

  A. The EMPLOYEES table will be changed to read-only mode during the shrink operation.

  B. The indexes created on the EMPLOYEES table will need to be rebuilt after the shrink operation is over.

  C. The shrink behavior will be cascaded to all dependent segments of the table that support a shrink operation.

  D. Data manipulation language (DML) operations will not be possible on the EMPLOYEES table during the COMPACTION phase of the shrink operation.

  Correct Answer: C

• Question 544:

  You have three temporary tablespace groups named G1, G2, and G3 in your database. You are creating a new temporary tablespace as follows:

  CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE '/u1/data/temp1.dbf' SIZE 10M TABLESPACE GROUP '';

  Which statement regarding the above command is correct?

  A. It will create the tablespace TEMP1 in group G1.

  B. It will create the tablespace TEMP1 in group G3.

  C. It will not add the tablespace TEMP1 to any group.

  D. It will create the tablespace TEMP1 in the default group.

  Correct Answer: C

• Question 545:

  Following is the list of locations in random order where oranfstab can be placed.

  1./etc/mtab 2.$ORACLE_HOME/dbs/oranfstab 3./etc/oranfstab

  What is the sequence in which Direct NFS will search the locations?

  A. 1, 2, 3

  B. 3, 2, 1

  C. 2, 3, 1

  D. 1, 3, 2

  Correct Answer: C

• Question 546:

  Which tasks can be accomplished using the DBMS_LOB.SETOPTIONS procedure?

  A. only encryption and compression settings for all SecureFile LOBs

  B. only encryption and deduplication settings for only SecureFile CLOBs

  C. deduplication, encryption, and compression settings for all SecureFile LOBs

  D. deduplication, encryption, and compression settings only for SecureFile CLOBs

  Correct Answer: C

  Refer to here.

  DBMS_LOB.SETOPTIONS()

  This procedure sets compression, deduplication and encryption features. It enables the features to be set on a per-LOB basis, overriding the default LOB settings. This call incurs a round trip to the server to make the changes persistent.

• Question 547:

  A PL/SQL procedure queries only those columns of a redefined table that were unchanged by the online table redefinition. What happens to the PL/SQL procedure after the online table redefinition?

  A. It remains valid.

  B. It becomes invalid for all options of online table redefinition but automatically gets revalidated the next time it is used.

  C. It becomes invalid for all options of online table redefinition and is automatically recompiled during online redefinition of the table.

  D. It becomes invalid only if the storage parameters have been modified and it automatically gets revalidated the next time it is used.

  Correct Answer: A

• Question 548:

  You create a new Automatic Database Diagnostic Monitor (ADDM) task:

  instance_analysis_mode_task. To view the ADDM report, you use the following command:

  SQL> SELECT dbms_addm.get_report('my_instance_analysis_mode_task') FROM dual;

  You want to suppress ADDM output relating to Segment Advisor actions on user SCOTT's segments.

  What would you do to achieve this?

  A. Add a finding directive for the ADDM task.

  B. Add a segment directive for the ADDM task.

  C. Add a parameter directive for the ADDM task.

  D. Disable the Segment Advisor from the Automatic Maintenance Task.

  Correct Answer: B

• Question 549:

  Which statement describes the information returned by the DBMS_SPACE.SPACE_USAGE procedure for LOB space usage?

  A. It returns space usage of only BasicFile LOB chunks.

  B. It returns space usage of only SecureFile LOB chunks.

  C. It returns both BasicFile and SecureFile LOB space usage for only nonpartitioned tables.

  D. It returns both BasicFile and SecureFile LOB space usage for both partitioned and nonpartitioned tables.

  Correct Answer: B

  SPACE_USAGE Procedures The first form of the procedure shows the space usage of data blocks under the segment High Water Mark. You can calculate usage for LOBs, LOB PARTITIONS and LOB SUBPARTITIONS. This procedure can only be used on tablespaces that are created with auto segment space management. The bitmap blocks, segment header, and extent map blocks are not accounted for by this procedure. Note that this overload cannot be used on SECUREFILE LOBs.

  

  The second form of the procedure returns information about SECUREFILE LOB space usage. It will return the amount of space in blocks being used by all the SECUREFILE LOBs in the LOB segment. The procedure displays the space actively used by the LOB column, freed space that has retention expired, and freed space that has retention unexpired. Note that this overload can be used only on SECUREFILE LOBs.

  

• Question 550:

  Which statement is true regarding online redefinition for the migration of BasicFile LOBs to SecureFile LOBs?

  A. It cannot be done in parallel.

  B. It can be done at the table level or partition level.

  C. It does not require additional storage because the operation is done online.

  D. Local and global indexes are maintained automatically during the operation.

  Correct Answer: B

  Refer to here Compatibility and Upgrading All features described in this document are enabled with compatibility set to 11.2.0.0.0 or higher. There is no downgrade capability after 11.2.0.0.0 is set. If you want to upgrade BasicFiles LOBs to SecureFiles LOBs, you must use typical methods for upgrading data (CTAS/ITAS, online redefinition, export/import, column to column copy, or using a view and a new column). Most of these solutions require twice the disk space used by the data in the input LOB column. However, partitioning and taking these actions on a partition-by- partition basis lowers the disk space requirements.

  Migrating Columns from BasicFiles LOBs to SecureFiles LOBs

  The method of migrating LOBs columns is presented in this section.

  Preventing Generation of REDO Space when Migrating to SecureFiles LOBs

  Generation of redo space can cause performance problems during the process of migrating BasicFiles LOB columns. Redo changes for the table are logged during the migration process only if the table has LOGGING set.

  Redo changes for the column being converted from BasicFiles LOB to SecureFiles LOB are logged only if the storage characteristics of the SecureFiles LOB column indicate LOGGING. The logging setting (LOGGING or NOLOGGING) for

  the LOB column is inherited from the tablespace in which the LOB is created.

  To prevent generation of redo space during migration make sure that you specify the NOLOGGING storage parameter for the new SecureFiles LOB column(s). You can turn LOGGING on once your migration is complete.

  Online Redefinition for BasicFiles LOBs

  Online redefinition is the only recommended method for migration of BasicFiles LOBs to SecureFiles

  LOBs. It can be done at the table or partition level.

  Online Redefinition Advantages

  No requirement to take the table or partition offline Can be done in parallel

  Online Redefinition Disadvantages

  Additional storage equal to the entire table or partition and all LOB segments must be available Global indexes must be rebuilt