jump to navigation

Oracle Milestones: From ZERO to HERO May 9, 2010

Posted by sendtoshailesh in Uncategorized.
2 comments

Oracle  – Name itself ignites anybody to innovate. In this post I am presenting an interesting History of Oracle evolution. These milestones are derived from one of article of David Kelly…………

1977 Larry Ellison, Bob Miner, and Ed Oates launch Software Development Laboratories, the predecessor of Oracle.

1978 Oracle Version 1, written in assembly language, runs on PDP-11 under RSX, in 128K of memory. Implementation separates Oracle code and user code. Oracle Version 1 is never officially released.

1979 Oracle Version 2, the first commercial SQL relational database management system (RDBMS), is released. The company changes its name to Relational Software Inc. (RSI).

1981 RSI begins developing tools for Oracle, including the Interactive Application Facility, a predecessor of Oracle Forms.

1982 RSI gets a new name—Oracle Systems—and hosts its first user conference, in San Francisco.

1983 Oracle Version 3, built on C, is the first RDBMS to run on mainframes, minicomputers, and PCs.

1984 Oracle Version 4, which supports read consistency, is released. Oracle ports Oracle Database to the PC platform. The MS-DOS version (4.1.4) of Oracle runs in only 512K of memory.

1985 Oracle releases Oracle Version 5, one of the first RDBMSs to operate in client/server environments.

1986 Oracle goes public on the NASDAQ exchange.

1987 Already the world’s largest database company, Oracle launches an effort to build enterprise applications that take advantage of Oracle Database.

1988 Oracle Version 6 debuts with major advances: Row-level locking allows multiple users to work in the same table, by processing only the specific data used in a transaction. Hot backup reduces system maintenance overhead, by allowing employees to continue working in the system while administrators duplicate and archive data. PL/SQL enables users to process data while it remains in the database.

1989 Oracle moves into its world headquarters in Redwood Shores, California.

1990 The company launches Oracle Applications Release 8, which includes accounting programs designed for the emerging client/server computing environment.

1992 Oracle7 is released, with performance enhancements, administrative utilities, application development tools, security features, stored procedures, triggers, support for declarative referential integrity, and the PL/SQL procedural language embedded in the database.

1993 Oracle is the first software company to rewrite business applications for client/server environments, automating business processes from a centralized data center.

1994 Oracle earns the industry’s first independent security evaluations, adding third-party assurance of the strength of Oracle’s products.

1995 Oracle becomes the first major software company to announce a comprehensive internet strategy.

1996 Oracle delivers Universal Server, enabling customers to use Oracle Database to manage any type of data—text, video, maps, sound, or images.

1997 Oracle releases Oracle8.

1998 With Oracle8 Database and Oracle Applications 10.7, Oracle is the first enterprise computing company to embrace Java.

1999 Internet capabilities saturate every Oracle offering, from support for open standards technologies such as XML and Linux to the latest versions of Oracle product lines, such as Oracle Applications 11i and Oracle8i Database.

2000 Oracle ships Oracle E-Business Suite Release 11i, the industry’s first integrated suite of enterprise applications.

2001 Oracle9i Database adds Oracle Real Application Clusters, giving customers the option to run their IT on connected, low-cost servers.

2002 Oracle launches the “Unbreakable” campaign to mark the unprecedented 15 independent security evaluations earned by Oracle Database.

2003 Oracle debuts Oracle Database 10g, the first grid computing product available for the enterprise. Oracle grid computing serves computing power across the enterprise as a utility, automatically shifting processing loads based on demand.

2004 Oracle provides a single customer view from multiple datasources with Oracle Customer Data Hub.

2005 Oracle completes the acquisition of PeopleSoft and announces its intention to acquire Siebel Systems.

2006 Oracle deepens a 30-year commitment to open standards computing with Oracle Unbreakable Linux—giving customers the same level of support for Linux that they receive for other Oracle products. The move in effect certifies the operating system for enterprise computing.

2007 Oracle launches five application product lines and acquires Hyperion Solutions, a provider of performance management

DBMS_METADATA GET_DDL : Avoid ORA-31603 error by using SELECT_CATALOG_ROLE May 8, 2010

Posted by sendtoshailesh in Uncategorized.
10 comments
While extracting DDLs from command-line, I always find the interesting thing to learn. Below post is my another learning with the same.
This is about how to avoid ORA-31603 error while extracting DDLs of other schema objects even one have all the privileges on the objects. This is somethings dealing with database security and about one role SELECT_CATALOG_ROLE.
Lets start with example with formulating the problem…………….
I’m testing this with two sample schema HR and SCOTT on 10g DB.
Lets connect to HR and test wether this user have access to SCOTT EMP table or not.
SQL> conn hr/hr
Connected.
SQL> desc scott.emp
ERROR:
ORA-04043: object scott.emp does not exist
Oh no. Lets give all the privilege on EMP table to HR.
SQL> conn scott/tiger
Connected.
SQL> grant all on emp to hr;
Grant succeeded.
Hmmm………. Now hr have all the privilge on SCOTT.EMP table. So why cant HR extract the DDLs for the same. Lets see….
SQL> conn hr/hr
Connected.
SQL> select dbms_metadata.get_ddl(‘TABLE’,’EMP’,’SCOTT’) from dual;
ERROR:
ORA-31603: object “EMP” of type TABLE not found in schema “SCOTT”
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 105
ORA-06512: at “SYS.DBMS_METADATA”, line 2805
ORA-06512: at “SYS.DBMS_METADATA”, line 4333
ORA-06512: at line 1
no rows selected
OOOOOps……….. So why HR cant do this. Even he has all the privilege on SCOTT.EMP table. This is becasue HR needs SELECT_CATALOG_ROLE to browse the dictionaries as HR is not privileged user like DBA. When DBMS_METADATA package is called, it attempts to browse the dictionaries. If Calling use does not have privilege to browse the dictionary the it fires error by saying invisibility to the target object that is ORA-31603 error.
Now lets complete the loop by granting this role to HR.
SQL> conn sys as sysdba
Connected.
SQL> grant select_catalog_role to hr;
Grant succeeded.
Ready to test it.
SQL> conn hr/hr
Connected.
SQL> set head off
SQL> set long 40000
SQL> select dbms_metadata.get_ddl(‘TABLE’,’EMP’,’SCOTT’) from dual;
CREATE TABLE “SCOTT”.”EMP”
( “EMPNO” NUMBER(4,0),
“ENAME” VARCHAR2(10),
“JOB” VARCHAR2(9),
“MGR” NUMBER(4,0),
“HIREDATE” DATE,
“SAL” NUMBER(7,2),
“COMM” NUMBER(7,2),
“DEPTNO” NUMBER(2,0),
CONSTRAINT “PK_EMP” PRIMARY KEY (“EMPNO”)
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE “USERS”  ENABLE,
CONSTRAINT “FK_DEPTNO” FOREIGN KEY (“DEPTNO”)
REFERENCES “SCOTT”.”DEPT” (“DEPTNO”) ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE “USERS”
Hurray !!
So to use DBMS_METADATA, one should have at least SELECT_CATALOG_ROLE. If user is already a DBA then it already have this role.
Thanks & Enjoy !!

is it possible to copy the stats? – YES >> DBMS_STATS >> COPY_TABLE_STATS May 8, 2010

Posted by sendtoshailesh in Uncategorized.
add a comment

While working in data integration project, I got the opportunity to tackle very large 2-Level partition tables. Here we have to add partition or I could say at granular level, sub-partition on every week-end. Moderately these partitions and subpartions get loaded with heaps of structured data.

Ah…at this stage we need to take care of table statistics. Most diffcult part is when, which can suit to user’s query. Its been very long time I felt need why we can’t copy the stats which is already genearted for some partitions to another partition?

Now the answer is YES.

DBMS_STATS.COPY_TABLE_STATS………..with this new feature in 11g it is possible.

I was going through several tech article but at below link one can find the best usability of this.

http://oracledoug.com/serendipity/index.php?/archives/1596-Statistics-on-Partitioned-Tables-Part-6a-COPY_TABLE_STATS.html

This is best explained with in-lined examples

enjoy!!

How to find the locked tables or object April 20, 2010

Posted by sendtoshailesh in Uncategorized.
add a comment 
select     oracle_username || ' (' || s.osuser || ')' username
,  s.sid || ',' || s.serial# sess_id
,  owner || '.' || object_name object
,  object_type
,  decode( l.block
,       0, 'Not Blocking'
,       1, 'Blocking'
,       2, 'Global') status
,  decode(v.locked_mode
,       0, 'None'
,       1, 'Null'
,       2, 'Row-S (SS)'
,       3, 'Row-X (SX)'
,       4, 'Share'
,       5, 'S/Row-X (SSX)'
,       6, 'Exclusive', TO_CHAR(lmode)) mode_held
from       v$locked_object v
,  dba_objects d
,  v$lock l
,  v$session s
where      v.object_id = d.object_id
and        v.object_id = l.id1
and        v.session_id = s.sid
order by oracle_username
,  session_id

Extract user-defined function or stored procedure source code or definition: Oracle vs SQL Server vs DB2 vs MYSQL vs Informix vs Sybase : count number of occurences March 30, 2010

Posted by sendtoshailesh in Uncategorized.
Tags:
1 comment so far

Digging dictionaries or system catalog is always interesting. For my one of project, I had to extract the source code or definition of user-defined function or stored procedure based on given keyword or pattern. this was on multiple database product. counting the number of occurrences in body of each function and procedures was also main stuff in the code.

For Oracle :

Stored Function search:

select schema_name, function_name,count(no_of_occur_in_this_line) total_no_of_occur

from (

select owner schema_name,name function_name,line line_no,

(LENGTH(upper(text)) – LENGTH(REPLACE(upper(text), upper(‘search_keyword’), ”)))

/ LENGTH(‘search_keyword’)  no_of_occur_in_this_line, text line_of_code

from all_source a

where type=’FUNCTION’ and upper(text) like ‘%’||upper(‘Search_Keyword’)||’%’)

group by schema_name, function_name;

Stored Procedure search:

select schema_name,procedure_name,count(no_of_occur_in_this_line) total_no_of_occur

from (

select owner schema_name,name procedure_name,line line_no,

(LENGTH(upper(text)) – LENGTH(REPLACE(upper(text), upper(‘search_keyword’), ”)))

/ LENGTH(‘search_keyword’) no_of_occur_in_this_line, text line_of_code

from all_source a

where type=’PROCEDURE’ and upper(text) like ‘%’||upper(‘Search_Keyword’)||’%’)

group by schema_name,procedure_name;

———————————————————————————————————————-

For DB2:

Stored Function search:

select ROUTINESCHEMA schema_name,ROUTINEMODULENAME function_name,

(LENGTH(upper(text)) – LENGTH(REPLACE(upper(text), upper(‘search_keyword’), ”)))

/ LENGTH(‘search_keyword’)  no_of_occur, text source_code

from SYSCAT.ROUTINES

where ROUTINETYPE=’F’ and upper(text) like ‘%’||upper(‘search_keyword’)||’%’;

Stored Procedure search:

select ROUTINESCHEMA schema_name,ROUTINEMODULENAME procedure_name,

(LENGTH(upper(text)) – LENGTH(REPLACE(upper(text), upper(‘search_keyword’), ”)))

/ LENGTH(‘search_keyword’)  no_of_occur, text source_code

from SYSCAT.ROUTINES

where ROUTINETYPE=’P’ and upper(text) like ‘%’||upper(‘search_keyword’)||’%’;

—————————————————————————————————————————–

For SQL Server:

Stored Function search:

Keyword occurrence count in function body

SELECT all_functions.object_id,all_functions.function_name,all_functions.schema_name,

all_functions.function_type,

(LEN(upper(f_source.definition)) – LEN(REPLACE(upper(f_source.definition), upper(‘Search_Keyword’), ”))) / LEN(‘Search_Keyword’) no_of_occurr

from (SELECT object_id,name AS function_name ,SCHEMA_NAME(schema_id) AS schema_name,

type_desc function_type

FROM sys.objects

WHERE type_desc LIKE ‘%FUNCTION%’) all_functions,sys.sql_modules f_source

Where (all_functions.function_name like ‘%’+upper(‘Search_Keyword’)+’%’

or

exists (Select ‘X’

from sys.sql_modules sm where all_functions.object_id=sm.object_id

and sm.definition like ‘%’+upper(‘Search_Keyword’)+’%’))

and all_functions.object_id=f_source.object_id;

List of functions where keyword found

SELECT object_id,function_name,schema_name,function_type from (

SELECT object_id,name AS function_name ,SCHEMA_NAME(schema_id) AS schema_name,type_desc function_type

FROM sys.objects

WHERE type_desc LIKE ‘%FUNCTION%’) all_functions

Where all_functions.function_name like ‘%’+upper(‘search_keyword’)+’%’

or

exists (Select ‘X’

from sys.sql_modules sm where all_functions.object_id=sm.object_id

and sm.definition like ‘%’+upper(‘search_keyword’)+’%’);

 
 
Stored Procedure search:
  
Keyword occurrence count in procedure body
  
SELECT all_procedures.object_id,all_procedures.procedure_name,all_procedures.schema_name, 

all_procedures.procedure_type, 

(LEN(upper(p_source.definition)) - LEN(REPLACE(upper(p_source.definition), upper('Search_Keyword'), ''))) / LEN('Search_Keyword') no_of_occurr
 from (SELECT object_id,name AS procedure_name ,SCHEMA_NAME(schema_id) AS schema_name, 
type_desc procedure_type
 FROM sys.objects 
WHERE type_desc LIKE '%PROCEDURE%') all_procedures,sys.sql_modules p_source
 Where (all_procedures.procedure_name like '%'+upper('Search_Keyword')+'%' 
or exists (Select 'X'
 from sys.sql_modules sm where all_procedures.object_id=sm.object_id 
and sm.definition like '%'+upper('Search_Keyword')+'%'))
 and all_procedures.object_id=p_source.object_id;  
List of procedures where keyword found
    
SELECT object_id,procedure_name,schema_name,procedure_type from ( 

SELECT object_id,name AS procedure_name , 

SCHEMA_NAME(schema_id) AS schema_name, type_desc procedure_type 

FROM sys.objects  

WHERE type_desc LIKE '%PROCEDURE%') all_procedures  

Where all_procedures.procedure_name like '%'+upper('search_keyword')+'%'  

or  exists (Select 'X' 

from sys.sql_modules sm where all_procedures.object_id=sm.object_id
 and sm.definition like '%'+upper('search_keyword')+'%');

-----------------------------------------------------------------------------------------------
For Informix:

Stored Function search:
  
select owner schema_name,procname function_name, 

(LENGTH(upper(data)) - LENGTH(REPLACE(upper(data), upper('Search_Keyword'), '')))

/ LENGTH('Search_Keyword')  no_of_occur, data source_code

from sysprocedures a,sysprocbody b

where a.procid=b.procid and b.datakey='T' and upper(data) like '%'||upper('Search_Keyword')||'%'
----------------------------------------------------------------------------------------------------
For MYSQL:


Stored Function search:
  
SELECT ROUTINE_SCHEMA SCHEMA_NAME, ROUTINE_NAME,ROUTINE_DEFINITION source_code,

(LENGTH(upper(ROUTINE_DEFINITION)) - LENGTH(REPLACE(upper(ROUTINE_DEFINITION), upper('search_keyword'), '')))

/ LENGTH('search_keyword')  no_of_occur

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_TYPE='FUNCTION' 

and (ROUTINE_NAME like concat(concat('%',upper('search_keyword')),'%')
 or ROUTINE_DEFINITION like concat(concat('%',upper('search_keyword')),'%'));



Stored Procedure search:
  
SELECT ROUTINE_SCHEMA SCHEMA_NAME, ROUTINE_NAME,ROUTINE_DEFINITION source_code,

(LENGTH(upper(ROUTINE_DEFINITION)) - LENGTH(REPLACE(upper(ROUTINE_DEFINITION), upper('search_keyword'), '')))

/ LENGTH('search_keyword')  no_of_occur

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_TYPE='PROCEDURE'

and (ROUTINE_NAME like concat(concat('%',upper('search_keyword')),'%')

or ROUTINE_DEFINITION like concat(concat('%',upper('search_keyword')),'%'));

 

--------------------------------------------------------------------------------------

For Sybase:

Stored Function search:
  
select creator schema_name,proc_name function_name, 

(LENGTH(upper(proc_defn)) - LENGTH(REPLACE(upper(proc_defn), upper('Search_Keyword'), '')))

/ LENGTH('Search_Keyword')  no_of_occur, proc_defn source_code

from sysprocedure

where upper(proc_defn) like '%'||upper('Search_Keyword')||'%'

 

Stored Procedure search:

select creator schema_name,proc_name procedure_name,

(LENGTH(upper(proc_defn)) - LENGTH(REPLACE(upper(proc_defn), upper('Search_Keyword'), '')))

/ LENGTH('Search_Keyword')  no_of_occur, proc_defn source_code

from sysprocedure

where upper(proc_defn) like '%'||upper('Search_Keyword')||'%'

DBA Questionnaire or FAQ: Ready for Interview…… March 12, 2010

Posted by sendtoshailesh in Uncategorized.
1 comment so far

Here, I am collecting all the question which can be asked to DBA at any time. May be at the time of interview or DBA can have this question by its own. I am making the list of question here and as an then I will have time I will try to answer it or link it to appropriate link:
I’ll glad to have answers through comments

I am choosing Oracle Database as an context to answers it

Here we go:

What are the data types allowed in a table ?
What is the functionality of SYSTEM table space
What is meant by Scrollable cursor
What is an anchoring object & what is its use?
The use of HAVING , WHERE and GROUPBY in one SQL
What are the modes of parameters that can be
What is a canvas-view?
What is a Rollback segment entry ?
What are the trigger associated with image items?
Why is a Where clause faster than a group filter
Deleting a page removes information about all
What triggers are associated with the radio group?
How image_items can be populate to field in forms 4.0?
What is the difference between system.current
What is the use of TABLES option in EXP command ?
List the factors that can affect the accuracy of the estima
Where is a procedure return in an external pl/sql library
What are the types of database links
What is Network Database link ?
What are the steps involved in Database Startup ?
What is the maximum size of a form ?
What are the Restrictions on Cursor Variables?
What is a library in Forms 4.0?
How will you monitor rollback segment status ?
What is term?
What is SAVE POINT ?
How you open and close a cursor variable ?
How will you monitor the space allocation ?
What is Restricted Mode of Instance Startup ?
How many types of database triggers can be
Is it possible to attach same library to more than one form?
What are the built_in used to trapping errors in forms 4?
What is an Oracle sequence
What is a private synonym
What is the use of OWNER option in EXP command ?
What is an extent
What are three panes that appear in the run
What is the diff. when Flex mode is mode
What are visual attributes?
What is redo log buffer
What are the data types allowed in a table
How to do the scheduled task/jobs in Unix platform
An you change the default value of the radio
List the system variables related in Block and Field?
How the space utilization takes place within
What is the “LOV of Validation” Property of an item
Can a repeating frame be created without
What is the difference between OLE Server
What is Distributed database ?
What is the function of Optimizer ?
What are the different approaches used by Optimizer
How are Indexes Update ?
What is a Sequence ?
Can Full Backup be performed when
How can a square be drawn in the layout
What is subqueries, explain?
How can you call a form on a form
Which function of summary item is the
How can a break order be created on
What are the two parts of a procedure ?
What buil-in routines are used to display
What is OCI. What are its uses?
What are the different types of segments
What do you mean by a page ?
Can a property clause itself be based on
When can hash cluster used
How will you estimate the space required
What is the use of ANALYZE command ?
What are the characteristics of data files
What is an LOV?
What is Database Buffers
What is new_form built-in?
What is a logical backup?
What is difference between a PROCEDURE
What is a transaction ?
What are ORACLE PRECOMPILERS?
Explain how procedures and functions are
Can you change the alert messages at run-time?
When will be a segment released ?
How do you control the constraints in forms ?
What is the use of COMPRESS option in EXP command ?
Which of the two views should objects
Explain about content canvas views?
How many number of columns a record group can have?
What is Tablespace Quota ?
Different types of table join.
What is the function of Redo Log ?
What is self-referential integrity constraint ?
What is a Segment ?
What are the different modes of mounting
What are the various sub events a mouse
What is Statement Auditing ?
Explain Connect by Prior ?
What are the different types of key triggers ?
Is it possible to set a filter condition in a cross
What are the built-ins used for finding Object ID function?
What are the uses of Database Trigger ?
What are the types of canvas-views?
What are various joins used while writing SUBQUERIES?
What is a schema
What is Table ?
What is On-line Redo Log?
What are the different display styles of list items?
How to drop the index
What is an object group?
When does an on-lock trigger fire ?
What built-in is used for changing the properties
How do you trap the error in forms 3.0 ?
What is Index Cluster ?
What are the responsibilities of a Database
What is the use of ROWS option in EXP command ?
What is a library?
What are the factors that affect OPTIMIZER
What is a physical page & logical page ?
How will you monitor the space allocation
What are the different windows events activated
What is a Control file ?
Can you change the color of the push button
How are the index updates
Can we delete tuples in a table based on date column?
What is the maximum number of CHECK constraints
What is a Synonym ?
What are the steps involved in Instance Recovery ?
What are the different modals of windows?
What is referential integrity constraint
What is a Check Box?
What are the two phases of block coordination?
How does the command POST differs from COMMIT ?
What are the advantages of having a Package ?
List the built-in routine for controlling window
How to implement the multiple control files
What is an index segment
What is dictionary cache
What is database link
What are the different methods of backing
What are the sql clauses supported in
What is hit ratio
What is a Layout Editor?
What are ORACLE PRECOMPILERS?
State the difference between implict and explict cursor’s
List some built-in routines used to manipulate
EXIT_FORM is a restricted package procedure ?
What is the disk migration?what isthe steps
What is the disk migration?what isthe steps
What are the different kind of export backups?
What is the diff. when confine mode is on and when it is off?
What are the important difference between property
What are the built -ins used for Modifying
Differentiate simple and complex, snapshots ?
What is row chaining
Can we use a restricted package procedure in ON-VALIDATE-FIELD Trigger ?
What is the use of image_zoom built-in?
What are the dictionary tables used to monitor
What is the use of FULL option in EXP command ?
What is Two-Phase Commit ?
What is a datafile
How can I message to passed to the user from reports?
What is mean by Program Global Area (PGA) ?
What is pop list?
What is SGA
What is a cursor for loop ?
What are the different types of SQL
What is difference between SUBSTR and INSTR?
What are the components of a PL/SQL Block ?
What is the built-in used to get and set lov
State the order in which these triggers are executed ?
What is use of rollback segments in Oracle database
What are the factors causing the reparsing
What are the ways to monitor the performance
What is the diff. bet. setting up of parameters
What is a property clause?
What are the different Levels of Auditing ?
What is DDL, DML?
What is a Data Dictionary ?
When can Hash Cluster used ?
Describe Referential Integrity ?
What are the advantages of operating a database
What is relation between the window and canvas views?
What is a segment
What are the different types of OUTER JOINS
What are the different types of Package Procedure ?
What is the advantage of the library?
What are built-ins used for Processing rows?
How many pages you can in a single form ?
Difference between procedure and function.
What is an Lov?
Which date function returns number value
What does a control file contains
What is the difference between a Function Key
What is the maximum no of chars the parameter can store?
What is a temporary segment
What is the purpose of the product order
What is default tablespace ?
What are the uses of rollback segment
What is a User_exit?
What is meant by recursive hints ?
What is trigger associated with the timer?
What is the role of PCTFREE parameter is storage clause
What are advantages of Stored Procedures
Implicit Cursor attributes
Display the number value in Words?
How to disable multiple triggers of a table at at a time?
List the built-in routines for the controlling
What is the difference between a POST-FIELD
What is the significance of having storage clause
How will you create multiple rollback segments
What are Most Common types of Complex master
What is a Text_io Package?
What is the use of place holder column?
What are the database administrators utilities avaliable ?
How is locking implemented?
What is the use of Redo Log Information ?
What is a Data File ?
What is Hash Cluster ?
What is Archived Redo Log ?
How can a text file be attached to a report while
Do you need a commit after DDL statements?
What is CYCLE/NO CYCLE in a Sequence ?
What Enter package procedure does ?
What are different types of modules available
What are the difference between lov & list item?
What is the content view and stacked view?
Difference between database triggers and form triggers?
What is a predefined exception available in forms 4.0?
How do I eliminate the duplicate rows
What is data block
What is a Trigger ?
What is the main difference between Reports 2.0 & Reports 2.5?
What is a Database instance ? Explain
What is strip sources generate options?
Can we decrease the Datafile size?
What is a deadlock ? Explain
What is WHEN-Database-record trigger?
What is the OPTIMAL parameter?
Explain about horizontal, Vertical tool
What is the difference between recovery
What is PL/SQL table ?
What is ROWID ?
Other way to replace query result null value with a text
How you will avoid your query from using indexes?
What is a stored procedure ?
System.effective_date system variable is
What SYNCHRONIZE procedure does ?
What is the use of INCTYPE option in EXP command ?
How will you monitor rollback segment status
What are the default extensions of the files
What is the difference between object embedding
What are the two panes that Appear in the
How will you enforce security using stored procedures?
What is a tablespace
What are the Limitations of a CHECK Constraint ?
What is a Data Segment ?
What is a Private Synonyms ?
What is Restricted Mode of Instance Startup ?
What are the Built-ins to display the user-named editor?
Do a view contain data
How do you find the numbert of rows in a Table ?
What is the difference between restricted and
What are the different types of Delete details we can
If a break order is set on a column would it affect
What are the types of visual attribute settings?
Display Odd/ Even number of records
How do you pass the parameters from one
Any three PL/SQL Exceptions
If the SQL * Plus hangs for a long time, what is the reason
minvalue.sql Select the Nth lowest value from a table
What is an OLE?
What does COMMIT do ?
Which of the above methods is the faster method?
What are the advantages of views
What is a profile ?
How do you reference a Parameter?
What is a data segment?
What is a timer?
What are the types of TRIGGERS ?
What are the differences between database
What is Referential Integrity ?
What is a Non- corelated subquery
Can a function take OUT parameters. If not why?
What are the components of a PL/SQL block ?
What is a display item?
When will ON-VALIDATE-FIELD trigger executed ?
List the factors that can affect the accuracy of the estimations?
How the space utilization takes place within rollback segments
What are the types of calculated columns available?
Name of the functions used to get/set canvas properties?
What are the default extensions of the files
How would you pass hints to the SQL processor?
What is an Oracle index
What are the uses of Rollback Segment ?
What is an Index Segment ?
What is Database Link ?
What is Partial Backup ?
What is a difference between pre-select and pre-query?
When does a Transaction end ?
How do you increase the performance of %LIKE operator
What do you mean by a pop-up window ?
What is a Static Record Group?
Explain about stacked canvas views?
What is the built-in function used for finding the alert?
How will you delete duplicating rows from a base table?
State the advatage and disadvantage of Cursor’s
How to drop the column in a table
What is the fastest way of accessing a row in a table ?
What is the difference between media recovery &
What is forms_DDL?
What is synchronize?
What is a lookup table in database?
What is Object Auditing & Privilege Auditing ?
What are modal windows?
What are the Built-ins used for sending Parameters to forms?
When do you use data parameter type?
What is an Alert ?
What is an Alert ?
What are different modes of parameters used
What are the advantages of VIEW
Explain the different types of joins
Can you use a commit statement within a database trigger?
Can you attach an lov to a field at design time?
List the editors availables in forms 4.0?
What package procedure used for invoke sql *plus from sql *forms ?
What is use of LOG (Ver 7) option in EXP command ?
What is the use of IGNORE option in IMP command ?
How do you call other Oracle Products from Oracle Forms?
What is the remove on exit property?
How can you find all the tables created by an user?
What is an Oracle view
What is a Public Synonyms ?
Explain the relationship among Database,
What is a Tablespace?
What is Mirrored on-line Redo Log ?
What are the triggers available in the reports?
What is a Temporary Segment ?
What is Raise_application_error ?
What is the significance of PAGE 0 in forms 3.0 ?
What is a combo box?
What is use of term?
What are the menu items that oracle forms 4.0 supports?
What are cursor attributes?
Give the structure of the function ?
How you will avoid duplicating records in a query?
What is difference between Procedures and Functions ?
What are disadvantages of having raw devices ?
What are the built-ins used for Getting cell values?
What is Data Block ?
What are the built-ins that are used for setting
What are Schema Objects
What are roles? How can we implement roles ?
Can you pass data parameters to forms?
What is a text list?
Is it possible to split the print reviewer into
What ERASE package procedure does ?
What is a OUTER JOIN?
What is a cursor?
Difference between SUBSTR and INSTR ?
What is a pseudo column. Give some examples?
List the windows event triggers available in Forms 4.0?
Give built-in routine related to a record groups?
How can you execute the user defined triggers in forms 3.0 ?
What is the use of FILE option in IMP command ?
What is the use of ANALYSE ( Ver 7) option in EXP command ?
What is the basic data structure that is
What are built-ins associated with timers?
What is the mechanism provided by ORACLE
How to define data block size
What are the types of synonyms
What are the advantages of Views ?
What are the different type of Segments ?
What is Rollback Segment ?
What is Full Backup ?
What is the use of hidden column?
What is Public Database Link ?
What is an Exception ? What are types of Exception ?
What is a Package Procedure ?
How can you enable automatic archiving ?
What is the use of transactional triggers?
What is the built-in used for showing lov at runtime?
If the entire disk is corrupted how will you
What is spooling
How do I display row number with records
Give the structure of the procedure ?
What is a Control file
What are the built-ins used for Creating
What is an Index ?
How can a cross product be created?
What is Execution Plan ?
What is a SNAPSHOT LOG ?
What is the Maximum allowed length of Record group Column?
What are the different types of Record Groups?
What is the difference between keystartup and pre-form ?
Differentiate between TRUNCATE and DELETE
What is a cursor for loop?
How to access the current value and next
What are various privileges that a user
What are the triggers associated with the image item?
What do you mean by a block in forms4.0?
What is the difference between NAME_IN and COPY ?
What is advantage of having disk shadowing/ Mirroring ?
Which parameter can be used to set read level
What are display items?
What is SYSTEM tablespace and when is it created
What is a data segment
What are the types of Database Links ?
What are Clusters ?
What are the Characteristics of Data Files ?
Can we use GO-BLOCK package in a pre-field trigger ?
What is a master detail relationship?
What is COST-based approach to optimization ?
What are the display styles of an alert?
Why query fails sometimes ?
How can we reduce the network traffic?
Is the After report trigger fired if the report
What are the cursor attributes used in PL/SQL ?
Explain the usage of WHERE CURRENT OF clause in cursors ?
What are two parts of package ?
How many LONG columns are allowed in a table
What are the different type of a record group?
How redo logs can be achieved
What is bind reference and how can it be created?
Display the records between two range?
What is coordination Event?
What does ROLLBACK do ?
What is a SQL * NET?
What are the different Parameter types?
What are parameters?
When will be a segment released
What are the design facilities available in forms 4.0?
What is a database link
What are various constraints used in SQL?
To view installed Oracle version information
Shall we create procedures to fetch more than one record
List system variables available in forms 4.0,
What is an Alert?
What package procedure is used for calling
What is meant by Redo Log file mirroring ?
What is the use of INDEXES option in IMP command ?
What are difference between post database
How can values be passed bet. precompiler
What are the various type of snapshots?
What is the use of redo log information
What is a public synonym
What are the components of Physical database structure
What is a View ?
What is Log Switch ?
What is an User Exits ?
What are the benefits of distributed options in databases?
What is the use of Control File ?
What are the display styles of list items?
What is redo log file mirroring
What is the frame & repeating frame?
How will you force database to use particular rollback segment
What will happen after commit statement ?
What are the datatypes a available in PL/SQL ?
How can you see the source code of the package
How will you a activate/deactivate integrity constraints ?
Can you attach an alert to a field?
What is cold backup? What are the elements of it?
What is the User-Named Editor?
What is a Shared SQL pool?
What is an user exit used for?
What is Read-Only Transaction ?
What dynamic data replication?
How do you reference a parameter indirectly?
What are the vbx controls?
What is the use of INDEXES option in EXP command ?
Give the Types of modules in a form?
Can a view be updated/inserted/deleted
What is a view ?
What is meant by SORTING and GROUPING
What is difference between Rename and Alias?
What built-in is used for showing the alert during run-time?
Explain types of Block in forms4.0?
Two popup pages can appear on the screen at a time ?
What is the use of RECORD LENGTH option in EXP command ?
What is the use of GRANT option in EXP command?
From which designation is it preferred to send
What is the difference between boiler
What are the options available to refresh snapshots ?
What are the components of physical database
What is a database instance and Explain
What is Private Database Link ?
What is SYSTEM tablespace and when is it Created?
What is an Extent ?
What does a Control file Contain ?
What is SGA? How it is different from Ver 6.0 and Ver 7.0?
Describe two phases of Two-phase commit ?
Can a View based on another View ?
What are the differences between Database Trigger
What are the return values of functions SQLCODE and SQLERRM ?
What is the built-in routine used to count the
What is a rollback segment entry
Give the sequence of execution of the various
What is a Query Record Group?
What is the use of PARFILE option in EXP command ?
How can we plan storage for very large tables ?
Where the Pre_defined_exceptions are stored ?
When do you use WHERE clause and when
What is difference between TRUNCATE & DELETE ?
What is the difference between stored function
What is meant by redo log buffer ?
Oracle interview questions -DBA
What are the data types allowed in a table ?
What is the functionality of SYSTEM table space
What is meant by Scrollable cursor
What is an anchoring object & what is its use?
The use of HAVING , WHERE and GROUPBY in one SQL
What are the modes of parameters that can be
What is a canvas-view?
What is a Rollback segment entry ?
What are the trigger associated with image items?
Why is a Where clause faster than a group filter
Deleting a page removes information about all
What triggers are associated with the radio group?
How image_items can be populate to field in forms 4.0?
What is the difference between system.current
What is the use of TABLES option in EXP command ?
List the factors that can affect the accuracy of the estima
Where is a procedure return in an external pl/sql library
What are the types of database links
What is Network Database link ?
What are the steps involved in Database Startup ?
What is the maximum size of a form ?
What are the Restrictions on Cursor Variables?
What is a library in Forms 4.0?
How will you monitor rollback segment status ?
What is term?
What is SAVE POINT ?
How you open and close a cursor variable ?
How will you monitor the space allocation ?
What is Restricted Mode of Instance Startup ?
How many types of database triggers can be
Is it possible to attach same library to more than one form?
What are the built_in used to trapping errors in forms 4?
What is an Oracle sequence
What is a private synonym
What is the use of OWNER option in EXP command ?
What is an extent
What are three panes that appear in the run
What is the diff. when Flex mode is mode
What are visual attributes?
What is redo log buffer
What are the data types allowed in a table
How to do the scheduled task/jobs in Unix platform
An you change the default value of the radio
List the system variables related in Block and Field?
How the space utilization takes place within
What is the “LOV of Validation” Property of an item
Can a repeating frame be created without
What is the difference between OLE Server
What is Distributed database ?
What is the function of Optimizer ?
What are the different approaches used by Optimizer
How are Indexes Update ?
What is a Sequence ?
Can Full Backup be performed when
How can a square be drawn in the layout
What is subqueries, explain?
How can you call a form on a form
Which function of summary item is the
How can a break order be created on
What are the two parts of a procedure ?
What buil-in routines are used to display
What is OCI. What are its uses?
What are the different types of segments
What do you mean by a page ?
Can a property clause itself be based on
When can hash cluster used
How will you estimate the space required
What is the use of ANALYZE command ?
What are the characteristics of data files
What is an LOV?
What is Database Buffers
What is new_form built-in?
What is a logical backup?
What is difference between a PROCEDURE
What is a transaction ?
What are ORACLE PRECOMPILERS?
Explain how procedures and functions are
Can you change the alert messages at run-time?
When will be a segment released ?
How do you control the constraints in forms ?
What is the use of COMPRESS option in EXP command ?
Which of the two views should objects
Explain about content canvas views?
How many number of columns a record group can have?
What is Tablespace Quota ?
Different types of table join.
What is the function of Redo Log ?
What is self-referential integrity constraint ?
What is a Segment ?
What are the different modes of mounting
What are the various sub events a mouse
What is Statement Auditing ?
Explain Connect by Prior ?
What are the different types of key triggers ?
Is it possible to set a filter condition in a cross
What are the built-ins used for finding Object ID function?
What are the uses of Database Trigger ?
What are the types of canvas-views?
What are various joins used while writing SUBQUERIES?
What is a schema
What is Table ?
What is On-line Redo Log?
What are the different display styles of list items?
How to drop the index
What is an object group?
When does an on-lock trigger fire ?
What built-in is used for changing the properties
How do you trap the error in forms 3.0 ?
What is Index Cluster ?
What are the responsibilities of a Database
What is the use of ROWS option in EXP command ?
What is a library?
What are the factors that affect OPTIMIZER
What is a physical page & logical page ?
How will you monitor the space allocation
What are the different windows events activated
What is a Control file ?
Can you change the color of the push button
How are the index updates
Can we delete tuples in a table based on date column?
What is the maximum number of CHECK constraints
What is a Synonym ?
What are the steps involved in Instance Recovery ?
What are the different modals of windows?
What is referential integrity constraint
What is a Check Box?
What are the two phases of block coordination?
How does the command POST differs from COMMIT ?
What are the advantages of having a Package ?
List the built-in routine for controlling window
How to implement the multiple control files
What is an index segment
What is dictionary cache
What is database link
What are the different methods of backing
What are the sql clauses supported in
What is hit ratio
What is a Layout Editor?
What are ORACLE PRECOMPILERS?
State the difference between implict and explict cursor’s
List some built-in routines used to manipulate
EXIT_FORM is a restricted package procedure ?
What is the disk migration?what isthe steps
What is the disk migration?what isthe steps
What are the different kind of export backups?
What is the diff. when confine mode is on and when it is off?
What are the important difference between property
What are the built -ins used for Modifying
Differentiate simple and complex, snapshots ?
What is row chaining
Can we use a restricted package procedure in ON-VALIDATE-FIELD Trigger ?
What is the use of image_zoom built-in?
What are the dictionary tables used to monitor
What is the use of FULL option in EXP command ?
What is Two-Phase Commit ?
What is a datafile
How can I message to passed to the user from reports?
What is mean by Program Global Area (PGA) ?
What is pop list?
What is SGA
What is a cursor for loop ?
What are the different types of SQL
What is difference between SUBSTR and INSTR?
What are the components of a PL/SQL Block ?
What is the built-in used to get and set lov
State the order in which these triggers are executed ?
What is use of rollback segments in Oracle database
What are the factors causing the reparsing
What are the ways to monitor the performance
What is the diff. bet. setting up of parameters
What is a property clause?
What are the different Levels of Auditing ?
What is DDL, DML?
What is a Data Dictionary ?
When can Hash Cluster used ?
Describe Referential Integrity ?
What are the advantages of operating a database
What is relation between the window and canvas views?
What is a segment
What are the different types of OUTER JOINS
What are the different types of Package Procedure ?
What is the advantage of the library?
What are built-ins used for Processing rows?
How many pages you can in a single form ?
Difference between procedure and function.
What is an Lov?
Which date function returns number value
What does a control file contains
What is the difference between a Function Key
What is the maximum no of chars the parameter can store?
What is a temporary segment
What is the purpose of the product order
What is default tablespace ?
What are the uses of rollback segment
What is a User_exit?
What is meant by recursive hints ?
What is trigger associated with the timer?
What is the role of PCTFREE parameter is storage clause
What are advantages of Stored Procedures
Implicit Cursor attributes
Display the number value in Words?
How to disable multiple triggers of a table at at a time?
List the built-in routines for the controlling
What is the difference between a POST-FIELD
What is the significance of having storage clause
How will you create multiple rollback segments
What are Most Common types of Complex master
What is a Text_io Package?
What is the use of place holder column?
What are the database administrators utilities avaliable ?
How is locking implemented?
What is the use of Redo Log Information ?
What is a Data File ?
What is Hash Cluster ?
What is Archived Redo Log ?
How can a text file be attached to a report while
Do you need a commit after DDL statements?
What is CYCLE/NO CYCLE in a Sequence ?
What Enter package procedure does ?
What are different types of modules available
What are the difference between lov & list item?
What is the content view and stacked view?
Difference between database triggers and form triggers?
What is a predefined exception available in forms 4.0?
How do I eliminate the duplicate rows
What is data block
What is a Trigger ?
What is the main difference between Reports 2.0 & Reports 2.5?
What is a Database instance ? Explain
What is strip sources generate options?
Can we decrease the Datafile size?
What is a deadlock ? Explain
What is WHEN-Database-record trigger?
What is the OPTIMAL parameter?
Explain about horizontal, Vertical tool
What is the difference between recovery
What is PL/SQL table ?
What is ROWID ?
Other way to replace query result null value with a text
How you will avoid your query from using indexes?
What is a stored procedure ?
System.effective_date system variable is
What SYNCHRONIZE procedure does ?
What is the use of INCTYPE option in EXP command ?
How will you monitor rollback segment status
What are the default extensions of the files
What is the difference between object embedding
What are the two panes that Appear in the
How will you enforce security using stored procedures?
What is a tablespace
What are the Limitations of a CHECK Constraint ?
What is a Data Segment ?
What is a Private Synonyms ?
What is Restricted Mode of Instance Startup ?
What are the Built-ins to display the user-named editor?
Do a view contain data
How do you find the numbert of rows in a Table ?
What is the difference between restricted and
What are the different types of Delete details we can
If a break order is set on a column would it affect
What are the types of visual attribute settings?
Display Odd/ Even number of records
How do you pass the parameters from one
Any three PL/SQL Exceptions
If the SQL * Plus hangs for a long time, what is the reason
minvalue.sql Select the Nth lowest value from a table
What is an OLE?
What does COMMIT do ?
Which of the above methods is the faster method?
What are the advantages of views
What is a profile ?
How do you reference a Parameter?
What is a data segment?
What is a timer?
What are the types of TRIGGERS ?
What are the differences between database
What is Referential Integrity ?
What is a Non- corelated subquery
Can a function take OUT parameters. If not why?
What are the components of a PL/SQL block ?
What is a display item?
When will ON-VALIDATE-FIELD trigger executed ?
List the factors that can affect the accuracy of the estimations?
How the space utilization takes place within rollback segments
What are the types of calculated columns available?
Name of the functions used to get/set canvas properties?
What are the default extensions of the files
How would you pass hints to the SQL processor?
What is an Oracle index
What are the uses of Rollback Segment ?
What is an Index Segment ?
What is Database Link ?
What is Partial Backup ?
What is a difference between pre-select and pre-query?
When does a Transaction end ?
How do you increase the performance of %LIKE operator
What do you mean by a pop-up window ?
What is a Static Record Group?
Explain about stacked canvas views?
What is the built-in function used for finding the alert?
How will you delete duplicating rows from a base table?
State the advatage and disadvantage of Cursor’s
How to drop the column in a table
What is the fastest way of accessing a row in a table ?
What is the difference between media recovery &
What is forms_DDL?
What is synchronize?
What is a lookup table in database?
What is Object Auditing & Privilege Auditing ?
What are modal windows?
What are the Built-ins used for sending Parameters to forms?
When do you use data parameter type?
What is an Alert ?
What is an Alert ?
What are different modes of parameters used
What are the advantages of VIEW
Explain the different types of joins
Can you use a commit statement within a database trigger?
Can you attach an lov to a field at design time?
List the editors availables in forms 4.0?
What package procedure used for invoke sql *plus from sql *forms ?
What is use of LOG (Ver 7) option in EXP command ?
What is the use of IGNORE option in IMP command ?
How do you call other Oracle Products from Oracle Forms?
What is the remove on exit property?
How can you find all the tables created by an user?
What is an Oracle view
What is a Public Synonyms ?
Explain the relationship among Database,
What is a Tablespace?
What is Mirrored on-line Redo Log ?
What are the triggers available in the reports?
What is a Temporary Segment ?
What is Raise_application_error ?
What is the significance of PAGE 0 in forms 3.0 ?
What is a combo box?
What is use of term?
What are the menu items that oracle forms 4.0 supports?
What are cursor attributes?
Give the structure of the function ?
How you will avoid duplicating records in a query?
What is difference between Procedures and Functions ?
What are disadvantages of having raw devices ?
What are the built-ins used for Getting cell values?
What is Data Block ?
What are the built-ins that are used for setting
What are Schema Objects
What are roles? How can we implement roles ?
Can you pass data parameters to forms?
What is a text list?
Is it possible to split the print reviewer into
What ERASE package procedure does ?
What is a OUTER JOIN?
What is a cursor?
Difference between SUBSTR and INSTR ?
What is a pseudo column. Give some examples?
List the windows event triggers available in Forms 4.0?
Give built-in routine related to a record groups?
How can you execute the user defined triggers in forms 3.0 ?
What is the use of FILE option in IMP command ?
What is the use of ANALYSE ( Ver 7) option in EXP command ?
What is the basic data structure that is
What are built-ins associated with timers?
What is the mechanism provided by ORACLE
How to define data block size
What are the types of synonyms
What are the advantages of Views ?
What are the different type of Segments ?
What is Rollback Segment ?
What is Full Backup ?
What is the use of hidden column?
What is Public Database Link ?
What is an Exception ? What are types of Exception ?
What is a Package Procedure ?
How can you enable automatic archiving ?
What is the use of transactional triggers?
What is the built-in used for showing lov at runtime?
If the entire disk is corrupted how will you
What is spooling
How do I display row number with records
Give the structure of the procedure ?
What is a Control file
What are the built-ins used for Creating
What is an Index ?
How can a cross product be created?
What is Execution Plan ?
What is a SNAPSHOT LOG ?
What is the Maximum allowed length of Record group Column?
What are the different types of Record Groups?
What is the difference between keystartup and pre-form ?
Differentiate between TRUNCATE and DELETE
What is a cursor for loop?
How to access the current value and next
What are various privileges that a user
What are the triggers associated with the image item?
What do you mean by a block in forms4.0?
What is the difference between NAME_IN and COPY ?
What is advantage of having disk shadowing/ Mirroring ?
Which parameter can be used to set read level
What are display items?
What is SYSTEM tablespace and when is it created
What is a data segment
What are the types of Database Links ?
What are Clusters ?
What are the Characteristics of Data Files ?
Can we use GO-BLOCK package in a pre-field trigger ?
What is a master detail relationship?
What is COST-based approach to optimization ?
What are the display styles of an alert?
Why query fails sometimes ?
How can we reduce the network traffic?
Is the After report trigger fired if the report
What are the cursor attributes used in PL/SQL ?
Explain the usage of WHERE CURRENT OF clause in cursors ?
What are two parts of package ?
How many LONG columns are allowed in a table
What are the different type of a record group?
How redo logs can be achieved
What is bind reference and how can it be created?
Display the records between two range?
What is coordination Event?
What does ROLLBACK do ?
What is a SQL * NET?
What are the different Parameter types?
What are parameters?
When will be a segment released
What are the design facilities available in forms 4.0?
What is a database link
What are various constraints used in SQL?
To view installed Oracle version information
Shall we create procedures to fetch more than one record
List system variables available in forms 4.0,
What is an Alert?
What package procedure is used for calling
What is meant by Redo Log file mirroring ?
What is the use of INDEXES option in IMP command ?
What are difference between post database
How can values be passed bet. precompiler
What are the various type of snapshots?
What is the use of redo log information
What is a public synonym
What are the components of Physical database structure
What is a View ?
What is Log Switch ?
What is an User Exits ?
What are the benefits of distributed options in databases?
What is the use of Control File ?
What are the display styles of list items?
What is redo log file mirroring
What is the frame & repeating frame?
How will you force database to use particular rollback segment
What will happen after commit statement ?
What are the datatypes a available in PL/SQL ?
How can you see the source code of the package
How will you a activate/deactivate integrity constraints ?
Can you attach an alert to a field?
What is cold backup? What are the elements of it?
What is the User-Named Editor?
What is a Shared SQL pool?
What is an user exit used for?
What is Read-Only Transaction ?
What dynamic data replication?
How do you reference a parameter indirectly?
What are the vbx controls?
What is the use of INDEXES option in EXP command ?
Give the Types of modules in a form?
Can a view be updated/inserted/deleted
What is a view ?
What is meant by SORTING and GROUPING
What is difference between Rename and Alias?
What built-in is used for showing the alert during run-time?
Explain types of Block in forms4.0?
Two popup pages can appear on the screen at a time ?
What is the use of RECORD LENGTH option in EXP command ?
What is the use of GRANT option in EXP command?
From which designation is it preferred to send
What is the difference between boiler
What are the options available to refresh snapshots ?
What are the components of physical database
What is a database instance and Explain
What is Private Database Link ?
What is SYSTEM tablespace and when is it Created?
What is an Extent ?
What does a Control file Contain ?
What is SGA? How it is different from Ver 6.0 and Ver 7.0?
Describe two phases of Two-phase commit ?
Can a View based on another View ?
What are the differences between Database Trigger
What are the return values of functions SQLCODE and SQLERRM ?
What is the built-in routine used to count the
What is a rollback segment entry
Give the sequence of execution of the various
What is a Query Record Group?
What is the use of PARFILE option in EXP command ?
How can we plan storage for very large tables ?
Where the Pre_defined_exceptions are stored ?
When do you use WHERE clause and when
What is difference between TRUNCATE & DELETE ?
What is the difference between stored function
What is meant by redo log buffer ?

Restore datafile without backup and then recover: CREATE DATAFILE of ALTER DATABASE command March 10, 2010

Posted by sendtoshailesh in Uncategorized.
Tags:
1 comment so far

Restore datafile without backup and then recover it

While in cafetria some admins were discussing that how to recover the lost datafile which was created after last backup. It might be created as part of any tablespace. I overheard of using CREATE DATAFILE clause of ALTER DATABASE command. fortunately I never cam across this situation. I thought let me test this whether, It can successfuly done or not. Here I am presenting the recover of a datafile whose backup is not there provided database is running in ARCHIVELOG mode.

Before doing any test lets confirm db should be running in archivelog mode.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     7
Next log sequence to archive   9
Current log sequence           9

Lets create a tablespace:

SQL> create tablespace myts datafile ‘d:\myts01.dbf’ size 10m;

Tablespace created.

Lets checkout file# of newly created datafile of tablespace:

SQL> select file#,name from v$datafile;

FILE#
———-
NAME
——————————————————————————–
1
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF

2
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF

3
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF

FILE#
———-
NAME
——————————————————————————–
4
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF

5
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF

6
D:\MYTS01.DBF

6 rows selected.

Now populate tablespace / datafile with some sample data:

SQL> create table tt tablespace myts as select * from dual;

Table created.

SQL> select * from tt;

D
–
X
Now rename the file. On UNIX platform this can be done but on windows it does not permit to do so. For this let take the datafile offline and rename it.
SQL> alter database datafile 6 offline;

Database altered.

Try to check whether database accept it reversal command after we renamed the file…..ahhh

SQL> alter database datafile 6 online;
alter database datafile 6 online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 6 – see DBWR trace file
ORA-01110: data file 6: ‘D:\MYTS01.DBF’

DB complains that there is no such file. Now create a file physically by using following command. why? because we do not have backup of this file. This is a unique situation where following command is playing an important role.

SQL> alter database create datafile 6 as ‘d:\myts01_new.dbf’;

Database altered.
Test without doing recovery, what happend when we take datafile online. Obviously DB will say that datafile requires recover.

SQL> alter database datafile 6 online;
alter database datafile 6 online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: ‘D:\MYTS01_NEW.DBF’

Here it recongnize that file is there but it not in sync with latest data. We know that we just created the file with only have header and no data. Lets recover the datafile. While recovering the datafile DB will read all the changes done in datafile from redo and archive log.
SQL> alter database recover datafile 6;

Database altered.


Recovery done. Take the datafile online and test whether we got our sample data back or not.

SQL> alter database datafile 6 online;

Database altered.

SQL> select * from tt;

D
–
X

Enjoy!!!!!!!!

tuning an Oracle insert SQL March 4, 2010

Posted by sendtoshailesh in Uncategorized.
add a comment

My one of ETL team member reported that its insert statement is taking very long time to execute. Normally It execute in 5 min which is part of ETL job. But now it is taking 2 hrs. Now there is an opportunity of DML tuning and insert have its own way of execution. While searching I found a good note. Here I am blogging it for my ready reference.

There are many factors that effect Oracle insert SQL performance and many things that you can do to tune an insert statement. There are many types or Oracle inserts, each with distinct performance characteristics

The fastest Oracle table insert rate I’ve ever seen was 400,000 rows per second, about 24 millions rows per minute, using super-fast RAM disk (SSD), but Greg Rahn of Oracle notes SQL insert rates of upwards of 6 million rows per second using the Exadata firmware:

“One of the faster bulk (parallel nologging direct path from external table using direct path compression) load rates I’ve seen is just over 7.7 billion rows in under 20 minutes which equates to around 385,000,000 per minute or about 6,416,666 per second.

All the CPUs are running at around 99% user CPU during that load. That was loading to spinning rust (Exadata Storage). It would be even faster had compression not been used. That was on a HP Oracle DB Machine (64 Intel Harpertown CPU cores). “

Here are some general guidelines for tuning inserts statements.

a – Manage segment header contention for parallel inserts – Make sure to define multiple freelist (or freelist groups) to remove contention for the table header. Multiple freelists add additional segment header blocks, removing the bottleneck. You can also use Automatic Segment Space Management (bitmap freelists) to support parallel DML.

b – Parallelize the load – You can invoke parallel DML (i.e. using the PARALLEL and APPEND hint) to have multiple inserts into the same table. For this INSERT optimization, make sure to define multiple freelists and use the SQL “APPEND” option. If you submit parallel jobs to insert against the table at the same time, using the APPEND hint may cause serialization, removing the benefit of parallel jobstreams.

c – APPEND into tables – By using the APPEND hint, you ensure that Oracle always grabs “fresh” data blocks by raising the high-water-mark for the table. If you are doing parallel insert DML, the Append mode is the default and you don’t need to specify an APPEND hint. If you’re going w/ APPEND, consider putting the table into NOLOGGING mode, which will allow Oracle to avoid almost all redo logging.

insert /*+ append */ into customer values (‘hello’,’;there’);

d – Use a large blocksize – By defining large (i.e. 32k) blocksizes for the target table, you reduce I/O because more rows fit onto a block before a “block full” condition (as set by PCTFREE) unlinks the block from the freelist.

e – Disable/drop indexes – It’s far faster to rebuild indexes after the data load, all at-once. Also indexes will rebuild cleaner, and with less I/O if they reside in a tablespace with a large block size.

f – RAM disk – You can use high-speed solid-state disk (RAM-SAN) to make Oracle inserts run up to 300x faster than platter disk.

Blocksize and insert performance

Here is my small single-CPU, single-user benchmark showing the performance of loads into a larger blocksize:

alter system set db_2k_cache_size=64m scope=spfile;
alter system set db_16k_cache_size=64m scope=spfile;
startup force
create tablespace twok blocksize 2k; <– using ASM defaults to 100m
create tablespace sixteenk blocksize 16k;
create table load2k tablespace twok as select * from dba_objects; < creates 8k rows
drop table load2k; <- first create was to preload buffers

set timing on;
create table load2k tablespace twok as select * from dba_objects;
create table load16k tablespace sixteenk as select * from dba_objects;

For a larger sample, I re-issued the create processes with:

select * from dba_source; — (80k rows)

Even with this super-tiny sample on Linux using Oracle10g (with ASM) the results were impressive, with a significant performance improvement using large blocksizes.

 2k 16k
 blksze blksze
8k table size 4.33 secs 4.16 secs 
80k table size 8.74 secs 8.31 secs

Search Table structure from Data dictionaries: Oracle vs SQL Server vs DB2 vs Informix vs MYSQL vs SYBASE March 4, 2010

Posted by sendtoshailesh in Uncategorized.
Tags:
add a comment

This is next to one of my previous blog about search tables…………..

This blogs explains how to dig the table structure, in case, if you do not want to use DESCRIBE SQL*PLUS command. Moreover, This is more useful when table structure need to fetch up programmatically. This blogs is comparative study across different database product.

you need to replace red marked search keyword from the codes with identified keyword that needs to be searched.

Oracle:

select owner schema,table_name,Column Name,DATA_TYPE,DATA_LENGTH

from all_tab_columns

where column_name like ‘%’||upper(‘search_keyword‘)||’%’

or table_name like ‘%’||upper(‘search_keyword’)||’%’;

DB2:

select TABSCHEMA schema,TABNAME Table_name,COLNAME Column_Name,TYPENAME Data_type,LENGTH DATA_LENGTH

from SYSCAT.COLUMNS

where COLNAME like ‘%’||upper(‘search_keyword’)||’%’

or TABNAME like ‘%’||upper(‘search_keyword’)||’%’;

MS-SQL Server:

SELECT table_name,column_name,schema_name,type_name,max_length, precision,scale from (

SELECT c.name AS column_name

,c.column_id

,SCHEMA_NAME(t.schema_id) AS schema_name

,t.name AS type_name

,t.is_user_defined

,t.is_assembly_type

,c.max_length

,c.precision

,c.scale

,o.name table_name

FROM sys.columns AS c

JOIN sys.types AS t ON c.user_type_id=t.user_type_id

JOIN sys.objects AS o on c.object_id = o.object_id

WHERE o.type_desc LIKE ‘%TABLE%’) all_tab_columns

WHERE all_tab_columns.column_name like ‘%’+upper(‘Search_Keyword‘)+’%’;

Informix:

select b.owner schema_name,b.tabname table_name,a.colname Column_Name,

decode(coltype,0,’CHAR’,1,’SMALLINT’,2,’INTEGER’,3,’FLOAT’,4,’SMALLFLOAT’,5,’DECIMAL’,6,’SERIAL’,7,’DATE’,

8,’MONEY’,9,’NULL’,10,’DATETIME’,11,’BYTE’,12,’TEXT’,13,’VARCHAR’,14,’INTERVAL’,15,’NCHAR’,16,’NVARCHAR’,

17,’INT8′,18,’SERIAL8′,19,’SET’,20,’MULTISET’,21,’LIST’,22,’Unnamed ROW’,40,’Variable-length opaque type’,

4118,’Named ROW’,to_char(coltype)) DATA_TYPE,

collength DATA_LENGTH

from syscolumns a,systables b

where a.tabid=b.tabid and b.tabtype=’T’

and upper(colname) like ‘%’||upper(‘Search_Keyword‘)||’%’;

MYSQL:

select table_schema schema_name,table_name,Column_Name,DATA_TYPE

from INFORMATION_SCHEMA.COLUMNS

where column_name like concat(concat(‘%’,upper(‘search_keyword’)),’%’)

or table_name like concat(concat(‘%’,upper(‘search_keyword’)),’%’);

SYBASE:

select b.creator schema,b.table_name,a.Column_Name,c.domain_name DATA_TYPE,width DATA_LENGTH

from syscolumn a,systable b,SYSDOMAIN c

where b.table_type not like ‘%VIEW%’

and a.table_id=b.table_id

and a.domain_id=c.domain_id

and column_name like ‘%’||upper(‘Search_Keyword‘)||’%’;

Working on Oracle, Sybase, Informix, SQL Server MS-SQL Server parallely March 3, 2010

Posted by sendtoshailesh in Uncategorized.
add a comment

I am working simultaneously on several database product.

I will try to blog most of learning……….