Tom Kyte

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

Want to use single select statement instead of various union all's for fetching previous quarter data

Sun, 2018-09-02 21:26
I have 5 select statements using union all(showing a few in here) want to use a single statement to fetch the revenue from a table . So based on the condition that AND period_id>=1009 AND Opp_Created_Date_Period_Id<=1009(for every previous quart...
Categories: DBA Blogs

FOR UPDATE SKIP LOCKED with ROWNUM

Sun, 2018-09-02 21:26
Hy guys, I searched all the forum but I didn't find any clue about it. I have a stage in table that multiple threads consume it. To avoid deadlock, i'm using something like this: <code> SELECT ID_MESSAGE, FROM TB_STAGE_IN S WHERE S.CD_STA...
Categories: DBA Blogs

Letter ù is OK for the table's name but not for a column's name

Sat, 2018-09-01 08:46
Hello The Oracle Masters, I have the following problem : impossible to use the lettre u for a column's name but it is OK for a table's name. Very important : I use Windows 7, in French, and the Oracle 12.1 virtual box with Linux. My principal c...
Categories: DBA Blogs

TABLE function and where clause parameters retrieving

Fri, 2018-08-31 14:26
Hi there, My goal is to return fieldX or fieldY depending on the where clause (which I don't have any control on since it's generated by a third party software). The TABLE() function is very close to what I want to achieve : select * from T...
Categories: DBA Blogs

Regarding performance issues

Fri, 2018-08-31 14:26
Hi Tom, I am stuck in a Scenario where I have to extract 85 million records in a table and that table is not having any primary key. After extracting all the data(full load) into target table my concern is I want to make this data loading increme...
Categories: DBA Blogs

Getting calling where clause or select statement in pl/sql function

Fri, 2018-08-31 14:26
Hi there ! Is there a way of getting the select statement that called a pl/sql function or its where clause ? For instance, if the call is : SELECT customFunction(parameter) AS FuncResult, field1 FROM table WHERE field2 = 'test' Is it pos...
Categories: DBA Blogs

Issue with Trigger creation

Fri, 2018-08-31 14:26
I am having issues with Trigger. Trigger creation is successful but Once user try to test it, It is failing. Before trigger creation, tester is able to add new email to email_t table. But Once I place trigger, they are getting this : <code> ...
Categories: DBA Blogs

system tablespace is full with audit files

Fri, 2018-08-31 14:26
<code></code>hi there, i have a problem here,my system tablespace is getting full these are current values for my system tablespace TABLESPACE_NAME FILE_NAME ALLOCATED_SIZE_IN_MB FREE_SIZE_IN_MB -------------...
Categories: DBA Blogs

Group by displaying location according to custom sort

Thu, 2018-08-30 20:06
i have a table id name location <code> 1 aaa home 1 aaa village 1 aaa office 2. bbb village 2. bbb office 3. ccc home 3. ccc office</code> when i write a query i should get single record for each having precendence home>office>villag...
Categories: DBA Blogs

Find Existing Oracle Password Encryption

Thu, 2018-08-30 20:06
We currently run Oracle 10g. We have WebDB applications. The Webpage where users can change their passwords to access the WebDB applications no longer works. The only access I have to modify or unlock user access is the Oracle Enterprise Managemen...
Categories: DBA Blogs

utl_http work with 11g and not with 12c

Thu, 2018-08-30 20:06
Hi Team Is there any reason why unsecured connection with basic authentication (http://user:password@server_address) is working with 11g and not with 12c (12.2.0.1.0)? I am using utl_http package to connect from database servers to the same external...
Categories: DBA Blogs

[Oracle Text] Score(1) and Contains

Thu, 2018-08-30 20:06
Hey AskTom, the documentation for Oracle Text is very poor so I have a question: Is it best to use the CONTAINS() clause to find a certain result, or should we use score(1) for this operator? I mean such a case where we are interested in score ...
Categories: DBA Blogs

Question with 2 partition conntain 6 datafiles

Thu, 2018-08-30 20:06
HI Tom ? ? I have a Database of more than 250 tables. I worked 3 Tablespaces (TB1, TB2, TB3). TB1: Used to store 5GB data tables TB2: Used to store basic key index indexes for general tables and has a size of 2 GB TB3: Used to store fields cont...
Categories: DBA Blogs

Need a query to identify the number of selects, inserts, updates and deletes from an application table

Thu, 2018-08-30 01:46
Hi, One of our customer wants to know how to get the DDL DML count happened on application tables with timestamp details before 60 or 90 days. Please provide some Query to get these details. They dont want to enable Auditing, is that possib...
Categories: DBA Blogs

expdp several tables in join query

Wed, 2018-08-29 07:26
I need to expdp the 5 tables data which satisfy the below query,I had read some documents on MOS and found that the doc 'Export/Import DataPump Parameter QUERY - How to Specify a Query (Doc ID 277010.1)',which show the use of QUERY parameter,but the ...
Categories: DBA Blogs

check the 2nd line of a file

Wed, 2018-08-29 07:26
Hello, I need to check if a file content is written on a single line or it is slit in multiple line. For example, I want to make difference between the following 2 structures: 123'456'789' vs. 123' 456' 789' My only idea is to check...
Categories: DBA Blogs

to_date(varchar2) compared to datetime

Wed, 2018-08-29 07:26
I have VARCHAR2 DateTime string. I want to convert it into DateTime and compare it with DateTime in the where clause. to_Date(VARCHAR2 field, 'MM/DD/YYYY HH:MI:SS AM')>= '09/10/2008 08:31:10 AM' If I just do SELECT to_Date(VARCHAR2 field, 'MM...
Categories: DBA Blogs

How to achieve Data Synchronization for oracle database between AIX and linux servers?

Wed, 2018-08-29 07:26
Hi Tom, We are having Oracle databases running on AIX servers and Planning to build parallel landscape for these databases on linux servers. The main objective for us is to keep data synchronized between these two platforms in real time. ...
Categories: DBA Blogs

Archive Table data and restore when needed

Tue, 2018-08-28 13:06
We have a table named DS_AUDIT_ENTRY where logs of audits are recorded. It contains many years data. Now we want to keep 6 months data only and archive old data. Also if required we have to fetch the old data whenever asked. How can I perform this a...
Categories: DBA Blogs

Loading PDFbox jars to oracle DB.

Tue, 2018-08-28 13:06
Hi, I am trying to add specific text to a pdf file using the apache pdfbox utility. I have loaded following jars on to DB using following command. loadjava -verbose -u user/pwd@ccoadev -jarasresource -genmissing -grant public -resolve pdfbox...
Categories: DBA Blogs

Pages