Oracle Frequently Asked Questions

Tom, I suspect your readers will find this interesting. I am trying to logically 'Delete Cascade' across four tables, and cannot implement R.I. because we cannot guarantee the quot;logical parentquot; table would always be inserted into before its children.......   Read Article.

Hi Tom, I have a tablespace on Oracle 9.2.0 that I wish to convert from DMTS to LMTS for obvious performace reasons. However I'm just confused how autoextend uses the NEXT and MAXSIZE parameters. I need to create a 8Gb tablespace but can only h......   Read Article.

Hi Tom, I definetly remember seeing something similar to this on this site, but could not find. If I am right, could you direct me to the link please? Thank you, Sinan here is my question. code -- Create table create table ASKTOM_TAS......   Read Article.

Hi Tom, Here is a sample sql from a package used for searching clients. The query is very fast when we remove the quot;ORquot; condition quot;ln_cl_id is nullquot; from the WHERE condition. There are many columns with similiar OR condition, which I have removed......   Read Article.

We have a requirement of pagination where the page size is 1000. we need to provide an option of first, next, prev, last to the user. As suggested in this forum we can use rownum to achieve first, next and prev as follows pagesize = 1000 foprm......   Read Article.

Tom, With the support of multiple block size in a database, how does a single UNDO tablespace store the before-images of data blocks from tablespaces with different blocksizes? Is there any performance impact on this? Thanks, Jay...   Read Article.

i have a table with timesheet data create table timesheet ( tsid number(15,5), ssn varchar2(9), hours number(5,2), weekstart date ) NOTE: tsid is a sequence to uniquely identify a particular timesheet record. ......   Read Article.

Hi, 1) Could you please explain me why does not commit or savepoint or rollback work when defined inside a trigger? Does it work inside an INSTEAD of trigger. Please explain. 2) Please explain me the functionality of raise, just quot;RAISE;quot; ins......   Read Article.

Hi I need to displays the details regarding the subjects for which no title exists in the titles table. SUBJECT table column subject_id subject_name TITLE table column title_id title_nm subject_nm publisher_nm I have two queries 1......   Read Article.

I often build a view to prototype/develop ETL transformation rules, then quot;wrapquot; the view in a PL/SQL package. Occasionally, I need to search the ETL code. However, xxx_VIEW.TEXT is LONG, while xxx_SOURCE is VARCHAR2(4000). I know I can run t......   Read Article.

Hi Tom, Whats the difference between the below two 1. DECLARE CURSOR c1 IS SELECT ENAME,DNAME FROM emp A,dept B WHERE A.deptno=B.deptno; BEGIN FOR i in c1 LOOP ---------- ---------- --------......   Read Article.

Hi Tom, I installed Oracle9i server in E drive of my computer and I formatted the C drive thinking Oracle is in E drive. But now I am unable to connect to oracle. Guide me what are the steps that i have to follow to start my oracle with the previo......   Read Article.

Hello Tom, I am looking into using Oracle Resource Manager to limit resources by AppDev folks on the production database so as allow them access yet not hurt production performance. 1) Is Resource Manager a good tool to help achieve my goal, ab......   Read Article.

hi tom, i have 2 questions :- 1) what different between the following fields :- - Oracle Database Administration - Oracle Erp Manufacturing - Oracle Erp Financial 2) are shifting to Administration make me a good developer ???......   Read Article.

Hi Tom, Good morning. While resolving many-many relationships, we create an intersection table and we create a FK for the other two table Primary keys. But if the PK tables have date fields in them i.e. hire date,create date etc, do we need to......   Read Article.

Hi, Here is the problem update (select t1.* from T1,T2 where t1.col=T2.col) t set col4='toto' ora-1779 even if i try /*+bypass_ujvc*/ in my update update /*+bypass_ujvc*/ (select t1.* from T1,T2 where t1.col=T2.col) t set col4='toto'......   Read Article.

Hi Tom, We have data coming into our application from different entities/agencies and being loaded into several different tables. Each transmission contains data that belongs to a unique entity. We can also receive a new transmission for each enti......   Read Article.

Tom,How do you create insert statments dynamically if I give a table name? in TOAD tool, they have this option for each table [Create insert statements] and I was wondering what kind of logic they might have used to create them. I was trying it my......   Read Article.

Tom, i am currently designing a new database 10g Rel2 and would appreciate your advice on the following Which method would you advise for the following business process OPTION 1 ######## DECLARE CURSOR c1 IS SELECT .................   Read Article.

I have a table which has been partitioned by the months.Also in the same table i have an indexes in 4 columns. I used partition as we will be loading the data everymonth. We will be having around 9 million rows everymonth. While inserting the rows ......   Read Article.