Practice Test Oracle 9i fundamentals 1

You examine the alert log file and notice that errors are being generated from a QL*Plus session. Which files are best for providing you with more information about the nature of the problem?
User trace files
Background trace files
Initialization parameter files
Which two actions cause a log switch?
The current online redo log group is filled & A transaction completes.
The current online redo log group is filled & The instance is started.
The current online redo log group is filled & The instance is shut down
Which statement about the shared pool is true?
The shared pool contains only fixed structures
The shared pool consists of the library cache and buffer cache.
The shared pool stores the most recently executed SQL statements and the most recently accessed data definitions
You need to create an index on the PASSPORT_RECORDS table. It contains 10 million rows of data. The key columns have low cardinality. The queries generated against this table use a combination of multiple WHERE conditions involving the OR operator. Which type of index would be best for this type of table?
Unique
Partitioned
Reverse key
Your database contains a locally managed uniform sized tablespace with automatic segment-space management, which contains only tables. Currently, the uniform size for the tablespace is 512 K. Because the tables have become so large, your configuration must change to improve performance. Now the tables must reside in a tablespace that is locally managed, with uniform size of 5 MB and automatic segment-space management. What must you do to meet the new requirements?
Re-create the control file with the correct settings.
Use the ALTER TABLESPACE command to increase the uniform size.
Create a new table space with correct settings then move the tables into the new table space.
Which statement is not true about the use of online redo log files?
Each redo log within a group is called a member.
Redo log files provide the means to redo transactions in the event of an instance failure
Redo log files are organized into a minimum of three groups.
You decided to use multiple buffer pools in the database buffer cache of your database. You set the sizes of the buffer pools with the DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE parameters and restarted your instance. What else must you do to enable the use of the buffer pools?
List each object with the appropriate buffer pool initialization parameter.
Shut down the database to change the buffer pool assignments for each schema object.
Issue the ALTER statement and specify the buffer pool in the BUFFER_POOL clause for the schema objects you want to assign to each buffer pool.
Which statement about an Oracle instance is true?
Multiple instances can execute on the same computer, each accessing its own physical database.
An Oracle instance is a combination of memory structures, background processes, and user processes.
In a shared server environment, the memory structure component of an instance consists of a single SGA and a single PGA.
User A issues this command: UPDATE emp SET id=200 WHERE id=1 Then user B issues this command: UPDATE emp SET id=300 WHERE id=1 User B informs you that the UPDATE statement seems to be hung. How can you resolve the problem so user B can continue working?
Ask user B to abort the statement
Ask user A to commit the transaction
Ask user B to commit the transaction
Which data dictionary view would you use to get a list of all database users and their Default settings?
USERS_USERS
DBA_USERS
V$SESSION
You need to create an index on the CUSTOMER_ID column of the CUSTOMERS table. The index has these requirements: 1. The index will be called CUST_PK. 2. The index should be sorted in ascending order. 3. The index should be created in the INDEX01 tablespace, which is a dictionary 4. All extents of the index should be 1 MB in size. 5. The index should be unique. 6. No redo information should be generated when the index is created. 7. 20% of each data block should be left free for future index entries. Which command creates the index and meets all the requirements?
CREATE UNIQUE INDEX cust_pk ON customers(customer_id) TABLESPACE index0lPCTFREE 20 STORAGE (INITIAL 1m NEXT 1m PCTINCREASE 0) NOLOGGING;
CREATE UNIQUE INDEX cust_pk ON customers(customer_id) TABLESPACE index0lPCTUSED 80 STORAGE (INITIAL lm NEXT lm PCTINCREASE 0) NOLOGGING;
CREATE UNIQUE INDEX cust_pk ON customers(customer_id) TABLESPACE index0lPCTUSED 80STORAGE (INITIAL lm NEXT lm PCTINCREASE 0);
You issue these queries to obtain information about the REGIONS table: SQL> SELECT segment_name, tablespace_name FROM user_segments WHERE segment_name = 'REGIONS'; SEGMENT_NAME TABLESPACE_NAME REGIONS SAMPLE SQL> SELECT constraint_name, constraint_type FROM user constraints WHERE table_name = ‘REGIONS’; CONSTRAINT_NAME C REGION_ID_NN C REG_ID P SQL> SELECT index_named FROM user indexes WHERE table_name = ‘REGIONS’; INDEX_NAME ---------------------------- REG_ID_PK You then issue this command to move the REGIONS table: ALTER TABLE regions MOVE TABLESPACE user_data; What else must you do to complete the move of the REGIONS table?
You must re-create the REGION_ID_NN and REG_ID_PK constraints.
You must drop the REGIONS table that is in the SAMPLE tablespace.
You must grant all privileges that were on the REGIONS table in the SAMPLE tablespace to the REGIONS table in the USER_DATA tablespace.
A new user, psmith, has just joined the organization. You need to create psmith as a valid user in the database. You have the following requirements: 1. Create a user who is authenticated externally. 2. Make sure the user has CONNECT and RESOURCE privileges. 3. Make sure the user does NOT have DROP TABLE and CREATE USER privileges. 4. Set a quota of 100 MB on the default table space and 500 K on the temporary table space. 5. Assign the user to the DATA_TS default table space and the TEMP_TS temporary table space. Which statement would you use to create the user?
CREATE USER psmith IDENTIFIED EXTERNALLY DEFAULT TABLESPACE data_ts QUOTA 500K ON temp_ts QUOTA 100M ON data_ts TEMPORARY TABLESPACE temp_ts; GRANT connect, resource TO psmith;
CREATE USER psmith IDENTIFIED EXTERNALLY DEFAULT TABLESPACE data_ts QUOTA 100M ON data_ts QUOTA 500K ON temp_ts TEMPORARY TABLESPACE temp_ts; GRANT connect TO psmith;
CREATE USER psmith INDENTIFIED GLOBALLY AS ‘’ DEFAULT TABLESPACE data_ts QUOTA 500K ON temp_ts QUOTA 100M ON data_ts TEMPORARY TABLESPACE temp_ts; GRANT connect, resource TO psmith; REVOKE DROP TABLE, CREATE USER from psmith ;
More stringent user access requirements have been issued. You need to do these tasks forthe user pward: 1. Change user authentication to external authentication. 2. Revoke the user's ability to create objects in the TEST TS tablespace. 3. Add a new default and temporary tablespace and set a quota of unlimited. 4. Assign the user to the CLERK profile. Which statement meets the requirements?
ALTER USER pward IDENTIFIED by pward DEFAULT TABLESPACE dsta_ts TEMPORARY TABLESPACE temp_ts QUOTA UNLIMITED ON data_ts QUOTA 0 ON test_ts PROFILE clerk;
ALTER USER pward IDENTIFIED EXTERNALLY DEFAULT TABLESPACE data_ts TEMPORARY TABLESPACE temp_ts QUOTA UNLIMITED ON data_ts QUOTA 0 ON test_ts PROFILE clerk;
ALTER USER pward IDENTIFIED EXTERNALLY DEFAULT TABLESPACE data_ts TEMPORARY TABLESPACE temp_ts QUOTA UNLIMITED ON data_ts QUOTA 0 ON test ts; GRANT clerk to pward;
There are three ways to specify National Language Support parameters: 1. initialization parameters 2. environment variables 3. ALTER SESSION parameters Match each of these with their appropriate definitions.
Parameters on the server side to specify the default server environment Parameters on the client side to specify locale-dependent behavior overriding the defaults set for the server Parameters override the default set for the session or the server
Parameters on the server side to specify the default server environment Parameters override the default set for the session or the server Parameters on the client side to specify locale-dependent behavior overriding the defaults set for the server
Parameters on the client side to specify locale-dependent behavior overriding the defaults set for the server Parameters override the default set for the session or the server Parameters on the server side to specify the default server environment
Which type of index does this syntax create? CREATE INDEX hr.employees_last_name_idx ON hr.employees(last_name) PCTFREE 30 STORAGE(INITIAL 200K NEXT 200K PCTINCREASE 0 MAXEXTENTS 50) TABLESPACE indx;
B-Tree
Partitioned
Reverse key
Which method enforces resource limits? A. ALTER SYSTEM SET RESOURCE_LIMIT= TRUE B. Set the RESOURCE_LIMIT parameter to FALSE C. CREATE PROFILE sessions LIMIT SESSIONS_PER_USER 2 CPU_PER_SESSION 10000 IDLE_TIME 60 CONNECT_TIME 480; D. ALTER PROFILE sessions LIMIT SESSIONS_PER_USER 2 CPU_PER_SESSION 10000 IDLE_TIME 60 CONNECT_TIME 480;
Set the RESOURCE_LIMIT parameter to FALSE
CREATE PROFILE sessions LIMIT SESSIONS_PER_USER 2 CPU_PER_SESSION 10000 IDLE_TIME 60 CONNECT_TIME 480;
ALTER PROFILE sessions LIMIT SESSIONS_PER_USER 2 CPU_PER_SESSION 10000 IDLE_TIME 60 CONNECT_TIME 480;
The database currently has one control file. You decide that three control files will provide better protection against a single point of failure. To accomplish this, you modify the SPFILE to point to the locations of the three control files. The message "system altered” was received after execution of the statement. You shut down the database and copy the control file to the new names and locations. On startup you receive the error ORA-00205: error in identifying control file. You look in the alert log and determine that you specified the incorrect path for the for control file. Which steps are required to resolve the problem and start the database?
Connect as SYSDBA. Shut down the database. Start the database in MOUNT mode. Remove the SPFILE by using a UNIX command.. Recreate the SPFILE from the PFILE. Use the ALTER SYSTEM SET CONTROL_FILES command to correct the error. Start the database.
Connect as SYSDBA.Shut down the database. Remove the control files using the OS command. Start the database in NOMOUNT mode. Remove the SPFILE by using an OS command. Re-create the SPFILE from the PFILE. Use the ALTER SYSTEM SET CONTROL_FILES command to define the control files. Shut down the database.
Shut down the database and Start the database.
Evaluate the following SQL: CREATE USER sh IDENTIFIED BY sh; GRANT CREATE ANY MATERIALIZED VIEW CREATE ANY DIMENSION , DROP ANY DIMENSION , QUERY REWRITE , GLOBAL QUERY REWRITE TO dw_manager WITH ADMIN OPTION; GRANT dw_manager TO sh WITH ADMIN OPTION; Which three actions is the user SH able to perform? (Choose three.)
Select from a table
Alter a materialized view that you created
Select from a materialized view that you created
You query DBA_CONSTRAINTS to obtain constraint information on the HR_EMPLOYEES table: SQL> select constraint_name, constraint_type, deferrable, deferred, validated from dba_constraints where owner = 'HR' and table_name='EMPLOYEES'; CONSTRAINT_NAME C DEFERRABLE DEFERRED VALIDATED --------------------- -------------- ----------- -------------------------------------------- EMP_DEPT_FK R NOT DEFERRABLE IMMEDIATE VALIDATED EMP_EMAIL_NV C NOT DEFERRABLE IMMEDIATE VALIDATED EMP_EMAIL_UK U NOT DEFERRABLE IMMEDIATE VALIDATED EMP_EMP_ID_PK P NOT DEFERRABLE IMMEDIATE VALIDATED EMP_HIRE_DATE_NN C NOT DEFERRABLE IMMEDIATE VALIDATED EMP_JOB_FK R NOT DEFERRABLE IMMEDIATE VALIDATED EMP_JOB_NN C DEFERRABLE DEFERRED NOT VALIDATED EMP_LAST_NAME_NN C NOT DEFERRABLE IMMEDIATE VALIDATED EMP_MANAGER_FK R NOT DEFERRABLE IMMEDIATE VALIDATED EMP_SALARY_MIN C NOT DEFERRABLE IMMEDIATE VALIDATED Which type of constraint is EMP_JOB_NN?
Unique
Not null
Primary key
Description:

Practice Test Oracle 9i fundamentals 1
Disclaimer: Content, such as images used in the questions (if any), have been picked up from various places for the sole purpose of Instruction.

Discussion

santosh

error plz solve it

3255 days 2 hours 2 minutes ago

binu

error in full module.....

3298 days 20 hours 2 minutes ago

Your Facebook Friends on WizIQ

More Tests By Author

ASP Server Controls
10 Questions | 667 Attempts

ASP.NET, Namespaces ASP.NET
10 Questions | 2028 Attempts

An ASP.NET Application
10 Questions | 437 Attempts