Chapter 9 - Managing Users, Security, and Globalization Support
OCA/OCP: Oracle9i DBA Fundamentals I Study Guide
by Biju Thomas and Bob Bryla
This is the last chapter
Review Questions
1. Profiles cannot be used to restrict which of the following?
A. CPU time used
B. Total time connected to the database
C. Maximum time a session can be inactive
D. Time spent reading blocks
----
There is no resource parameter in the profile definition to monitor the time spent reading blocks, but you can restrict the number of blocks read per SQL statement or per session.
Ans: D.
2. Which command is used to assign a profile to an existing user?
A. ALTER PROFILE.
B. ALTER USER.
C. SET PROFILE.
D. The profile should be specified when creating the user; it cannot be changed.
----
You use the PROFILE clause in the ALTER USER command to set the profile for an existing user. You must have the ALTER USER privilege to do this.
Ans: B.
How does one enforce strict password control?
3. Which resource is not used to calculate the COMPOSITE_LIMIT?
A. PRIVATE_SGA
B. CPU_PER_SESSION
C. CONNECT_TIME
D. LOGICAL_READS_PER_CALL
----
Call-level resources are not used to calculate the COMPOSITE_LIMIT. You can set the resource cost of the four resources (the fourth is LOGICAL_READS_PER_SESSION) using the ALTER RESOURCE COST command.
Ans: D
4. Choose the option that is not true.
A. Oracle creates a profile named DEFAULT when the database is created.
B. Profiles cannot be renamed.
C. DEFAULT is a valid name for a profile resource.
D. The SESSIONS_PER_USER resource in the DEFAULT profile initially has a value of 5.
---
All resources in the default profile have a value of UNLIMITED when the database is created. You can change these values.
Ans: D.
5. What is the maximum number of profiles that can be assigned to a user?
A. 1
B. 2
C. 32
D. Unlimited
----
A user can have only one profile assigned. You can query the profile assigned to a user from the DBA_USERS view.
Ans: A.
6. What happens when you create a new user and do not specify a profile?
A. Oracle prompts you for a profile name.
B. No profile is assigned to the user.
C. The DEFAULT profile is assigned.
D. The SYSTEM profile is assigned.
---
The DEFAULT profile is created when the database is created and is assigned to users if you do not specify a profile for the new user. Before you can assign a profile, you must create the user in the database.
Ans: C.
7. Which resource specifies the value in minutes?
A. CPU_PER_SESSION
B. CONNECT_TIME
C. PASSWORD_LOCK_TIME
D. All the above
---
CONNECT_TIME is specified in minutes, CPU_PER_SESSION is specified in hundredths of a second, and PASSWORD_LOCK_TIME is specified in days.
Ans: B.
8. Which password parameter in the profile definitions can restrict the user from using the old password for 90 days?
A. PASSWORD_REUSE_TIME
B. PASSWORD_REUSE_MAX
C. PASSWORD_LIFE_TIME
D. PASSWORD_REUSE_DAYS
---
PASSWORD_REUSE_TIME specifies the number of days required before the old password can be reused; PASSWORD_REUSE_MAX specifies the number of password changes required before a password can be reused. At least one of these parameters must be set to UNLIMITED.
Ans: A.
9. Which dictionary view shows the password expiration date for a user?
A. DBA_PROFILES
B. DBA_USERS
C. DBA_PASSWORDS
D. V$SESSION
---
The DBA_USERS view shows the password expiration date, account status, and locking date along with the user’s tablespace assignments, profile, creation date, and so on.
Ans: B.
10. Which clause in the CREATE USER command can be used to specify no limits on the space allowed in tablespace APP_DATA?
A. DEFAULT TABLESPACE
B. UNLIMITED TABLESPACE
C. QUOTA
D. PROFILE
---
You use the QUOTA clause to specify the amount of space allowed on a tablespace; you can specify a size or UNLIMITED. The user will have unlimited space if the system privilege UNLIMITED TABLESPACE is granted.
Ans: C.
11. User JAMES has a table named JOBS created on the tablespace USERS. When you issue the following statement, what effect it will have on the JOBS table?
ALTER USER JAMES QUOTA 0 ON USERS;
A. No more rows can be added to the JOBS table.
B. No blocks can be allocated to the JOBS table.
C. No new extents can be allocated to the JOBS table.
D. The table JOBS cannot be accessed.
----
When the space quota is exceeded or quota is removed from a user on a tablespace, the tables remain in the tablespace, but no new extents can be allocated.
Ans: C
12. Which view would you query to see whether John has the CREATE TABLE privilege?
A. DBA_SYS_PRIVS
B. DBA_USER_PRIVS
C. DBA_ROLE_PRIVS
D. DBA_TAB_PRIVS
---
CREATE TABLE is a system privilege. You can query system privileges from DBA_SYS_PRIVS or USER_SYS_PRIVS.
Ans: A.
13. Which clause should you specify to enable the grantee to grant the system privilege to other users?
A. WITH GRANT OPTION
B. WITH ADMIN OPTION
C. CASCADE
D. WITH MANAGE OPTION
---
The WITH ADMIN OPTION specified with system privileges enables the grantee to grant the privileges to others, and the WITH GRANT OPTION specified with object privileges enables the grantee to grant the privilege to others.
Ans: B.
14. Which of the following is not a system privilege?
A. SELECT
B. UPDATE ANY
C. EXECUTE ANY
D. CREATE TABLE
----
SELECT, INSERT, UPDATE, DELETE, EXECUTE, and REFERENCES are object privileges. SELECT ANY, UPDATE ANY, and so on are system privileges.
Ans: A.
15. Which data dictionary view can you query to see whether a user has the EXECUTE privilege on a procedure?
A. DBA_SYS_PRIVS
B. DBA_TAB_PRIVS
C. DBA_PROC_PRIVS
D. SESSION_PRIVS
---
The DBA_TAB_PRIVS, USER_TAB_PRIVS, and ALL_TAB_PRIVS views show information about the object privileges.
Ans: B
16. To grant the SELECT privilege on the table CUSTOMER to all users in the database, which statement would you use?
A. GRANT SELECT ON CUSTOMER TO ALL USERS;
B. GRANT ALL ON CUSTOMER TO ALL;
C. GRANT SELECT ON CUSTOMER TO ALL;
D. GRANT SELECT ON CUSTOMER TO PUBLIC;
----
PUBLIC is the group or class of database users to which all users of the database belong.
Ans: D.
17. Which role in the following list is not a predefined role from Oracle?
A. SYSDBA
B. CONNECT
C. IMP_FULL_DATABASE
D. RESOURCE
---
SYSDBA and SYSOPER are not roles; they are system privileges.
Ans: A.
18. How do you enable a role?
A. ALTER ROLE
B. ALTER USER
C. SET ROLE
D. ALTER SESSION
----
You use the SET ROLE command to enable or disable granted roles for the user. The view SESSION_ROLES shows the roles that are enabled in the session. All default roles are enabled when the user connects to the database.
Ans: C.
19. What is accomplished when you issue the following statement?
ALTER USER JOHN DEFAULT ROLE ALL;
A. John is assigned all the roles created in the database.
B. Future roles granted to John will not be default roles.
C. All of John’s roles are enabled, except the roles with passwords.
D. All of John’s roles are enabled when he connects to the database.
---
Ans D. Default roles are enabled when a user connects to the database even if the roles are password authorized.
20. Which command defines CONNECT and RESOURCE as the default roles for user JAMES?
A. ALTER USER
B. ALTER ROLE
C. SET ROLE
D. SET PRIVILEGE
---
Ans: A. The ALTER USER command defines the default role(s) for a user.
21. Which data dictionary view shows the database character set?
A. V$DATABASE
B. NLS_DATABASE_PARAMETERS
C. NLS_INSTANCE_PARAMETERS
D. NLS_SESSION_PARAMETERS
-----
Ans: B. The NLS_DATABASE_PARAMETERS view shows the database character set and all the NLS parameter settings. The character set cannot be changed at the instance or session level, so the character set information does not show up in the NLS_INSTANCE_PARAMETERS and NLS_SESSION_PARAMETERS views.
22. Choose two NLS parameters that cannot be modified using the ALTER SESSION statement.
A. NLS_CHARACTERSET
B. NLS_SORT
C. NLS_NCHAR_CHARACTERSET
D. NLS_TERRITORY
----
You cannot change the character set after creating the database. The CHARACTER SET and NATIONAL CHARACTER SET clauses are used in the CREATE DATABASE command.
Ans: A and C.
Manage Users
Thursday, September 14, 2006
Monday, September 11, 2006
Fundamental I Ch8 - Managing Tables, Indexes, and Constraints
Chapter 8 - Managing Tables, Indexes, and Constraints
OCA/OCP: Oracle9i DBA Fundamentals I Study Guide
Review Questions
1. A table is created as follows:
CREATE TABLE MY_TABLE (COL1 NUMBER)
STORAGE (INITIAL 2M NEXT 2M MINEXTENTS 6 PCTINCREASE 0);
When you issue the following statement, what will be the size of the table, if the high-water mark of the table is 200KB?
ALTER TABLE MY_TABLE DEALLOCATE UNUSED KEEP 1000K;
A. 1000KB
B. 200KB
C. 12000KB
D. 2MB
E. 13MB
---
Ans:
C. You use the KEEP parameter in the DEALLOCATE clause to specify the amount of space you want to keep in the table above the HWM. If you do not specify the KEEP parameter, Oracle deallocates all the space above the HWM if the HWM is above MINEXTENTS; otherwise, free space is de-allocated above MINEXTENTS.
2. Which command is used to drop a constraint?
A. ALTER TABLE MODIFY CONSTRAINT
B. DROP CONSTRAINT
C. ALTER TABLE DROP CONSTRAINT
D. ALTER CONSTRAINT DROP
----
Ans C. Constraints are defined on the table and are dropped using the ALTER TABLE command DROP clause. For dropping the primary key, you can also specify PRIMARY KEY instead of the constraint name. Similarly, to drop a unique constraint, you can also specify UNIQUE ().
3. When you define a column with datatype TIMESTAMP WITH LOCAL TIME ZONE, what is the precision of seconds stored?
A. 2
B. 6
C. 9
D. 0
---
Ans:
B. The TIMESTAMP datatypes have a default precision of 6 digits. The range of values can be from 0 to 9.
4. Which data dictionary view has the timestamp of the table creation?
A. DBA_OBJECTS
B. DBA_SEGMENTS
C. DBA_TABLES
D. All the above
---
Ans: A. The DBA_OBJECTS view has information about all the objects created in the database and has the timestamp and status of the object in the column CREATED. DBA_TABLES does not show the timestamp.
5. What happens when you issue the following statement and the CHAINED_ROWS table does not exist in the current schema?
ANALYZE TABLE EMPLOYEE LIST CHAINED ROWS;
A. Oracle creates the CHAINED_ROWS table.
B. Oracle updates the dictionary with the number of chained rows in the table.
C. Oracle creates the CHAINED_ROWS table under the SYS schema; if one already exists under SYS, Oracle uses it.
D. The statement fails.
---
Ans: D. If you do not specify a table name to insert the ROWID of chained/ migrated rows, Oracle looks for a table named CHAINED_ROWS in the user’s schema. If the table does not exist, Oracle returns an error. The dictionary (the DBA_TABLES view) is updated with the number of chained rows when you do a COMPUTE STATISTICS on the table.
6. The following statement is issued against the primary key constraint (PK_BONUS) of the BONUS table. (Choose two statements that are true.)
ALTER TABLE BONUS MODIFY CONSTRAINT PK_BONUS DISABLE VALIDATE;
A. No new rows can be added to the BONUS table.
B. Existing rows of the BONUS table are validated before disabling the constraint.
C. Rows can be modified, but the primary key columns cannot change.
D. The unique index created when defining the constraint is dropped.
---
6. A and D. DISABLE VALIDATE disables the constraint and drops the index, but keeps the constraint valid. No DML operation is allowed on the table.
7. Which clause in the ANALYZE command checks for the integrity of the rows in the table?
A. COMPUTE STATISTICS
B. VALIDATE STRUCTURE
C. LIST INVALID ROWS
D. None of the above
----
Ans: B. The VALIDATE STRUCTURE clause of the ANALYZE TABLE command checks the structure of the table and makes sure all rows are readable.
8. Which statement is not true?
A. A partition can be range-partitioned.
B. A subpartition can be range-partitioned.
C. A partition can be hash-partitioned.
D. A subpartition can be hash-partitioned.
----
Ans:
B. Subpartitions can only be hash-partitioned. A partition can be range-partitioned or hash-partitioned.
9. A table is created with an INITRANS value of 2. Which value would you choose for INITRANS of an index created on this table?
A. 4
B. 2
C. 1
----
Ans:
A. Since index blocks hold more entries per block than table data blocks hold, you should provide a higher value of INITRANS to the index than to the table.
10. When validating a constraint, why would you specify the EXCEPTIONS clause?
A. To display the ROWIDs of the rows that do not satisfy the constraint
B. To move the bad rows to the table specified in the EXCEPTIONS clause
C. To save the ROWIDs of the bad rows in the table specified in the EXCEPTIONS clause
D. To save the bad rows in the table specified in the EXCEPTIONS clause
---
Ans:
C. If you specify the EXCEPTIONS INTO clause when validating or enabling a constraint, the ROWIDs of the rows that do not satisfy the constraint are saved in the table specified in the EXCEPTIONS clause. You can remove the bad rows or fix the column values and enable the constraint.
11. Which keyword is not valid for the BUFFER_POOL parameter of the STORAGE clause?
A. DEFAULT
B. LARGE
C. KEEP
D. RECYCLE
----
The BUFFER_POOL parameter specifies a buffer pool cache for the table or index. The KEEP pool retains the blocks in the SGA. RECYCLE removes blocks from the SGA as soon as the operation is completed, and the DEFAULT pool is for objects for which KEEP or RECYCLE is not specified.
Ans: B
12. Which clause in the ALTER TABLE command do you use to reorganize a table?
A. REORGANIZE
B. REBUILD
C. RELOCATE
D. MOVE
----
You use the MOVE clause to reorganize a table. You can specify new tablespace and storage parameters. Queries are allowed on the table, but no DML operations are allowed during the move.
Ans: D.
13. Which line in the following code has an error?
1 ALTER TABLE MY_TABLE
2 STORAGE (
3 MINEXTENTS 4
4 NEXT 512K)
5 NOLOGGING;
A. 2
B. 3
C. 4
D. 5
----
When you change the storage parameters for an existing index or table, you cannot change the MINEXTENTS and INITIAL values.
Ans: B.
14. Which component is not part of the ROWID?
A. Tablespace
B. Data file number
C. Object ID
D. Block ID
----
The format of a ROWID is OOOOOOFFFBBBBBBRRR; OOOOOO is the object number, FFF is the relative data file number where the block is located, BBBBBB is the block ID where the row is located, and RRR is the row in the block.
Ans: A.
15. Which keyword do you use in the CREATE INDEX command to create a function-based index?
A. CREATE FUNCTION INDEX
B. CREATE INDEX ORGANIZATION INDEX
C. CREATE INDEX FUNCTION BASED
D. None of the above
----
You don’t need to specify a keyword to create a function-based index; you need only to specify the function itself. To enable a function- based index, you set the parameter QUERY_REWRITE_ENABLED to TRUE, and you set QUERY_REWRITE_INTEGRITY to TRUSTED.
Ans: D.
16. Which data dictionary view shows statistical information from the ANALYZE INDEX VALIDATE STRUCTURE command?
A. INDEX_STATS
B. DBA_INDEXES
C. IND
D. None; VALIDATE STRUCTURE does not generate statistics.
---
The INDEX_STATS and INDEX_HISTOGRAMS views show statistical information from the ANALYZE INDEX VALIDATE STRUCTURE statement.
Ans: A.
17. A constraint is created with the DEFERRABLE INITIALLY IMMEDIATE clause. What does this mean?
A. Constraint checking is done only at commit time.
B. Constraint checking is done after each SQL statement, but you can change this behavior by specifying SET CONSTRAINTS ALL DEFERRED.
C. Existing rows in the table are immediately checked for constraint violation.
D. The constraint is immediately checked in a DML operation, but subsequent constraint verification is done at commit time.
---
DEFERRABLE specifies that the constraint can be deferred using the SET CONSTRAINTS command. INITIALLY IMMEDIATE specifies that the constraint’s default behavior is to validate the constraint for each SQL statement.
Ans: B.
18. Which script creates the CHAINED_ROWS table?
A. catproc.sql
B. catchain.sql
C. utlchain.sql
D. No script is necessary; ANALYZE TABLE LIST CHAINED ROWS creates the table.
---
The utlchain.sql script, located in the rdbms/admin directory for the Oracle software installation, creates the table. When chained or migrated rows are found in the table after you issue the ANALYZE TABLE LIST CHAINED ROWS command, the ROWIDs of such chained/ migrated rows are inserted into the CHAINED_ROWS table.
Ans: C.
19. What is the difference between a unique constraint and a primary key constraint?
A. A unique key constraint requires a unique index to enforce the constraint, whereas a primary key constraint can enforce uniqueness using a unique or non-unique index.
B. A primary key column can be NULL, but a unique key column cannot be NULL.
C. A primary key constraint can use an existing index, but a unique constraint always creates an index.
D. A unique constraint column can be NULL, but primary key column(s) cannot be NULL.
----
Columns that are part of the primary key cannot accept NULL values.
Ans: D.
20. You can monitor an index for its usage by using the MONITORING USAGE clause of the ALTER INDEX statement. Which data dictionary view do you use to query the index usage?
A. USER_INDEX_USAGE
B. V$OBJECT_USAGE
C. V$INDEX_USAGE
D. DBA_INDEX_USAGE
----
The V$OBJECT_USAGE view has information about the indexes that are monitored. The START_MONITORING and END_ MONITORING columns give the start and end timestamp of monitoring. If an index is used, the USED column will have a value YES.
Ans: B.
OCA/OCP: Oracle9i DBA Fundamentals I Study Guide
Review Questions
1. A table is created as follows:
CREATE TABLE MY_TABLE (COL1 NUMBER)
STORAGE (INITIAL 2M NEXT 2M MINEXTENTS 6 PCTINCREASE 0);
When you issue the following statement, what will be the size of the table, if the high-water mark of the table is 200KB?
ALTER TABLE MY_TABLE DEALLOCATE UNUSED KEEP 1000K;
A. 1000KB
B. 200KB
C. 12000KB
D. 2MB
E. 13MB
---
Ans:
C. You use the KEEP parameter in the DEALLOCATE clause to specify the amount of space you want to keep in the table above the HWM. If you do not specify the KEEP parameter, Oracle deallocates all the space above the HWM if the HWM is above MINEXTENTS; otherwise, free space is de-allocated above MINEXTENTS.
2. Which command is used to drop a constraint?
A. ALTER TABLE MODIFY CONSTRAINT
B. DROP CONSTRAINT
C. ALTER TABLE DROP CONSTRAINT
D. ALTER CONSTRAINT DROP
----
Ans C. Constraints are defined on the table and are dropped using the ALTER TABLE command DROP clause. For dropping the primary key, you can also specify PRIMARY KEY instead of the constraint name. Similarly, to drop a unique constraint, you can also specify UNIQUE (
3. When you define a column with datatype TIMESTAMP WITH LOCAL TIME ZONE, what is the precision of seconds stored?
A. 2
B. 6
C. 9
D. 0
---
Ans:
B. The TIMESTAMP datatypes have a default precision of 6 digits. The range of values can be from 0 to 9.
4. Which data dictionary view has the timestamp of the table creation?
A. DBA_OBJECTS
B. DBA_SEGMENTS
C. DBA_TABLES
D. All the above
---
Ans: A. The DBA_OBJECTS view has information about all the objects created in the database and has the timestamp and status of the object in the column CREATED. DBA_TABLES does not show the timestamp.
5. What happens when you issue the following statement and the CHAINED_ROWS table does not exist in the current schema?
ANALYZE TABLE EMPLOYEE LIST CHAINED ROWS;
A. Oracle creates the CHAINED_ROWS table.
B. Oracle updates the dictionary with the number of chained rows in the table.
C. Oracle creates the CHAINED_ROWS table under the SYS schema; if one already exists under SYS, Oracle uses it.
D. The statement fails.
---
Ans: D. If you do not specify a table name to insert the ROWID of chained/ migrated rows, Oracle looks for a table named CHAINED_ROWS in the user’s schema. If the table does not exist, Oracle returns an error. The dictionary (the DBA_TABLES view) is updated with the number of chained rows when you do a COMPUTE STATISTICS on the table.
6. The following statement is issued against the primary key constraint (PK_BONUS) of the BONUS table. (Choose two statements that are true.)
ALTER TABLE BONUS MODIFY CONSTRAINT PK_BONUS DISABLE VALIDATE;
A. No new rows can be added to the BONUS table.
B. Existing rows of the BONUS table are validated before disabling the constraint.
C. Rows can be modified, but the primary key columns cannot change.
D. The unique index created when defining the constraint is dropped.
---
6. A and D. DISABLE VALIDATE disables the constraint and drops the index, but keeps the constraint valid. No DML operation is allowed on the table.
7. Which clause in the ANALYZE command checks for the integrity of the rows in the table?
A. COMPUTE STATISTICS
B. VALIDATE STRUCTURE
C. LIST INVALID ROWS
D. None of the above
----
Ans: B. The VALIDATE STRUCTURE clause of the ANALYZE TABLE command checks the structure of the table and makes sure all rows are readable.
8. Which statement is not true?
A. A partition can be range-partitioned.
B. A subpartition can be range-partitioned.
C. A partition can be hash-partitioned.
D. A subpartition can be hash-partitioned.
----
Ans:
B. Subpartitions can only be hash-partitioned. A partition can be range-partitioned or hash-partitioned.
9. A table is created with an INITRANS value of 2. Which value would you choose for INITRANS of an index created on this table?
A. 4
B. 2
C. 1
----
Ans:
A. Since index blocks hold more entries per block than table data blocks hold, you should provide a higher value of INITRANS to the index than to the table.
10. When validating a constraint, why would you specify the EXCEPTIONS clause?
A. To display the ROWIDs of the rows that do not satisfy the constraint
B. To move the bad rows to the table specified in the EXCEPTIONS clause
C. To save the ROWIDs of the bad rows in the table specified in the EXCEPTIONS clause
D. To save the bad rows in the table specified in the EXCEPTIONS clause
---
Ans:
C. If you specify the EXCEPTIONS INTO clause when validating or enabling a constraint, the ROWIDs of the rows that do not satisfy the constraint are saved in the table specified in the EXCEPTIONS clause. You can remove the bad rows or fix the column values and enable the constraint.
11. Which keyword is not valid for the BUFFER_POOL parameter of the STORAGE clause?
A. DEFAULT
B. LARGE
C. KEEP
D. RECYCLE
----
The BUFFER_POOL parameter specifies a buffer pool cache for the table or index. The KEEP pool retains the blocks in the SGA. RECYCLE removes blocks from the SGA as soon as the operation is completed, and the DEFAULT pool is for objects for which KEEP or RECYCLE is not specified.
Ans: B
12. Which clause in the ALTER TABLE command do you use to reorganize a table?
A. REORGANIZE
B. REBUILD
C. RELOCATE
D. MOVE
----
You use the MOVE clause to reorganize a table. You can specify new tablespace and storage parameters. Queries are allowed on the table, but no DML operations are allowed during the move.
Ans: D.
13. Which line in the following code has an error?
1 ALTER TABLE MY_TABLE
2 STORAGE (
3 MINEXTENTS 4
4 NEXT 512K)
5 NOLOGGING;
A. 2
B. 3
C. 4
D. 5
----
When you change the storage parameters for an existing index or table, you cannot change the MINEXTENTS and INITIAL values.
Ans: B.
14. Which component is not part of the ROWID?
A. Tablespace
B. Data file number
C. Object ID
D. Block ID
----
The format of a ROWID is OOOOOOFFFBBBBBBRRR; OOOOOO is the object number, FFF is the relative data file number where the block is located, BBBBBB is the block ID where the row is located, and RRR is the row in the block.
Ans: A.
15. Which keyword do you use in the CREATE INDEX command to create a function-based index?
A. CREATE FUNCTION INDEX
B. CREATE INDEX ORGANIZATION INDEX
C. CREATE INDEX FUNCTION BASED
D. None of the above
----
You don’t need to specify a keyword to create a function-based index; you need only to specify the function itself. To enable a function- based index, you set the parameter QUERY_REWRITE_ENABLED to TRUE, and you set QUERY_REWRITE_INTEGRITY to TRUSTED.
Ans: D.
16. Which data dictionary view shows statistical information from the ANALYZE INDEX VALIDATE STRUCTURE command?
A. INDEX_STATS
B. DBA_INDEXES
C. IND
D. None; VALIDATE STRUCTURE does not generate statistics.
---
The INDEX_STATS and INDEX_HISTOGRAMS views show statistical information from the ANALYZE INDEX VALIDATE STRUCTURE statement.
Ans: A.
17. A constraint is created with the DEFERRABLE INITIALLY IMMEDIATE clause. What does this mean?
A. Constraint checking is done only at commit time.
B. Constraint checking is done after each SQL statement, but you can change this behavior by specifying SET CONSTRAINTS ALL DEFERRED.
C. Existing rows in the table are immediately checked for constraint violation.
D. The constraint is immediately checked in a DML operation, but subsequent constraint verification is done at commit time.
---
DEFERRABLE specifies that the constraint can be deferred using the SET CONSTRAINTS command. INITIALLY IMMEDIATE specifies that the constraint’s default behavior is to validate the constraint for each SQL statement.
Ans: B.
18. Which script creates the CHAINED_ROWS table?
A. catproc.sql
B. catchain.sql
C. utlchain.sql
D. No script is necessary; ANALYZE TABLE LIST CHAINED ROWS creates the table.
---
The utlchain.sql script, located in the rdbms/admin directory for the Oracle software installation, creates the table. When chained or migrated rows are found in the table after you issue the ANALYZE TABLE LIST CHAINED ROWS command, the ROWIDs of such chained/ migrated rows are inserted into the CHAINED_ROWS table.
Ans: C.
19. What is the difference between a unique constraint and a primary key constraint?
A. A unique key constraint requires a unique index to enforce the constraint, whereas a primary key constraint can enforce uniqueness using a unique or non-unique index.
B. A primary key column can be NULL, but a unique key column cannot be NULL.
C. A primary key constraint can use an existing index, but a unique constraint always creates an index.
D. A unique constraint column can be NULL, but primary key column(s) cannot be NULL.
----
Columns that are part of the primary key cannot accept NULL values.
Ans: D.
20. You can monitor an index for its usage by using the MONITORING USAGE clause of the ALTER INDEX statement. Which data dictionary view do you use to query the index usage?
A. USER_INDEX_USAGE
B. V$OBJECT_USAGE
C. V$INDEX_USAGE
D. DBA_INDEX_USAGE
----
The V$OBJECT_USAGE view has information about the indexes that are monitored. The START_MONITORING and END_ MONITORING columns give the start and end timestamp of monitoring. If an index is used, the USED column will have a value YES.
Ans: B.
Monday, September 04, 2006
Fundamentals I Ch7 - Segments and Storage Structures
Chapter 7 - Segments and Storage Structures
OCA/OCP: Oracle9i DBA Fundamentals I Study Guide
by Biju Thomas and Bob Bryla
Review Questions
1. Place the following logical storage structures in order—from the smallest logical storage unit to the largest.
A. Segment
B. Block
C. Tablespace
D. Extent
---
Ans:
B, D, A, and C. A data block is the smallest logical storage unit in Oracle. An extent is a group of contiguous blocks. A segment consists of one or more extents. A segment can belong to only one tablespace. A tablespace can have many segments.
2. When a table is updated, where is the before-image information (which can be used for undoing the changes) stored?
A. Temporary segment
B. Redo log buffer
C. Undo buffer
D. Rollback segment
---
Ans: D.
Before any DML operation, the undo information (before-image of data) is stored in the undo segments. This information is used to undo the changes and to provide a read-consistent view of the data.
3. Which parameter specifies the number of transaction slots in a data block?
A. MAXTRANS
B. INITRANS
C. PCTFREE
D. PCTUSED
----
Ans:
B. INITRANS specifies the number of transaction slots in a data block. Oracle uses a transaction slot when the data block is being modified. INITRANS reserves space for the transactions in the block.
MAXTRANS specifies the maximum number of concurrent transactions allowed in the block. The default of INITRANS for a block in a data segment is 1, and the default for the block in an index segment is 2.
4. Select the statement that is not true regarding undo tablespaces.
A. Undo tablespaces will not be created if they are not specified in the CREATE DATABASE statement.
B. Two undo tablespaces may be active if a new undo tablespace was specified and there are pending transactions on the old one.
C. You can switch from one undo tablespace to another.
B. UNDO_MANAGEMENT cannot be changed dynamically while the instance is running.
----
Ans:
A. If a specific undo tablespace is not defined in the CREATE DATABASE statement, Oracle automatically creates one with the name SYS_UNDOTBS .
5. Which of the following database objects consists of more than one segment?
A. Nested Table
B. Partitioned table
C. Index Partition
D. Undo segment
E. None of the above
---
Ans: B.
A partitioned table consists of multiple table partition segments in different tablespaces.
Notes: How about Nested Table?
Nested Table - If a table has columns that are tables themselves (nested tables), each column is stored in its own segment. Each segment may have its own storage parameters.
6. Which of the following segment allocation parameters is ignored when automatic segment space management is in effect for a tablespace?
A. FREELISTS
B. PCTFREE
C. INITRANS
D. MAXTRANS
----
Ans: A. Enabling automatic segment space management uses bitmaps instead of freelists to manage free space.
Notes: PCTUSED is ignored too.
7. Which data dictionary view would you query to see the free extents in a tablespace?
A. DBA_TABLESPACES
B. DBA_FREE_SPACE
C. DBA_EXTENTS
D. DBA_SEGMENTS
---
Ans:
B. DBA_FREE_SPACE shows the free extents in a tablespace. DBA_EXTENTS shows all the extents that are allocated to a segment.
8. Which two data dictionary views can account for the total amount of space in a data file?
A. DBA_FREE_SEGMENTS
B. DBA_FREE_SPACE
C. DBA_SEGMENTS
D. DBA_EXTENTS
---
Ans: B, D. The sum of the free space in DBA_FREE_SPACE plus the space allocated for extents in DBA_EXTENTS should add up to the total space specified for that tablespace. DBA_FREE_SEGMENTS is not a valid data dictionary view, and DBA_SEGMENTS only contains the number of extents and blocks allocated to each segment.
9. Which portion of the data block stores information about the table having rows in this block?
A. Common and variable header
B. Row directory
C. Table directory
D. Row data
Ans:
C. The table directory portion of the block stores information about the table having rows in the block. The row directory stores information such as row address and size of the actual rows stored in the row data area.
10. When does Oracle stop adding rows to a block?
A. When free space reaches the PCTFREE threshold
B. When row data reaches the PCTFREE threshold
C. When free space drops below the PCTUSED threshold
D. When row data drops below the PCTUSED threshold
---
Ans: A.
The PCTFREE and PCTUSED parameters are used to manage the free space in the block. Oracle inserts rows into a block until the free space falls below the PCTFREE threshold. PCTFREE is the amount of space reserved for future updates. Oracle considers adding more rows to the block only when the free space falls below the PCTUSED threshold.
11. What main restriction is placed on tablespaces defined with automatic segment space management?
A. The tablespace cannot contain nested tables.
B. The tablespace cannot be transportable.
C. The tablespace cannot contain LOBs.
D. The bootstrap segment cannot reside in a tablespace that has automatic segment space management enabled.
----
Ans:
C. Table segments that have LOBs cannot reside in a locally managed tablespace that has automatic segment space management enabled.
12. Which dynamic performance view can help you adjust the size of an undo tablespace?
A. V$UNDOSTAT
B. V$ROLLSTAT
C. V$SESSION
D. V$ROLLNAME
----
Ans:
A. The V$UNDOSTAT view, in conjunction with the value for UNDO_RETENTION and DB_BLOCK_SIZE parameters, can be used to calculate an optimal undo tablespace size when database activity is at its peak.
13. What is the default value of PCTFREE?
A. 40
B. 0
C. 100
D. 10
---
Ans:
D. The default value of PCTFREE is 10, and the default for PCTUSED is 40.
14. Which data dictionary view can you query to see the OPTIMAL value for a rollback segment?
A. DBA_ROLLBACK_SEGS
B. V$ROLLSTAT
C. DBA_SEGMENTS
D. V$ROLLNAME
----
Ans:
B. You can query the OPTIMAL value from the V$ROLLSTAT view. This view does not show the offline rollback segments.
15. What is row migration?
A. A single row spread across multiple blocks
B. Moving a table from one tablespace to another
C. Storing a row in a different block when there is not enough room in the current block for the row to expand
D. Deleting a row and adding it back to the same table
---
Ans:
C. Row migration is the movement of a row from one block to a new block. Row migration occurs when a row is updated and its new size cannot fit into the free space of the block; Oracle moves the row to a new block, leaving a pointer in the old block to the new block. You can avoid this problem by either setting a higher PCTFREE value or specifying a larger block size at database creation.
16. What can cause the Snapshot too old error?
A. Smaller rollback extents
B. Higher MAXEXTENTS value
C. Larger rollback extents
D. Higher OPTIMAL value
----
Ans:
A. Smaller rollback extents can cause the Snapshot too old error if there are long-running queries in the database.
17. The sum of the values PCTFREE and PCTUSED cannot exceed which of the following:
A. 255
B. DB_BLOCK_SIZE
C. The maximum is operating system dependent.
D. 100
---
D. These two numbers are percentages that are defined as the percentage of a given block, and since these areas cannot overlap, the sum cannot be greater than 100 percent.
18. Which of the following statements may require a temporary segment?
A. CREATE TABLE
B. CREATE INDEX
C. UPDATE
D. CREATE TABLESPACE
-----
Ans:
B. Operations that require a sort may need a temporary segment (when the sort operation cannot be completed in the memory area specified by SORT_AREA_SIZE ). Queries that use DISTINCT , GROUP BY , ORDER BY , UNION , INTERSECT , or MINUS clauses also need a sort of the result set.
19. How does Oracle determine the extent sizes for a temporary segment?
A. From the initialization parameters
B. From the tables involved in the sort operation
C. Using the default storage parameters for the tablespace
D. The database block size
---
Ans:
C. The default storage parameters for the tablespace determine the extent sizes for temporary segments.
20. Fill in the blank: The parameter MAXTRANS specifies the maximum number of concurrent transactions per __________.
A. Table
B. Segment
C. Extent
D. Block
---
Ans:
D. MAXTRANS specifies the maximum allowed concurrent transactions per block. Oracle needs transaction space for each concurrent transaction in the block s variable header. You can pre allocate space by specifying INITRANS .
OCA/OCP: Oracle9i DBA Fundamentals I Study Guide
by Biju Thomas and Bob Bryla
Review Questions
1. Place the following logical storage structures in order—from the smallest logical storage unit to the largest.
A. Segment
B. Block
C. Tablespace
D. Extent
---
Ans:
B, D, A, and C. A data block is the smallest logical storage unit in Oracle. An extent is a group of contiguous blocks. A segment consists of one or more extents. A segment can belong to only one tablespace. A tablespace can have many segments.
2. When a table is updated, where is the before-image information (which can be used for undoing the changes) stored?
A. Temporary segment
B. Redo log buffer
C. Undo buffer
D. Rollback segment
---
Ans: D.
Before any DML operation, the undo information (before-image of data) is stored in the undo segments. This information is used to undo the changes and to provide a read-consistent view of the data.
3. Which parameter specifies the number of transaction slots in a data block?
A. MAXTRANS
B. INITRANS
C. PCTFREE
D. PCTUSED
----
Ans:
B. INITRANS specifies the number of transaction slots in a data block. Oracle uses a transaction slot when the data block is being modified. INITRANS reserves space for the transactions in the block.
MAXTRANS specifies the maximum number of concurrent transactions allowed in the block. The default of INITRANS for a block in a data segment is 1, and the default for the block in an index segment is 2.
4. Select the statement that is not true regarding undo tablespaces.
A. Undo tablespaces will not be created if they are not specified in the CREATE DATABASE statement.
B. Two undo tablespaces may be active if a new undo tablespace was specified and there are pending transactions on the old one.
C. You can switch from one undo tablespace to another.
B. UNDO_MANAGEMENT cannot be changed dynamically while the instance is running.
----
Ans:
A. If a specific undo tablespace is not defined in the CREATE DATABASE statement, Oracle automatically creates one with the name SYS_UNDOTBS .
5. Which of the following database objects consists of more than one segment?
A. Nested Table
B. Partitioned table
C. Index Partition
D. Undo segment
E. None of the above
---
Ans: B.
A partitioned table consists of multiple table partition segments in different tablespaces.
Notes: How about Nested Table?
Nested Table - If a table has columns that are tables themselves (nested tables), each column is stored in its own segment. Each segment may have its own storage parameters.
6. Which of the following segment allocation parameters is ignored when automatic segment space management is in effect for a tablespace?
A. FREELISTS
B. PCTFREE
C. INITRANS
D. MAXTRANS
----
Ans: A. Enabling automatic segment space management uses bitmaps instead of freelists to manage free space.
Notes: PCTUSED is ignored too.
7. Which data dictionary view would you query to see the free extents in a tablespace?
A. DBA_TABLESPACES
B. DBA_FREE_SPACE
C. DBA_EXTENTS
D. DBA_SEGMENTS
---
Ans:
B. DBA_FREE_SPACE shows the free extents in a tablespace. DBA_EXTENTS shows all the extents that are allocated to a segment.
8. Which two data dictionary views can account for the total amount of space in a data file?
A. DBA_FREE_SEGMENTS
B. DBA_FREE_SPACE
C. DBA_SEGMENTS
D. DBA_EXTENTS
---
Ans: B, D. The sum of the free space in DBA_FREE_SPACE plus the space allocated for extents in DBA_EXTENTS should add up to the total space specified for that tablespace. DBA_FREE_SEGMENTS is not a valid data dictionary view, and DBA_SEGMENTS only contains the number of extents and blocks allocated to each segment.
9. Which portion of the data block stores information about the table having rows in this block?
A. Common and variable header
B. Row directory
C. Table directory
D. Row data
Ans:
C. The table directory portion of the block stores information about the table having rows in the block. The row directory stores information such as row address and size of the actual rows stored in the row data area.
10. When does Oracle stop adding rows to a block?
A. When free space reaches the PCTFREE threshold
B. When row data reaches the PCTFREE threshold
C. When free space drops below the PCTUSED threshold
D. When row data drops below the PCTUSED threshold
---
Ans: A.
The PCTFREE and PCTUSED parameters are used to manage the free space in the block. Oracle inserts rows into a block until the free space falls below the PCTFREE threshold. PCTFREE is the amount of space reserved for future updates. Oracle considers adding more rows to the block only when the free space falls below the PCTUSED threshold.
11. What main restriction is placed on tablespaces defined with automatic segment space management?
A. The tablespace cannot contain nested tables.
B. The tablespace cannot be transportable.
C. The tablespace cannot contain LOBs.
D. The bootstrap segment cannot reside in a tablespace that has automatic segment space management enabled.
----
Ans:
C. Table segments that have LOBs cannot reside in a locally managed tablespace that has automatic segment space management enabled.
12. Which dynamic performance view can help you adjust the size of an undo tablespace?
A. V$UNDOSTAT
B. V$ROLLSTAT
C. V$SESSION
D. V$ROLLNAME
----
Ans:
A. The V$UNDOSTAT view, in conjunction with the value for UNDO_RETENTION and DB_BLOCK_SIZE parameters, can be used to calculate an optimal undo tablespace size when database activity is at its peak.
13. What is the default value of PCTFREE?
A. 40
B. 0
C. 100
D. 10
---
Ans:
D. The default value of PCTFREE is 10, and the default for PCTUSED is 40.
14. Which data dictionary view can you query to see the OPTIMAL value for a rollback segment?
A. DBA_ROLLBACK_SEGS
B. V$ROLLSTAT
C. DBA_SEGMENTS
D. V$ROLLNAME
----
Ans:
B. You can query the OPTIMAL value from the V$ROLLSTAT view. This view does not show the offline rollback segments.
15. What is row migration?
A. A single row spread across multiple blocks
B. Moving a table from one tablespace to another
C. Storing a row in a different block when there is not enough room in the current block for the row to expand
D. Deleting a row and adding it back to the same table
---
Ans:
C. Row migration is the movement of a row from one block to a new block. Row migration occurs when a row is updated and its new size cannot fit into the free space of the block; Oracle moves the row to a new block, leaving a pointer in the old block to the new block. You can avoid this problem by either setting a higher PCTFREE value or specifying a larger block size at database creation.
16. What can cause the Snapshot too old error?
A. Smaller rollback extents
B. Higher MAXEXTENTS value
C. Larger rollback extents
D. Higher OPTIMAL value
----
Ans:
A. Smaller rollback extents can cause the Snapshot too old error if there are long-running queries in the database.
17. The sum of the values PCTFREE and PCTUSED cannot exceed which of the following:
A. 255
B. DB_BLOCK_SIZE
C. The maximum is operating system dependent.
D. 100
---
D. These two numbers are percentages that are defined as the percentage of a given block, and since these areas cannot overlap, the sum cannot be greater than 100 percent.
18. Which of the following statements may require a temporary segment?
A. CREATE TABLE
B. CREATE INDEX
C. UPDATE
D. CREATE TABLESPACE
-----
Ans:
B. Operations that require a sort may need a temporary segment (when the sort operation cannot be completed in the memory area specified by SORT_AREA_SIZE ). Queries that use DISTINCT , GROUP BY , ORDER BY , UNION , INTERSECT , or MINUS clauses also need a sort of the result set.
19. How does Oracle determine the extent sizes for a temporary segment?
A. From the initialization parameters
B. From the tables involved in the sort operation
C. Using the default storage parameters for the tablespace
D. The database block size
---
Ans:
C. The default storage parameters for the tablespace determine the extent sizes for temporary segments.
20. Fill in the blank: The parameter MAXTRANS specifies the maximum number of concurrent transactions per __________.
A. Table
B. Segment
C. Extent
D. Block
---
Ans:
D. MAXTRANS specifies the maximum allowed concurrent transactions per block. Oracle needs transaction space for each concurrent transaction in the block s variable header. You can pre allocate space by specifying INITRANS .
An example - Snapshot too old
Snapshot too old
User SCOTT has updated the EMP table and has not committed the changes. The old values of the rows updated by SCOTT are written to the undo segment. When user JAKE queries the EMP table, Oracle uses the undo segment to produce a read-consistent view of the table. If JAKE initiated a long query, Oracle fetches the blocks in multiple iterations. User SCOTT can commit his transaction, and the undo segment is marked committed. If another transaction overwrites the same undo segment, JAKE’s transaction will not be able to get the view of the EMP table when the transaction started. This produces a snapshot too old error.
User SCOTT has updated the EMP table and has not committed the changes. The old values of the rows updated by SCOTT are written to the undo segment. When user JAKE queries the EMP table, Oracle uses the undo segment to produce a read-consistent view of the table. If JAKE initiated a long query, Oracle fetches the blocks in multiple iterations. User SCOTT can commit his transaction, and the undo segment is marked committed. If another transaction overwrites the same undo segment, JAKE’s transaction will not be able to get the view of the EMP table when the transaction started. This produces a snapshot too old error.
Row chaining and Row Migration
row chaining and row migration.
If the table row length is bigger than a block, or if the table has LONG or LOB columns, it is difficult to fit one row entirely in one block. Oracle stores such rows in more than one block. This situation is unavoidable, and storing such rows in multiple blocks is known as row chaining.
In some cases, the row will fit into a block with other rows, but due to an update activity, the row length increases and no free space remains available to accommodate the modified row. Oracle then moves the entire row from its original block to a new block, leaving a pointer in the original block to refer to the new block. This process is known as row migration.
Both row migration and row chaining affect the performance of queries, because Oracle has to read more than one block to retrieve the row.
~
If the table row length is bigger than a block, or if the table has LONG or LOB columns, it is difficult to fit one row entirely in one block. Oracle stores such rows in more than one block. This situation is unavoidable, and storing such rows in multiple blocks is known as row chaining.
In some cases, the row will fit into a block with other rows, but due to an update activity, the row length increases and no free space remains available to accommodate the modified row. Oracle then moves the entire row from its original block to a new block, leaving a pointer in the original block to refer to the new block. This process is known as row migration.
Both row migration and row chaining affect the performance of queries, because Oracle has to read more than one block to retrieve the row.
~
Subscribe to:
Comments (Atom)
