lz0-031- Question to be reviewed again

Qestions for future review

Drop a column.
- You need first mark column as UNUSED with
SQL> ALTER TABLE table_name SET UNUSED COLUMN column_name.
- And only after that you can drop this column from the table with
SQL> ALTER TABLE table_name DPOP UNUSED COLUMNS



Dropping a Column: Example
This statement illustrates the drop_column_clause with CASCADE CONSTRAINTS. Assume table t1 is created as follows:CREATE TABLE t1 (
pk NUMBER PRIMARY KEY,
fk NUMBER,
c1 NUMBER,
c2 NUMBER,
CONSTRAINT ri FOREIGN KEY (fk) REFERENCES t1,
CONSTRAINT ck1 CHECK (pk > 0 and c1 > 0),
CONSTRAINT ck2 CHECK (c2 > 0)
);


An error will be returned for the following statements:/* The next two statements return errors:
ALTER TABLE t1 DROP (pk); -- pk is a parent key
ALTER TABLE t1 DROP (c1); -- c1 is referenced by multicolumn
-- constraint ck1


Submitting the following statement drops column pk, the primary key constraint, the foreign key constraint, ri, and the check constraint, ck1:ALTER TABLE t1 DROP (pk) CASCADE CONSTRAINTS;

(note: you can do:
??> how to add a table column?


----------------------------------------------------------------

QUESTION 38
For a tablespace created with automatic segment-space management, where is free space managed?
A. In the extent
B. In the control file
C. In the data dictionary
D. In the undo tablespace
Answer: D
Free space is managed in the undo tablespace for a tablespace created with automatic segment-

DS: How is it mananged?

-----------------------

QUESTION 41
After running the ANALYZE INDEX orders cust_idx VALIDATE STRUCTURE command, you query the
INDEX_STATS
view and discover that there is a high ratio of DEL_LF_ROWS to LF_ROWS values for this index.
You decide to reorganize the index to free up the extra space, but the space should remain allocated to the
ORDERS_CUST_IDX index so that it can be reused by new entries inserted into the index.
Which command(s) allows you to perform this task with the minimum impact to any users who run queries that
need to access this index while the index is reorganized?
A. ALTER INDEX REBUILD
B. ALTER INDEX COALESCE
C. ALTER INDEX DEALLOCATE UNUSED
D. DROP INDEX followed by CREATE INDEX
Answer: B
To perform this task you need to use the ALTER INDEX COALESCE command to avoid overhead on the users
who run queries that
need access this index while the index is reorganized.
Incorrect Answers
A: The ALTER INDEX REBUILD command is used to correct index staleness. In order to rebuild the index,
Oracle places a SDML lock on the base table whose index is about to be rebuilt. During the time Oracle holds
this DML lock, you cannot make any changes to data in the base table. You can also use a less-restrictive
locking mechanisms. It requires to use keyword ONLINE in the ALTER INDEX REBUILD command.
C: The ALTER INDEX DEALLOCATE UNUSED can cause an impact to users who run queries.
D: You cannot drop and than recreate index because of heavy index usage by users.
-------------------------------------------


QUESTION 58
Which type of table is usually created to enable the building of scalable applications, and is useful for large
tables that can be queried or manipulated using several processes concurrently?
A. Regular table
B. Clustered table
C. Partitioned table
D. Index-organized table

Answer: C
Partitioned tables can be queried or manipulated using several processes concurrently. This type of tables is
very
helpful to build scalable applications with large tables.
Incorrect Answers
A: Regular tables can be also queried or manipulated using parallel processing, but the gain of partitioned
tables is more significant.
B: Clustered tables cannot be manipulated using several processes concurrently because they contain some
blocks as a common part for two or more tables. Also there is no sense to build clusters on the large tables with
high level of inserts, updates and deletes.
D: Index-organized tables are particularly useful for data that is mostly retrieved based on primary key. They
are not good source for parallel data processing.

-----------------------------------------------------------------------

QUESTION 74
You set the value of the OS_AUTHENT_PREFIX initialization parameter to OPS$ and created a user account
by issuing this SQL statement:
CREATE USER OPS$smith
IDENTIFIED EXTERNALLY;
Which two statements are true? (Choose two.)
A. Oracle server assigns the DEFAULT profile to the user.
B. You can specify the PASSWORD EXPIRE clause for an external user account.
C. The user does not require CREATE SESSION system privilege to connect to the database.
1Z0-031
D. If you query the DBA_USERS data dictionary view the USERNAME column will contain the value SMITH.
E. The user account is maintained by Oracle, but password administration and user authentication are performed
by the operating
system or a network service.
Answer: A, E
If you set the value of the OS_AUTHENT_PREFIX initialization parameter to OPS$ Oracle server assigns the
DEFAULT profile to the user defined externally. Also the user account is maintained by Oracle, but password
administration and user authentication are performed by the operating system or a network service, not Oracle.
A user defined externally does not require CREATE SESSION system privilege to connect to the database. The
DBA_USERS data
dictionary will show in the USERNAME column value SMITH, not OPS$SMITH.
Incorrect Answers
B: You cannot specify the PASSWORD EXPIRE clause for an external user account.
C: The user requires CREATE SESSION system privilege to connect to the database as any other database user.
D: The DBA_USERS will show OPS$SMITH value in the USERNAME column, not SMITH.
OCA Oracle 9i Associate DBA Certification Exam Guide, Jason Couchman, p. 807-810
Chapter 15: Managing Database Users

----------------------

QUESTION 75
Which two statements about segments are true? (Choose two.)
A. Each table in a cluster has its own segment.
B. Each partition in a partitioned table is a segment.
C. All data in a table segment must be stored in one tablespace.
D. If a table has three indexes only one segment is used for all indexes.
E. A segment is created when an extent is created, extended, or altered.
F. A nested table of a column within a table uses the parent table segment.
Answer: B, C
Each partition in a partitioned table is a segment. All data in a table segment must be stored in one tablespace.
Incorrect Answers
A: Each table in a cluster does not have its own segment. Clustered tables contain some blocks as a common
part for two
or more tables. Clusters enable you to store data from several tables inside a single segment so users can
retrieve data from those two tables together very quickly.
D: It can be some index segments for the one table.
E: A segment is created when an extent is created, not when extent is extended or altered.
F: A nested table of a column within a table does not use the parent table segment: it has its own.
--------------------------------

QUESTION 77
Examine the command:
CREATE TABLE employee
( employee_id NUMBER CONSTRAINT employee_empid_pk
PRIMARY KEY,
employee_name VARCNAR2(30),
manager_id NUMBER CONSTRAINT employee_mgrid_fk
REFERENCES employee (employee_id));
The EMP table contains self referential integrity requiring all NOT NULL values inserted in the
MANAGER_ID column to
exist in the EMPLOYEE_ID column.
Which view or combination of views is required to return the name of the foreign key constraint and the
referenced primary
key?
A. DBA_TABLES only
B. DBA_CONSTRAINTS only
C. DBA_TABS_COLUMNS only
D. DBA_CONS_COLUMNS only
E. DBA_TABLES and DBA_CONSTRAINTS
F. DBA_TABLES and DBA_CONS_COLUMNS
Answer: B
The DBA_CONSTRAINTS view is required to return the name of the foreign key constraint and the referenced
primary key. The
CONSTRAINT_NAME column provides the name of the foreign key constraint. And the R_
CONSTRAINT_NAME shows the
referenced primary key.
Incorrect Answers

A: The DBA_TABLES dictionary view is used to provide only information about tables, not constraints.
C: The DBA_TABS_COLUMNS dictionary view shows information only about columns in the tables.
D: This view lists detailed information about every column associated with a constraint, but it does not return
the name of the referenced primary key.
E: You don't need to use DBA_TABLES dictionary view to retrieve this information.
F: You don't need to use DBA_TABLES and DBA_CONS_COLUMNS data dictionary views to retrieve this
information.

---------------

QUESTION 146
Your company hired Joe, a DBA who will be working from home. Joe needs to have the ability to start the
database remotely.
You created a password file for your database and set REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE
in the parameter file.
Which command adds Joe to the password file, allowing him remote DBA access?
A. GRANT DBA TO JOE;
B. GRANT SYSDBA TO JOE;
C.GRANT RESOURCE TO JOE;
D. orapwd file=orapwdPROD user=JOE password=DBA
Answer: B
The GRANT SYSDBA TO JOE command will add Joe to the password file, allowing him remote DBA access
when the
REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE in the parameter file.
Incorrect Answers
A: With this command Joe will be granted DBA role, but the password file needs to be edited to allow remote
access for the user to the database.
C: This command just grants RESOURCE role to the user. It will not provide remote access for the user.
D: Joe can be added to the password file using ORAPWD utility to open the remote access for the user if
REMOTE_LOGIN_PASSWORDFILE is set to SHARED.
OCA Oracle 9i Associate DBA Certification Exam Guide, Jason Couchman, p. 509-516
Chapter 10: Basics of the Oracle Database Architecture

----------------

QUESTION 148
Temporary tablespaces should be locally managed and the uniform size should be a multiple of the ________.
1Z0-031
A. DB_BLOCK_SIZE
B. DB_CACHE_SIZE
C. SORT_AREA_SIZE
D. Operating system block size
Answer: C
It's requirement that the uniform size of the temporary tablespace should be a multiple of the
SORT_AREA_SIZE
initialization parameter. Because, by the definition of a disk sort, the data written to disk will equal
SORT_AREA_SIZE, your extents must be at least that large. Size your initial sort segment according to the
formula num X SORT_AREA_SIZE + DB_BLOCK_SIZE, where num is a small number of your choice used
as multiplier of SORT_AREA_SIZE.
Incorrect Answers
A: The uniform size should be a multiple of the SORT_AREA_SIZE, not the DB_BLOCK_SIZE.
B: The uniform size has nothing to do with DB_CACHE_SIZE. DB_CACHE_SIZE specifies the size of the
DEFAULT buffer pool for buffers with the primary block size (the block size defined by the
DB_BLOCK_SIZE parameter).
D: It is not related with operating system block size either.
--------------

QUESTION 158
You are trying to determine how many disk sorts are happening on the database right now. Which of the
following dictionary tables would you use to find that information?
A. V$SESSION
B. V$SYSSTAT
C. DBA_SEGMENTS
D. V$SORT_USAGE
Answer: D
The V$SORT_USAGE view shows the sessions that are using sort segments in your database. Although you
may want to join that data in V$SESSION to see the username corresponding with the session, this view by
itself gives no indication about current disk sorts. V$SESSTAT or DBA_SEGMENTS do not either,
eliminating those choices as well.
-----------------------------------

QUESTION 159
When determining the number of UNDO segments in a database, which of the following choices identifies a
factor to consider?
A. Size of typical transactions
B. Concurrent transactions
C. Size of rows in table most frequently changed
D. Number of anticipated disk sorts
Answer: B
The number of concurrent transactions is used in part to determine the number of UNDO segments your
database should have. Had the question asked for which choice played a role in determining the size of extents
or total UNDO segment size, then size of typical transactions or concurrent transactions would have been
correct. Because disk sorts have little impact on UNDO segments, under no circumstances should you have
chosen number of anticipated disk sorts.
Oracle 9i: Administrator's Guide
Oracle 9i: Concepts
-----------------------------

QUESTION 190
You are defining areas on your Oracle database. Which of the following profile areas can be used to control the
resource usage for the other four?
A. LOGICAL_READS_PER_SESSION
B. CONNECT_TIME
C. COMPOSITE_LIMIT
D. CPU_PER_SESSION
E. PRIVATE_SGA
Answer: C
In this question, you must read the choices carefully. And understand what is being asked. The real question here is whether you understand resource costs and composite limits. Each of the choices other than
COMPOSITE_LIMIT can be rolled up into COMPOSITE_LIMIT with the use of resource costing. Only the
resources available for profiles can be included as part of a composite limit.
------------------------

QUESTION 200
The rows inside three Oracle tables supporting a customer order entry system are frequently accessed together
by means of a table join. Because data is always being added to the tables, you leave a lot of extra space inside
each block to accommodate growth. Which of the following types of tables would be useful for storing the data
in this context?
A. Temporary tables
B. Index-organized tables
C. Cluster tables
D. Standard Oracle tables
Answer: D
Although cluster tables initially might seem like the right answer, notice that the question states that this table
experiences frequent data change activity - the bane of a cluster table's existence. Thus, you must use standard
tables, and this choice is correct. Nothing in the question indicates that you need the functionality offered by
temporary tables. Finally, nothing in the question indicates the need for an IOT.