Hi Team,
Attend plsql written test and most of the question are confusing, Could you please answer below questions.
May be it will be multipul answers.
Question : What are all the privileges required to create the materialized view as "CREATOR" when the creator owns the materialized view
1. CREATE MATERIALZIED VIEW, CREATE TABLE, CREATE VIEW
2. CREATE ANY MATERIALIZED VIEW, CREATE ANY TABLE, CREATE ANY VIEW
3. SELECT privilege on the master
4. SELECT ANY TABLE on the master
5. CREATE PROCEDURE
Ans: 2
Question : Which of these is not a benefit of Triggers?
1. Enforce Complex business rules
2. Prevent invalid Transactions
3. Provide transparent event logging
4. Maintain Non replicated Tables Only
Ans: 2
Question : Which of the following subtype of NUMBER can hold Floating-point with maximum precision of 63 Binary Digits?
1. INTEGER
2. REAL
3. DECIMAL
4. DOUBLE PRECISION
Ans: 2
Question : Which of the following is true about temporary LOBS
1.Temporary LOBS provide an interface for creating and deleting the LOBs that act like local variables
2.Temporary LOB can be created using the DBMS_LOB.CREATETEMPORARY procedure
3.Temporary LOBs do not generate any redo or rollback information since they are faster than persistent LOBS
4.Temporary LOBs don't support EMPTY_B/CLOB() functions, since the temporary LOB is empty when created
ANS: ALL above
Question: Which of these is a Restrictions on Cursor Expressions?
A. You cannot use a cursor expression with a SQL cursor
B. Cursor expressions can appear only in the outermost SELECT list of the query specification
C. You cannot perform BIND and EXECUTE operations on cursor expressions
Ans: All
Question: Which of the following is True about Database Roles
1. A database role is traditionally thought of as a named set of database privileges.
2. They are granted privileges, just as database users can be granted privileges.
3. They serve as intermediaries for mapping database privileges to database users
4. None of the above
Ans : 1,2,3
Question : Which of the following is true about the WHERE CURRENT OF clause in CURSORS.
1. If a cursor is declared FOR UPDATE and includes UPDATE and DELETE statements then we can make use of WHERE CURRENT OF clause.
2. This allows updates or deletes to be applied on the single row, which is currently being addressed, without the need to explicitly reference ROWID.
3. This clause evaluates to the row that was just retrieved by the cursor
4. None of the above
ANS: 1,2,3
Question: What is a Materialized View
1. Like a view
2. Consists both definition of the view and the data related to the query result
3. Consists definition of the view, data related to the query result and the MV logs.
4. Consists only the data related to the query result
5. A materialized view is a replica of a target master from a single point in time
Ans : 2
Question: How to create READ ONLY materialized view?
1.After creating the materialized view, run the command "ALTER materialized_view_name READ ONLY;
2. Before creating the materialized view, set the session to READ ONLY using ALTER SESSION CURRENT READ ONLY:"
3. While creating, the materialized view do not use "FOR WRITE "clause
4.While creating, the materialized view do not use "FOR UPDATE "clause
5. Disabling the equivalent "FOR UPDATE" clause in replication management tool
Ans: None of the above
Question: When you normalize a relation by breaking it into two smaller relations, what must you do to maintain data integrity?
1. Link the relations by a common field
2. Remove any functional dependencies from both relations
3. Assign both relations the same primary key field(s)
4. Create a primary key(s) for the new relation
5.None of the above
Ans : 1,3,4
Question: Which of the following is true about functions when called from SQL statements +4
1. When called from SELECT statement or parallelized INSERT, UPDATE, or DELETE statement, function cannot modify database tables
2. When called from INSERT, UPDATE, or DELETE statement, function cannot query or modify any database tables modified by that statement
3. When called from a SELECT, INSERT, UPDATE, or DELETE statement, the function cannot execute SQL transaction control statements (such as COMMIT), session control statements (such as SET ROLE), or system control statements (such as ALTER SYSTEM
4. It cannot execute DDL statements (such as CREATE) because they are followed by an automatic commit
5. None of the above
Ans :3
Question: What is Mutating in the trigger?Please select all the correct answers.
1. Same table is referred in more than one triggers
2. Same trigger is referred in more than one tables
3. Same table is referred more than once in the same trigger
4. A trigger is called by another trigger with same table name
5. A trigger is called by another trigger with different table name
Ans : 3,4
Question: Which of the following is True about principals in Real Application Security
1. Principals are users and roles
2. principal is an application user or an application role
3. principal is a database user or a database role
4. None of the above
ANS: 1
Question: Which of the following defines a Security Class in Orace Database Real Application Security
1. A security class is a scope for a set of application privileges
2. A security class includes application privileges that it inherits from her security classes, and it can include application privileges that it defines.
3. A security class is typically associated with an access control list (ACL),
4. None of the above
ANS : 1,2,3
Question: When trying to show inheritance in a table with a parent-child relationship, three main steps are needed. Which of the following is NOT one of the three main steps
1. graphically represent database design
2. normalize the data
3. bottom-up hierarchical query
4. find root in hierarchical queries
Ans :4
Question: You are working on an interface which is prone to SQL Injections.
Which of these techniques can be used to avoid this threat?
A. Using Bind Arguments
B. Using Validation Checks
C. Using Explicit Format Models
ANS: a,b ,c
Question: Which of the following is false about parameterized cursors
1. Parameters allow values to be passed to a cursor when it is opened and used within the query when it executes
2. These can be opened more than once in a block, by passing different sets of values each time
3. The datatype of the parameters are the same as those for scale variables, but are not given sizes/scales
4. "The parameters are treated like PL/SQL variables within the cursor's query expression."
5. None of the above
Ans: 3