Elance Oracle Database Test Answers 2015



What does the acronym RDBMS mean?
Rational Database Management System
Ratio Database Management System
Rational Database Manager System
Relational Database Management System
Relational Database Manager System


What are the parts of a Package?
Spec and Body
Main, Content and Footer
Header and Footer
Header, Body and Footer


Which data dictionary table should you query to view the object privileges granted to the user on specific columns?
USER_COL_PRIVS
USER_TAB_PRIVS_MADE
USER_TAB_PRIVS
USER_COL_PRIVS_MADE


Which of these is NOT an Oracle "database option?"
Database Vault
Active Data Guard
Oracle Real Application Testing
Oracle Questions
Total Recall


The "materialized view" in Oracle stores:
No output
Output in logical space
No output only the definition
Output in different physical space


"Select for Update" performs:
Lock the record on result set
Unlock the record on result set
Update the record set
Select the record and deelete


What is true about this set of statements? CREATE TABLE dept (deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13)); ROLLBACK; DESCRIBE DEPT;
The ROLLBACK statement frees the storage space occupied by the DEPT table.
The DESCRIBE DEPT statement displays the structure of the DEPT table.
The DESCRIBE DEPT statement displays the structure of the DEPT table only if there is a COMMIT statement introduced before the ROLLBACK statement.
The DESCRIBE DEPT statement returns an error ORA-04043:object DEPT does not exist


What is the definition of data buffer cache?
An area in SGA that is used to store the most recently used data block
Circular buffer that holds information about changes made to the database
An area in SGA taht contains the database checkpoints
An area that is used to allocate the I/O buffers from shared memory


In Oracle "view" consists of:
Indexed output of the table
The whole table
The definition only
The output of the table


The database administrator of your company created a public synonym called HR for the HUMAN_RESOURCES table of the GENERAL schema, because many users frequently use this table. As a user of the database, you created a table called HR in your schema. What happens when you execute this query? select * from HR
You get an error message because you cannot retrieve from a table that has the same name as a public synonym
You obtain the results retrieved from both the public synonym HR and the HR table that belongs to your schema, as a Cartesian product.
You obtain the results retrieved from the HR table that belongs to your schema.
You obtain the results retrieved from the public synonym HR created by the database administrator


REPLACE ('CUCKOO AND CARLIE', 'C', 'CH') will return:
CHUCHKOO AND CHARLIE
CUCKOO AND CARLIE
CHUCHKOO AND CARLIE
CHUCKOO AND CHARLIE


What command is used to restore an "accidentally" dropped table?
rollback to segment
flashback table <tablename> to before drop
alter database cancel drop <tablename>
rollback table <tablename> to before drop


Regarding Oracle, what does DES most commonly mean?
Data Extraction Segment
Data Entry Sheet
Dynamic Energy Saver
Digital Encryption System
Data Encryption Standard


What will the shortcut "Ctrl + F11" do?
Exit
Execute Query
Update Record
Enter Query
Clear Record


A block is the smallest unit of logical storage that the Relational Database Management System (RDBMS) can manipulate. Block size is determined by which of the following database parameter?
DB_SYSTEM_CLUSTER_SIZE
DB_BLOCK_SIZE
DB_SYSTEM_BLOCK_SIZE
DB_CLUSTER_SIZE


Which one of the following is not a DDL (Data Definition Language) command?
Alter
Create
Update
Drop


Which naming rules are applied to tables?
Can contain characters such as $, _ and #
Start with a letter
All of these
Can have the same name as any database object if that object is not owned by the same user


Which syntax turns an existing constraint on?
ALTER TABLE table_name ENABLE constraint_name;
ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;
ALTER TABLE table_name TURN ON CONSTRAINT constraint_name;
LTER TABLE table_name STATUS ENABLE CONSTRAINT constraint_name;


Which statement about views are true?
A view must have aliases defined for the column names in the SELECT statement.
A view cannot have an ORDERBY clause in the SELECT statement.
A view cannot be created with a GROUP BY clause in the SELECT statement.
A view can be created as a join on two or more tables.


Which of these is not a real Oracle suite option?
Oracle E-Business Suite
Oracle Developer Suite
Oracle Customer Service Suite
Oracle Enterprise Manager
Oracle Application Server


Which of the following statement correctly decribes how to rebuild an index?
ALTER INDEX index_name REBUILD
ALTER INDEX REBUILD index_name
ALTER index_name REBUILD
index_name REBUILD


What view would you use to look at the size of a data file?
DBA_FREE_SPACE
FREE_DATA_FILES
FILES_DBA_SPACE
DBA_DATA_FILES


The source code of procedure, function and package bodies can be queried from which data dictionary?
ALL_SOURCE
DBA_SOURCE
USER_SOURCE
All of these


Which of the following statements is true about rebuilding indexes?
All of these
Rebuilding indexes doesn't need a lock on the table
They can be rebuilt online
Performed by issuing the command ALTER INDEX


In a sequence, Curval returns:
More than current value
Less then current value
Zero
Present Value


The transaction control that prevents more than one user from updating data in a table is which of the following (choose one)?
lock
commit
savepoint
rollback


Remote access of database can be made by:
PMON
SGA
Database Link
SQL star plus


DBMS_SCHEDULER, along with the power of PL/SQL, provides a mechanism for automating some of the daily tasks. Which of the following is an advantage of using DBMS_SCHEDULER?
Is capable of running a program, anonymous PL/SQL blocks, stored procedure, executables or even a chain of commands
Makes your environment the same across the enterprise
All of these
Is relatively easy to grant or revoke access for each specific job to other user


How many columns are there in a Dual table?
1
8
9
7


Which of these is not a language element of SQL?
Clauses
None. All are language elements of SQL.
Queries
Statements
Expressions


A rollback segment can be specified as either PUBLIC or PRIVATE. The value of PCTINCREASE of a rollback segment is always set to:
0
2
4
6


SQL statement operator that tests whether a field value is unavailable, unassigned or unknown is:
IS NULL
= NULL
IS EMPTY
IS NOTHING


Which of the following are system privileges?
SELECT and DELETE
CREATE TABLE and DROP TABLE
DELETE and UPDATE
CREATE TABLE and SELECT


Which of the following operators can be used to substitute the 'IN' operator in a SELECT statement?
>=
AND
BETWEEN ... AND
<=


Define Index:
Random form of data
Create another table
Ordered form of data
Create another column


In business, what is the most common definition for CRM?
Customer Relationship Management
Contact Relationship Marketing
Customer Relationship Marketing
Client Relationship Management
Customer Resource Management


Which of the following is(are) physical storage file(s) of Oracle?
Control files
Redo log files
Data files
All of these


True or False: "Select for Update" performs the function "Lock the record on result set."
False
True


What is the purpose of the Rollback command?
Undo the last uncommitted transactions
Save the last uncommitted transactions
Redo the last uncommitted transactions
Maintains Log of the last uncommitted transactions


Which of the following is not DML (Data Manipulation Language)?
Rename
Insert
Delete
Update


The Oracle Certification Program contains three levels. Which of these is not one of them?
Oracle Certified Master (OCM)
Oracle Certified Professional (OCP)
Oracle Certified Associate (OCA)
Oracle Certified Developer (OCD)


The DBA assigns permission to the user using this command:
Role
Table
Lock


Order the following entities from the smallest to the largest:
Segments, extents, data blocks, tablespaces
Extents, data blocks, segments, tablespaces
Table spaces, data blocks, extents, segments
Data blocks, extents, segments, table spaces


When a user process fails, this background process will clean up after it:
PMON
SQLMON
SESSIONMON
WRAP


Which /SQL*Plus feature can be used to replace values in the WHERE clause?
Instead-of variables
Substitution variables
This feature cannot be implemented through /SQL*Plus.
Replacementvariables


You need to give the MANAGER role the ability to select from, insert into, and modify existing rows in the STUDENT_GRADES table. Anyone given this MANAGER role should be able to pass those privileges on to others. Which statement accomplishes this?
.GRANT select, insert, modify ON student_grades TO ROLE manager WITH GRANT OPTION;
GRANTselect,insert,modify ON student_grades TO manager WITH GRANT OPTION;
GRANT select, insert, update ON student_grades TO manager WITH GRANT OPTION;
GRANT select, insert, update ON student_grades TO manager;


You need to search for text data in a column, but you only remember part of the string. Which of the following SQL operations allows the use of wildcard comparisons?
IN
BETWEEN
LIKE
EXISTS


What clause follows "group by" in SQL?
Not in
When
Having
In


Which operator will help to filter out all duplicate records?
distinct
order by
join
delete


In terms of Oracle, what is the most common meaning of OEM?
Original Equipment Manufacturer
Oracle Equipment Model
Oracle Enterprise Manager
Order Entry Module
Oracle Entry Manager


SQL stands for:
Structured Query Language
Symmetric Query Language
Standard Query Language
Sequential Query Language


In business, what is the most common definition for ERP?
Electronic Resource Planning
Early Retirement Plan
Electronic Resource Pricing
Enterprise Resource Planning
Employee Referral Program


What operator would you choose to prevent this Oracle error message? ORA-01427:single -row subquery returns more than one row
Use the >= operator
Use the CAN EXIST operator
Use the <= operator
Use the = operator
Use the IN operator


Which of the following actions are performed by the MINUS operator?
Returns the output of top query minus the output of the query below the MINUS operator.
Returns the result of one numeric value minus another.
Returns all of the columns for the tables specified in the FROM clause minus those specified after the MINUS operator.


Which of these statement is NOT true?
A database has at least one datafile
A datafile can be associated with one or more database
Control file is used to identify the data files and the redo log files
A tablespace is a logical unit of database storage formed by one or more data files


The IN operator is used when you are using a subquery which returns more than one record. What is the operator used in a correlated subquery?
None of these
UNION
INTERSECT
EXISTS


How would you display a listing of the sums of employee salaries for those employees not making a commission, for each job type, including only those sums greater than 2500?
select job, sum(sal) from emp where sum(sal) > 2500 and comm is null group by job;
select job, sum(sal) from emp group by job having sum(sal) > 2500 and comm is not null;
select job, sum(sal) from emp where comm is null group by job having sum(sal) > 2500;
none of the above
select job, sum(sal) from emp where sum(sal) > 2500 and comm is null;


What will happen if PL/SQL variables occur in SQL statements and have the same name as a table column
Oracle will ignore the column name
None of these
Oracle will popup an execution error
Oracle will assume that it is the column that is being referenced


This protects the library cache from becoming corrupted by concurrent modifications by two sessions or by one session trying to read information that is being modified by another one.
Pitch
Latch
Cache
Power lock


A "virtual" table name to query the current time is:
V$PARAMETERS
DUAL
ALL_PARAMS
SYSDATE


What happends when you execute one transcaction and then truncate another table?
No effect on transaction
Transaction will rollback
Transcation will commit automatically
Truncate will not work


Implicit Cursor works when:
Executing a procedure
User logs in
DML operation is done
Executing a function


When you issue the command "ALTER DATABASE BACKUP CONTROLFILE TO TRACE", a text script version of backup control file will be created. Where is this file located?
In the same folder as the running command
In a folder pointed by SYSTEM_DUMP_DEST
In a folder pointed by USER_DUMP_DEST
In the ORACLE_HOME/backup folder


Which function is not in DBMS_OUTPUT?
PUT_LINE
PUT
PREV_LINE
NEW_LINE


Which of these programs is the most popular choice to load data directly into Oracle E-Business Suite?
Excel
Cognos
Forms Data Loader
Access
DataLoad


You need to modify the STUDENTS table to add a primary key on the STUDENT_ID column. The table is currently empty. Which statement accomplishes this task?
ALTER TABLE students ADD CONSTRAINT PRIMARY KEY (student_id);
ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
ALTER TABLE students ADD PRIMARY KEY student_id;
ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id;


Given the following data in the emp table: ENAME SALARY : PING 5000 AILYN 4999 SAM 1000 LESLIE 3000 TOM 2500 RAVI 10000 What will the following select statement produce? SELECT ename FROM emp WHERE salary BETWEEN 3000 AND 5000;
AILYN
None of these
PING AILYN LESLIE
an error


This type of error can be solved by increasing the undo retention of increasing the size of rollbacks.
ORA-02511
ORA-01555
ORA-01155
ORA-03620


What happens if the command ALTER TABLE <tablename> DEALLOCATE UNUSED is used without the KEEP clause?
None of these
Oracle will deallocate all unused space above the high water mark
Oracle will deallocate all unused space without any condition
Oracle will deallocate all unused space less than the high water mark


Which of the following is a good suggestion for avoiding unnecessary extension of a rollback segment?
Always use INITIAL = NEXT for rollback segments
Set OPTIMAL value to minimize the allocation or deallocation of rollback segment extents
Avoid setting MAXEXTENT to UNLIMITED
Always place rollback segment in a separate exclusive tablespace


The init.ora parameter that controls the location of the Alert log file is:
USER_DUMP_DEST
LOG_BUFFER
BACKGROUND_DUMP_DEST
UTL_FILE_DIR


The background process that checks for consistency of the database is called:
PMON
SMON
DBW0
LGWR


The most effective way to re-order the columns in a table is:
Write a stored procedure to manipulate the columns
Drop the table and re-create it
By using CREATE TABLE AS SELECT
Use ALTER TABLE MODIFY COLUMN command


Oracle default optimization follows:
Rule based optimization
Both Cost and Rule
Cost based optimiztion


How does PL/SQL allow programmers to use it on any host environment?
It modularizes program development
It is portable
It improves process performance
It processes Oracle errors with exception-handling routines


Which of the following SQL functions can operate on any datatype?
CEIL
LOWER
CEIL
TO_CHAR
MAX


When executing the command: "show parameter xxxx", ORA-00942: table or view does not exist appears. Which of the following best explains this error?
Parameter table does not exist
The statement is executed using "normal" user
The parameter has been changed but the transaction is never committed
The database is not started up


Before any SQL statement is parsed, Oracle will check the _______ to see if that same statement already exists there.
Dictionary cache
None of these
Library cache
Shared pool


This structure is not included in the Shared Pool contents.
Data dictionary cache
Database buffer cache
Library cache
User Global Area


One of these datatypes is NOT a base scalar datatype?
NUMBER
BOOLEAN
VARCHAR2
INT


The Oracle server provides a number of standard data dictionary views to obtain information on database and instance. These views are:
V$SGA, V$BGPROCESS,V$USERS
V$DATAFILE, V$DATABASE, V$SESSIONS
V$DATATABLE, V$USERS, V$SESSIONS
V$SGA, V$INSTANCE, V$PROCESS


Which of the following cannot be placed in the declaration part of PL/SQL?
SQL statements
Variables
Constants
User-defined exceptions


Which of the following best describes how to have requests serviced by an Oracle server (using either dedicated or shared server)?
For each open session, a new dedicated server will be created separately from the instance in a one-to-one mapping
In dedicated server, Oracle uses a pool of shared processes for a large community of users
A big difference between shared and dedicated server connections is that the client process connected to the database never talks directly to a dedicated server, as it would to a shared server
The client process will be in direct communication with this shared server over some networking conduit such as TCP/IP socket.


Which one of these statements is true about a concatenated index?
Columns in concatenated index need to be in the same order as the columns in the table
It is created on multiple columns in a table.
There is no limit of how many columns can be included in a composite key
It is created when using functions or expressions that involve one or more columns in a table


The basic units that make up a PL/SQL are called:
Logical blocks
Sub-routines
Nested blocks
Executable blocks


In an Oracle RAC environment, a physical standby database will be registered with the clusterware. When creating the clusterware resource for the standby database, which of the following commands do you use to perform this registration?
srvctl alter database
srvctl add database register
srvctl register database
srvctl add database


Which of the following parameters specifies whether Oracle checks for a password file?
REMOTE_LOGIN_PASSWORDFILE
REMOTE_LOGIN_PASSWORD_FILE
None of these
LOGIN_PASSWORDFILE


What is Extended Rowid?
datafile number+data block+row+data object number
data object number+datafile number
datafile number+data object number+data block+row
datafile number+data block+row
data object number+datafile number+data block+row


There are 2 classifications of an index: by logical design and by physical implementation. From its logical design, indexes can have the following types, except:
Single column or concatenated
B-Tree
Unique or non unique
Function-based


How do you switch from an init.ora file to a spfile?
update parameter USE_SPFILE to TRUE
CREATE SPFILE FROM PFILE;
ALTER DATABASE USE SPFILE;
ALTER SYSTEM USE SPFILE;
Delete init.ora file, 10g and greater automatically creates an SPFILE if init.ora is missing


In Forms, what keyboard shortcut will access the shortcut menu?
F5
Ctrl + M
F11
Ctrl + K
Ctrl + S


Which of the following statements are true about deleting or updating a statement?
For a delete, the row will not be removed from the data block until the end user execute commit command
No entry in the undo segment header block is created for this transaction
The undo log buffer will contain the corresponding delete or update statement
The data block is read, loading it into a memory structure called a buffer cache


The only users recognized by the password file are:
SYSMAN and SYSTEM
SYS and INTERNAL
SYS and SYSTEM
SYSTEM and INTERNAL


What is the number of user defined triggers in Oracle?
12
8
2
16


In what value do these 3 statements differ from one another? select * from employees where department_id = 60; SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 60; select /* a_comment */ * from employees where department_id = 60;
The last statement contains syntax error
The execution result
All statements don't differ from one another
Hash values


Does "Not in" use an index?
Yes
No


Rowid on oracle stores:
Physical position of the data
Logical position of the data
Indexed position of data
Log stored position


Which of the following falsely sums up Oracle’s locking policy?
A writer of data is blocked only when a reader of data has already locked the row it was going after.
Oracle never locks data just to read it. There are no locks placed on rows of data by simple reads
Oracle locks data at the row level on modification. There is no lock escalation to a block or table level
A reader of data never blocks a writer of data


The default value of SHARED_POOL_SIZE is:
2048000 bytes
1024000 bytes
16000000 bytes
3500000 bytes


Which of these tasks is NOT performed during the database mounting process?
Allocating the SGA
Reading the control files to obtain the names and status of the data files
Associating the database with a previously started instance
Locating and opening the control files


Users that can see any changed data are:
DBAs and users in the same group
Only the ones making the changes
Only DBAs
No one except the default user for the corresponding schema


Which DATETIME data types cannot be used when specifying column definitions?
TIMESTAMP
INTERVAL MONTH TO DAY
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND


To truncate table changes, use DATA_OBJECT_ID.
False
True


Which of the follwing is NOT associated with an instance?
Trace files
Parameter files
Alert files
Control files

No comments:

Post a Comment