Q~:What is the purpose of database links in Oracle?
Database links are created to establish communication between different databases or different environments such as development, test and production of the same database. The database links are usually designed to be read-only to access other database information . They are also useful when you want to copy production data into test environment for testing.
Q. What is Oracle's data dictionary used for?
Data dictionary in Oracle contains information about all database objects such as tables, triggers, stored procedures, functions, indexes, constraints, views, users, roles, monitoring information, etc.
Q. Which data dictionary objects are used to retrieve the information about the following objects from a given schema?
Q~:) tables
Q~:) views
Q~:) triggers
Q~:) procedures
Q~:) constraints
Q~:) all of the above mentioned objects
The objects used are:
a> user_tables or tabs
b> user_views
c> user_triggers
d> user_procedures
e> user_constraints
f> user_objects
different SQL queries in the same PL/SQL program vs. design time declared explicit cursors with an association to only one query.
Q. You want to view top 50 rows from Oracle table. How do I this?
Use ROWNUM, the pseudo column in where clause as follows:
Where rownum < 51
After complete execution of query and before displaying output of SQL query to the user oracle internally assigns sequential numbers to each row in the output. These numbers are held in the hidden column or pseudo column that is a ROWNUM column. Now it is so simple to apply the above logical condition, as you would have done to any other column of the table.
Q. How do you reference column values in BEFORE and AFTER insert and delete triggers?
The BEFORE and AFTER insert triggers can reference column values by new collection using keyword “:new.column name”. The before and after delete triggers can reference column values by old collection using keyword “:old. column name”.
Q. Can you change the inserted value in one of the columns in AFTER insert trigger code?
This is not possible as the column values supplied by the insert SQL query are already inserted into the table. If you try to assign new value to the column in AFTER insert trigger code then oracle error would be raised. To alter any values supplied by insert SQL query create BEFORE insert trigger.
Q. Explain use of SYSDATE and USER keywords.
SYSDATE is a pseudo column and refers to the current server system date. USER is a pseudo column and refers to the current user logged onto the oracle session. These values come handy when you want to monitor changes happening to the table.
Q. What is the difference between explicit cursor and implicit cursor?
When a single insert, delete or update statement is executed within PL/SQL program then oracle creates an implicit cursor for the same, executes the statement, and closes the cursor. You can check the result of execution using SQL%ROWCOUNT function.
Explicit cursors are created programmatically. The cursor type variable is declared and associated with SQL query. The program then opens a cursor, fetches column information into variables or record type variable, and closes cursor after all records are fetched. To check whether cursor is open or not use function SQL%ISOPEN and to check whether there are any records to be fetched from the cursor use function SQL%FOUND.
Q. Why does a query in Oracle run faster when ROWID is used as a part of the where clause?
ROWID is the logical address of a row - it is not a physical column. It is composed of file number, data block number and row number within data block. Therefore I/O time is minimized retrieving the row, resulting in a faster query.
Q. What type of exception will be raised in the following situations:
a> select..into statement returns more than one row.
b> select..into statement does not return any row.
c> insert statement inserts a duplicate record.
The errors returned are:
a> TOO_MANY_ROWS
b> NO_DATA_FOUND
c> DUP_VAL_ON_INDEX
Q~: Describe the difference between a procedure, function and anonymous pl/sql block.
Level: Low
Expected answer : Candidate should mention use of DECLARE statement, a function must return a value while a procedure doesn't have to.
Q~:. What is a mutating table error and how can you get around it?
Level: Intermediate
Expected answer: This happens with triggers. It occurs because the trigger is trying to update a row it is currently using. The usual fix involves either use of views or temporary tables so the database is selecting from one while updating the other.
Q~:. Describe the use of %ROWTYPE and %TYPE in PL/SQL
Level: Low
Expected answer: %ROWTYPE allows you to associate a variable with an entire table row.
The %TYPE associates a variable with a single column type.
Q~:. What packages (if any) has Oracle provided for use by developers?
Expected answer: Oracle provides the DBMS_ series of packages. There are many
which developers should be aware of such as DBMS_SQL, DBMS_PIPE, DBMS_TRANSACTION,
DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_DDL, UTL_FILE. If they can mention a few of these and describe how they used them, even better. If they include the SQL routines provided by Oracle, great, but not really what was asked.
Q~:. Describe the use of PL/SQL tables
Expected answer: PL/SQL tables are scalar arrays that can be referenced by a
binary integer. They can be used to hold values for use in later queries
or calculations. In Oracle 8 they will be able to be of the %ROWTYPE designation, or RECORD.
Q~:. When is a declare statement needed ?
The DECLARE statement is used in PL/SQL anonymous blocks such as with stand alone, non-stored PL/SQL procedures. It must come first in a PL/SQL stand alone file if it is used.
Q~:. In what order should a open/fetch/loop set of commands in a PL/SQL block be implemented if you use the NOTFOUND cursor variable in the exit when statement? Why?
Expected answer: OPEN then FETCH then LOOP followed by the exit when. If not specified in this order will result in the final return being done twice because of the way the %NOTFOUND is handled by PL/SQL.
Q~:. What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?
Expected answer: SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.
Q~:. How can you find within a PL/SQL block, if a cursor is open?
Expected answer: Use the %ISOPEN cursor status variable.
Q~:. How can you generate debugging output from PL/SQL?
Expected answer: Use the DBMS_OUTPUT package. Another possible method is to just use the SHOW ERROR command, but this only shows errors. The DBMS_OUTPUT package can be used to show intermediate results from loops and the status of variables as the procedure is executed. The new package UTL_FILE can
also be used.
Q~:. What are the types of triggers?
Expected Answer: There are 12 types of triggers in PL/SQL that consist of
combinations of the BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE and
ALL key words:
BEFORE ALL ROW INSERT
AFTER ALL ROW INSERT
BEFORE INSERT
AFTER INSERT etc.
How do I display row number with records?
To achive this use rownum pseudocolumn with query, like SQL> SQL> select rownum, ename from emp;
Output:
1 Scott
2 Millor
3 Jiyo
4 Smith
Display the records between two range
select rownum, empno, ename from emp where rowid in
(select rowid from emp where rownum <=&upto
minus
select rowid from emp where rownum<&Start);
Enter value for upto: 10
Enter value for Start: 7
ROWNUM EMPNO ENAME
--------- --------- ----------
1 7782 CLARK
2 7788 SCOTT
3 7839 KING
4 7844 TURNER
I know the nvl function only allows the same data type(ie. number or char or date Nvl(comm, 0)), if commission is null then the text “Not Applicable” want to display, instead of blank space. How do I write the query?
SQL> select nvl(to_char(comm.),'NA') from emp;
Output :
NVL(TO_CHAR(COMM),'NA')
-----------------------
NA
300
500
NA
1400
NA
NA
To view installed Oracle version information
SQL> select banner from v$version;
Display the number value in Words
SQL> select sal, (to_char(to_date(sal,'j'), 'jsp'))
from emp;
the output like,
SAL (TO_CHAR(TO_DATE(SAL,'J'),'JSP'))
--------- -----------------------------------------------------
800 eight hundred
1600 one thousand six hundred
1250 one thousand two hundred fifty
If you want to add some text like,
Rs. Three Thousand only.
SQL> select sal "Salary ",
(' Rs. '|| (to_char(to_date(sal,'j'), 'Jsp'))|| ' only.'))
"Sal in Words" from emp
/
Salary Sal in Words
------- ------------------------------------------------------
800 Rs. Eight Hundred only.
1600 Rs. One Thousand Six Hundred only.
1250 Rs. One Thousand Two Hundred Fifty only.
Which date function returns number value?
months_between
. Any three PL/SQL Exceptions?
Too_many_rows, No_Data_Found, Value_Error, Zero_Error, Others
. What are PL/SQL Cursor Exceptions?
Cursor_Already_Open, Invalid_Cursor
. Other way to replace query result null value with a text
SQL> Set NULL ‘N/A’
to reset SQL> Set NULL ‘’
Q~:. What are the more common pseudo-columns?
SYSDATE, USER , UID, CURVAL, NEXTVAL, ROWID, ROWNUM
Q~:. What is the output of SIGN function?
1 for positive value,
Q~: for Zero,
-1 for Negative value.
Q~:. What is the maximum number of triggers, can apply to a single table?
12 triggers.
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.
. Name three advisory statistics you can collect.
Buffer Cache Advice, Segment Level Statistics, & Timed Statistics
Explain materialized views and how they are used.
Materialized views are objects that are reduced sets of information that have been summarized, grouped, or aggregated from base tables. They are typically used in data warehouse or decision support systems.
When a user process fails, what background process cleans up after it?
PMON
Q~:. What background process refreshes materialized views?
The Job Queue Processes.
How would you force a log switch?
ALTER SYSTEM SWITCH LOGFILE;
Give two methods you could use to determine what DDL changes have been made.
You could use Logminer or Streams
Name a tablespace automatically created when you create a database.
The SYSTEM tablespace.
When creating a user, what permissions must you grant to allow them to connect to the database?
Grant the CONNECT to the user.
How would you determine who has added a row to a table?
Turn on fine grain auditing for the table.
How can you rebuild an index?
ALTER INDEX <index_name> REBUILD;
How can you gather statistics on a table?
The ANALYZE command.
How can you enable a trace for a session?
Use the DBMS_SESSION.SET_SQL_TRACE or
Use ALTER SESSION SET SQL_TRACE = TRUE;
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.
Q~:. Name two files used for network connection to a database.
TNSNAMES.ORA and SQLNET.ORA
What is Parsing ? (Intermediate)
- Syntax checking, privileges checking, allocating Private SQL Area.
What is the difference between anonymous blocks and stored procedures ? ( Basic)
- Anonymous block is compiled only when called.
- Stored procedure is compiled and stored in database with the dependency information as well.
- Former is PL/SQL code directly called from an application. Latter is stored in database.
- Former has declare statement.Latter doesnt.
What are the advantages of procedures ? ( )
- Loaded once and used many times
- Performance better coz all SQL stmts are sent in one go from the application to the database
- Security ( no object privileges are given directly )
- Invoker's rights possible
- Data integrity, productivity
What are standalone procedures ? ( )
- Those that are not part of package
How is a PL/SQL program stored in database ?
- Parsed code is stored. It's called P-code
How is a PL/SQL program executed ?(Advanced)
- Prior to Oracle 9i, we have only bytecode and a virtual machine in the database runs it. Later versions have faster native code execution.
- PL/SQL engine is the main component that executes procedural stmt and passes the SQL to the SQL statement executor.
What are the advantages and disadvantages of DBMS_SQL ? (Intermediate)
- It has all the advantages of dynamic sql .. like runtime construction of sql, DDL statements can be executed.
- Its advantage over EXECUTE IMMEDIATE is it can Describe objects
- It's kind of bulky and difficult compared to EXECUTE IMMEDIATE.
What is a package spec and package body ? Why the separation ? ( )
- Spec declares public constructs. Body defines public constructs, additionally declares and defines Private constructs
- Separation helps make development easier
- Dependency is simplified. You can modify body without invalidating dependent objects.
What are the advantages of Packages ? ( )
- Encapsulation of code logic
- Privileges to objects can be controlled
- Loaded once into memory , used subsequently.
- Dependency simplified
- Public/private procs, functions, variables
How do you handle exceptions for bulk operations ? (Intermediate)
- Use the SAVE EXCEPTIONS clause ( FORALL index IN bound_clause SAVE EXCEPTIONS LOOP ... END LOOP )
- Use 'Exceptions When Others' to handle the exceptions
- SQL%BULK_EXCEPTIONS(i).ERROR_CODE,
SQL%BULK_EXCEPTIONS(i).ERROR_INDEX
SQL%BULK_EXCEPTIONS.COUNT
Tell some tips to avoid performance problems in PL/SQL. (Intermediate to )
- Use FORALL instead of FOR, and use BULK COLLECT to avoid looping many times
- Tune SQL statements to avoid CPU overhead
- Use NOCOPY for OUT and IN OUT if the original value need not be retained. Overhead of keeping a copy of OUT is avoided.
- Reorder conditional tests to put least expensive ones first
- Minimize datatype conversions => Assign data to exact same type variables
- Use PLS_INTEGER for computation intensive code. NUMBER, INTEGER maintain precision and scale but not optimized for performance as additional checks are made to maintain precision and scale.
- Do not use subtypes like POSITIVE, NATURAL, INTEGER as they have additional checks
- Use BINARY_FLOAT, BINARY_DOUBLE
- EXECUTE IMMEDIATE is faster than DBMS_SQL
How to know PL/SQL compile parameters ?( )
- SHOW PARAMETERS PLSQL
- ALL_PLSQL_OBJECT_SETTINGS
What is MERGE ?( )
- Combination of INSERT and UPDATE
Tell some new features in PL/SQL in 10g ( )
- Regular expression functions REGEXP_LIKE , REGEXP_INSTR, REGEXP_REPLACE, and REGEXP_SUBSTR
- Compile time warnings
- Conditional compilation
- Improvement to native compilation
- BINARY_INTEGER made similar to PLS_INTEGER
- INDICES OF , VALUES OF in FORALL lets you work on non-consecutive indices
- Quoting mechanism . Instead of quoting single quotes twice everytime, give your own delimiter to go on using single quotes.
Ex: q'!I'm a string, you're a string.!'
- Flashback Query functions. SCN_TO_TIMESTAMP, TIMESTAMP_TO_SCN
- Implicit conversion between CLOB and NCLOB
- Improved Overloading
- New datatypes BINARY_FLOAT, BINARY_DOUBLE
- Global optimization enabled
- PLS_INTEGER range increased to 32bit
- DYNAMIC WRAP using DBMS_DDL
What is a sequence ? ( )
- A database object that offers high-speed access to an integer value
- Guaranteed to be unique (within that sequence).
-Used commonly to generate Primary key values
Q~:
Q~:How can you tell if an UPDATE updated no rows? SQL%NOTFOUND.
Q~:What is an autonomous transaction? Identified by pragma autonomous. A child transaction separate from the parent that MUST be committed or rolled back.
Q~:What is a nested subquery? A subquery in a where clause.
Q~:Describe the block structure of PLSQL. Declaration, Begin, exception, end.
Q~:What is an anonymous block? Unnamed PL/SQL block.
Q~:What is a PL/SQL collection? PL/SQL Table, Varray, PL/SQL Array, etc.
Q~:What is the difference between an explicit cursor and a select into. You might get something about performance but that's a myth. An explicit cursor is just more typing. A cursor for loop would be used to return more than a single row.
Q~:Why would you choose to use a package versus straight procedures and functions? I look for maintenance, grouping logical functionality, dependency management, etc. I want to believe that they believe using packages is a "good thing".
Database links are created to establish communication between different databases or different environments such as development, test and production of the same database. The database links are usually designed to be read-only to access other database information . They are also useful when you want to copy production data into test environment for testing.
Q. What is Oracle's data dictionary used for?
Data dictionary in Oracle contains information about all database objects such as tables, triggers, stored procedures, functions, indexes, constraints, views, users, roles, monitoring information, etc.
Q. Which data dictionary objects are used to retrieve the information about the following objects from a given schema?
Q~:) tables
Q~:) views
Q~:) triggers
Q~:) procedures
Q~:) constraints
Q~:) all of the above mentioned objects
The objects used are:
a> user_tables or tabs
b> user_views
c> user_triggers
d> user_procedures
e> user_constraints
f> user_objects
different SQL queries in the same PL/SQL program vs. design time declared explicit cursors with an association to only one query.
Q. You want to view top 50 rows from Oracle table. How do I this?
Use ROWNUM, the pseudo column in where clause as follows:
Where rownum < 51
After complete execution of query and before displaying output of SQL query to the user oracle internally assigns sequential numbers to each row in the output. These numbers are held in the hidden column or pseudo column that is a ROWNUM column. Now it is so simple to apply the above logical condition, as you would have done to any other column of the table.
Q. How do you reference column values in BEFORE and AFTER insert and delete triggers?
The BEFORE and AFTER insert triggers can reference column values by new collection using keyword “:new.column name”. The before and after delete triggers can reference column values by old collection using keyword “:old. column name”.
Q. Can you change the inserted value in one of the columns in AFTER insert trigger code?
This is not possible as the column values supplied by the insert SQL query are already inserted into the table. If you try to assign new value to the column in AFTER insert trigger code then oracle error would be raised. To alter any values supplied by insert SQL query create BEFORE insert trigger.
Q. Explain use of SYSDATE and USER keywords.
SYSDATE is a pseudo column and refers to the current server system date. USER is a pseudo column and refers to the current user logged onto the oracle session. These values come handy when you want to monitor changes happening to the table.
Q. What is the difference between explicit cursor and implicit cursor?
When a single insert, delete or update statement is executed within PL/SQL program then oracle creates an implicit cursor for the same, executes the statement, and closes the cursor. You can check the result of execution using SQL%ROWCOUNT function.
Explicit cursors are created programmatically. The cursor type variable is declared and associated with SQL query. The program then opens a cursor, fetches column information into variables or record type variable, and closes cursor after all records are fetched. To check whether cursor is open or not use function SQL%ISOPEN and to check whether there are any records to be fetched from the cursor use function SQL%FOUND.
Q. Why does a query in Oracle run faster when ROWID is used as a part of the where clause?
ROWID is the logical address of a row - it is not a physical column. It is composed of file number, data block number and row number within data block. Therefore I/O time is minimized retrieving the row, resulting in a faster query.
Q. What type of exception will be raised in the following situations:
a> select..into statement returns more than one row.
b> select..into statement does not return any row.
c> insert statement inserts a duplicate record.
The errors returned are:
a> TOO_MANY_ROWS
b> NO_DATA_FOUND
c> DUP_VAL_ON_INDEX
Q~: Describe the difference between a procedure, function and anonymous pl/sql block.
Level: Low
Expected answer : Candidate should mention use of DECLARE statement, a function must return a value while a procedure doesn't have to.
Q~:. What is a mutating table error and how can you get around it?
Level: Intermediate
Expected answer: This happens with triggers. It occurs because the trigger is trying to update a row it is currently using. The usual fix involves either use of views or temporary tables so the database is selecting from one while updating the other.
Q~:. Describe the use of %ROWTYPE and %TYPE in PL/SQL
Level: Low
Expected answer: %ROWTYPE allows you to associate a variable with an entire table row.
The %TYPE associates a variable with a single column type.
Q~:. What packages (if any) has Oracle provided for use by developers?
Expected answer: Oracle provides the DBMS_ series of packages. There are many
which developers should be aware of such as DBMS_SQL, DBMS_PIPE, DBMS_TRANSACTION,
DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_DDL, UTL_FILE. If they can mention a few of these and describe how they used them, even better. If they include the SQL routines provided by Oracle, great, but not really what was asked.
Q~:. Describe the use of PL/SQL tables
Expected answer: PL/SQL tables are scalar arrays that can be referenced by a
binary integer. They can be used to hold values for use in later queries
or calculations. In Oracle 8 they will be able to be of the %ROWTYPE designation, or RECORD.
Q~:. When is a declare statement needed ?
The DECLARE statement is used in PL/SQL anonymous blocks such as with stand alone, non-stored PL/SQL procedures. It must come first in a PL/SQL stand alone file if it is used.
Q~:. In what order should a open/fetch/loop set of commands in a PL/SQL block be implemented if you use the NOTFOUND cursor variable in the exit when statement? Why?
Expected answer: OPEN then FETCH then LOOP followed by the exit when. If not specified in this order will result in the final return being done twice because of the way the %NOTFOUND is handled by PL/SQL.
Q~:. What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?
Expected answer: SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.
Q~:. How can you find within a PL/SQL block, if a cursor is open?
Expected answer: Use the %ISOPEN cursor status variable.
Q~:. How can you generate debugging output from PL/SQL?
Expected answer: Use the DBMS_OUTPUT package. Another possible method is to just use the SHOW ERROR command, but this only shows errors. The DBMS_OUTPUT package can be used to show intermediate results from loops and the status of variables as the procedure is executed. The new package UTL_FILE can
also be used.
Q~:. What are the types of triggers?
Expected Answer: There are 12 types of triggers in PL/SQL that consist of
combinations of the BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE and
ALL key words:
BEFORE ALL ROW INSERT
AFTER ALL ROW INSERT
BEFORE INSERT
AFTER INSERT etc.
How do I display row number with records?
To achive this use rownum pseudocolumn with query, like SQL> SQL> select rownum, ename from emp;
Output:
1 Scott
2 Millor
3 Jiyo
4 Smith
Display the records between two range
select rownum, empno, ename from emp where rowid in
(select rowid from emp where rownum <=&upto
minus
select rowid from emp where rownum<&Start);
Enter value for upto: 10
Enter value for Start: 7
ROWNUM EMPNO ENAME
--------- --------- ----------
1 7782 CLARK
2 7788 SCOTT
3 7839 KING
4 7844 TURNER
I know the nvl function only allows the same data type(ie. number or char or date Nvl(comm, 0)), if commission is null then the text “Not Applicable” want to display, instead of blank space. How do I write the query?
SQL> select nvl(to_char(comm.),'NA') from emp;
Output :
NVL(TO_CHAR(COMM),'NA')
-----------------------
NA
300
500
NA
1400
NA
NA
To view installed Oracle version information
SQL> select banner from v$version;
Display the number value in Words
SQL> select sal, (to_char(to_date(sal,'j'), 'jsp'))
from emp;
the output like,
SAL (TO_CHAR(TO_DATE(SAL,'J'),'JSP'))
--------- -----------------------------------------------------
800 eight hundred
1600 one thousand six hundred
1250 one thousand two hundred fifty
If you want to add some text like,
Rs. Three Thousand only.
SQL> select sal "Salary ",
(' Rs. '|| (to_char(to_date(sal,'j'), 'Jsp'))|| ' only.'))
"Sal in Words" from emp
/
Salary Sal in Words
------- ------------------------------------------------------
800 Rs. Eight Hundred only.
1600 Rs. One Thousand Six Hundred only.
1250 Rs. One Thousand Two Hundred Fifty only.
Which date function returns number value?
months_between
. Any three PL/SQL Exceptions?
Too_many_rows, No_Data_Found, Value_Error, Zero_Error, Others
. What are PL/SQL Cursor Exceptions?
Cursor_Already_Open, Invalid_Cursor
. Other way to replace query result null value with a text
SQL> Set NULL ‘N/A’
to reset SQL> Set NULL ‘’
Q~:. What are the more common pseudo-columns?
SYSDATE, USER , UID, CURVAL, NEXTVAL, ROWID, ROWNUM
Q~:. What is the output of SIGN function?
1 for positive value,
Q~: for Zero,
-1 for Negative value.
Q~:. What is the maximum number of triggers, can apply to a single table?
12 triggers.
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.
. Name three advisory statistics you can collect.
Buffer Cache Advice, Segment Level Statistics, & Timed Statistics
Explain materialized views and how they are used.
Materialized views are objects that are reduced sets of information that have been summarized, grouped, or aggregated from base tables. They are typically used in data warehouse or decision support systems.
When a user process fails, what background process cleans up after it?
PMON
Q~:. What background process refreshes materialized views?
The Job Queue Processes.
How would you force a log switch?
ALTER SYSTEM SWITCH LOGFILE;
Give two methods you could use to determine what DDL changes have been made.
You could use Logminer or Streams
Name a tablespace automatically created when you create a database.
The SYSTEM tablespace.
When creating a user, what permissions must you grant to allow them to connect to the database?
Grant the CONNECT to the user.
How would you determine who has added a row to a table?
Turn on fine grain auditing for the table.
How can you rebuild an index?
ALTER INDEX <index_name> REBUILD;
How can you gather statistics on a table?
The ANALYZE command.
How can you enable a trace for a session?
Use the DBMS_SESSION.SET_SQL_TRACE or
Use ALTER SESSION SET SQL_TRACE = TRUE;
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.
Q~:. Name two files used for network connection to a database.
TNSNAMES.ORA and SQLNET.ORA
What is Parsing ? (Intermediate)
- Syntax checking, privileges checking, allocating Private SQL Area.
What is the difference between anonymous blocks and stored procedures ? ( Basic)
- Anonymous block is compiled only when called.
- Stored procedure is compiled and stored in database with the dependency information as well.
- Former is PL/SQL code directly called from an application. Latter is stored in database.
- Former has declare statement.Latter doesnt.
What are the advantages of procedures ? ( )
- Loaded once and used many times
- Performance better coz all SQL stmts are sent in one go from the application to the database
- Security ( no object privileges are given directly )
- Invoker's rights possible
- Data integrity, productivity
What are standalone procedures ? ( )
- Those that are not part of package
How is a PL/SQL program stored in database ?
- Parsed code is stored. It's called P-code
How is a PL/SQL program executed ?(Advanced)
- Prior to Oracle 9i, we have only bytecode and a virtual machine in the database runs it. Later versions have faster native code execution.
- PL/SQL engine is the main component that executes procedural stmt and passes the SQL to the SQL statement executor.
What are the advantages and disadvantages of DBMS_SQL ? (Intermediate)
- It has all the advantages of dynamic sql .. like runtime construction of sql, DDL statements can be executed.
- Its advantage over EXECUTE IMMEDIATE is it can Describe objects
- It's kind of bulky and difficult compared to EXECUTE IMMEDIATE.
What is a package spec and package body ? Why the separation ? ( )
- Spec declares public constructs. Body defines public constructs, additionally declares and defines Private constructs
- Separation helps make development easier
- Dependency is simplified. You can modify body without invalidating dependent objects.
What are the advantages of Packages ? ( )
- Encapsulation of code logic
- Privileges to objects can be controlled
- Loaded once into memory , used subsequently.
- Dependency simplified
- Public/private procs, functions, variables
How do you handle exceptions for bulk operations ? (Intermediate)
- Use the SAVE EXCEPTIONS clause ( FORALL index IN bound_clause SAVE EXCEPTIONS LOOP ... END LOOP )
- Use 'Exceptions When Others' to handle the exceptions
- SQL%BULK_EXCEPTIONS(i).ERROR_CODE,
SQL%BULK_EXCEPTIONS(i).ERROR_INDEX
SQL%BULK_EXCEPTIONS.COUNT
Tell some tips to avoid performance problems in PL/SQL. (Intermediate to )
- Use FORALL instead of FOR, and use BULK COLLECT to avoid looping many times
- Tune SQL statements to avoid CPU overhead
- Use NOCOPY for OUT and IN OUT if the original value need not be retained. Overhead of keeping a copy of OUT is avoided.
- Reorder conditional tests to put least expensive ones first
- Minimize datatype conversions => Assign data to exact same type variables
- Use PLS_INTEGER for computation intensive code. NUMBER, INTEGER maintain precision and scale but not optimized for performance as additional checks are made to maintain precision and scale.
- Do not use subtypes like POSITIVE, NATURAL, INTEGER as they have additional checks
- Use BINARY_FLOAT, BINARY_DOUBLE
- EXECUTE IMMEDIATE is faster than DBMS_SQL
How to know PL/SQL compile parameters ?( )
- SHOW PARAMETERS PLSQL
- ALL_PLSQL_OBJECT_SETTINGS
What is MERGE ?( )
- Combination of INSERT and UPDATE
Tell some new features in PL/SQL in 10g ( )
- Regular expression functions REGEXP_LIKE , REGEXP_INSTR, REGEXP_REPLACE, and REGEXP_SUBSTR
- Compile time warnings
- Conditional compilation
- Improvement to native compilation
- BINARY_INTEGER made similar to PLS_INTEGER
- INDICES OF , VALUES OF in FORALL lets you work on non-consecutive indices
- Quoting mechanism . Instead of quoting single quotes twice everytime, give your own delimiter to go on using single quotes.
Ex: q'!I'm a string, you're a string.!'
- Flashback Query functions. SCN_TO_TIMESTAMP, TIMESTAMP_TO_SCN
- Implicit conversion between CLOB and NCLOB
- Improved Overloading
- New datatypes BINARY_FLOAT, BINARY_DOUBLE
- Global optimization enabled
- PLS_INTEGER range increased to 32bit
- DYNAMIC WRAP using DBMS_DDL
What is a sequence ? ( )
- A database object that offers high-speed access to an integer value
- Guaranteed to be unique (within that sequence).
-Used commonly to generate Primary key values
Q~:
Q~:How can you tell if an UPDATE updated no rows? SQL%NOTFOUND.
Q~:What is an autonomous transaction? Identified by pragma autonomous. A child transaction separate from the parent that MUST be committed or rolled back.
Q~:What is a nested subquery? A subquery in a where clause.
Q~:Describe the block structure of PLSQL. Declaration, Begin, exception, end.
Q~:What is an anonymous block? Unnamed PL/SQL block.
Q~:What is a PL/SQL collection? PL/SQL Table, Varray, PL/SQL Array, etc.
Q~:What is the difference between an explicit cursor and a select into. You might get something about performance but that's a myth. An explicit cursor is just more typing. A cursor for loop would be used to return more than a single row.
Q~:Why would you choose to use a package versus straight procedures and functions? I look for maintenance, grouping logical functionality, dependency management, etc. I want to believe that they believe using packages is a "good thing".
No comments:
Post a Comment