41⟩ Explain the use of table functions.
Table functions are designed to return a set of rows through PL/SQL logic but are intended to be used as a normal table or view in a SQL statement. They are also used to pipeline information in an ETL process.
Database Administrator (DBA)
Home Databases Programming Database Administrator (DBA)
“If you a Database Administrator and need to update DBA technical knowledge or need to prepare for a job interview? Then you are at eht right place explore this collection of DATABASE Administrator (DBA) Interview Questions and Answers here. This DBA Interview Questions and Answers are basic and advance level so every one who is DBA can get more help with these database interview questions and answers tutorial and guide.”
253 Database Administrator (DBA) Questions And Answers
42⟩ What are the system resources that can be controlled through Profile?
The number of concurrent sessions the user can establish the CPU processing time available to the user’s session the CPU processing time available to a single call to ORACLE made by a SQL statement the amount of logical I/O available to the user’s session the amount of logical I/O available to a single call to ORACLE made by a SQL statement the allowed amount of idle time for the user’s session the allowed amount of connect time for the user’s session.
43⟩ Name three advisory statistics you can collect.
Buffer Cache Advice
Segment Level Statistics
&
Timed Statistics
44⟩ Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.
A function and procedure are the same in that they are intended to be a collection of PL/SQL code that carries a single task. While a procedure does not have to return any values to the calling application, a function will return a single value. A package on the other hand is a collection of functions and procedures that are grouped together based on their commonality to a business function or application.
45⟩ What is the use of ANALYZE command?
To perform one of these function on an index, table, or cluster: - To collect statistics about object used by the optimizer and store them in the data dictionary. - To delete statistics about the object used by object from the data dictionary. - To validate the structure of the object.. - To identify migrated and chained rows off the table or cluster.
46⟩ What command would you use to encrypt a PL/SQL application?
WRAP
47⟩ How would you determine the time zone under which a database was operating?
select DBTIMEZONE from dual;
48⟩ Explain the use of setting GLOBAL_NAMES equal to TRUE.
Setting GLOBAL_NAMES dictates how you might connect to a database. This variable is either TRUE or FALSE and if it is set to TRUE it enforces database links to have the same name as the remote database to which they are linking.
49⟩ Explain the difference between $ORACLE_HOME and $ORACLE_BASE.
ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is where the oracle products reside.
50⟩ Explain an ORA-01555
You get this error when you get a snapshot too old within rollback. It can usually be solved by increasing the undo retention or increasing the size of rollbacks. You should also look at the logic involved in the application getting the error message.
51⟩ What is Two-Phase Commit?
Two-phase commit is mechanism that guarantees a distributed transaction either commits on all involved nodes or rolls back on all involved nodes to maintain data consistency across the global distributed database. It has two phase, a Prepare Phase and a Commit Phase.
52⟩ How can you Enforce Referential Integrity in snapshots?
Time the references to occur when master tables are not in use. Perform the reference the manually immediately locking the master tables. We can join tables in snapshots by creating a complex snapshots that will based on the master tables.
53⟩ What is a SQL * NET?
SQL *NET is ORACLEs mechanism for interfacing with the communication protocols used by the networks that facilitate distributed processing and distributed databases. It is used in Clint-Server and Server-Server communications.
54⟩ How would you determine who has added a row to a table?
Turn on fine grain auditing for the table.
55⟩ How can you rebuild an index?
ALTER INDEX <index_name> REBUILD;.
56⟩ Explain what partitioning is and what its benefit is.
Partitioning is a method of taking large tables and indexes and splitting them into smaller, more manageable pieces.
57⟩ How can you gather statistics on a table?
The ANALYZE command.
58⟩ You have just compiled a PL/SQL package but got errors, how would you view the errors?
SHOW ERRORS
59⟩ How can you enable a trace for a session?
Use the DBMS_SESSION.SET_SQL_TRACE or
Use ALTER SESSION SET SQL_TRACE = TRUE;
60⟩ What is the difference between the SQL*Loader and IMPORT utilities?
These two Oracle utilities are used for loading data into the database. The difference is that the import utility relies on the data being produced by another Oracle utility EXPORT while the SQL*Loader utility allows data to be loaded that has been produced by other utilities from different data sources just so long as it conforms to ASCII formatted or delimited files.
