Oracle Corporation (Nasdaq: ORCL) is the world's leading supplier of software for information management, and the world's second largest independent software company. With annual revenues of more than $10.1 billion, the company offers its database, tools and application products, along with related consulting, education, and support services, in more than 145 countries around the world. Headquartered in Redwood Shores, California, Oracle is the first software company to develop and deploy 100 percent internet-enabled enterprise software across its entire product line: database, server, enterprise business applications, and application development and decision support tools.
Oracle is the only company capable of implementing complete global e-business solutions that extend from front office customer relationship management to back office operational applications to platform infrastructure. Oracle software runs on PCs, workstations, minicomputers, mainframes and massively parallel computers, as well as on personal digital assistants and set-top devices. As more and more companies transform themselves into e-businesses, Oracle's Internet-enabled solutions provide a cost-effective way to expand market opportunities, improve business process efficiencies, and attract and retain customers. By replacing expensive, unwieldy client/server computing models with the efficiency and reach of the internet, companies can deploy a wealth of innovative applications that can be accessed with a Web browser.
The only software company to offer a full suite of e-business products, Oracle provides:
• An Internet-ready platform for building and deploying Web-based applications
• A comprehensive suite of Internet-enabled business applications
• Professional services for help in formulating e-business strategy, as well as in designing, customizing,
and implementing e-business solutions
Oracle: Past, Present and Future
The beginnings.
When CEO Lawrence J. Ellison and a few associates formed
Oracle in 1977, they were out to prove wrong the prevailing
theory that relationship databases could not be commercially
viable.
Larry Ellison, Bob Miner and Ed Oates found Software Development Laboratories. Inspired by a research paper written in 1970 by an IBM researcher titled " A Relational Model of Data for Large Shared Data Banks", the three decide to build a new type of database called a relational database system. Their orginal project is for the government and is titled Oracle. The founders believe that Oracle, meaning source of wisdom,
would be an appropriate name for their project and receive permission from the CIA to use it.
What's in a name?
In 1978, Software Development Laboratories moved from their office in Santa Clara to a new one on Sand Hill Road in Menlo Park, the heart of Silicon Valley. In an attempt to explain what their company does, they changed their name to Relational Software Inc., or RSI. The newly-christened company shipped its first commercial SQL-based database, V2, in 1979 (V1 was never officially released). In 1982 RSI changed its name to Oracle Systems Corporation, which later become Oracle Corporation.
Tapping the Internet.
In 1983, Oracle decided to make RDBMS portable, and introduced V3 - the first portable database to run on PCs, mini computers and mainframes. Today, Oracle targets high-end workstations and mini computers as the server platforms on which to run its database systems. Along with few others, Oracle has long been a champion of network computers. It now boasts that is was the world's first software company to develop and deploy 100 percent Internet-enabled enterprise software across its entire product line: database, server, enterprise business applications, applications development and decision support tools. In fact, CEO Ellison has said, "If the Internet turns out not to be the future of computing, we're toast. But if it is, we're golden."
A phenomenal success story.
Today, as proof of their success, they've parlayed an initial $2,000 investment in the company into an annual revenue exceeding $10.1 billion. Based in Redwood shores, California, it has more that 43,000 employees worldwide and does business in over 150 countries. Oracle (ORCL) is publicly traded on the NASDAQ.
Little known facts about Oracle
Oracle is ranked among the 10 best companies for a six-month return on equity.
Oracle's relational database was the world's first to support the Structured Query Language, now an industry standard.
Today, the Oracle DBMS is supported on over 80 different operating environments, ranging from IBM mainframes and DEC VAX minicomputers, to UNIX-based minicomputers and Windows NT platforms.
Oracle spends nearly 13% of their revenues for research and development.
Oracle V1 ran on PDP-11 under RSX, 128 KB max memory and was written in assembly language.
Oracle created the first database to run on a massively parallel computer.
All 10 of the world's largest Web sites- from Amazon.com to Yahoo! - use Oracle.
65% of the Fortune 100 use Oracle for e-business.
pattern
Here is the pattern of Oracle written text.
Section 1:Aptitude: 30 questions. concentration is more on the English part.
a) There will be a sentence and two fill ups. Choice were given to fill the blanks.
b) Analogy
c) There will be 4 sentences. You have to arrange them in order.
d) Only 6-7 questions from apti.Section 2:Technical: 30 questions.
a) C questions. around 8.
b) C++ questions around 2.
c) Java 2 questions.
d) Reaming were SQL.
Note: No negative marking.
Time 1 hr
Date of Test/Interview: 2004
Category: FRESHERSORACLE PAPER - 2004 - WARANGAL
REC Warangal ORACLE-2004
========================paper having two parts.
part 1: apptitute test -30question (20min)
part 2: technical Test -30question (20min)Part 1:
1)find the range of x*x -5x+6>0
X=32)if 5x*x+14*x+k=0 having two reciprocal roots find the value of k.
3&4)two sentence are given ,in between four sentence are given in change order. (like between 1 & 6 ,abcd four sentence are given 1 a b c d 6)
Find the correct order?5)if first term of AP is 5 .16th term is 45.find the sum of first 16 terms.
6)x,y and z are odd no. not necessarly in sequence.then y=?
(1)mean of x and z is 114.
(2)mean of y and z is 113.Ans: 115
7)ram is 27 year old then mohan.after 7 year ram age is thrice as mohan
age .find ram age?
Ans : 67/28)if three tapes are filling a tank of capacity of 500lit with speed of
30lit/sec,48lit/sec and 36lit/sec. Find after how long time tank will
fill?
Ans : 500/1149)find the root of given eq x-2/(x-1)=1-2/(x-2) if possible.
Ans : x = 010)find the point of intersection of \
15x+9y=15
5x+3y=21Ans : NO
11)suppose in a pot 20 cards are there label 1-20 on that. find the
probability of getting two prime.Ans : 8/20 = 2 / 5
12)in an island three persons are there Jam ,Dam and Sam .sam having
pencil,dam having cap and Jam having book .we have find who is author
among them if.
(1)Sam says ,Jam is author but author wearing cap.
(2)Dam says ,I am author ,but author not having cap.
(3)Sam says, I am author ,but author having note book.
Find who is author?Ans : jam
13)Jo ,Do and So are three person.
(1) So says Jo is football player, I also football player.
(2) Do says Jo is football player, I also football player.
(3) Jo says Do is football player, I also football player.
Find the football player player among them.Ans : jo
14-16)question based analogy . like LION:CUB find the pair in given choice.
17)in a basket 3 mangos,4 apples and 4 bananas are there. Find no of ways we select randomly 3 fruit from basket?
18) if a^x=b^y=c^z and b^2=ac then find x+z/xz=?
Ans : 2/y19)(loga)^2-(logb)^2 = ?
ans :- logab-loga/b20)in a 60 liters of mixture of spirit and water is 7 %. in order to make water 19%,how much water should be added.(some thing like this)
21-25)A one and half page length passage will be given and 4 to 5
questions will be asked based on this passage.26)
Technical part:-
==================
pattern: 1) pl/sql—>7 quest
2) java—>5 quest
3) c & pointers—>9 quest
4) dbms and dfd’s—>5 quest
5) ds—>4 questsome quest are here:-
1.what is not a c- storage class
Ans.stack2.if dba creates one user what are the privilages the user will get?
3. which keyword is used to const in java
Ans. final4.char *array[4]={”some string”}
printf(”%d”,&array[4]-(array));
Ans.45.remote object will be in the package
Ans.java.net (verity it)6.#define sq(a) (a*a)
printf (”%d”,sq (3+2));
Ans.117.#define max 20
printf (”%d”, ++max);
Ans.lvalue required — error8.printf (”%d”,sizeof (2>3?4?:4:3?4:2));(some thing like this)
Ans.syntax error9. what is important of free().
10.property of constructor in java program.
11.impotance of macro.
12.underfolw condition of stack.
(1)empty,2)push,3)pop,4)none)
====================1)Which of the following is not a storage class in C?
Static
Register
Extern
Stack2)Which of the following ‘return’ statement is correct?
return, return;
return(1, 2, 3);
return(return 4);
(return 5, return 6);3)The second argument to fopen() function is?
char
const char *
int *
FILE *4)What is the output of the program?
#include
void main()
{
char buffer[10] = {”Genesis”};
printf(” %d “, &buffer[4]- (buffer));
}
3
4
0
Illegal pointer subtraction
45)If “arr” is an array of 5 x 5 dimension, arr[2][4] is same as
**(a+3+4)
*(a+3)+*(a+4)
**(a+3)+4
*(*(a+2)+4)—————————————————–
APTITUDE 30 questionsLog37
1: ——- = ? ( Log67 )
1 + Log322: log(a-b) = log a - log b; then a = ?
(b/(b2+1));3: 3 black 7 white balls. prob of drawing 2 white balls ? (7/15)
4: In a lock of 4 rings there r five characters how
many unsuccessful attempts can be made ? (624)..5: sqrt(6+sqrt(6+sqrt (6 + ..))))) = ? 3
6: a solution is prepared by mixing two solution with sprit 20% & 60%..In
what ration they should be mixed to get the 50% sprit in resultant solution.
(1:3)7: X2 - (A-3)X +(A+3) = 0
for what value of A the sum of square of roots will be least ? (A=2);8: ‘a’ number of persons work daily ‘b’ hours to produce ‘c’ items. If ‘d’ persons walks away from the group what will be the number of hours to produce ‘c’ items. ? (ab/(a-d))
9: - 13 Passage
14 - 18 venn diagram.
19-22 picking based (VERBAL);
23-25 picking odd once out (words )
26: m = ax , n = ay mxny = a2(x+y) find 1/x + 1/y? (1)
27 xy - 2y - 6 =0;
x 2 x >2 ..
y is always postive.28
The diameter of circle can be determined from
1.area.
2.circumference.
Ans either of them.29: sum of 2 no. is 7 & product s 10 fnd larger number.
Three beauty pageant finalists-Cindy, Amy and Linda-The winner was musician. The one who was not last or first was a math major.The one who came in third had black hair. Linda had red hair. Amy had no musical abilities. Who was first?
(A) Cindy (B) Amy (C) Linda (D) None of theseTwo twins have certain peculiar characteristics. One of them always lies on Monday, Wednesday, Friday. The other always lies on Tuesdays, Thursday and Saturdays. On the other days they tell the truth. You are given a conversation.Person A- today is Sunday, my name is Anil Person B-today is Tuesday, my name is Bill What day is today?
(A) Sunday (B) Tuesday (C) Monday (D) ThursdayThe difference of a number and its reciprocal is 1/2.The sum of their squares is
(A) 9/4 (B) 4/5 (C) 5/3 (D) 7/4The difference of a number and its square is 870.What is the number?
(A) 42 (B) 29 (C) 30 (D) 32A trader has 100 Kg of wheat, part of which he sells at 5% profit and the rest at 20% profit. He gains 15% on the whole. Find how much is sold at 5% profit?
(A) 60 (B) 50 (C) 66.66 (D) 33.3Which of the following points are collinear?
(A) (3,5) (4,6) (2,7) (B) (3,5) (4,7) (2,3)
(C) (4,5) (4,6) (2,7) (D) (6,7) (7,8) (2,7)A man leaves office daily at 7pm.a driver with car comes from his home to pick him from office and bring back home. One day he gets free at 5.30 and instead of waiting for driver he starts walking towards home. In the wayhe meets the car and returns home on car. He reaches home 20 minutes earlier than usual. In how much time does the man reach home usually?
(A) 1 hr 20 min (B) 1 hr (C) 1 hr 10 min (D) 55 minIf m:n = 2:3,the value of 3m+5n/6m-n is
(A) 7/3 (B) 3/7 (C) 5/3 (D) 3/5dog taken four leaps for every five leaps of hare but three leaps of the dog is equal to four leap of the hare. Compare speed?
(A) 12:16 (B) 19:20 (C) 16:15 (D) 10:12A watch ticks 90 times in 95 seconds. And another watch ticks 315 times in 323 secs. If they start together, how many times will they tick together in first hour?
(A) 100 times (B) 101 times (C) 99 times (D) 102 timesThe purpose of defining an index is
(A) Enhance Sorting Performance (B) Enhance Searching Performance
(C) Achieve Normalization (D) All of the aboveA transaction does not necessarily need to be
(A) Consistent (B) Repeatable (C) Atomic (D) IsolatedTo group users based on common access permission one should use
(A) User Groups (B) Roles (C) Grants (D) None of the abovePL/SQL uses which of the following
(A) No Binding (B) Early Binding (C) Late Binding (D) Deferred BindingWhich of the constraint can be defined at the table level as well as at the column level
(A) Unique (B) Not Null (C) Check (D) All the aboveTo change the default date format in a SQLPLUS Session you have to
(A) Set the new format in the DATE_FORMAT key in the windows Registry.
(B) Alter session to set NLS_DATE-FORMAT.
(C) Change the Config.ora File for the date base.
(D) Change the User Profile USER-DATE-FORMAT.Which of the following is not necessarily an advantages of using a package rather than independent stored procedure in data base.
(A) Better performance. (B) Optimized memory usage.
(C) Simplified Security implementation. (D) Encapsulation.Integrity constrains are not checked at the time of
(A) DCL Statements. (B) DML Statements.
(C) DDL Statements. (D) It is checked all the above cases.Roll Back segment is not used in case of a
(A) DCL Statements. (B) DML Statements. (C) DDL Statements. (D) all of the above.An Arc relationship is applicable when
(A) One child table has multiple parent relation, but for anyone instance of a child record
only one of the relations is applicable.
(B) One column of a table is related to another column of the same table.
(C) A child table is dependent on columns other than the primary key columns of the parent
table.
(D) None of the above.What is true about the following C functions?
(A) Need not return any value. (B) Should always return an integer.
(C) Should always return a float. (D) Should always return more than one value.enum number { a=-1, b=4, c,d,e,} what is the value of e?
(A) 7 (B) 4 (C) 5 (D) 3Which of the following about automatic variables within a function is correct?
(A) Its type must be declared before using the variable. (B) They are local.
(C) They are not initialized to zero. (D) They are global.Consider the following program segment
int n, sum=5;
switch(n)
{
case 2:sum=sum-2;
case 3:sum*=5;
break;
default:sum=0;
}
if n=2, what is the value of the sum?
(A) 0 (B) 15 (C) 3 (D) None of these.Which of the following is not an infinite loop?
(A) x=0; (B) # define TRUE 0....
do{ While(TRUE){....}
/*x unaltered within the loop*/ (C) for(;;) {....}
....}
While(x==0); (D) While(1) {....}Output of the following program is
main()
{
int i=0;
for(i=0;i<20;i++)
{
switch(i)
{
case 0:
i+=5;
case 1:
i+=2;
case 5:
i+=5;
default:
i+=4;
break;
}
}
}
(A) 5,9,13,17 (B) 12,17,22 (C) 16,21 (D) syntax error.What does the following function print?
func(int i)
{
if(i%2) return 0;
else return 1;
}
main()
{
int i=3;
i=func(i);
i=func(i);
printf("%d",i);
}
(A) 3 (B) 1 (C) 0 (D) 2What will be the result of the following program?
char*g()
{
static char x[1024];
return x;
}
main()
{
char*g1="First String";
strcpy(g(),g1);
g1=g();
strcpy(g1,"Second String");
printf("Answer is:%s", g());
}
(A) Answer is: First String (B) Answer is: Second String
(C) Run time Error/Core Dump (D) None of theseConsider the following program
main()
{
int a[5]={1,3,6,7,0};
int *b;
b=&a[2];
}
The value of b[-1] is
(A) 1 (B) 3 (C) -6 (D) noneGiven a piece of code
int x[10];
int *ab;
ab=x;
To access the 6th element of the array which of the following is incorrect?
(A) *(x+5) (B) x[5] (C) ab[5] (D) *(*ab+5) .
Test was of 1 hour 30 questions. g aptitude and 30 of technical.......some questions. of aptitude
were very lengthy so don't end up in solving them.....time was less......some 15 questions. from
arithmetic......and rest 15 logical,statement interpretation,arrangement,tense correction......and
like that......
Given a square matrix which consists only of 1 and 0......find which rows,which cols and which diagonals consist entirely of 1's.
Given an array of integers find all possible combinations of the numbers whose sum equal to 90.....
ans : knapsack problem (in data structures - aho ullman)Note : for them solution was not the criteria.......but the approach was important......the solution
should be generalised and optimized........optimization was given the top priority........
Technical Papers
const int MAX=10;
main()
{
enum a {a,b,MAX};
print MAX;
}
ans. 2enum variable is a const variable which can only be assigned a value at initialization or a non const variable which can be assigned any value in the middle of the program?
ans. const variablevoid *p; what operation cannot be performed on p? ans : arithmetic operation unless it is properly typecasted
char **p="Hello";
print p,*p,**p
ans. Hello (null)
warning: suspicious pointer conversionmain()
{
char str[]="Geneius";
print (str);
}
print(char *s)
{
if(*s)
print(++s);
printf("%c",*s);
}
ans. suienewhat does the function fcloseall() does ?
ans. fcloseall() closes all open streams except stdin,stdout,stderr,stdprn and stdauxmain()
{
printf("Genius %d",fun(123));
}
fun(int n)
{
return (printf("%d",n));
}
ans. 123genius3difference between definition and declaration. ans. definition once while declaration more than once
find the error?
main()
{
void fun();
fun();
}
void fun()
{
int i=10;
if(i<10)
return 2.0;
return 3.0;
}
ans. no error but warningint a=9,b=5,c=3,d;
d=(b-c)<(c-a) ? a : b;
print d
ans 51+2/3*4+1=? Ans. 2
In C++, a variable can be defined wherever needed whereas not in C
main()
{
int i=4;
fun(i=i/4);
print i;
}
fun(int i)
{
return i/2;
}
ans 1what is an array ?
ans. contiguous collection of memory occupied by similar data typesprintf("\"NITK %%SURAHKAL%% !\"");
ans. "NITK %SURATHKAL% !"difference between scanf and gets in case of string input ans. scanf does not accepts white space while gets does
table t1 m rows and x cols table t2 n rows and y cols
in t1*t2 rows? cols=?
ans. m*n ,x+ysymbol of realtionship between 2 entities?
which one cannot come in data modelling a. customer b. student c. office d. speed ans speed
can a database table exist without a primary key ?
whether higher normal forms better than lower forms as far redundancy is concerned ? ans. higher
file is at which level ans. conceptual level
what is a foreign key ans. primary key in some other table
InterviewWhich are initial RDBMS, Hierarchical & N/w database ?
RDBMS - R system
Hierarchical - IMS
N/W - DBTGWhat is Functional Dependency
Given a relation R, attribute Y of R is functionally dependent on attribute X of R if and only if each X-value has associated with it precisely one -Y value in RWhat is Auditing ?
The database has the ability to audit all actions that take place within it.
a) Login attempts, b) Object Accesss, c) Database Action Result of Greatest(1,NULL) or Least(1,NULL) NULLWhile designing in client/server what are the 2 imp. things to be considered ?
Network Overhead (traffic), Speed and Load of client serverWhen to create indexes ?
To be created when table is queried for less than 2% or 4% to 25% of the table rows.How can you avoid indexes ?
TO make index access path unavailable - Use FULL hint to optimizer for full table scan - Use INDEX or AND-EQUAL hint to optimizer to use one index or set to indexes instead of another. - Use an expression in the Where Clause of the SQL.What is the result of the following SQL :
Select 1 from dual
UNION
Select 'A' from dual;
ErrorCan database trigger written on synonym of a table and if it can be then what would be the effect if original table is accessed.
Yes, database trigger would fire.Can you alter synonym of view or view ?
NoCan you create index on view ?
NoWhat is the difference between a view and a synonym ?
Synonym is just a second name of table used for multiple link of database. View can be created with many tables, and with virtual columns and with conditions. But synonym can be on view.What is the difference between alias and synonym ?
Alias is temporary and used with one query. Synonym is permanent and not used as alias.What is the effect of synonym and table name used in same Select statement ?
ValidWhat's the length of SQL integer ?
32 bit lengthWhat is the difference between foreign key and reference key ?
Foreign key is the key i.e. attribute which refers to another table primary key. Reference key is the primary key of table referred by another table.Can dual table be deleted, dropped or altered or updated or inserted ?
YesIf content of dual is updated to some value computation takes place or not ?
YesIf any other table same as dual is created would it act similar to dual?
YesFor which relational operators in where clause, index is not used ?
<> , like '% ...' is NOT functions, field +constant, field || ''Assume that there are multiple databases running on one machine. How can you switch from one to another ?
Changing the ORACLE_SIDWhat are the advantages of Oracle ?
Portability : Oracle is ported to more platforms than any of its competitors, running on more than 100 hardware platforms and 20 networking protocols.
Market Presence : Oracle is by far the largest RDBMS vendor and spends more on R & D than most of its competitors earn in total revenue. This market clout means that you are unlikely to be left in the lurch by Oracle and there are always lots of third party interfaces available.
Backup and Recovery : Oracle provides industrial strength support for on-line backup and recovery and good software fault tolerence to disk failure. You can also do point-in-time recovery.
Performance : Speed of a 'tuned' Oracle Database and application is quite good, even with large databases. Oracle can manage > 100GB databases.
Multiple database support : Oracle has a superior ability to manage multiple databases within the same transaction using a two-phase commit protocol.What is a forward declaration ? What is its use ?
PL/SQL requires that you declare an identifier before using it. Therefore, you must declare a subprogram before calling it. This declaration at the start of a subprogram is called forward declaration. A forward declaration consists of a subprogram specification terminated by a semicolon.What are actual and formal parameters ?
Actual Parameters : Subprograms pass information using parameters. The variables or expressions referenced in the parameter list of a subprogram call are actual parameters. For example, the following procedure call lists two actual parameters named emp_num and amount:
Eg. raise_salary(emp_num, amount);
Formal Parameters : The variables declared in a subprogram specification and referenced in the subprogram body are formal parameters. For example, the following procedure declares two formal parameters named emp_id and increase: Eg. PROCEDURE raise_salary (emp_id INTEGER, increase REAL) IS current_salary REAL;What are the types of Notation ?
Position, Named, Mixed and Restrictions.What all important parameters of the init.ora are supposed to be increased if you want to increase the SGA size ?
In our case, db_block_buffers was changed from 60 to 1000 (std values are 60, 550 & 3500) shared_pool_size was changed from 3.5MB to 9MB (std values are 3.5, 5 & 9MB) open_cursors was changed from 200 to 300 (std values are 200 & 300) db_block_size was changed from 2048 (2K) to 4096 (4K) {at the time of database creation}.
The initial SGA was around 4MB when the server RAM was 32MB and The new SGA was around 13MB when the server RAM was increased to 128MB.If I have an execute privilege on a procedure in another users schema, can I execute his procedure even though I do not have privileges on the tables within the procedure ?
YesWhat are various types of joins ?
Equijoins, Non-equijoins, self join, outer joinWhat is a package cursor ?
A package cursor is a cursor which you declare in the package specification without an SQL statement. The SQL statement for the cursor is attached dynamically at runtime from calling procedures.If you insert a row in a table, then create another table and then say Rollback. In this case will the row be inserted ?
Yes. Because Create table is a DDL which commits automatically as soon as it is executed. The DDL commits the transaction even if the create statement fails internally (eg table already exists error) and not syntactically.What are the various types of queries ??
Normal Queries
Sub Queries
Co-related queries
Nested queries
Compound queriesWhat is a transaction ?
A transaction is a set of SQL statements between any two COMMIT and ROLLBACK statements.What is implicit cursor and how is it used by Oracle ?
An implicit cursor is a cursor which is internally created by Oracle. It is created by Oracle for each individual SQL.Which of the following is not a schema object : Indexes, tables, public synonyms, triggers and packages ?
Public synonymsWhat is PL/SQL?
PL/SQL is Oracle's Procedural Language extension to SQL. The language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance), and so, brings state-of-the-art programming to the Oracle database server and a variety of Oracle tools.Is there a PL/SQL Engine in SQL*Plus?
No. Unlike Oracle Forms, SQL*Plus does not have a PL/SQL engine. Thus, all your PL/SQL are send directly to the database engine for execution. This makes it much more efficient as SQL statements are not stripped off and send to the database individually.Is there a limit on the size of a PL/SQL block?
Currently, the maximum parsed/compiled size of a PL/SQL block is 64K and the maximum code size is 100K. You can run the following select statement to query the size of an existing package or procedure.
SQL> select * from dba_object_size where name = 'procedure_name'Can one read/write files from PL/SQL?
Included in Oracle 7.3 is a UTL_FILE package that can read and write files. The directory you intend writing to has to be in your INIT.ORA file (see UTL_FILE_DIR=... parameter). Before Oracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command.
DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN('/home/oracle/tmp', 'myoutput','W');
UTL_FILE.PUTF(fileHandler, 'Value of func1 is %sn', func1(1));
UTL_FILE.FCLOSE(fileHandler);
END;How can I protect my PL/SQL source code?
PL/SQL V2.2, available with Oracle7.2, implements a binary wrapper for PL/SQL programs to protect the source code. This is done via a standalone utility that transforms the PL/SQL source code into portable binary object code (somewhat larger than the original). This way you can distribute software without having to worry about exposing your proprietary algorithms and methods. SQL*Plus and SQL*DBA will still understand and know how to execute such scripts. Just be careful, there is no "decode" command available.
The syntax is:
wrap iname=myscript.sql oname=xxxx.yyyCan one use dynamic SQL within PL/SQL? OR Can you use a DDL in a procedure ? How ?
From PL/SQL V2.1 one can use the DBMS_SQL package to execute dynamic SQL statements.
Eg: CREATE OR REPLACE PROCEDURE DYNSQL
AS
cur integer;
rc integer;
BEGIN
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur,'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE);
rc := DBMS_SQL.EXECUTE(cur);DBMS_SQL.CLOSE_CURSOR(cur);
END;
How does one use SQL*Loader to load images, sound clips and documents? (for DBA)
SQL*Loader can load data from a "primary data file", SDF (Secondary Data file - for loading nested tables and VARRAYs) or LOGFILE. The LOBFILE method provides and easy way to load documents, images and audio clips into BLOB and CLOB columns. Look at this example:
Given the following table:
CREATE TABLE image_table (
image_id NUMBER(5),
file_name VARCHAR2(30),
image_data BLOB);
Control File:
LOAD DATA
INFILE *
INTO TABLE image_table
REPLACE
FIELDS TERMINATED BY ','
(
image_id INTEGER(5),
file_name CHAR(30),
image_data LOBFILE (file_name) TERMINATED BY EOF
)
BEGINDATA
001,image1.gif
002,image2.jpgWhat is the difference between the conventional and direct path loader? (for DBA)
The conventional path loader essentially loads the data by using standard INSERT statements. The direct path loader (DIRECT=TRUE) bypasses much of the logic involved with that, and loads directly into the Oracle data files. More information about the restrictions of direct path loading can be obtained from the Utilities Users Guide.GENERAL INTERVIEW QUESTIONS
What are the various types of Exceptions ?
User defined and Predefined Exceptions.Can we define exceptions twice in same block ?
No.What is the difference between a procedure and a function ?
Functions return a single variable by value whereas procedures do not return any variable by value. Rather they return multiple variables by passing variables by reference through their OUT parameter.Can you have two functions with the same name in a PL/SQL block ?
Yes.Can you have two stored functions with the same name ?
Yes.Can you call a stored function in the constraint of a table ?
No.
What are the various types of parameter modes in a procedure ?
IN, OUT AND INOUT.What is Over Loading and what are its restrictions ?
OverLoading means an object performing different functions depending upon the no. of parameters or the data type of the parameters passed to it.Can functions be overloaded ?
Yes.Can 2 functions have same name & input parameters but differ only by return datatype ?
No.What are the constructs of a procedure, function or a package ?
The constructs of a procedure, function or a package are :
variables and constants
cursors
exceptions
Why Create or Replace and not Drop and recreate procedures ?
So that Grants are not dropped.Can you pass parameters in packages ? How ?
Yes. You can pass parameters to procedures or functions in a package.What are the parts of a database trigger ?
The parts of a trigger are:
A triggering event or statement
A trigger restriction
A trigger actionWhat are the various types of database triggers ?
There are 12 types of triggers, they are combination of :
Insert, Delete and Update Triggers.
Before and After Triggers.
Row and Statement Triggers.
(3*2*2=12)What is the advantage of a stored procedure over a database trigger ?
We have control over the firing of a stored procedure but we have no control over the firing of a trigger.What is the maximum no. of statements that can be specified in a trigger statement ?
One.Can views be specified in a trigger statement ?
NoWhat are the values of :new and :old in Insert/Delete/Update Triggers ?
INSERT : new = new value, old = NULL
DELETE : new = NULL, old = old value
UPDATE : new = new value, old = old valueWhat are cascading triggers? What is the maximum no of cascading triggers at a time?
When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading. Max = 32.What are mutating triggers ?
A trigger giving a SELECT on the table on which the trigger is written.What are constraining triggers ?
A trigger giving an Insert/Update on a table having referential integrity constraint on the triggering table.Describe Oracle database's physical and logical structure ?
Physical : Data files, Redo Log files, Control file.
Logical : Tables, Views, Tablespaces, etc.Can you increase the size of a tablespace ? How ?
Yes, by adding datafiles to it.What is the use of Control files ?
Contains pointers to locations of various data files, redo log files, etc.What is the use of Data Dictionary ?
Used by Oracle to store information about various physical and logical Oracle structures e.g. Tables, Tablespaces, datafiles, etcWhat are the advantages of clusters ?
Access time reduced for joins.What are the disadvantages of clusters ?
The time for Insert increases.Can Long/Long RAW be clustered ?
No.Can null keys be entered in cluster index, normal index ?
Yes.Can Check constraint be used for self referential integrity ? How ?
Yes. In the CHECK condition for a column of a table, we can reference some other column of the same table and thus enforce self referential integrity.What are the min. extents allocated to a rollback extent ?
TwoWhat are the states of a rollback segment ? What is the difference between partly available and needs recovery ?
The various states of a rollback segment are :
ONLINE, OFFLINE, PARTLY AVAILABLE, NEEDS RECOVERY and INVALID.What is the difference between unique key and primary key ?
Unique key can be null; Primary key cannot be null.An insert statement followed by a create table statement followed by rollback ? Will the rows be inserted ?
No.
an you define multiple savepoints ?
Yes.Can you Rollback to any savepoint ?
Yes.What is the maximum no. of columns a table can have ?
254.What is the significance of the & and && operators in PL SQL ?
The & operator means that the PL SQL block requires user input for a variable. The && operator means that the value of this variable should be the same as inputted by the user previously for this same variable. If a transaction is very large, and the rollback segment is not able to hold the rollback information, then will the transaction span across different rollback segments or will it terminate ? It will terminate (Please check ).Can you pass a parameter to a cursor ?
Explicit cursors can take parameters, as the example below shows. A cursor parameter can appear in a query wherever a constant can appear. CURSOR c1 (median IN NUMBER) IS SELECT job, ename FROM emp WHERE sal > median;
What are the various types of RollBack Segments ?
Public Available to all instances
Private Available to specific instanceCan you use %RowCount as a parameter to a cursor ?
Yes
Is the query below allowed :
Select sal, ename Into x From emp Where ename = 'KING'
(Where x is a record of Number(4) and Char(15))
YesIs the assignment given below allowed :
ABC = PQR (Where ABC and PQR are records)
Yes
Is this for loop allowed :
For x in &Start..&End Loop
Yes
How many rows will the following SQL return :
Select * from emp Where rownum <>
9 rowsHow many rows will the following SQL return :
Select * from emp Where rownum = 10;
No rowsWhich symbol preceeds the path to the table in the remote database ?
@Are views automatically updated when base tables are updated ?
YesCan a trigger written for a view ?
NoIf all the values from a cursor have been fetched and another fetch is issued, the output will be : error, last record or first record ?
Last RecordA table has the following data : [[5, Null, 10]]. What will the average function return ?
7.5Is Sysdate a system variable or a system function?
System FunctionConsider a sequence whose currval is 1 and gets incremented by 1 by using the nextval reference we get the next number 2. Suppose at this point we issue an rollback and again issue a nextval. What will the output be ?
3Definition of relational DataBase by Dr. Codd (IBM)?
A Relational Database is a database where all data visible to the user is organized strictly as tables of data values and where all database operations work on these tables.What is Multi Threaded Server (MTA) ?
In a Single Threaded Architecture (or a dedicated server configuration) the database manager creates a separate process for each database user. But in MTA the database manager can assign multiple users (multiple user processes) to a single dispatcher (server process), a controlling process that queues request for work thus reducing the databases memory requirement and resources.
What are the components of physical database structure of Oracle database?
Oracle database is comprised of three types of files. One or more datafiles, two are more redo log files, and one or more control files.What are the components of logical database structure of Oracle database?
There are tablespaces and database's schema objects.What is a tablespace?
A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical structures together.What is SYSTEM tablespace and when is it created?
Every Oracle database contains a tablespace named SYSTEM, which is automatically created when the database is created. The SYSTEM tablespace always contains the data dictionary tables for the entire database.Explain the relationship among database, tablespace and data file ?
Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace.What is schema?
A schema is collection of database objects of a user.What are Schema Objects?
Schema objects are the logical structures that directly refer to the database's data. Schema objects include tables, views, sequences, synonyms, indexes, clusters, database triggers, procedures, functions packages and database links.Can objects of the same schema reside in different tablespaces?
Yes.Can a tablespace hold objects from different schemes?
Yes.What is Oracle table?
A table is the basic unit of data storage in an Oracle database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.What is an Oracle view?
A view is a virtual table. Every view has a query attached to it. (The query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)What is Partial Backup ?
A Partial Backup is any operating system backup short of a full backup, taken while the database is open or shut down.What is Full Backup ?
A full backup is an operating system backup of all data files, on-line redo log files and control file that constitute ORACLE database and the parameter.Can a View based on another View ?
Yes.Can a Tablespace hold objects from different Schemes ?
Yes.
Can objects of the same Schema reside in different tablespace ?
Yes.What is the use of Control File ?
When an instance of an ORACLE database is started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed. It is also used in database recovery.Do View contain Data ?
Views do not contain or store data.What are the Referential actions supported by FOREIGN KEY integrity constraint ?
UPDATE and DELETE Restrict - A referential integrity rule that disallows the update or deletion of referenced data. DELETE Cascade - When a referenced row is deleted all associated dependent rows are deleted.What are the type of Synonyms?
There are two types of Synonyms Private and Public.What is a Redo Log ?
The set of Redo Log files YSDATE,UID,USER or USERENV SQL functions, or the pseudo columns LEVEL or ROWNUM.What is an Index Segment ?
Each Index has an Index segment that stores all of its data.
Explain the relationship among Database, Tablespace and Data file?
Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespaceWhat are the different type of Segments ?
Data Segment, Index Segment, Rollback Segment and Temporary Segment.What are Clusters ?
Clusters are groups of one or more tables physically stores together to share common columns and are often used together.What is an Integrity Constrains ?
An integrity constraint is a declarative way to define a business rule for a column of a table.What is an Index ?
An Index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.What is an Extent ?
An Extent is a specific number of contiguous data blocks, obtained in a single allocation, and used to store a specific type of information.What is a View ?
A view is a virtual table. Every view has a Query attached to it. (The Query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)What is Table ?
A table is the basic unit of data storage in an ORACLE database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.Can a view based on another view?
Yes.What are the advantages of views?
- Provide an additional level of table security, by restricting access to a predetermined set of rows and columns of a table.
- Hide data complexity.
- Simplify commands for the user.
- Present the data in a different perspective from that of the base table.
- Store complex queries.What is an Oracle sequence?
A sequence generates a serial list of unique numbers for numerical columns of a database's tables.What is a synonym?
A synonym is an alias for a table, view, sequence or program unit.What are the types of synonyms?
There are two types of synonyms private and public.What is a private synonym?
Only its owner can access a private synonym.What is a public synonym?
Any database user can access a public synonym.What are synonyms used for?
- Mask the real name and owner of an object.
- Provide public access to an object
- Provide location transparency for tables, views or program units of a remote database.
- Simplify the SQL statements for database users.What is an Oracle index?
An index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.How are the index updates?
Indexes are automatically maintained and used by Oracle. Changes to table data are automatically incorporated into all relevant indexes.What is a Tablespace?
A database is divided into Logical Storage Unit called tablespace. A tablespace is used to grouped related logical structures togetherWhat is Rollback Segment ?
A Database contains one or more Rollback Segments to temporarily store "undo" information.What are the Characteristics of Data Files ?
A data file can be associated with only one database. Once created a data file can't change size. One or more data files form a logical unit of database storage called a tablespace.How to define Data Block size ?
A data block size is specified for each ORACLE database when the database is created. A database users and allocated free database space in ORACLE data blocks. Block size is specified in INIT.ORA file and can?t be changed latter.What does a Control file Contain ?
A Control file records the physical structure of the database. It contains the following information.
Database Name
Names and locations of a database's files and redolog files.
Time stamp of database creation.What is Index Cluster ?
A Cluster with an index on the Cluster KeyWhen does a Transaction end ?
When it is committed or Rollbacked.What is the effect of setting the value "ALL_ROWS" for OPTIMIZER_GOAL parameter of the ALTER SESSION command ? What are the factors that affect OPTIMIZER in choosing an Optimization approach ?
Answer The OPTIMIZER_MODE initialization parameter Statistics in the Data Dictionary the OPTIMIZER_GOAL parameter of the ALTER SESSION command hints in the statement.What is the effect of setting the value "CHOOSE" for OPTIMIZER_GOAL, parameter of the ALTER SESSION Command ?
The Optimizer chooses Cost_based approach and optimizes with the goal of best throughput if statistics for atleast one of the tables accessed by the SQL statement exist in the data dictionary. Otherwise the OPTIMIZER chooses RULE_based approach.How does one create a new database? (for DBA)
One can create and modify Oracle databases using the Oracle "dbca" (Database Configuration Assistant) utility. The dbca utility is located in the $ORACLE_HOME/bin directory. The Oracle Universal Installer (oui) normally starts it after installing the database server software.
One can also create databases manually using scripts. This option, however, is falling out of fashion, as it is quite involved and error prone. Look at this example for creating and Oracle 9i database:
CONNECT SYS AS SYSDBA
ALTER SYSTEM SET DB_CREATE_FILE_DEST='/u01/oradata/';
ALTER SYSTEM SET DB_CREATE_ONLINE_LOG_DEST_1='/u02/oradata/';
ALTER SYSTEM SET DB_CREATE_ONLINE_LOG_DEST_2='/u03/oradata/';
CREATE DATABASE;What database block size should I use? (for DBA)
Oracle recommends that your database block size match, or be multiples of your operating system block size. One can use smaller block sizes, but the performance cost is significant. Your choice should depend on the type of application you are running. If you have many small transactions as with OLTP, use a smaller block size. With fewer but larger transactions, as with a DSS application, use a larger block size. If you are using a volume manager, consider your "operating system block size" to be 8K. This is because volume manager products use 8K blocks (and this is not configurable).What are the different approaches used by Optimizer in choosing an execution plan ?
Rule-based and Cost-based.What does ROLLBACK do ?
ROLLBACK retracts any of the changes resulting from the SQL statements in the transaction.How does one coalesce free space ? (for DBA)
SMON coalesces free space (extents) into larger, contiguous extents every 2 hours and even then, only for a short period of time.
SMON will not coalesce free space if a tablespace's default storage parameter "pctincrease" is set to 0. With Oracle 7.3 one can manually coalesce a tablespace using the ALTER TABLESPACE ... COALESCE; command, until then use:
SQL> alter session set events 'immediate trace name coalesce level n';
Where 'n' is the tablespace number you get from SELECT TS#, NAME FROM SYS.TS$;
You can get status information about this process by selecting from the SYS.DBA_FREE_SPACE_COALESCED dictionary view.How does one prevent tablespace fragmentation? (for DBA)
Always set PCTINCREASE to 0 or 100.
Bizarre values for PCTINCREASE will contribute to fragmentation. For example if you set PCTINCREASE to 1 you will see that your extents are going to have weird and wacky sizes: 100K, 100K, 101K, 102K, etc. Such extents of bizarre size are rarely re-used in their entirety. PCTINCREASE of 0 or 100 gives you nice round extent sizes that can easily be reused. E.g.. 100K, 100K, 200K, 400K, etc.
Use the same extent size for all the segments in a given tablespace. Locally Managed tablespaces (available from 8i onwards) with uniform extent sizes virtually eliminates any tablespace fragmentation. Note that the number of extents per segment does not cause any performance issue anymore, unless they run into thousands and thousands where additional I/O may be required to fetch the additional blocks where extent maps of the segment are stored.Where can one find the high water mark for a table? (for DBA)
There is no single system table, which contains the high water mark (HWM) for a table. A table's HWM can be calculated using the results from the following SQL statements:
SELECT BLOCKS
FROM DBA_SEGMENTS
WHERE OWNER=UPPER(owner) AND SEGMENT_NAME = UPPER(table);
ANALYZE TABLE owner.table ESTIMATE STATISTICS;
SELECT EMPTY_BLOCKS
FROM DBA_TABLES
WHERE OWNER=UPPER(owner) AND SEGMENT_NAME = UPPER(table);
Thus, the tables' HWM = (query result 1) - (query result 2) - 1
NOTE: You can also use the DBMS_SPACE package and calculate the HWM = TOTAL_BLOCKS - UNUSED_BLOCKS - 1.What is COST-based approach to optimization ?
Considering available access paths and determining the most efficient execution plan based on statistics in the data dictionary for the tables accessed by the statement and their associated clusters and indexes.What does COMMIT do ?
COMMIT makes permanent the changes resulting from all SQL statements in the transaction. The changes made by the SQL statements of a transaction become visible to other user sessions transactions that start only after transaction is committed.How are extents allocated to a segment? (for DBA)
Oracle8 and above rounds off extents to a multiple of 5 blocks when more than 5 blocks are requested. If one requests 16K or 2 blocks (assuming a 8K block size), Oracle doesn't round it up to 5 blocks, but it allocates 2 blocks or 16K as requested. If one asks for 8 blocks, Oracle will round it up to 10 blocks.
Space allocation also depends upon the size of contiguous free space available. If one asks for 8 blocks and Oracle finds a contiguous free space that is exactly 8 blocks, it would give it you. If it were 9 blocks, Oracle would also give it to you. Clearly Oracle doesn't always round extents to a multiple of 5 blocks.
The exception to this rule is locally managed tablespaces. If a tablespace is created with local extent management and the extent size is 64K, then Oracle allocates 64K or 8 blocks assuming 8K-block size. Oracle doesn't round it up to the multiple of 5 when a tablespace is locally managed.Can one rename a database user (schema)? (for DBA)
No, this is listed as Enhancement Request 158508. Workaround:
Do a user-level export of user A
create new user B
Import system/manager fromuser=A touser=B
Drop user ADefine Transaction ?
A Transaction is a logical unit of work that comprises one or more SQL statements executed by a single user.What is Read-Only Transaction ?
A Read-Only transaction ensures that the results of each query executed in the transaction are consistant with respect to the same point in time.What is a deadlock ? Explain .
Two processes wating to update the rows of a table which are locked by the other process then deadlock arises. In a database environment this will often happen because of not issuing proper row lock commands. Poor design of front-end application may cause this situation and the performance of server will reduce drastically.
These locks will be released automatically when a commit/rollback operation performed or any one of this processes being killed externally.What is a Schema ?
The set of objects owned by user account is called the schema.What is a cluster Key ?
The related columns of the tables are called the cluster key. The cluster key is indexed using a cluster index and its value is stored only once for multiple tables in the cluster.What is Parallel Server ?
Multiple instances accessing the same database (Only In Multi-CPU environments)
What are the basic element of Base configuration of an oracle Database ?
It consists of
one or more data files.
one or more control files.
two or more redo log files.
The Database contains
multiple users/schemas
one or more rollback segments
one or more tablespaces
Data dictionary tables
User objects (table,indexes,views etc.,)
The server that access the database consists of
SGA (Database buffer, Dictionary Cache Buffers, Redo log buffers, Shared SQL pool)
SMON (System MONito)
PMON (Process MONitor)
LGWR (LoG Write)
DBWR (Data Base Write)
ARCH (ARCHiver)
CKPT (Check Point)
RECO
Dispatcher
User Process with associated PGSWhat is clusters ?
Group of tables physically stored together because they share common columns and are often used together is called Cluster.What is an Index ? How it is implemented in Oracle Database ?
An index is a database structure used by the server to have direct access of a row in a table. An index is automatically created when a unique of primary key constraint clause is specified in create table comman (Ver 7.0)What is a Database instance ? Explain
A database instance (Server) is a set of memory structure and background processes that access a set of database files.
The process can be shared by all users. The memory structure that are used to store most queried data from database. This helps up to improve database performance by decreasing the amount of I/O performed against data file.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 of the table or cluster.What is default tablespace ?
The Tablespace to contain schema objects created without specifying a tablespace name.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 amout 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.
No comments:
Post a Comment