OCA Oracle 10g Ch3 Database storage and Schema Objects

OCA Oracle 10g

Review Questions

1. Which of the following statements about tablespaces is true?
A. A tablespace is the physical implementation of logical structure called a namespace.
B. A tablespace can hold the objects of only one schema.
C. A bigfile tablespace can have only one datafile.
D. The SYSAUX tablespace is an optional tablespace only created if you install certain database
options.
-----
Ans: C
Bigfile tablespaces are new to Oracle10g and can have only a single datafile. The traditional
or smallfile tablespace can have many datafiles.

2. Automatic segment space management on the tablespace causes which of the following table
attributes in that tablespace to be ignored?
A. The whole storage clause
B. NEXT and PCTINCREASE
C. BUFFERPOOL and FREEPOOL
D. PCTFREE and PCTUSED
Ans: D.
Segment space management refers to free space management, with automatic segment space
management using bitmaps instead of FREELISTS, PCTFREE, and PCTUSED.

3. Which objects share the same namespace and therefore cannot have the same name?
A. Tables and indexes
B. Tables and procedures
C. Tables and constraints
D. Tables and triggers
--
Ans: B. Indexes, constraints, and triggers all have separate namespaces. Tables share a namespace with
views, sequences, private synonyms, procedures, functions, packages, materialized views, and
user-defined types. Objects within the same schema sharing a namespace must have unique names.

4. Which is not a type of segment that is stored in a tablespace?
A. Undo
B. Redo
C. Permanent
D. Temporary
--
Ans: B.
Redo information is not stored in a segment; it is stored in the redo logs. Undo segments are
stored in the undo tablespace, temporary segments are in the temporary tablespace, and permanent
segments go into all the other tablespaces.

5. With which parameters do you specify unlimited datafile growth?
A. MAXSIZE UNLIMITED
B. UNLIMITED GROWTH
C. MAXEXTENTS UNLIMITED
D. Datafile size cannot change.
--
Ans: A.
The autoextend MAXSIZE parameter tells Oracle how large a data or temp file can grow to.
UNLIMITED specifies no bounds to the automatic growth.

6. Which of the following is not a character datatype that can be used in a table column definition?
A. char
B. varchar
C. nvarchar2
D. string
---
Ans D.
The character datatypes include char, nchar, varchar, varchar2, nvarchar2, and long
but do not include string.

7. With which numeric datatype can you represent infinity?
A. double
B. float
C. binary_float
D. Infinity cannot be represented in the database.
---
Ans C.
With Oracle 6 through Oracle 9i, infinity could not be represented in the database. With
Oracle10g, however, binary_float and binary_double can represent infinity, not a number
(NAN), as well as several other special values.

8. A table name can never include the special meta-character dollar sign ($).
A. True
B. False
C. Only if the table name is enclosed in double quotes
D. Only if the table name is enclosed in single quotes
--
Ans B. Objects in an Oracle10g database can always include letters, numbers, and the characters
$, _, and # (dollar sign, underscore, and pound sign). Names can include any other character only
if they are enclosed in double quotes. The character dollar sign is not a special meta-character in
an Oracle10g database.

9. Which of the following column specifications results in a column that will store time values
down to the microsecond decimals of precision?
A. timestamp(3)
B. time(3)
C. datetime(3)
D. date(3)
--
Ans: A.
There is no time or datetime datatype in Oracle10g. The date datatype cannot store
subsecond granularity. The timestamp datatype stores subsecond granularity, defaulting to
six digits of precision.

10. Which type of index can specify as a unique index?
A. Bitmap
B. Heap organized
C. Btree
D. XOR
--
Ans C.
Heap organized is a table that is not index organized; it is not an index type. XOR is bitwise
function and not an index type. Bitmap is an index type, but cannot be used for a unique index.
Btree indexes make fine unique indexes.

11. Which operation can you not do to a table that is created with the following SQL statement?
CREATE TABLE properties
("Location" NUMBER primary key
,value NUMBER(15)
,lot varchar2(12)
,constraint positive_value check
(value > 0)
);
A. Rename the primary key to properties.
B. Insert a null into the value column.
C. Add a column named owner.
D. Rename the index supporting primary key to properties.
E. None of the above.
--
Ans: E. You can rename both a constraint and an index to the same name as a table. they are in
separate namespaces. Columns can be added, and owner is a valid column name. If the check
constraint condition evaluates to FALSE, the data value will not be allowed; if the condition evaluates
to either TRUE or NULL, the value is allowed.

12. Which of the statements is true regarding the table created with the following SQL statement?
CREATE TABLE autos
(vin VARCHAR2(64) primary key
,style VARCHAR2(15) default 'TUDOR'
,year char(4)
,make varchar2(12)
,model varchar2(30)
);
A. MAKE is a reserved word; the CREATE TABLE will fail.
B. The column style will always have a value.
C. There is no index on this table.
D. The column style can have a NULL value.
---
Ans: D.
A default clause ensures that the column does not contain a NULL after an insert, but not after
an update. MAKE is not a reserved word, and an index will be created on the primary key.

13. Which constraint-checking model is the default?
A. Initially immediate and deferrable
B. Initially immediate and not deferrable
C. Initially deferred and not immediately
D. Initially deferrable and not immediate
---
Ans B.
Constraints can be created as deferrable and initially deferred, but deferred constraint checking
is not the default.

14. Which statement on views is true?
A. A view can only be on one base table, although that base table can be joined to itself.
B. A view cannot be created with the same name and columns as the base table.
C. Inserts into a view are not allowed.
D. Privileges on a view can be different from those on the base table.
---
Ans: D.
Views can be created on one or more base table. Views share the same namespace as tables and
therefore cannot have the same name; columns, however, can be named the same as the base table.
SELECT, INSERT, UPDATE, and DELETE are all valid operations on a view. One of the uses for a view
is to hide portions of the base table, by granting different privileges to the view than the base table.

15. What can tablespaces be used for?
A. To organize tables and indexes into manageable groupings
B. To make sure that data stored in the tablespace does not change
C. To move data from one database to another
D. All of the above
--
Ans: D.
The primary use for a tablespace is to organize tables and indexes into manageable units.
Some of the manageable operations that you can do to a tablespace include making it read-only
or moving it from one database to another.

16. Which allocation unit is the smallest?
A. Datafile
B. Extent
C. Data block
D. Segment
--
Ans: C.
An extent is composed of two or more data blocks; a segment is composed of one or more
extents, and a datafile houses all these.


17. Which is a valid tablespace extent management specification?
A. Automatic
B. Local
C. Manual
D. Temporary
--
Ans: B.
A tablespace can have either dictionary extent management or local extent management.
B.

18. Which of the following is not a valid Oracle10g datatype?
A. timestamp with local timezone
B. binary
C. blob
D. urowid
--
Ans: B.
Although binary_float and binary_double are valid datatypes, binary is not.


19. How do you specify that a temporary table will be emptied at the end of a user’s session?
A. Create the temporary table with the ON COMMIT PRESERVE ROWS option.
B. Create the temporary table with the ON DISCONNECT PRESERVE ROWS option.
C. Create the temporary table with the ON DISCONNECT PURGE ROWS option.
D. Create the temporary table with the ON COMMIT DELETE ROWS option.
--
Ans: A. The options for temporary tables are either ON COMMIT DELETE ROWS, with causes the table to
flush at the end of each transaction, or ON COMMIT PRESERVE ROWS, which causes the table to flush
at the end of each session.

20. How can you change the comment assigned to the columns in a table?
A. Use the ALTER TABLE MODIFY COLUMN statement.
B. Use the COMMENT ON TABLE statement.
C. Use the RENAME statement.
D. Use the COMMENT ON COLUMN statement.
--
Ans: D.
You assign or change comments on a column with the COMMENT ON COLUMN statement. The
COMMENT ON TABLE statement is used to add or change the comment assigned to a table.