Oracle Frequently Asked Questions

I am using LUA to write an application that should run some Oracle functions that need parameters, and also execute inserts or update DML commands. Is there any way of running these functions without using a select (function) command ? I was thinkin......   Read Article.

I am attempting to partition a Table T1 with existing data. Table T1 is as follows: COLUMN DATATYPE ----------------- COLUMN1 NUMBER PK COLUMN2 NUMBER COLUMN3 NUMBER I am using this approach: 1- ALTER TABLE T1 RENAME TO T1_TEMP 2- CREAT......   Read Article.

Dear Tom; I need to query a SQLServer table from within an Oracle session, for example SQLPlus. Is there an easy way to do this, something like: CODE select * from t@sql_server_link /CODE May you redirect me to the appropriate documentatio......   Read Article.

Sir, I have joined this team having severe problems with SQL and found one having excessive CpU from AWR reports. There are many changes that we have suggested to this insert, could you please give your valuable inputs also please. On a 10G in......   Read Article.

Hello Tom Thanks for your time.. I have a question: I understand that by default Oracle uses read committed by statement level, normally other session can't see the modification execept the session that makes the modification itself. Read co......   Read Article.

Hi Tom, I'm facing a problem with updateable views and instead of triggers. I've to deal with a quot;bigquot; table, more than 300 columns and hundred thousands of records. I would like to replace this table with a view and splitting the quot;bigquot; ta......   Read Article.

Hello Tom Thanks for your time.. I am using oracle 9i(9.2.0.1) databases. And some day I find there is a column (status) in the v$transaction and I understand if there is a transaction open and not committed, the status always shows active. If ......   Read Article.

While trawling David Aldridge's site http://oraclesponge.wordpress.com/2008/03/07/timestamps-old-fogeys-and-data-warehouses/ I wondered, but could not find in the documentation: Is there any way of changing the default precision of 6 for TIMESTAMP......   Read Article.

I'm having a problem with External tables. Most of the time I can read the data in the table without any issue. But when I issue the query below I get an error. bContents of test.csv file/b codeTool type,ID Common,1 Type1,2 Type2,3 ......   Read Article.

DEAR TOM i learn a lot from this platform as a new dba i have a situation here. i did't write code/script here because i havn't any idea that how i ask from you? i have tried to convey my.. i have a database of almost 60 tables. application de......   Read Article.

Hi Tom, I am new to this blog. I have one doubt regarding fragmentation. select TABLESPACE_NAME, sum(BYTES), max(BYTES) from dba_free_space group by TABLESPACE_NAME; If we give this query we will get sum(BYTES)-s......   Read Article.

what do you think of the technique used in http://jonathanlewis.wordpress.com/2008/04/27/manual-optimisation/ ? I would like to hear your opinion. It's very interesting but I think we should not use it, am I right ?...   Read Article.

Hi Tom, I have an Oracle Database 8.1.7.4 Standard Edition installed on a Windows 2000 Server. My question is for curiosity more than need. Let's assume that we have enough physical memory and that we can guarantee that the server will never......   Read Article.

Tom, I have two questions, please, under the category of automated shutdown/startup of the database (i.e. using 'dbstart' and 'dbshut' in association with /etc/rc.d/init.d/dbora server init file). (1) How do you automate changing database init......   Read Article.

Pl suggest me if i can insert the data directly from one database which is 9i to another database which is for Oracle Apps running on 10g. The 2 database are running on different machine , 9i on Win2k and 10g on Linex rel 5....   Read Article.

Tom, When a before update for each row trigger fires and a certain column was not set in the update statement, the :new value for the quot;certain columnquot; appears to be set to the :old value by the time the trigger fires. Is there a way to determine i......   Read Article.

Hi, We have a database table from which the rows are deleted and we have no idea how is it happening. Users claim that they are not deleting it through application. I put an ondelete trigger on that table and stored the rows that were deleted in ......   Read Article.

Hi Tom, A transacion entry is created in the ITL in the data/index block header when a transaction modifies a block. How long does this entry stays in the ITL after the transaction is committed and do you get a snapshoot too old if a long runnig que......   Read Article.

Hi Tom, How do we find out if a particular query or session is resource hogging due to which other sessions in the DB are affected.How to cofirm this, if it happens. What to check ? How to find out for a particular active session how much ......   Read Article.

While writing SQL in oracle 10g 1) Should we ensure the joins are the first in where clause i.e where a.table1=b.table2 and a.table1=:b 2) Is there a thumb rule that says in all cases bind varibles queries will give faster results than......   Read Article.