Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 7 hours 16 min ago

Is there another option for Next Value For

Wed, 2016-12-07 17:46
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for 64-bit Windows: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production How would I go abo...
Categories: DBA Blogs

Flashback Archive Internal History table is not compressed -- Kindly suggest

Tue, 2016-12-06 05:06
Hi Tom, Once flashback archive is enabled for a table, a corresponding history table will be created by oracle internally. It is automatically partitioned and compressed as well. But when I have enabled FBA for a table, the history table is partit...
Categories: DBA Blogs

Column with default value

Tue, 2016-12-06 05:06
I have a table in which one of the column is as below create_date TIMESTAMP(6) default SYSDATE, When the record is inserted from the GUI(URL) , it doesn't insert any value for create_Date .. But when inserted from other than URL i...
Categories: DBA Blogs

Redo Log 4K Blocksize

Tue, 2016-12-06 05:06
Good Evening, In 11g, I've read about the possibility of setting redo logs to have a blocksize of 4k. Supposedly, the blocksize is automatically set based on the block sector of the disk. Supposedly, high capacity disks have block sectors of 4k....
Categories: DBA Blogs

SQL profile is usable?

Tue, 2016-12-06 05:06
Hi,guy! I have some question about the SQL PROFILE, when use SQL PROFILE to bind the SQL,it performanced good,but as time goes by,the data in the table will grow rapidly and the SQL which use SQL PROFILE performanced bad. So what's the meaning of th...
Categories: DBA Blogs

db2 Query in Unix: load from /dev/null of del replace into Schema.Tablename nonrecoverable;

Tue, 2016-12-06 05:06
Hi Tom, In Unix db2 I am using the below query to clear the table. db2 Query in Unix: load from /dev/null of del replace into Schema.Tablename nonrecoverable; What would be the best approach to do the same thing in Oracle. I don't thin...
Categories: DBA Blogs

How mandatory is to use DBMS_AUDIT_MGMT

Tue, 2016-12-06 05:06
Hello everyone, One question please, how mandatory is to use the package DBMS_AUDIT_MGMT for Oracle. a)There is a NOT ADVISABLE suggestion from Oracle to work in aud$ table directly, to force to use ALWAYS the package. b) is simple we ADVISE ...
Categories: DBA Blogs

Latch Free

Tue, 2016-12-06 05:06
Hi Connor, I have no intention of complaining. But all over the web I find lot of discussion about latch , latch spin, and latch sleep. And the description goes like below. 1] Try to acquire a latch 2] Failed ! Try again after sometime 3] Ret...
Categories: DBA Blogs

About Joins

Mon, 2016-12-05 10:46
Hello I am facing problem with alias name in the joins. Pleae help me out. Below is the example select y.employee_id from ((select employee_id,department_id,manager_id from employees)a join (select department_id,manager_id from departme...
Categories: DBA Blogs

Dynamic Cursors

Mon, 2016-12-05 10:46
i have to write a procedure where the procedure takes a parameter say employee_status(Retired/Not-Retired) based on the parameter i've to use a cursor select employees from mytable who are retired or not retired and loop over all the employees (Ret...
Categories: DBA Blogs

Import Data from Excel to DB Table

Mon, 2016-12-05 10:46
Hi I need all-time executable SP that can import data from an Excel and do some DML operation on live tables. First of all, is that possible? IF so 1. what are the many ways that we can do that? 2. How do we do that using a Stored Procedu...
Categories: DBA Blogs

Oracle 11g Database , and Weblogic Server 11g Forms , Reports and facing dead slow

Sun, 2016-12-04 16:26
i am using Oracle 11g Database , and Weblogic Server 11g Forms , Reports and facing dead slow issue at developers level as well as user level. i test 6i forms on same database same tables its running ok please any one help me what could be the ...
Categories: DBA Blogs

string patterns

Sun, 2016-12-04 16:26
I need to write a query which will look for the data in a particular column for the strings of patterns nnnnnn or Annnnn where n stands for number and A stands for capital letter. Any strings which are in a different pattern other than these two shou...
Categories: DBA Blogs

LOG(2,4/8/16 Issue

Sun, 2016-12-04 16:26
Hi Chris&Connor..Good Morning! You both are doing good job. My question is: The minimum height of a Red-Black tree H = FLOOR(LOG(2,N)), where N is nodes. Ideally, when N=4 then H=2 and when N=8 then H=3 and so on as per the formula. But, the LOG(...
Categories: DBA Blogs

Tom - what is going to happen to this website?

Sun, 2016-12-04 16:26
I wish you much happiness in your upcoming retirement. What will happen to this website? Could one buy an archived copy? It has been such an amazing resource through my career. Even if you are going to hang it up, I'm sure it is still very usefu...
Categories: DBA Blogs

Data Migration

Fri, 2016-12-02 09:26
Hi! I have few questions regarding data migration/replication from Oracle: 1. We have a column (of datatype NUMBER) in Oracle table which stores Transaction AMOUNT in the format: 10,52 (instead of 10.52). When we migrate data (or replicate data...
Categories: DBA Blogs

Oracle dynamic database link from variables

Fri, 2016-12-02 09:26
hi tom i am facing this query in dynamic sql using dblinks plz simplifie this query and explain me sqlquery := 'select sv.display_value status_id,s.parent_account_no,s.emf_config_id,cm.mkt_code' || ' from service@' ...
Categories: DBA Blogs

Bug in recursive subquery factoring ?

Fri, 2016-12-02 09:26
Hi AskTom team. I was playing with recursive subqueries and tried to do some string manipulations, but I noticed something strange: <code>SQL> WITH r(str, outtxt, outtxt2) AS ( 2 SELECT 'aabbba', '', '' FROM dual 3 UNION ALL 4 ...
Categories: DBA Blogs

Pivot Rows into Columns

Fri, 2016-12-02 09:26
Hi Tom, I have a Query: SELECT A.SALES_ORDER_COMPANY as A_1, A.SALES_ORDER_NUMBER as A_2, A.SOURCE_SALES_ORDER_TYPE as A_3, A.SALES_LINE_NUMBER as A_4, A.SOURCE_SALESPERSON1_CODE as B, A.COMMISSION_AMT as C FROM SLS_PHI_BROKER_COMM_STG A W...
Categories: DBA Blogs

'out blob' vs 'out nocopy blob'

Fri, 2016-12-02 09:26
Say I have a table: create table emp( empid number(5), name varchar2(30), photo blob ); create or replace procedure get_photo(p_empid in number, p_name out varchar2, p_photo OUT BLOB) as begin select name, photo into p_name, p_ph...
Categories: DBA Blogs

Pages