5 Basic Concept QnA
1. What is the basic
PL/SQL block structure?
2. What is the
difference between %ROWTYPE and %TYPE and what is the main advantage to using
these?
3. How might you
display compile time warnings for PL/SQL code?
4. Define 'scope' and
'visibility' for PL/SQL variables.
5. What is an
overloaded procedure?
5 Oracle DB QnA
1. Identify the
primary key and foreign keys within the table structures.
2. Where might I put
the total amount of an order (which table)? Why?
3. If I were writing
an application what would have to be the order of population for this set of
tables?
4. For the following
SQL (A through F) identify which SQL is the best for displaying the customers
that have placed an order.
5. If you had to put
any indexes on these tables, where would you put them?
5 Query tactics QnA
1. How do you go
about tuning your PL/SQL code?
2. How might you get
around hard coding the elements in a fetch cursor?
3. How do you get
around repeating SQL code?
4. How many COMMIT
statements do you put in your code?
5. What are the four
dynamic SQL methods?
6 Security QnA
1. What is
Encryption?
2. What is Network
Encryption?
3. What is
Transparent Data Encryption?
4. You get the Oracle
error "ORA-28365: wallet is not open", what is wrong and how to fix?
5. Any design
limitations on encrypted columns?
6. Compare and
contrast Transparent Data Encryption (TDE) with Transparent Tablespace
Encryption (TTE). What about performance?
7 User Defined Encryption QnA
1. What would you
consider an algorithm to be in regards to encryption?
2. What is a key in
regards to encryption?
3. Algorithms are
commonly and openly available on the Net. How can this be secure if everyone
has access to the algorithms we use to encrypt our data?
4. What is the
difference between a public key and a private key?
5. Key management is
of great concern. Where might a key be stored for security purposes?
6. What is the
DBMS_CRYPTO package?
7. How would you
generate a key with the DBMS_CRYPTO package?
9 DB Object QnA
1. Define
"Normal Form" and give brief descriptions of the levels
2. Name some of the
database views you might use to find the structure of a table or index
3. IF you needed to
extract the full table DDL for a table, how might you do that?
6. What is a
"Nested Table?"
7. What is a table
function?
9. An interview about
database objects wouldn't be complete unless there were a couple of SQL
examples to help / determine if you know how to extract data from them.
9 PLSQL Performance QnA
1. PL/SQL code can be
an issue with performance (loops, conditional statements, etc.) but if a
section of PL/SQL code has been targeted as being a performance hog where might
you first look?
2. After getting the
DBA group involved with a performance what might the DBA group do for you to
help determine if the application is potentially at fault?
3. What
tools/utilities might you use to help tune your SQL?
4. Besides running an
EXPLAIN PLAN to view the execution path of a SQL statement, what other means
might you use to view explain plans?
5. What does the
PL/SQL Optimizer do?
6. Name the two
profiler tools and describe what they do.
7. Name a tracing
utility that helps isolate PL/SQL problems and describe what it does.
8. How might you
determine to use PL/SQL Native Compilation to speed your code?
9. Have you ever used
bulk processing? Why?
10 Proc, Fns, Packages QnA
1. Would you say you
use modularization as a best practice when producing PL/SQL code?
2. What is the
difference between a procedure and a function?
3. Explain the
difference between IN and OUT parameters.
4. What is module
overloading and why might you use it?
5. Describe read
consistency
6. What is an
autonomous transaction and how does it affect the commit process?
7. What are packages?
8. How might you
hide/protect your source code if distributed to customers?
9. Name two PL/SQL
conditional control statements
10. Name two loop
control statement
Technical – DBA QnA
Technical – Oracle
QnA
4. Explain the
difference between a data block, an extent and a segment.
5. Give two examples
of how you might determine the structure of the table DEPT.
7. Compare and
contrast TRUNCATE and DELETE for a table.
8. Give the reasoning
behind using an index.
9. Give the two types
of tables involved in producing a star schema and the type of data they hold.
10. What type of index
should you use on a fact table?
11. Give two examples
of referential integrity constraints.
12. A table is
classified as a parent table and you want to drop and re-create it. How would
you do this without affecting the children tables?
13. Explain the
difference between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and
disadvantages to each.
16. What column
differentiates the V$ views to the GV$ views and how?
17. How would you go
about generating an EXPLAIN plan?
18. How would you go
about increasing the buffer cache hit ratio?
19. Explain an
ORA-01555
20. Explain the
difference between $ORACLE_HOME and $ORACLE_BASE.
21. How would you
determine the time zone under which a database was operating?
22. Explain the use of
setting GLOBAL_NAMES equal to TRUE.
23. What command would
you use to encrypt a PL/SQL application?
24. Explain the
difference between a FUNCTION, PROCEDURE and PACKAGE.
25. Explain the use of
table functions.
26. Name three
advisory statistics you can collect.
28. Explain
materialized views and how they are used.
29. When a user
process fails, what background process cleans up after it?
30. What background
process refreshes materialized views?
31. How would you
determine what sessions are connected and what resources they are waiting for?
32. Describe what redo
logs are.
34. Give two methods
you could use to determine what DDL changes have been made.
35. What does
coalescing a tablespace do?
36. What is the
difference between a TEMPORARY tablespace and a PERMANENT tablespace?
37. Name a tablespace
automatically created when you create a database.
38. When creating a
user, what permissions must you grant to allow them to connect to the database?
42. What view would
you use to determine free space in a tablespace?
43. How would you
determine who has added a row to a table?
44. How can you
rebuild an index?
45. Explain what
partitioning is and what its benefit is.
46. You have just
compiled a PL/SQL package but got errors, how would you view the errors?
47. How can you gather
statistics on a table?
48. How can you enable
a trace for a session?
49. What is the
difference between the SQL*Loader and IMPORT utilities?
50. Name two files
used for network connection to a database.
Technical - UNIX QnA
1. How do you list
the files in an UNIX directory while also showing hidden files?
2. How do you execute
a UNIX command in the background?
3. What UNIX command
will control the default file permissions when files are created?
4. Explain the read,
write, and execute permissions on a UNIX directory.
5. What is the
difference between a soft link and a hard link?
6. Give the command
to display space usage on the UNIX file system.
7. Explain iostat,
vmstat and netstat.
8. How would you
change all occurrences of a value using VI?
9. Give two UNIX
kernel parameters that effect an Oracle install
10. Briefly, how do
you install Oracle software on UNIX.
5
Basic Concept QnA
1. What is the basic PL/SQL block structure?
The basic
unit of a PL/SQL program is the block, which may consists of a label,
declarative section, execution section, and exception section. Keywords include
DECLARE, BEGIN, EXCEPTION, and END where BEGIN and END are the only required
keywords and the execution section is the only required section. The individual
pieces would look like the following:
SQL> l
1 CREATE
OR REPLACE PROCEDURE callit (anumber INTEGER) IS
2 var1
INTEGER;
3 BEGIN
4 var1 :=
1;
5
DBMS_OUTPUT.PUT_LINE('Invoked callit, var1 is : '||var1);
6
EXCEPTION
7 WHEN
OTHERS THEN
8
DBMS_OUTPUT.PUT_LINE('Error!');
9* END
callit;
SQL> /
Procedure
created.
SQL>
exec callit(1);
Invoked
callit, var1 is : 1
PL/SQL
procedure successfully completed.
Where
lines:
1.
Label for procedure
2.
Declarative section
3.
Keyword BEGIN
4.
Start of the execution section
5.
More execution section
6.
Keyword EXCEPTION
7.
Start of the exception section
8.
More exception section
9.
Keyword END
2. What is the difference between %ROWTYPE and
%TYPE and what is the main advantage to using these?
The
%ROWTYPE allows the coder to indirectly represent a full or partial row of a DB
table or view, whereas the %TYPE allows for the coder to indirectly represent
the data type from a previously declared variable or column. Basically,
%ROWTYPE works on a full object whereas %TYPE works on a single column. The
advantage to using either of these enables the coder to maintain data type
declarations without ever having to know or change the data type for the items
that use these. Below is an example of how the %TYPE allows for a layer of
abstraction between names; allowing the coder to just change the first
occurrence of the data type.
DECLARE
name VARCHAR(50);
fname name%TYPE;
lname name%TYPE;
city name%TYPE;
country name%TYPE;
BEGIN
Execution section;
END;
/
3.
How might you display compile time warnings for PL/SQL code?
There
are actually two methods to show compile time warnings. While both 'SHOW
ERRORS' and the *_errors views (USER_ERRORS used here) show basically the same
information; I tend to like the SHOW ERRORS command as it seems quicker to
type. The advantage to using the *_errors views is that you can actually
monitor every developer's current errors when using a view such as DBA_ERRORS,
as there is an additional column for OWNER that will tell you the user
encountering errors.
SQL> SHOW ERRORS
Errors for PROCEDURE CALLIT:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/10 PLS-00103: Encountered the symbol "=" when expecting one of the
following:
:= . ( @ % ;
The symbol ":= was inserted before "=" to continue.
8/7 PLS-00103: Encountered the symbol "DBMS_OUTPUT" when expecting
one of the following:
then or
The symbol "then" was substituted for "DBMS_OUTPUT" to continue.
SQL> SELECT * FROM user_errors;
NAME TYPE SEQUENCE LINE POSITION TEXT ATTRIBUTE MESSAGE_NUMBER
------ ------------ -------- ---- -------- -------------------- --------- --------------
CALLIT PROCEDURE 1 4 10 PLS-00103: Encounter ERROR 103
ed the symbol "=" wh
en expecting one of
the following:
:= . ( @ % ;
The symbol ":= was i
nserted before "=" t
o continue.
CALLIT PROCEDURE 2 8 7 PLS-00103: Encounter ERROR 103
ed the symbol "DBMS_OUTPUT" when expecti
ng one of the following: then or
The symbol "then" was substituted for "D
BMS_OUTPUT" to continue.
4.
Define 'scope' and 'visibility' for PL/SQL variables.
The
definition of scope and visibility for a variable is actually quite close with
the only difference being if you have to qualify the variable. The scope of a
variable refers to the region (breadth) of code where the variable can be
referenced. The visibility refers to the region of code you can reference the
variable without qualifying it. So, hopefully you can see, visibility is a
subset of the scope and requires the variable to be qualified (told where it
comes from) in order to use. An example is clearly the best option here to help
explain. Consider the PL/SQL code:
SQL> l
1 CREATE OR REPLACE PROCEDURE zero IS
2 x VARCHAR2(1); -- scope of zero.x begins
3 PROCEDURE a
4 IS
5 x VARCHAR2(1); -- scope of a.x begins
6 BEGIN -- visible a.x
7 x := 'a';
8 DBMS_OUTPUT.PUT_LINE('In procedure a, x = ' || x);
9 -- even though zero.x is not visible it can still be qualified/referenced
10 DBMS_OUTPUT.PUT_LINE('In procedure a, zero.x = ' || zero.x);
11 END; -- scope of a.x ends
12 PROCEDURE b
13 IS
14 BEGIN -- visible zero.x
15 DBMS_OUTPUT.PUT_LINE('In procedure b, x(zero) = ' || x);
16 DBMS_OUTPUT.PUT_LINE('In procedure a, zero.x = ' || zero.x);
17 END;
18 BEGIN -- visible zero.x
19 x:='0';
20 DBMS_OUTPUT.PUT_LINE('In zero, x = ' || x);
21 a;
22 b;
23* END; -- scope of zero.x ends
SQL> exec zero
In zero, x = 0
In procedure a, x = a
In procedure a, zero.x = 0
In procedure b, x(zero) = 0
In procedure a, zero.x = 0
PL/SQL procedure successfully completed.
Probably
the biggest thing to notice about the scope of a variable, while all variables
referenced ('x') are the same, just ask yourself if you need to qualify it and
that will determine if it is visible. Notice in 'PROCEDURE b' where there is no
local 'x' variable so the 'x' from 'PROCEDURE zero' is still visible and really
doesn't need to be qualified, even though you still can. Moreover, if you ever
get lost, Oracle is sometimes gracious to help by telling you something is out
of scope.
LINE/COL ERROR
-------- -----------------------------------------------------------------
15/2 PL/SQL: Statement ignored
15/44 PLS-00225: subprogram or cursor 'A' reference is out of scope
5.
What is an overloaded procedure?
An
overloaded procedure is nothing more than the a mechanism that allows the coder
to reuse the same procedure name for different subprograms inside a PL/SQL
block by varying the number of parameters or the parameter data type. Below is
an example of where the same subprogram (callit) is reused but the data type
for the input parameter is changed from INTEGER to VARCHAR2; Oracle is smart
enough to know the input parameter type and call the proper subprogram.
SQL>
1 DECLARE
2 PROCEDURE callit (anumber INTEGER) IS
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE('Invoked number callit');
5 END callit;
6
7 PROCEDURE callit (acharacter VARCHAR2) IS
8 BEGIN
9 DBMS_OUTPUT.PUT_LINE('Invoked character callit');
10 END callit;
11
12 BEGIN
13 callit(1);
14 callit('1');
15* END;
SQL> /
Invoked number callit
Invoked character callit
PL/SQL procedure successfully completed.
5
Oracle DB QnA
The following tables represent a basic set of
tables that might be used in an order entry system. Granted I've eliminated
many columns and attributes but that isn't really important for this exercise.
Again, as you read these, especially if you're getting ready for an interview,
remember that these questions are not hard and fast, many of them beg for
further investigation, many of them you should try within your own sandbox, and
you should extend into other methods of filtering within your SQL.
|
Customer
|
Customerorder
|
orderline
|
Product
|
|
CUSTOMERID
NAME
ADDRESS
CITY
STATE
ZIP
|
ORDERID
CUSTOMERID
ORDERDATE
|
ORDERID
LINEID
PARTID
QTY
PRICE
|
PARTID
DESCRIPTION
COST
QTYONHAND
PRICE
|
1. Identify the primary key and foreign keys within
the table structures
One of the very first things for any proficient SQL
coder to do is properly understand the data model they are working with. The
given model is actually quite easy as it is one that most everyone has seen in
some form or fashion, a quick customer order system. All primary keys are of a
single column except for the table ORDERLINE, which has a concatenated primary
key (ORDERID, LINEID) to uniquely identify a given row. Foreign keys are also
straight forward as the CUSTOMERID in CUSTOMERORDER references CUSTOMERID in
CUSTOMER and PARTID in ORDERLINE references PARTID in PRODUCT.
|
Customer
|
Customerorder
|
orderline
|
Product
|
|
CUSTOMERID (PK)
NAME
ADDRESS
CITY
STATE
ZIP
|
ORDERID (PK)
CUSTOMERID (FK Customer)
ORDERDATE
|
ORDERID (PK) (FK Customerorder)
LINEID (PK)
PARTID (FK Product)
QTY
PRICE
|
PARTID (PK)
DESCRIPTION
COST
QTYONHAND
PRICE
|
2. Where might I put the total amount of an order
(which table)? Why?
Again, understanding and having simple design
skills is important to be able to talk intelligently during code reviews and
data modeling sessions with DAs and DBAs. Here, to put a total order amount
clearly breaks some of the normalization rules but would be put in the
CUSTOMERORDER table to give us quick access to a total amount without having to
look up each order line and calculating price, shipping, etc.; clearly for
performance reasons.
3. If I were writing an application what would have
to be the order of population for this set of tables?
The order of population is dependent upon the
referential integrity between the tables. Here CUSTOMERORDER references
CUSTOMER and ORDERLINE references PRODUCT and CUSTOMERORDER. Therefore, before
CUSTOMERORDER can be populated, the CUSTOMER table must have an entry for the
customer. Likewise, populating ORDERLINE is dependent on having an open order
(CUSTOMERORDER) and some product to order (PRODUCT). Therefore, proper
population of these tables would dictate something like PRODUCT, CUSTOMER,
CUSTOMERORDER, and then ORDERLINE. Please note that I put PRODUCT first before
CUSTOMER as we more than likely would have walk-in customers and product is
clearly something we should have before a customer.
4. For the following SQL (A through F) identify
which SQL is the best for displaying the customers that have placed an order
A)
select customerid from customer where
customerid in (select customerid from customerorder);
B)
select customer.customerid from customer,
customerorder where customer.customerid = customerorder.customerid;
C)
select customer.customerid from customer inner
join customerorder on customer.customerid =
customerorder.customerid;
D)
select unique customer.customerid from
customer inner join customerorder on customer.customerid =
customerorder.customerid;
E)
select customerid from customer where
exists ( select customerid from customerorder where
customer.customerid = customerorder.customerid);
F)
select customerid
from customer where customerid = ( select
customerid from customerorder where
customer.customerid = customerorder.customerid);
This is a great question as it, when you're under
pressure in an interview, allows you to really stand out and articulate that
you know something about SQL processing. The key word in the question is
"best". I would highly recommend, if you're rusty on SQL coding
(including ANSI syntax standards) to brush up on some SQL. It will go a long
way on a test.
Let's first begin by saying that all of these SQL
statements will run without error EXCEPT SQL statement F, as the subquery could
possibly return more than one row. SQL statements B & C produce multiple
rows for the same customer (not the best, as we would have to filter in the
application). SQL statement D is the same as C except for the UNIQUE keyword
that, while it produces one row for each customer will incur unnecessary
sorting. SQL statement A, while producing the proper results unfortunately
(within the IN clause) will first produce a complete list of customer. SQL
statement E, on the other hand, will through the subquery immediately return to
the main part of the query as soon as an existence condition is met, thus the
best SQL statement.
5. If you had to put any indexes on these tables,
where would you put them?
If I were asked to create indexes on these tables,
about the only place I'd add an index, not knowing the application, would be on
the foreign keys. This helps safeguard against full table scans when validating
the referential integrity between the two tables when INSERTing, DELETEing, and
UPDATEing rows.
5 Query tactics QnA
1. How do you go about
tuning your PL/SQL code?
This really hits at the
core of this article. We must all understand, and relate this to our
interviewer that we know that it is the SQL that will always cause the most
difficulty, performance wise, integrity wise, bug wise, within our PL/SQL code.
We can always talk about EXPLAIN plan usage, TKPROF, gathering runtime
statistics, index optimization, and the list goes on, but let me suggest
another tactic here that might get you noticed. Try working in the fact that
you understand that data can change drastically within an organization and a
static application (PL/SQL code) often does not cut it. What is needed, and
what you will bring to the table is an ability to place an abstraction layer,
using views, functions, triggers, procedures, etc. that maintains the integrity
of the PL/SQL logic but allows for simplified maintenance to the data the
PL/SQL code requires. As a very simplistic example, imagine you needed to
select a number of employees within your PL/SQL code. A very simple solution
would be to SELECT directly all the employees form the EMP table. However,
let's say we acquired another company and wanted this code to work with two
different EMP tables. The old code would have to be modified to possibly
perform a join. The better solution, one not affecting the code, would be to
always use a view and then modify the view when the new company was acquired. A
little abstraction goes a long way when requirements change.
2. How might you get
around hard coding the elements in a fetch cursor?
I'd have to say that
this is one of the most common forms of hard coding, other than actual
values/IDs being used in a SQL statement. Practitioners will often use the %TYPE
notation for individual variables, which is fine and well, within the
declaration section but seem to lose sight of the %ROWTYPE. When fetching a
cursor INTO variables those variables are often strung out in a list such as:
FECTH empId, empFname, empLname INTO vempId, vempFname, vempLname; clearly
requiring the addition of another variable in the declaration section and at
the end of the INTO clause. What should happen here is use the %ROWTYPE and
just issue something like: FETCH empId, empFname, empLname INTO empRowtype;
removing all hard coding in the body of the PL/SQL code.
3. How do you get around
repeating SQL code?
The answer seems simple
and many would agree that repeating code is an accident waiting to happen;
increasing the probability of changing all but one code segment and having a
very difficult bug to find. Instead, we should always, for straight code or SQL
statements, ensure we never perform the same function in two different places
in our code. Instead, we should hide the SQL behind subprograms and then call
those subprograms repeatedly. Not only will this make your code more efficient
and maintainable but these subprograms can be called by other applications;
creating a much more flexible environment.
4. How many COMMIT
statements do you put in your code?
This is somewhat of a
tricky question and I hope you are following the general theme of this article,
that of making your PL/SQL code flexible and more importantly conveying to your
interviewer that you have this mindset. However, the real answer here is that
you should really have no COMMIT statements within your application code. The
better way is to call a procedure to do the commit for you. I can see a lot of
funny faces while you are reading this but the example I draw upon is very simple.
Just ask yourself how many times you've commented out the commit statement for
testing purposes. It is our duty to make our applications as flexible as
possible and with hard coded commit points in our applications, we are telling
ourselves we know exactly how the application will run, when we need to commit,
and it will never change. I have all too often had to modify the commit
frequency within an application that I hold to this rule very strictly.
5. What are the four
dynamic SQL methods?
This is the first
distinction you should make when analyzing the type of dynamic SQL you should
be implementing. Understand what these are and how you might code them. You
should note that as the method number increases, so does the complexity or
generality of the type of statement.
1. non-query without host variables; use EXECUTE IMMEDIATE
2. non-query with known number of input host variables; use EXECUTE
IMMEDIATE with USING
3. query with known number of select-list items and input host variables;
use EXECUTE IMMEDIATE with USING and INTO for single row but EXECUTE IMMEDIATE
with USING and BULK COLLECT INTO or OPEN FOR with dynamic string for multi-row
4. query with unknown number of select-list items or input host variables;
use DBMS_SQL
6 Security QnA
1. What is Encryption?
This seems like a very simple question, and it is.
Encryption is nothing more than a way to transform what would normally be
legible/recognizable data into an unreadable format through the use of a secret
key and encryption algorithm. It is extremely important in today's
environments, to encrypt data such as social security numbers, credit card
numbers, any account number, passwords, health information, as well as just
general information you don't want various people to see because of corporate
structure.
2. What is Network Encryption?
Network encryption is nothing more than ensuring
data that travels through a network and between client and server is encrypted;
helping reduce the possibility that a network packet sniffer has been put in
place to capture and steal information.
3. What is Transparent Data Encryption?
Transparent Data Encryption refers to the ability
to encrypt specific table columns or a tablespace; relying on the database to
automatically encrypt data as it is modified within an object. As users select
column data that has been encrypted this way it is then decrypted; making this
a very easy way to implement encryption and is quite transparent for user and
performance aspects.
Clearly, you can see that this type of encryption
relies heavily on application security as well as database security since
decryption automatically happens if given access to a tool or application that
can simply SELECT and access information from the table. It does however
provide a nice, and easy, encryption method that will help protect data if an
attack is made that circumvents traditional database access control mechanisms.
Creating a table that has a column encrypted is as easy as using the ENCRYPT
keyword:
CREATE TABLE scott.emp_ssn
( empno NUMBER(9), ssn NUMBER(9) ENCRYPT USING
'AES256');
4. You get the Oracle error "ORA-28365: wallet
is not open", what is wrong and how to fix?
Obviously, the wallet is not opened and you must
open one but let's take this up a notch. A wallet is where Oracle stores the
master encryption key, outside the database and in an operating system file. A
wallet can be created with a series of commands such as the following:
a. Add to the end of the
$ORACLE_HOME/network/admin/sqlnet.ora file.
ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=file)
(METHOD_DATA=
(DIRECTORY=/opt/app/oracle/product/11.2.0/dbhome_1/ora_wallets)))
b. As the oracle user, create the wallet directory.
[oracle@wagner
dbhome_1]$ mkdir ora_wallets
c. Make sure the compatible parameter is greater
than 10.2
d. As sys, or high ranking user with admin
privileges, set an encryption password for the wallet.
ALTER
SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "";
5. Any design limitations on encrypted columns?
Developers are often asked to design data models
and this can be a big issue if you are not aware of it. Encrypted columns
cannot be used as primary keys or used in a foreign key relation. Consider the
following example and the error given.
SQL> CREATE TABLE scott.emp_ssn (
empno NUMBER(9) ENCRYPT USING 'AES256',
ssn NUMBER(9) ENCRYPT USING 'AES256');
Table created.
SQL> ALTER TABLE scott.emp_ssn ADD PRIMARY KEY
(empno);
2 ALTER TABLE scott.emp_ssn
*
ERROR at line 1:
ORA-28338: Column(s) cannot be both indexed and
encrypted with salt
However, for objects created in tablespaces that
have encryption enabled, the ability to add constraints such as a primary key
are not restricted; clearly giving data modelers an added advantage to using
encrypted tablespaces.
SQL> CREATE TABLESPACE encrypts
DATAFILE '/opt/app/oracle/oradata/vm11r2/encryptts01.dbf'
SIZE 100M
ENCRYPTION USING '3DES168' DEFAULT
STORAGE(ENCRYPT);
Tablespace created.
SQL> CREATE TABLE scott.emp_ssn (
empno NUMBER(9),
ssn NUMBER(9))
TABLESPACE encryptts;
Table created.
SQL> ALTER TABLE scott.emp_ssn ADD PRIMARY KEY
(empno);
Table altered.
SQL> select dts.tablespace_name, dts.encrypted
from dba_tables dt, dba_tablespaces dts
where dt.table_name = 'EMP_SSN'
and dt.tablespace_name = dts.tablespace_name;
TABLESPACE_NAME ENC
------------------------------ ---
ENCRYPTTS YES
6. Compare and contrast Transparent Data Encryption
(TDE) with Transparent Tablespace Encryption (TTE). What about performance?
By the above two questions we can easily see that
TDE disallows constraints such as primary keys and foreign key relations, TTE
is a better choice when constraints are involved. By encrypting and decrypting
one could almost guess that there would be some form of performance hit to the
database. After all, there are extra CPU cycles needed to encrypt before
INSERTing into a table and extra CPU cycles needed to decrypt when SELECTing.
Moreover, you would be right. Actually, TDE also poses some additional
performance issues that are negated by TTE; the biggest being that with TTE,
after reading encrypted data, it is decrypted and resides in the SGA as clear
text, allowing index scans to operate more efficiently and requiring decryption
to happen only once as long as decrypted data remains in SGA.
I hope that this article will get you started down
the road to understanding encryption, at least the easiest encryption method
within Oracle. You should be able to at least talk briefly about what
encryption is and how TDE & TTE work. Just remember that TDE and TTE by
nature only protect data from outside the database. As soon as access
privileges are granted, through either database authorization or application, a
user can very quickly and easily see the information. Clearly, a much more
stringent method of encryption may be needed for your environment.
7
User Defined Encryption QnA
1. What would you consider an algorithm to be in
regards to encryption?
An algorithm, often called a cipher, is the method,
code, logic, or means, by which we alter the value of sensitive information.
2. What is a key in regards to encryption?
A key is the known piece of special knowledge, or
unknown to those trying to break the encryption, that is used in conjunction
with an algorithm to uniquely encrypt information. Stated another way, the key
is a special piece of information, parameter, value, etc. that is used in the
algorithm to help produce the output of the algorithm, the encrypted
information.
3. Algorithms are commonly and openly available on
the Net. How can this be secure if everyone has access to the algorithms we use
to encrypt our data?
Obviously, the code itself that performs the
encryption is not sacred, what is sacred is the key we choose, the encryption
key, which determines how secure our encryption process is. For this reason,
selection of a key and key management is of great importance. If someone is
able to guess our key or find our key, they will then have a head start on
decrypting our information.
4. What is the difference between a public key and
a private key?
Very basically, usage of a private key requires a
secured method to send a single key that is used both for encryption and
decryption. Usage of a public key, on the other hand, instead uses a generated
related key pair by the receiver of information where the public key is sent to
the sender of information and used for encryption but then the private key is
used for decryption on the receiver side.
5. Key management is of great concern. Where might
a key be stored for security purposes?
Keys may be stored within the database, out on the
file system, or somewhere that is controlled by an end user. Storing the key
within the database has some advantages that include ease of access for SQL or
PL/SQL codes as well as some inherent security reasons as it is hidden from
intruders until database access rights are broken. Storing the key on the file
system allows us to disassociate database with key but now brings up both
system and database security issues depending on who and how system and access
to the key file is managed from within the database. Letting end users keep
their own keys and control usage, while taking the burden off DBAs and system
administrators poses a completely new set of issues that we normally associate
with stolen or misplaced keys.
6. What is the DBMS_CRYPTO package?
DBMS_CRYPTO is a package supplied by Oracle that
contains cryptographic functions and procedures that aid in the encryption and
decryption process. While it is not feasible for me to present an example or
all the functions or procedures, you should at least understand what the main
components are of this package. Therefore, DBMS_CRYPTO:
- is installed within the SYS schema
- contains hash functions
- contains message authentication code functions
- contains encryption algorithms
- contains block cipher suites
- contains block cipher chaining modifiers
- contains block cipher padding modifiers
7. How would you generate a key with the
DBMS_CRYPTO package?
Here is a bit of a trick question. The DBMS_CRYPTO
package actually does not generate keys, nor does it do any management of your
keys. DBMS_CRYPTO does allow you to use some of its functions to aid in the
generation of key material but does not generate them explicitly.
9 DB Object
QnA
1. Define "Normal Form" and give brief
descriptions of the levels
Most shops want everyone to at least understand
that there is some order in the universe and that "Normal Form" is
what controls this within our databases.
For most purposes this is correct so do brush up on what "Normal
Form" is by reviewing these very basic definitions; realizing that there
is more to each definition and there are higher levels then third normal form but
this will be plenty for most interviews.
- Normal Form - is nothing more than a set of criteria within relational database theory that aids in determining a table's degree of vulnerability against logical inconsistencies and anomalies.
- First Normal Form - No repeating elements or groups of elements
- Second Normal Form - No partial dependencies on the primary key
- Third Normal Form - No dependencies on non-key attributes
2. Name some of the database views you might use to
find the structure of a table or index
I don't know many developers that cannot rattle off
some of the database views to check database structures. Here, for tables and
indexes, the most commonly used are the DBA, USER, or ALL variations of
_TABLES, _INDEXES, _TAB_COLUMNS, _IND_COLUMNS.
3. IF you needed to extract the full table DDL for
a table, how might you do that?
This is where it gets a bit tricky and many
developers might start rattling off the various database tools out there that
can reverse engineer database structures. This is good and many shops use these
tools but if your interviewer is looking for a developer bent a little on the
DBA side you may want to impress them by rattling off a few of the
DBMS_METADATA functions such as GET_DDL, GET_DEPENDENT_DDL, GET_GRANTED_DDL,
etc. These DBMS_METADATA functions can easily extract the DDL for various
objects quite nicely.
As an added bonus to this simple DDL extraction,
impress the interviewer again by telling them that you also are able to compare
schemas by using the DBMS_METADATA_DIFF package; providing great insight into
differences in dev, test, QA, and production environments before migration of
an application.
6. What is a "Nested Table?"
I'm confident we all know what a normal table is. A
nested table is nothing more than a normal table that has a column whose
datatype is another table, a table within a table (nested), a column that is of
type table, or a column that has for its values another table. This is much
like having a "relationship" defined directly inside the table,
unfortunately eliminating the ability of other tables to also creating a
relationship without going through the parent data.
7. What is a table function?
A table function allows you to define a set of
PL/SQL statements that will, when queried, behave just as a regular query to a
table would. The added benefit here is to have a table function perform some
transformation to the data in question before it is returned in the result set.
9. An interview about database objects wouldn't be
complete unless there were a couple of SQL examples to help / determine if you
know how to extract data from them.
While this is a very important section, I'm not
going to bog down the article with examples. There are many out there on the
Net. Just we aware that you DO need to understand how to not only create
relationships between database objects but you should be able to query them as
well. It is not uncommon for an interviewer to give an actual test here, giving
you a set of un-related tables, ask you to create the relationships, identify
the indexes, and generate some SQL in response to business questions about the
data.
9
PLSQL Performance QnA
1. PL/SQL code can be an issue with performance (loops, conditional
statements, etc.) but if a section of PL/SQL code has been targeted as being a
performance hog where might you first look?
Pointing our fingers at a section of PL/SQL code is
always a very easy thing to do. Many DBAs, who "know" their database
is tuned properly, often point fingers at the application. The VERY first thing
we should do is remove the barriers between application groups and
administrative groups and then start to understand the true problem.
2. After getting the DBA group involved with a
performance what might the DBA group do for you to help determine if the
application is potentially at fault?
Very simply the DBA group should be able to zero in
on the application code that is executing, specifically the SQL being
performed, trace and report to you if database issues are really causing the
problem. As an example, a DBA should be able to tell you what wait events, if
any, are accumulating and causing your application to perform poorly or if
there is contention for internal resources.
3. What tools/utilities might you use to help tune
your SQL?
It amazes me that I've in the past picked up books
on PL/SQL code and they never even have a small section on tuning SQL. While
tuning SQL might not be on the top of a PL/SQL developer's list of things to
do, properly tuned SQL is at the core of producing well executing PL/SQL code
and applications. Being able to run an EXPLAIN PLAN, and understanding its
output, is critical and I'd never hire a developer that didn't understand how
to produce efficient SQL. The following EXPLAIN COMMAND utility could be used
to produce an explain plan for the given SELECT statement:
SQL> EXPLAIN PLAN FOR select * from mytable;
4. Besides running an EXAPLAIN PLAN to view the
execution path of a SQL statement, what other means might you use to view
explain plans?
After a SQL statement has executed you can view the
EXPLAIN PLAN (if it's still in the shared SQL area) through the V$SQL_PLAN
view. It is good to note that, while obtaining an EXPAIN PLAN through the use
of the EXPLAIN PLAN command utility, as in question #3, viewing the EXPLAIN
PLAN through the V$SQL_PLAN view gives the real access path taken during
execution. Overloading modules is nothing more than a mechanism that allows the
coder to reuse the same name for different programs that are within the same
scope. Overloading is probably one of my favorite mechanisms to share and
increase usability within code.
5. What does the PL/SQL Optimizer do?
The PL/SQL Optimizer will rearrange code for better
performance during the translation of source code to system code; this is done
by default. Valid ranges for the parameter PLSQL_OPTIMIZE_LEVEL are from 0 to 3
where the higher the value the more the compiler will try and optimize.
6. Name the two profiler tools and describe what
they do.
- The Profiler API, DBMS_PROFILER package, will compute the time a PL/SQL program spends at each line of code and within each subprogram; very handy if you're trying to just figure out where time is spent. This package will save the statistics it generates into database tables so you can query them.
- PL/SQL hierarchical profiler, DBMS_HPROF package, will report on the dynamic execution profile of the PL/SQL code; generating a report with an option to also store into database tables for reporting.
7. Name a tracing utility that helps isolate PL/SQL
problems and describe what it does.
The Trace API, DBMS_TRACE package, enables you to
trace the orders in which subprograms run; allowing you to also specify which
subprograms to run and placement of statistics gathered in database tables for
custom reporting.
8. How might you determine to use PL/SQL Native
Compilation to speed your code?
While you can natively compile any PL/SQL code, it
is not always the best thing to do. Native compilation is suited better for
those computational intensive procedures, not code that just runs SQL
statements.
9. Have you ever used bulk processing? Why?
You've got to say yes here, right? Bulk processing
enables you, through FORALL (for selecting data) and BULK COLLECT (for inserts,
updates, deletes), to tune the communication layer (context switching) between
the PL/SQL engine and SQL engine to improve performance. These are two very
important PL/SQL performance enhancers that you cannot do without, both in your
code and for an interview.
10 Proc, Fns, Packages QnA
1. Would you say you use modularization as a best
practice when producing PL/SQL code?
Of course, you will say yes as soon as you hear
this question. Nevertheless, I, like many others, have seen way too much
spaghetti code in my life that the question begs for an answer on why you do
code this way, assuming you do. Just remind yourself that with modularized code
we are able to separate by task/function so that we can improve things such as
maintainability, reusability, readability, reliability, and actually enforce
boundaries between key programming components. I don't know how many times, but
it has been many, that I've separated components that were nothing more than a
few lines of code. Often times it is these few lines of code that allow me to
drastically simplify not only calls to those lines of code but also the
components I'd broken them out from.
2. What is the difference between a procedure and a
function?
This is one of those questions that, depending on
your interview, you may get right or wrong depending on how you answer it. In
the past, I've often stated that the difference between a procedure and a
function is that a procedure is a program that performs one or more actions
while a function's main purpose is to return a value. This is true but since a
procedure can also return (interestingly with a RETURN clause) values, there is
something else missing. The difference, that should get you some brownie
points, is in the way procedures and functions are called. A procedure is
called as an executable PL/SQL statement while a function is called like a
PL/SQL expression. Consider the following and you will see the difference.
Procedure call
BEGIN
raiseEmployeeSalary(7369, 200);
END;
Function call
BEGIN
employeeSalary := getEmployeeSalary(7369);
END;
3. Explain the difference between IN and OUT
parameters.
An IN parameter allows us to pass values into
PL/SQL code while the OUT parameter allows us to pass values back out of PL/SQL
code. Also, remember that a parameter can be specified as both IN and OUT with
the IN OUT declaration.
4. What is module overloading and why might you use
it?
Overloading modules is nothing more than a
mechanism that allows the coder to reuse the same name for different programs
that are within the same scope. Overloading is probably one of my favorite
mechanisms to share and increase usability within code.
5. Describe read consistency
It has been my experience that, while this is an
easy concept, many fall short of understanding it in practice. Read consistency
is nothing more than Oracle's way of quarantining that the data / result set
you request at a specific time will be available until the request is complete.
This means that if I issue a SQL statement at 7:00am, and it takes 10 minutes
to produce the result set, and someone were to alter the data at 7:05am, the
data I get back will look like it did at 7:00am as if no one had modified it.
6. What is an autonomous transaction and how does
it affect the commit process?
An autonomous transaction creates, when called from
another transaction, an independent and separate transaction that can issue
commits without affecting the calling transaction.
7. What are packages?
A package is nothing more than a way to
consolidate/group/organize/etc., common elements of PL/SQL code into a single
named entity. While packages do help improve things like portability and
maintainability of code, packages can also help improve the performance of the
code.
8. How might you hide/protect your source code if
distributed to customers?
Oracle's wrap utility provides a good way of hiding
PL/SQL source code; protecting it and making it difficult for others to view.
You can either wrap source code with the wrap utility or use the
DBMS_DDL subprograms.
9. Name two PL/SQL conditional control statements.
Very easily, the conditional statements are the IF
and the CASE statements.
10. Name two loop control statements.
The loop control statements consist of LOOP, FOR
LOOP, and WHILE LOOP.
Technical – Oracle QnA
4.
Explain the difference between a data block, an extent and a segment.
A
data block is the smallest unit of logical storage
for a database object. As objects grow they take chunks of additional storage
that are composed of contiguous data blocks. These groupings of contiguous data
blocks are called extents. All the extents that an object takes when grouped
together are considered the segment of the database object.
5.
Give two examples of how you might determine the structure of the table DEPT.
Use
the describe command or use the dbms_metadata.get_ddl package.
7.
Compare and contrast TRUNCATE and DELETE for a table.
Both
the truncate and delete command have the desired outcome of getting rid of all
the rows in a table. The difference between the two is that the truncate
command is a DDL operation and just moves the high water mark and produces a
now rollback. The delete command, on the other hand, is a DML operation, which
will produce a rollback and thus take longer to complete.
8.
Give the reasoning behind using an index.
Faster
access to data blocks in a table.
9.
Give the two types of tables involved in producing a star schema and the type
of data they hold.
Fact
tables and dimension tables. A fact table contains measurements while dimension
tables will contain data that will help describe the fact tables.
10.
. What type of index should you use on a fact table?
A
Bitmap index.
11.
Give two examples of referential integrity constraints.
A
primary key and a foreign key.
12.
A table is classified as a parent table and you want to drop and re-create it.
How would you do this without affecting the children tables?
Disable
the foreign key constraint to the parent, drop the table, re-create the table,
enable the foreign key constraint.
13.
Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the
benefits and disadvantages to each.
ARCHIVELOG
mode is a mode that you can put the database in for creating a backup of all
transactions that have occurred in the database so that you can recover to any
point in time. NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode
and has the disadvantage of not being able to recover to any point in time.
NOARCHIVELOG mode does have the advantage of not having to write transactions
to an archive log and thus increases the performance of
the database slightly.
16.
What column differentiates the V$ views to the GV$ views and how?
The
INST_ID column which indicates the instance in a RAC environment the
information came from.
17.
How would you go about generating an EXPLAIN plan?
Create
a plan table with utlxplan.sql.
Use
the explain plan set statement_id = 'tst1' into plan_table for a SQL statement
Look
at the explain plan with utlxplp.sql or utlxpls.sql
18.
How would you go about increasing the buffer cache hit ratio?
Use
the buffer cache advisory over a given workload and then query the
v$db_cache_advice table. If a change was necessary then I would use the alter
system set db_cache_size command.
19.
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.
20.
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.
21.
How would you determine the time zone under which a database was operating?
select DBTIMEZONE from dual;
22.
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.
23.
What command would you use to encrypt a PL/SQL application?
WRAP
24.
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.
25.
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.
26.
Name three advisory statistics you can collect.
Buffer
Cache Advice, Segment Level Statistics, & Timed Statistics
28.
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.
29.
When a user process fails, what background process cleans up after it?
PMON
30.
What background process refreshes materialized views?
The
Job Queue Processes.
31.
How would you determine what sessions are connected and what resources they are
waiting for?
Use
of V$SESSION and V$SESSION_WAIT
32.
Describe what redo logs are.
Redo
logs are logical and physical structures that are designed to hold all the
changes made to a database and are intended to aid in the recovery
of a database.
34.
Give two methods you could use to determine what DDL changes have been made.
You
could use Logminer or Streams
35.
What does coalescing a tablespace do?
Coalescing
is only valid for dictionary-managed tablespaces and de-fragments space by
combining neighboring free extents into large single extents.
36.
What is the difference between a TEMPORARY tablespace and a PERMANENT
tablespace?
A
temporary tablespace is used for temporary objects such as sort structures
while permanent tablespaces are used to store those objects meant to be used as
the true objects of the database.
37.
Name a tablespace automatically created when you create a database.
The
SYSTEM tablespace.
38.
When creating a user, what permissions must you grant to allow them to connect
to the database?
Grant
the CONNECT to the user.
42.
What view would you use to determine free space in a tablespace?
DBA_FREE_SPACE
43.
How would you determine who has added a row to a table?
Turn
on fine grain auditing for the table.
44.
How can you rebuild an index?
ALTER INDEX REBUILD;
45.
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.
46.
You have just compiled a PL/SQL package but got errors, how would you view the
errors?
SHOW ERRORS
47.
How can you gather statistics on a table?
The
ANALYZE command.
48.
How can you enable a trace for a session?
Use
the DBMS_SESSION.SET_SQL_TRACE or
Use
ALTER SESSION SET SQL_TRACE = TRUE;
49.
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.
50.
Name two files used for network connection to a database.
TNSNAMES.ORA
and SQLNET.ORA
Technical
– UNIX QnA
1.
How do you list the files in an UNIX directory while also showing hidden files?
ls -ltra
2.
How do you execute a UNIX command in the background?
Use
the "&"
3.
What UNIX command will control the default file permissions when files are
created?
Umask
4.
Explain the read, write, and execute permissions on a UNIX directory.
Read
allows you to see and list the directory contents.
Write
allows you to create, edit and delete files and subdirectories in the
directory.
Execute
gives you the previous read/write permissions plus allows you to change into
the directory and execute programs or shells from the directory.
5.
the difference between a soft link and a hard link?
A
symbolic (soft) linked file and the targeted file can be located on the same or
different file system while for a hard link they must be located on the same
file system.
6.
Give the command to display space usage on the UNIX file system.
df -lk
7.
Explain iostat, vmstat and netstat.
Iostat
reports on terminal, disk and tape I/O
activity.
Vmstat
reports on virtual memory statistics for processes, disk, tape and CPU
activity.
Netstat
reports on the contents of network data structures.
8.
How would you change all occurrences of a value using VI?
Use
:
%s///g
9.
Give two UNIX kernel parameters that effect an Oracle install
SHMMAX
& SHMMNI
10.
Briefly, how do you install Oracle software on UNIX.
Basically,
set up disks, kernel parameters, and run orainst.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.