oracle11g interview questions
Top oracle11g frequently asked interview questions
I have defined a name for each of the constraint for the multiple tables that I have created in Oracle SQL.
The problem is that to drop a constraint for the column of a particular table I need to know the name that I have supplied for each constraints, which I have forgotten.
How do I list out all the names of constraints that I have specified for each column of a table?
Is there any SQL statement for doing so?
Source: (StackOverflow)
Is there a Hibernate dialect for Oracle Database 11g? Or should I use the org.hibernate.dialect.Oracle10gDialect that ships with Hibernate?
Source: (StackOverflow)
I installed Oracle 11g. I didn't change the passwords for SYSTEM and SYS. However now I find that the default passwords do not work. Please help.
Source: (StackOverflow)
I have installed Oracle Database 11g Expressed Edition on my pc (windows 7)
and I have installed Oracle SQL Developer as well.
I want to create a simple database to start with, maybe with one table or two and then use Oracle SQL Developer to insert data and query it.
When I open Oracle SQL Developer, it asks me to create a new connection, therefore it assumes that a database has already been created.
So my question is, how do I create an initial database in Oracle 11g?
Source: (StackOverflow)
Can any one tell me whether below 2 queries are an example of Left Outer Join or Right Outer Join??
Table Part:
Name Null? Type
PART_ID NOT NULL VARCHAR2(4)
SUPPLIER_ID VARCHAR2(4)
PART_ID SUPPLIER_ID
P1 S1
P2 S2
P3
P4
Table Supplier:
Name Null? Type
SUPPLIER_ID NOT NULL VARCHAR2(4)
SUPPLIER_NAME NOT NULL VARCHAR2(20)
SUPPLIER_ID SUPPLIER_NAME
S1 Supplier#1
S2 Supplier#2
S3 Supplier#3
Display all the parts irrespective of whether any supplier supplies them or not:
SELECT P.Part_Id, S.Supplier_Name
FROM Part P, Supplier S
WHERE P.Supplier_Id = S.Supplier_Id (+)
SELECT P.Part_Id, S.Supplier_Name
FROM Part P, Supplier S
WHERE S.Supplier_Id (+) = P.Supplier_Id
Thanks!
Source: (StackOverflow)
I have an application that I can't get connected to my Oracle Database 11g Express Edition. I created a test database in this edition, and I can connect to the database fine using Oracle SQL Developer, create tables, views etc. However, I'm having a hard time getting connected via my application. Where is the connection information? In what file? I wanted to compare my connection info with what is set up in the SQL Explorer's file. I found all the *.ora files and renamed them to see if I could find what file (through the process of elimination) the connections were stored in, but I wasn't successful. Any help would be appreciated.
Source: (StackOverflow)
Can someone advise me on how to create a user in Oracle 11g and only grant that user the ability only to execute one particular stored procedure and the tables in that procedure.
I am not really sure how to do this!
Source: (StackOverflow)
We have an application running locally which two days worked fine and now we get the:
TNS:listener does not currently know of service requested in connect descriptor error.
I've tested the connection using TNSPing which resolved correctly and
Tried SQLPlus to try connecting, which failed with the same error. (i.e. Sqlplus username/passowrd@addressname[or host name]
The TNS Listener on the server is running.
Oracle itself on the server is running.
We don't know of any changes that were made to this environment.
Anything else we can test to figure this one out?
Source: (StackOverflow)
With help of this command show databases; I can see databases in MySQL.
How to show the available databases in Oracle?
Source: (StackOverflow)
This is a two part question:
I would like to know where does Oracle ADF 11g stand as a framework to develop / deploy Web / Java EE Applications as compared to other frameworks.
How much is Oracle ADF being used as a framework to Develop Web Applications.
I am asking from a perspective that whether or not there are good job opportunities as an Oracle ADF Developer and what the future looks like for those holding Oracle ADF skills.
Also as Oracle plans to use ADF as core technology for Fusion Applications, Is it good time to build Oracle ADF skills as a siebel developer? As the future Oracle CRM Applications like ERP, SCM will be based on ADF should developers start building ADF skills. Around when will customers start implementing those applications based on ADF. Will these be available On Premise or mostly On Demand in SaaS way.
Source: (StackOverflow)
In the example below I have written one to_str() function and one set() procedure for every pls_integer subtype. The functions and procedures are almost identical except the type.
How I can eliminate the need to write yet another to_str() and set() for a new subtype without giving up the constraint provided by the subtype ?
Falling back to varchar2 like
procedure set(list in varchar2, prefix in varchar2)
and then calling it as
set(to_str(list), 'foos:')
doesn't sound too great idea and I still need to provide to_str() for each subtype.
I'm open for all kind of different proposals as I'm Oracle newbie and new Oracle features suprise me almost daily.
I'm running 11.2.0.1.0.
create table so1table (
id number,
data varchar(20)
);
create or replace package so1 as
subtype foo_t is pls_integer range 0 .. 4 not null;
type foolist is table of foo_t;
procedure set(id_ in number, list in foolist default foolist(1));
subtype bar_t is pls_integer range 5 .. 10 not null;
type barlist is table of bar_t;
procedure set(id_ in number, list in barlist default barlist(5));
end;
/
show errors
create or replace package body so1 as
/* Do I have always to implement these very similar functions/procedures for
every single type ? */
function to_str(list in foolist) return varchar2 as
str varchar2(32767);
begin
for i in list.first .. list.last loop
str := str || ' ' || list(i);
end loop;
return str;
end;
function to_str(list in barlist) return varchar2 as
str varchar2(32767);
begin
for i in list.first .. list.last loop
str := str || ' ' || list(i);
end loop;
return str;
end;
procedure set(id_ in number, list in foolist default foolist(1)) as
values_ constant varchar2(32767) := 'foos:' || to_str(list);
begin
insert into so1table (id, data) values (id_, values_);
end;
procedure set(id_ in number, list in barlist default barlist(5)) as
values_ constant varchar2(32767) := 'bars:' || to_str(list);
begin
insert into so1table (id, data) values (id_, values_);
end;
end;
/
show errors
begin
so1.set(1, so1.foolist(0, 3));
so1.set(2, so1.barlist(5, 7, 10));
end;
/
SQLPLUS> select * from so1table;
ID DATA
---------- --------------------
1 foos: 0 3
2 bars: 5 7 10
Source: (StackOverflow)
I have applied for an internship in a company and as a question they have asked me to create a schema for their company with certain requirements and mail them the ddl file.i have installed oracle 11g express edition,but how do i create a new schema in oracle 11g?i have searched in the net for a solution but could understand what to do.and after creating a schema ,which file should i mail them?
Source: (StackOverflow)
As detailed here and confirmed here, the default number of rows Oracle returns at time when querying for data over JDBC is 10. I am working on an app that to has to read and compare lots of data from our database. I thought that if we just increase defaultRowPrefetch to be something like 1000, then surely our app would perform faster. As it turned out, it performed slower and by about 20%.
We then decided to just slowly increase the number from 10 and see how it performs. We've seen about a 10% increase by setting it somewhere between 100 and 200. I would have never guessed, however, that setting it higher would make our app perform more slowly. Any ideas why this might happen?
Thanks!
EDIT:
Just for clarification, I'm using Oracle 11g R2 and Java 6.
EDIT 2:
Okay, I wanna restate my question to be clear, because judging from the answers below, I am not expressing myself properly:
How is it possible that if I set a higher fetch size, my app performs slower? To me, that sounds like saying "We're giving you a faster internet connection, i.e. a fatter pipe, but your web browsing will be slower.
All other things being equal, as they have been in our tests, we're super curious about how our app could perform worse with only this one change.
Source: (StackOverflow)
I'm trying to install Oracle 11g Release 2 (client). But it gives an error like that :
Environment variable: "PATH" - This test checks whether the length of the environment variable "PATH" does not exceed the recommended length.
Expected Value: 1023
Actual Value : 1028
List of errors: - PRVF-3929 : Adding the Oracle binary location to the PATH
environment variable will exceed the OS length limit of [ "1023" ] for the
variable on the node "KamalNuriyev-PC" -
Cause: The installer needs to update the PATH environment variable to
include the value "%ORACLE_HOME%/bin;". However, doing so will
cause PATH to exceed the maximum allowable length that this
operating system allows. - Action: Ensure that the sum of the
lengths of your current PATH environment variable and that of
"%ORACLE_HOME%/bin;" does not exceed the operating system limit.
Restart the installer after correcting the setting for
environment variable.
Do u have any ideas?
Thank u
Source: (StackOverflow)