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
Subscribe to:
Posts (Atom)
