SQL
2 Questions
Study Guide: This page shows the correct answers and explanations for all questions in this topic.
Review these to understand the key concepts.
Question:
Which two statements are true regarding Oracle database space management within blocks managed by Automatic Segment Space Management (ASSM)?
Correct Answer: A,C
- A. ASSM assigns blocks to one of the four fullness categories based on what percentage of the block is allocated for rows.
- C. Insert operations always insert new rows into blocks with fr ee space appropriate to the length of the row being inserted.
Explanation
A .True. ASSM categorizes blocks (e.g., 0-25%, 25 -50%) for efficient space use. B .False. Updates may cause chaining/migration, not always relocation. C .True. ASSM optimizes inserts into suitable blocks. D .False. ASSM uses a bitmap, not necessarily the first block. E .False. PCTFREE is segment-specific, not universally 10%.
Question:
Examine these commands: [oracle@host01 ~]$ sqlplus u1/oracle SQL> SELECT * FROM emp; ENO ENAME DN 1 Alan 2 2 Ben 2 SQL> exit [oracle@host01 ~]$ cat emp.dat 1, Alan, 2 3, Curl, 4 4, Bob, 4 [oracle@host01 ~]$ sqlldr u1/oracle TABLE=emp Which two statements are true?
Correct Answer: B,C
- B. It generates a log that contains control file entries, wh ich can be used with normal SQL*Loader operations.
- C. It appends data from EMP.DAT to EMP.
Explanation
SQL*Loader (sqlldr) loads data from external files into Ora cle tables. The command sqlldr u1/oracle TABLE=emp uses defaults since no control file is sp ecified. Let’s evaluate: A . It overwrites the data for Alan and adds data for Curl and Bob. False. SQLLoader’s default mode is APPEND, not REPLACE. It doesn’t “overwrite” existing rows unless REPLACE or TRUNCATE is specified in a control file. Here, row 1, Alan, 2 exists, and SQLLoader will either skip it (if a primary key rejects duplicates) or raise an erro r, but it won’t overwrite. 3, Curl, 4 and 4, Bob, 4 are appended. Mechanics:Without a control file, SQL*Loader assumes APPEND and matches columns positionally (ENO, ENAME, DN). B . It generates a log that contains control file entries, whic h can be used with normal SQL*Loader operations. True. SQL*Loader always generates a log file (e.g., emp.lo g) when invoked. With no control file specified, it auto-generates one internally and logs it, inc luding entries like LOAD DATA INFILE 'emp.dat' APPEND INTO TABLE emp FIELDS TERMINATED BY ',' (ENO, ENAME, DN). This can be reused. Practical Use:The log’s control section is editable for future runs (e.g., changing to REPLACE). C . It appends data from EMP.DAT to EMP. True. Default behavior without a control file is APPEND, a dding new rows (3, Curl, 4 and 4, Bob, 4) to EMP. Existing rows (1, Alan, 2, 2, Ben, 2) remain unless constrai ned (e.g., unique key violations). Mechanics:SQL*Loader processes each line of emp.dat, skipping duplicates if constrained, appending otherwise. D . It generates a SQL script that it uses to load data from EMP.DAT to EMP. False. SQL*Loader doesn’t generate SQL scripts; it uses direct path or conventional path loading, not SQL scripts. The log contains control file syntax, not a script. E . It overwrites all data in EMP with data from EMP.DAT. False. REPLACE or TRUNCATE would overwrite, but these require a control fi le with those options. Default APPEND preserves existing data.