Neelkanth

• Sep 21st, 2006

There are only three tablespaces as follows:-1.Permanent tablespace2.Undo tablespace3.Temperory tablespace

na123

 Profile Answers by na123 Questions by na123

• Mar 24th, 2006

A tablespace is a collection of one or more datafiles.all database objects are stored in tablespaces.diff types of tablespaces:-1)system tablespace.2)temp tablespace.3)tools tablespace4)users tablespace.5)rollback tablespace.6)data and index tablespace.

Answer Question  Select Best Answer

pratyush

• Oct 22nd, 2006

in cases where a very small table is being joind to large table ,the oracle hash join will often dramatically speedup the querry hash join are for faster than nested loops join same cashes.regards pratyush singh

anu_dw

 Profile Answers by anu_dw Questions by anu_dw

• Sep 25th, 2006

The Hash join will be used, if there are no adequate indexes on the joined columns. This is a worst situation. In this case, hash table will be created. Hash join is most efficient when one of the tables is significantly differ in size than another one.Regards,Anu

Answer Question  Select Best Answer

Vamseek

 Profile Answers by Vamseek Questions by Vamseek

• Nov 10th, 2006

SELECT * FROM (SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME LIKE 'TEST');Above query will give the result as TEST.If you dont want to use the table name directly in the query, you can create synonym for that table and can use that synonym in your query.-Vamsee

hs_kashyap

 Profile Answers by hs_kashyap Questions by hs_kashyap

• Apr 25th, 2006

SELECT * FROM (SELECT TABLENAME FROM USER_TABLES WHERE TABLENAME LIKE 'TEM_')Please make check the column name is TABLENAME or TABLE_NAME.

Answer Question  Select Best Answer

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Dec 7th, 2006

 DBMS_OBFUSCATION_TOOLKIT  package provides the encryption of password.even DBA can not come to know what is the password, ofcouse he can reset  the new password.

opbang

 Profile Answers by opbang Questions by opbang

• Sep 22nd, 2006

use DBMS_OBFUSCATION_TOOLKIT package.see Oracle supplied pl/sql packages docs for more info

Answer Question  Select Best Answer

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Dec 7th, 2006

sorting mean puting the value in a particular order , could by alhphabaticgouping mean making a group of same type of values and displaying a single row for that groupexample select deptno, sum(sal)&n...

ravi wadalkar

• Jan 2nd, 2006

sorry to say,sorting will not give distinct values, but will just sort the data depending on asceding[default]or desc. group by will group the data depending upon the distinct values with in a column by which we are grouping

Answer Question  Select Best Answer

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Dec 7th, 2006

select .nextval from dual;select .currval from dual;orselect * from user_sequences where sequence_name = ;here last_number is your currval and last_number + increment_by is your nextval

kishorebabukm

 Profile Answers by kishorebabukm Questions by kishorebabukm

• Sep 20th, 2005

I would like to give you a small example to use the sequence.currval and sequence.nextvalcreate sequence seq_name start with 1minvalue 1 maxvalue 999 increment by 1 nocycleinsert into table_name (sno,name) values (seqname.nextval,'abc');select seqname.currval from dual

Answer Question  Select Best Answer

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Dec 7th, 2006

Alter table modify constraint < constraintname> disable/enableexamplealter table CONTACT_US modify constraint CONTACT_US_UK disable;

Mathivanan

• Jul 16th, 2005

Using enable/disable clause along with Alter command, we can activate/deactive constraints.  For example:  Alter table Empolyee disable constraint pk_EmpNumer

Answer Question  Select Best Answer

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Dec 7th, 2006

if index is create by primary key /unique key constraint thenby droping the constriant , the index will be droped automaticallyelsedrop index

koi.milgaya

• Jul 10th, 2005

Drop Index Indexname

Answer Question  Select Best Answer

Retrieves rows in hierarchical order.e.g. select empno, ename from emp where.

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Dec 7th, 2006

Select a.*, level from emp astart with mgr is nullconnect by mgr = prior empno

deva

• Sep 20th, 2006

connect by prior is used with the key word "level". Its used to get the records in the hierarchical manner. Eg   To get the empno of the employee who is working under manager. and the subordinates of this empno...and so on

Answer Question  Select Best Answer

INSTR (String1,String2(n,(m)),INSTR returns the position of the mth occurrence of the string 2 instring1. The search begins from nth position of string1.SUBSTR (String1 n,m)SUBSTR returns a character string of size m in string1, starting from nth position of string1.

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Dec 7th, 2006

substr is used to get a part of given string,instr is used to search a charcter in the given string.select substr(ename,1,3) three_charename, instr(ename,'A',1) a_present_at from emp;

tarun1282

 Profile Answers by tarun1282 Questions by tarun1282

• Apr 27th, 2006

Substr is used to get a sub part of a string but instr is a function that returns the position of a specific character and also according to it's occurence in that string.

Answer Question  Select Best Answer

INTERSECT returns all distinct rows selected by both queries.MINUS - returns all distinct rows selected by the first query but not by the second.UNION - returns all distinct rows selected by either queryUNION ALL - returns all rows selected by either query, including all duplicates.

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Dec 8th, 2006

union:  distinct result set of two queries.union all : result set of two or more queries including duplicatesminus: result set of first query  "-" resultset of second queryintersect : common result set of both query;

Answer Question  Select Best Answer

Maintaining data integrity through a set of rules that restrict the values of one or more columns of the tables based on the values of primary key or unique key of the referenced table.

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Dec 8th, 2006

Foreign key constraints is know as referential integrity. which refers to a primary or ( unique ,not null) column

Answer Question  Select Best Answer

SAVEPOINTS are used to subdivide a transaction into smaller parts. It enables rolling back part of a transaction. Maximum of five save points are allowed.

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Dec 8th, 2006

Savepoint is used to give some break points between multiple transactions so that if some transaction is required to be rolledback then some of transaction will still available in buffer. commit will save the data and remove all savepoints

Answer Question  Select Best Answer

CHAR,VARCHAR2,NUMBER,DATE,RAW,LONG and LONG RAW.

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Dec 8th, 2006

CHAR,VARCHAR2,NUMBER,DATE,RAW,LONG and LONG RAW,BLOB, CLOB,NCLOBFOR ORACEL 9I AND ABOVE.TIMESTAMP, TIMESTAMP WITH TIME ZONE, YEAR TO DAY, DAY TO SECOND

Answer Question  Select Best Answer

Only one LONG columns is allowed. It is not possible to use LONG column in WHERE or ORDER BY clause.

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Dec 8th, 2006

Only one LONG columns is allowed. It is not possible to use LONG column in WHERE or ORDER BY clause

Answer Question  Select Best Answer

The integrity constraints are stored in USER_CONSTRAINTS.

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Dec 8th, 2006

User_constraints, dba_constraints, all_constraints

Answer Question  Select Best Answer

The integrity constraints can be enabled or disabled by ALTER TABLE ENABLE constraint/DISABLE constraint.

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Dec 8th, 2006

Alter table modify constraint disable/enable

samiksc

 Profile Answers by samiksc Questions by samiksc

• Jan 19th, 2006

Alter table disable constraint unique(col1)

Answer Question  Select Best Answer

CYCLE specifies that the sequence continues to generate values after reaching either maximum or minimum value. After pan ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum.NO CYCLE specifies that the sequence cannot generate more values after reaching its maximum or minimum...

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Dec 8th, 2006

No cycle:  after reaching to max val sequence will not give nextvalcycle     after reaching to max val sequence will restart with startwith option vlues

tarun1282

 Profile Answers by tarun1282 Questions by tarun1282

• Apr 27th, 2006

In a sequence if we use cycle:- the sequence will repeat after the last sequence number with the first sequence number.in a sequence if we use no cycle:-the sequence will not repeat and continue to upward state.

Answer Question  Select Best Answer

ROWID is a pseudo column attached to each row of a table. It is 18 character long, blockno, rownumber are the components of ROWID.

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Dec 8th, 2006

rowid is a unique value assigned by system automatically when ever a insert statement gets successful.rowid contains the address of data file,data block, object id, rownumber

tarun1282

 Profile Answers by tarun1282 Questions by tarun1282

• Apr 27th, 2006

Rowid is a uniquely identified number to each row which is given by the oracle server automatically

Answer Question  Select Best Answer

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Dec 8th, 2006

NVLselect empno, ename, mgr, nvl(to_char(mgr),'no manager') from emp;

koi.milgaya

• Jul 10th, 2005

NVL or Decode

Answer Question  Select Best Answer